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

本記事は、2022年12月4日に公開済みの記事を移行して再掲載したものです。

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

はじめに

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

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

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

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

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

BigQuery上で発行されたクエリの発行結果は、宛先テーブルとして保存されますが、意図してクエリの設定を変更しない限り、宛先テーブルは「一時テーブル（Temporary table）」として24時間の有効期限付きで保持されます。

Temporary table をクリックすると、有効期限24時間のテーブルがランダムな文字列で作成されているのが分かります。

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

つまり、この一時テーブルに対して ORDER BY + LIMIT OFFSET のクエリを投げれば、 再集計なしの「低コスト」で「高速化」されたデータ が得られます。

• 低コスト：フィルタやグルーピング等で容量が圧縮されたデータ
• 高速化：再集計不要な集計処理済みデータの一部のみを都度取得

一時テーブル(Temporary table)へ直接クエリを発行

処理されるバイト数は、一時テーブルのサイズに依存 (512KB未満は 0 B 扱い？)

対象データや集計クエリ次第ではありますが、 数十秒かかったクエリの並べ替えが1秒未満 で得られる事、 処理されたバイト数が数十GB→数KBに圧縮 される事を確認できています。

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

BigQuery SQL ワークスペースでの動作は確認できましたが、これをWebアプリ上で実現するために、一時テーブルのパス( 上記の例 project.temp_datasetxxxxxxxxx.temp_tablexxxxxxxx 部分 )を取得する必要があります。
Pythonの場合は、下記のように Job の Destination 情報から取得できます。

from google.cloud import bigquery
from pprint import pprint

client = bigquery.Client(
    <適宜>
)
job = client.query(<初回集計クエリ>)

# 一時テーブルの情報を出力
pprint(job.destination.to_api_repr())Python

出力結果例

{'datasetId': 'xxtemp_dataset_id_xxxxxxxxxxxxx',
 'projectId': 'project_id',
 'tableId': 'xxtemp_table_id_xxxxxxxxxxxxxxxxx'}

この情報を元に <productId>.<datasetId>.<tableId> のテーブルに

SELECT column1, column2
  FROM `project_id.xxtemp_dataset_id_xxxxxxxxxxxxx.xxtemp_table_id_xxxxxxxxxxxxxxxxx`
 ORDER BY column1
 LIMIT 100
OFFSET 100

のようにクエリを発行すれば良いことになります。
ちなみに、オンデマンド課金の場合で、課金された合計バイト数を知りたい場合は

pprint(job.total_bytes_billed)

で取得できます。(pprintする必要はありませんが)

total_bytes_billed で None が返る時は、非同期処理が完了していない可能性があるので、
while not job.done(): なりで取得完了を待機する処理を挟んで、Jobの完了を待ちましょう。

利用上の注意

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

まとめ

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