BigQueryのコスト削減に取り組みました
こんにちは、ソフトウェアエンジニアの高橋です。
現在seawise社が提供しているデータプラットフォームビジネスの支援をしています。seawiseでは船舶の生産性向上のためにセンサーなどのデバイスを中心に、船に関わるさまざまなデータを集めて、ユーザー向けに船の状態を可視化したり、そこから得られた示唆に基づき意思決定を支援するようなアプリケーションを提供しています。データ基盤にはBigQueryを採用しています。この記事ではどのようにBigQueryのコストを調査し修正したかについて紹介します。
背景
全体アーキテクチャは上図のようになっており、今回は赤枠の箇所に注目します。
船にはセンサーが取り付けられておりそこから収集したデータ、いわゆる生データがデータプラットフォームに流れています。データは毎時間、1行あたり1分で区切られており、CSV形式となっています。今蓄積されているものがざっくり数千万行のオーダーです。
現時点でも相当のデータ量がありますが、今後のユーザー増加に伴いさらに増えていく予定です。また、現在は一定間隔のバッチ処理で行っていますが、なるべくリアルタイムに近づけたいという検討もしています。
そのためBigQueryのコストが莫大に増える可能性があり、今回どのようなクエリが実行されているのか調査しつつ、修正していくことになりました。
INFORMATION_SCHEMA.JOBSビューからコスト算出
BigQueryのコストはコンピューティング料金とストレージ料金の2つで構成されています。
今回はコンピューティング料金に着目します。基本的には1TBあたり6.25ドルの従量課金制となっています。
特にBigQueryのWebコンソール上でクエリを実行するときは、dry-runで料金の目安を知ることができます。これによって思いも寄らない高額なクエリの実行を防ぐのに役立ちます。またどれだけのデータがスキャンされるかはコストを減らすためにとても意識しておくべきポイントです。
具体的な料金はINFORMATION_SCHEMA.JOBSビューを実行すれば調べることができます。
またクエリの作成にこちらを参考にさせていただきました。
https://ex-ture.com/blog/2023/08/13/bigqueryで高額課金が発生しているクエリの呼び出し元
SELECT query,
user_email,
ROUND(SUM(total_bytes_billed) / POWER(1024, 4), 3) AS billed_tera_bytes,
count(*) as query_count
FROM `region`.INFORMATION_SCHEMA.JOBS
WHERE TRUE
AND cache_hit = FALSE
AND creation_time > '2024-02-01'
AND creation_time < '2024-03-01'
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 100
; 最も高価なクエリは
これで1番目に来たのが以下のようなクエリでした。
SELECT MAX(time_stamp) AS ts
FROM project_name.dataset_name.monitoring
WHERE ship_id = @shipId
AND time_stamp > @rangeStart
AND time_stamp < @rangeEnd具体的な料金は伏せるのですが、これがプロジェクトでのBigQueryコスト全体の35%を占めていました。これは倒しがいがありそうです（笑）やっていることはいわゆる最新のtime_stampを取得しています。
さてBigQueryでこのテーブルを見てみると、ざっくり5億行入っていました。担当者に聞いたところ、このテーブルはモニタリングデータを正規化して変換したテーブルということでした。
またtime_stampのDAYパーティションの設定がありました。BigQueryのパーティションは1つのテーブルに対して内部のデータを分割してもたせる仕組みで、WHERE句と組み合わせてスキャン量を減らすことができます。
JDSCでよくあるパターンが、機械学習モデルに食わせるための時系列データを日付単位などでパーティショニングしています。スキャン量も節約できますし、一定期間使わなくなったデータはパーティションごと別のところに移したり、勝手にストレージ料金が安くなるので非常に便利です。
ついでに小ネタ。実はBigQueryは何もしないままWHERE句で絞り込んでもスキャン量は変わりません。ついでにLIMIT句もスキャン量とは関係ありません。一方で列名の絞り込みはスキャン量が減ります。なぜなのか？
これはBigQueryのような列指向型データベース、OLAP系のデータベースの特徴もあります。まずデータを列単位で保存することで保存領域を節約しています。これは列の値はそもそも似たような値を取ることが多いことから、圧縮効率が良くなります。代償として列を部分的に取ってくることができずWHERE句の絞り込みでスキャン量が減らないと考えられます。もしもパーティショニングを行えば物理領域そもそもが分割されるため、結果的に行を絞り込みつつスキャン量が減るのです。とはいえやたらむやみにパーティショニングはできず、1テーブル1列、最大4000分割までの制限があります。
一般的なデータ構造に関してはオライリーの『データ指向アプリケーションデザイン』に詳しく書かれています。個人的に列指向は圧縮効率が良いというのを聞いたとき、体に電流が走りました（！？）
クエリを直してみる
さてクエリに戻ります。今回monitoringテーブルとは別のテーブルを参照することにしました。幸いdashboardというテーブルがあり、こちらは全体で200万行程度で、monitoringを集約したもので、それでいて今回の要件と同じ結果が取れることがわかりました。
ただし本当にmonitoringとdashboardの結果は一貫性があるのか？というのは議論が必要です。つまりmonitoringの最新をdashboardから取ってきてもよいのか？常に同じ結果が得られるのか？という疑問が出てきます。調査してみたところどちらも同じバッチ処理で更新しているので整合性が取れていると判断しました。
結果
元のクエリが648.81 MB、修正後のクエリが809.72 KBとなりました。ざっくり1000倍は安くなった計算です。将来的に船の数が増えると効いてくるところだとは思います。
さいごに
今回はBigQueryの扱い方を含めて紹介しました。特にデータ構造がどのようになっているのかという、低レイヤーの話は直接業務に関連する内容ではないことが多いです。しかし、普段アプリケーションやデータパイプラインの処理を作成していく中で深い知識を持っておくと、さまざまな選択肢が出てきた際に最適なものを選ぶことができるようになります。また、新しい技術を見た時に「内部はこんな仕組みになっているのだろう」と想像するのも、より楽しくなります。調べていく過程で知らなかったことが多く、非常に勉強になりました。
JDSCではseawiseプラットフォームをはじめ、さまざまな業界の課題にエンジニアリングとデータを組み合わせて解決に取り組んでいます。このような課題を私たちと一緒にチャレンジしていただける方、少しでも興味が湧いた方、ぜひご応募・カジュアル面談お待ちしています！
