BigQuery Studio (JupyteNotebook)でPythonの変数を埋め込んでSQLを実行する

tl;dr

Pythonのdict型で変数を書く。かならず値はint、str、floatにする。

params = {
  "start_time": "2023-12-25T00:00:00+09:00",
  "end_time": "2023-12-26T00:00:00+09:00",
}

%%bigquery results --params $paramsを付けて実行する。

%%bigquery results --project hoge-project --params $params
SELECT
  COUNT(*) count,
  MAX(T.ts) max,
  MIN(T.ts) min,
FROM `hoge-project.hoge_dataset.hoge_table` T
WHERE T.ts BETWEEN @start_time AND @end_time

仕様の解説

BigQuery Studioは、BigQueryのWebコンソール上でJupyterNotebookを実行する機能。%%bigqueryと書いたセル作るとクエリを実行できる。

これはJupyter NotebookのCell Magicという機能で実現されている。このCell Magicには引数が与えられ、その場合にPythonの変数を参照するには $params と記述する。%%bigqueryがBigQueryのCell Magicの呼び出しである。

BigQueryのクエリには、query_parametersという仕様（QueryJobConfig参照）があり、クエリ中に@start_date などと書くと埋め込むことができる。BigQueryのCell Magicでは--params $paramsでdict型の変数を与えるとこのクエリパラメータに埋め込むことができる。

他に便利なノウハウ

--destination_table hoge_dataset.hoge_table を付けると、格納先テーブルを指定できる。

クエリ結果をdataframeとしての取得する必要がない場合も、常に全件取得してしまい時間がかかる仕様になっている。その場合、--max_results 1とかしておくと、1件しか取得しないので早い。

参考資料

BigQueryのQueryJobConfigのドキュメント

IPython Magic BigQueryのドキュメント