📈

BigQueryの一時テーブルで低コスト・高速化を図る

Qiita - フェズ Advent Calendar 2022 4日目の記事です。

はじめに

3日目に引き続き、バックエンド開発担当の中川からお届けします。
そういえば今年、ハンター試験(狩猟免許:罠猟)に合格しました。
それはさておき、今回はイノシシから離れ、業務で使えるかもしれないデータ分析周りの知見を共有できればと考えています。

BigQueryの一時テーブルを有効利用する

高コスト・低速化を招くBigQueryの使用例

バックエンドでBigQueryに接続されており、複雑な集計クエリを実行後に、その結果をページネーション付きでテーブル出力するWebアプリケーションがあるとします。
初回実行時は、いくらか時間を要する事を許容するとして、集計結果の各列要素において並べ替えを行いたいと考えた場合、ORDER BY を追加して再度BigQueryに集計クエリを投げることになるかと思います。
しかし、集計対象が頻繁に更新される類のデータではない場合、並び替えのたびに再集計で待たされるのではなく、集計済みの結果を並べ替えて取得した方が、集計コストが圧倒的に少なく、高速化も見込めます。
この「並べ替えの時は、再集計しない」を実現できれば、低コストかつ高速化を実現できることになります。

集計結果は、一時テーブルとして保存されている

BigQuery上で発行されたクエリの発行結果は、宛先テーブルとして保存されますが、意図してクエリの設定を変更しない限り、宛先テーブルは「一時テーブル(Temporary table)」として24時間の有効期限付きで保持されます。
Temporary table をクリックすると、有効期限24時間のテーブルがランダムな文字列で作成されているのが分かります。

一時テーブルに直接クエリを投げる

つまり、この一時テーブルに対して ORDER BY + LIMIT OFFSET のクエリを投げれば、再集計なしの「低コスト」で「高速化」されたデータが得られます。
  • 低コスト:フィルタやグルーピング等で容量が圧縮されたデータ
  • 高速化:再集計不要な集計処理済みデータの一部のみを都度取得
 一時テーブル(Temporary table)へ直接クエリを発行
 一時テーブル(Temporary table)へ直接クエリを発行
  処理されるバイト数は、一時テーブルのサイズに依存 (512KB未満は 0 B 扱い?)
  処理されるバイト数は、一時テーブルのサイズに依存 (512KB未満は 0 B 扱い?)
対象データや集計クエリ次第ではありますが、数十秒かかったクエリの並べ替えが1秒未満で得られる事、処理されたバイト数が数十GB→数KBに圧縮される事を確認できています。

Pythonで一時テーブルの情報を得る

BigQuery SQL ワークスペースでの動作は確認できましたが、これをWebアプリ上で実現するために、一時テーブルのパス( 上記の例 部分 )を取得する必要があります。 Pythonの場合は、下記のように Job の Destination 情報から取得できます。
出力結果例
この情報を元に のテーブルに
のようにクエリを発行すれば良いことになります。
ちなみに、オンデマンド課金の場合で、課金された合計バイト数を知りたい場合は
で取得できます。(pprintする必要はありませんが)
total_bytes_billed で None が返る時は、非同期処理が完了していない可能性があるので、 なりで取得完了を待機する処理を挟んで、Jobの完了を待ちましょう。

利用上の注意

リソースの無駄遣いが無くなるので、非常にリーズナブルな手法ではなかろうかと考えていますが、1点、注意事項があります。 提供されている仕組みであるとはいえ、公式にアナウンスされている手法ではないため非推奨であり、BigQueryの開発ロードマップ次第では「ある日突然使えなくなる可能性もある」という事は留意しておく必要があり、プロダクションで利用するかどうかは、慎重に検討する必要があるかと思います。
そもそも「24時間という有効期限の妥当性」や、「宛先テーブルは一時テーブルでなくとも明示的に作成が可能」なので、マテリアライズドビュー等も含めた「用途に応じた検討」をした方が良いという点は、Google社の技術サポートチームからもアドバイスを頂いています。

まとめ

BigQueryが標準で提供している一時テーブルを利用して、無駄な再集計を回避する方法をご紹介しました。
意図的に作成せずとも利用できる(自動で作ってくれる)点において便利ですが、キャッシュテーブルを明示的に作成する事も当然可能ですので、「そういう使い方もあるんだな」というのを頭の片隅におきながら、状況に応じて使い分けるのが良いかと思います。