BigQuery でプロジェクト毎に少しだけ違う SQL を実行する方法

Google Cloud BigQuery

備忘録です。

目的

Google Cloud のプロジェクトを環境毎に作成していて、BigQuery の設定名がプロジェクト単位で若干違う、という事があると思います。今回の下記例では、プロジェクト毎にデータセット名が異なっていて、それ以外は全く同一の SQL を実行したい、という場合の対応例です。

SQL

DECLARE project_id STRING;
DECLARE dataset_id STRING;

-- INFORMATION_SCHEMA.SCHEMATA
-- https://cloud.google.com/bigquery/docs/information-schema-datasets#schemata_view
SET project_id = (SELECT DISTINCT(catalog_name) FROM region-us-central1.INFORMATION_SCHEMA.SCHEMATA);

-- IF
-- https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#if
IF project_id = "project-pro" THEN SET dataset_id = "pro";
  ELSEIF project_id = "project-dev" THEN SET dataset_id = "dev";
END IF;

-- EXECUTE IMMEDIATE
-- https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#execute_immediate
-- CONCAT
-- https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#concat
EXECUTE IMMEDIATE CONCAT(
  "SELECT * FROM `" || dataset_id || ".data`;"
);

解説

クエリを発行したプロジェクト名を INFORMATION_SCHEMA より取得して、プロジェクト名の値よりデータセット名を設定、 EXECUTE IMMEDIATE 式と CONCAT 関数を利用して、 FROM 句を強引に設定しています。

EXECUTE IMMEDIATE 式は、文字列や変数を利用して動的な SQL を生成できる機能ですが、BigQuery では FROM 句にクエリパラメーターを指定できないという制限があります。そのため、以下のような素直な EXECUTE IMMEDIATE の SQL は怒られてしまいます。

EXECUTE IMMEDIATE
  """
  SELECT * FROM ?.data;
  """
USING dataset_id;

Invalid EXECUTE IMMEDIATE sql string SELECT * FROM ?.data;, Query parameters cannot be used in place of table names at [15:3]

その点を CONCAT 関数で文字列連結して、強引に実行させています。