TimescaleDBに1兆件の気象データをロード

 (aliramadhan.me)
3 ポイント 投稿者 GN⁺ 2024-04-17 | 2件のコメント | WhatsAppで共有

気象データウェアハウスを構築する パート1: TimescaleDBに1兆行の気象データをロードする

私たちがやっていることの意味

気象データウェアハウスを構築する理由

  • 気候変動の兆候を分析するために、世界中の過去の気象データを集めて分析するのがよいと考えた
  • 大規模な気象データウェアハウスがあれば、ジャカルタが実際により暖かくなったのか、嵐が激しくなったのか、チリが全体的に暑くなったのか、あるいは雲が多くなったのかなどを地域別に把握できる
  • これにより、地球上のどの地域が最も大きな気候変動を経験したのか、どのような種類の変化があったのかを把握できる
  • 世界規模でこうした分析を行うには、データウェアハウスのクエリ速度を高める必要があり、データ量も非常に多い
  • 最初のステップはデータをPostgreSQLにロードすること。TimescaleDBで時系列クエリを高速化し、PostGISで地理空間クエリを高速化するのが有望に見える

データ紹介

  • 実際の観測データではなく、ERA5気候再解析(climate reanalysis)プロダクトのデータを使用
  • ERA5は、観測データによって制約された気候モデルの実行結果であり、観測が多い場所では観測に近く、観測のない場所では物理的に一貫しており、気候統計とも整合する
  • ERA5は1940年から全球を0.25度の解像度で1時間ごとのデータとして提供。気温、降水量、雲量、風速など、変数ごとに7億5千万行以上のデータがある
  • このデータをリレーショナルDBに高速で挿入するのは簡単ではない

データ挿入方法

単一行のinsert文

  • 最も単純な方法だが非常に遅い。毎秒3000件の挿入では、全データのロードに約8年かかる
  • 構文解析、テーブル/カラム検証、実行計画、テーブルロック、バッファ書き込み、ディスク書き込み、コミットなどのオーバーヘッドが大きい

複数値insert

  • 1つのinsert文で複数行を挿入。ネットワーク、構文解析、実行計画のオーバーヘッドを削減
  • psycopg3が毎秒25,000〜30,000件で最も高速
  • それでも全データのロードには約10か月かかる

copy文

  • 大量データロードに最適化された方法。CSVやバイナリファイルから直接読み込むことで、構文解析、計画、WAL利用を最適化
  • すでにCSVがあるなら、単純にcopy文を使える
  • psycopg3のcopyは毎秒10万件以上の挿入が可能。オーバーヘッドを含めても、3か月以内に全データをロード可能
  • copyで長時間高速挿入を行う場合は、ボトルネックに注意が必要

並列copy

  • 複数のcopy作業を並列に実行して高速化
  • 単一テーブルへの挿入は並列化の効果が大きくなく、16 workerを超えても性能改善はない

外部ツールの利用

  • pg_bulkloadとtimescaledb-parellel-copyをベンチマーク
  • pg_bulkloadは高速だが、デフォルトでWALをスキップするため安全ではない
  • timescaledb-parallel-copyは複数workerで毎秒30万件以上を安全に挿入できる

PostgreSQL設定の調整

  • fsyncとfull_page_writesを無効にすると、ディスク書き込みを避けてさらに高速化できるが危険
  • unloggedテーブルもWALを使わないため高速だが、クラッシュ時に切り詰められる。hypertableはunloggedにできない

最良の方法は？

  • psycopg3でhypertableへ直接copyするのが最適。CSVファイルならtimescaledb-parallel-copyを使う
  • 並列化は12〜16 workerが適切
  • 制約を外せば毎秒46万件まで可能だが危険
  • ハードウェアをアップグレードすればさらに高速化できる
  • ClickHouseのほうが速いかもしれないが、PostgreSQLを学びたかったのでTimescaleDBを選択
  • 毎秒46万件なら、20日以内に全データをロード可能

GN⁺の意見

  • ERA5データをリレーショナルDBに入れて分析しようとする試みは興味深いですね。従来はxarrayやdaskでNetCDFデータを直接分析するのが一般的でしたが、データウェアハウスを構築すれば、より複雑なクエリを実行できそうです。
  • 著者のハードウェアスペックが5年前のものなのに、毎秒46万件を投入できる点は印象的です。最新のハードウェアなら毎秒100万件も可能でしょう。ただし、fsyncとfull_page_writesを無効にするのはDBの整合性を損なう可能性があるため注意が必要です。
  • PostgreSQLの並列処理機能は、単一テーブルにはあまり大きな助けにならないようです。並列処理とパーティショニングを組み合わせれば、さらに高い性能を出せるでしょう。CitusのようなPostgresの水平スケーリングソリューションも検討に値します。
  • ERA5データが気候変動分析に活用できる点は興味深いです。観測データが不足している地域の過去気候を分析できそうです。ただし、ERA5はあくまでモデルの出力結果であるという限界があります。観測データで補正されてはいますが、不確実性がある点は考慮すべきでしょう。
  • 分析プラットフォームとしてはSnowflakeやBigQueryのようなクラウドデータウェアハウスを使うのが一般的です。しかし、著者のように自前のハードウェアを扱いながら学ぶことにも大きな意味があります。特に気候データは容量が大きいため、クラウドへ移すのは簡単ではありません。今後の実際の分析結果に期待したいです。

関連記事

2件のコメント

 
jangsc0000 2024-04-18

GN+のコメントが丁寧語ですね..?
 
GN⁺ 2024-04-17
Hacker Newsの意見

要約すると次のとおり。

  • 地理空間データを分析する際は、座標参照系（CRS）と地図投影法を理解することが重要。大規模な地理空間処理では Google BigQuery が最も優れている。

  • リレーショナルDBが格子状の気象データに適しているかは、実験を通じて確認する必要がある。

  • Timescale で Hypertable が遅い理由は、デフォルトで作成される timestamp カラムのインデックスかもしれない。create_default_indexes=>false オプションでインデックス作成をスキップするか、データ投入後にインデックスを作るのがよい。

  • 気象データを RDBMS に移すことにどんな利点があるのかについての分析が不足している。Serverless + オブジェクトストレージでも非常に高速な応答速度を得られる。

  • ERA5 のような大半の気象・気候データセットは、規則的な緯度経度グリッドで構成されているため、構造を完全に壊すのは望ましくない。ARCO-ERA5 のようなクラウド最適化版を活用する方がよい。

  • PostgreSQL で WAL を無効にし、VACUUM FREEZE コマンドを定期的に実行すれば、大量データのロード時の性能をさらに高められる。

  • COPY を使えないなら、行を JSON 文字列としてエンコードして単一のクエリパラメータで送り、json_to_recordset を使うのもよい方法。