マルチステートメント クエリを使用する

このドキュメントでは、BigQuery で複数ステートメント クエリを使用する方法について説明します。たとえば、複数ステートメント クエリの作成方法、複数ステートメント クエリでの一時テーブルの使用方法、複数ステートメント クエリでの変数の参照方法、複数ステートメントのクエリをデバッグ方法などです。

マルチステートメント クエリは、1 つのリクエストで実行できる SQL ステートメントの集合です。マルチステートメント クエリでは、共有状態で複数のステートメントを順番に実行できます。マルチステートメント クエリには、テーブルデータの追加や変更などの副作用が生じることがあります。

複数ステートメント クエリはストアド プロシージャで頻繁に使用され、プロシージャ言語ステートメントをサポートします。これにより、変数の定義、制御フローの実装などを行うことができます。

複数ステートメント クエリを作成、実行、保存する

マルチステートメント クエリは、セミコロンで区切られた 1 つ以上の SQL ステートメントで構成されます。マルチステートメント クエリでは、有効な SQL ステートメントを使用できます。マルチステートメント クエリには、プロシージャル言語ステートメントを含めることもできます。これにより、変数を使用したり、SQL ステートメントで制御フローを実装したりできます。

複数ステートメントのクエリを作成する

BigQuery ではマルチステートメント クエリを記述できます。次のマルチクエリ ステートメントのクエリは、変数を宣言し、その変数を IF ステートメント内で使用します。

DECLARE day INT64;
SET day = (SELECT EXTRACT(DAYOFWEEK from CURRENT_DATE));
if day = 1 or day = 7 THEN
  SELECT 'Weekend';
ELSE
  SELECT 'Weekday';
END IF

BigQuery では、CREATE TEMP FUNCTION ステートメントの後に 1 つの SELECT ステートメントでステートメント全体が構成されている場合を除いて、複数のステートメントを含むリクエストが解釈されます。たとえば、以下の対象は複数ステートメント クエリとはみなされません。

CREATE TEMP FUNCTION Add(x INT64, y INT64) AS (x + y);
SELECT Add(3, 4);

複数ステートメント クエリを実行する

複数ステートメント クエリは、他のクエリと同じ方法で（たとえば、Google Cloud Console やコマンドライン ツールを使用して）実行できます。

マルチステートメント クエリのドライラン

マルチステートメント クエリで読み取られるバイト数を見積もるには、ドライランを検討してください。マルチステートメント クエリのドライランは、SELECT ステートメントのみを含むクエリで最も正確です。

ドライランでは、次のクエリとステートメントのタイプに対して特別な処理が行われます。

  • CALL ステートメント: ドライランは、呼び出されたプロシージャが存在し、指定された引数に一致するシグネチャを持つことを検証します。呼び出されたプロシージャの内容と、CALL ステートメントより後のステートメントはすべて検証されません。
  • DDL ステートメント: ドライランは最初の DDL ステートメントを検証してから、停止します。後続のステートメントはすべてスキップされます。CREATE TEMP TABLE ステートメントのドライランはサポートされていません。
  • DML ステートメント: ドライランは DML ステートメントを検証してから、後続のステートメントを引き続き検証します。この場合、バイト数の見積もりは元のテーブルサイズに基づいており、DML ステートメントの結果は考慮されません。
  • EXECUTE IMMEDIATE ステートメント: ドライランはクエリ式を検証しますが、動的クエリ自体は評価しません。EXECUTE IMMEDIATE ステートメントに続くすべてのステートメントはスキップされます。
  • パーティション フィルタで変数を使用するクエリ: ドライランは、最初のクエリと後続のステートメントを検証します。ただし、ドライランでは、パーティション フィルタ内の変数のランタイム値を計算できません。これは読み取りバイト数の見積もりに影響します。
  • FOR SYSTEM TIME AS OF 句のタイムスタンプ式で変数を使用するクエリ: ドライランはテーブルの現在の内容を使用し、FOR SYSTEM TIME AS OF 句を無視します。これは、現在のテーブルとテーブルの以前の反復処理との間にサイズの違いがある場合に、読み取りバイト数の見積もりに影響します。
  • FOR、IF、WHILE 制御ステートメント: ドライランはすぐに停止します。条件式、制御ステートメントの本文、すべての後続のステートメントは検証されません。

ドライランはベスト エフォート ベースで実施されるため、基盤となるプロセスは変更される可能性があります。ドライランでは、次の条件が適用されます。

  • ドライランを正常に完了したクエリは、正常に実行されない可能性があります。たとえば、ドライランでは検出されない理由で、実行時にクエリが失敗することがあります。
  • クエリが正常に実行されたとしても、ドライランが正常に完了しないことがあります。たとえば、実行時にキャッチされた理由でドライランが失敗することがあります。
  • 現在正常に実行されたドライランが、将来必ず実行されるとは限りません。たとえば、ドライランの実装に変更を加えると、以前は検出されなかったクエリのエラーが検出される場合があります。

複数ステートメント クエリを保存する

マルチステートメント クエリを保存するには、保存したクエリの操作をご覧ください。

複数ステートメント クエリで変数を使用する

複数ステートメント クエリには、ユーザー作成変数とシステム変数を含めることができます。

  • ユーザー作成変数を宣言し、変数に値を割り当てて、クエリ全体で参照できます。

  • クエリ内でシステム変数を参照し、その一部に値を割り当てることができますが、ユーザー定義変数とは異なり、宣言はしません。システム変数は BigQuery に組み込まれています。

ユーザー作成変数を宣言する

複数ステートメントのクエリの開始時、または BEGIN ブロックの先頭で、ユーザー作成変数を宣言する必要があります。マルチステートメント クエリの開始時に宣言された変数は、クエリ全体のスコープ内にあります。BEGIN ブロック内で宣言された変数には、そのブロックのスコープがあります。対応する END ステートメントの後に、スコープの範囲外になります。変数の最大サイズは 1 MB で、マルチステートメント クエリで使用されるすべての変数の最大サイズは 10 MB です。

次のように、DECLARE プロシージャル ステートメントを使用して変数を宣言できます。

DECLARE x INT64;

BEGIN
DECLARE y INT64;
-- Here you can reference x and y
END;

-- Here you can reference x, but not y

ユーザー作成変数を設定する

ユーザー作成変数を宣言した後、次のように SET プロシージャル ステートメントを使用して値を割り当てることができます。

DECLARE x INT64 DEFAULT 0;
SET x = 10;

システム変数を設定する

システム変数は作成しませんが、一部の変数は以下のようにデフォルト値をオーバーライドできます。

SET @@dataset_project_id = 'MyProject';

また、複数ステートメント クエリで、システム変数を設定して暗黙的に使用できます。たとえば、次のクエリでは、新しいテーブルを作成するたびにプロジェクトを指定する必要があります。

BEGIN
  CREATE TABLE MyProject.MyDataset.MyTempTableA (id STRING);
  CREATE TABLE MyProject.MyDataset.MyTempTableB (id STRING);
END;

プロジェクトをテーブルパスに複数回追加しない場合は、マルチステートメント クエリの @@dataset_project_id システム変数にデータセットのプロジェクト ID MyProject を割り当てることができます。この割り当てにより、MyProject が残りのクエリのデフォルト プロジェクトになります。

SET @@dataset_project_id = 'MyProject';

BEGIN
  CREATE TABLE MyDataset.MyTempTableA (id STRING);
  CREATE TABLE MyDataset.MyTempTableB (id STRING);
END;

同様に、@@dataset_id システム変数を設定して、クエリのデフォルトのデータセットを割り当てることができます。例:

SET @@dataset_project_id = 'MyProject';
SET @@dataset_id = 'MyDataset';

BEGIN
  CREATE TABLE MyTempTableA (id STRING);
  CREATE TABLE MyTempTableB (id STRING);
END;

また、マルチステートメント クエリのさまざまな部分で、@@dataset_id などのシステム変数を明示的に参照することもできます。詳しくは、システム変数の参照をご覧ください。

ユーザー作成変数を参照する

ユーザー作成変数を宣言して設定したら、複数ステートメント クエリで参照できます。変数と列が同じ名前を共有している場合は、列が優先されます。

これは column x + column x を返します。

DECLARE x INT64 DEFAULT 0;
SET x = 10;

WITH Numbers AS (SELECT 50 AS x)
SELECT (x+x) AS result FROM Numbers;

+--------+
| result |
+--------+
| 100    |
+--------+

これは column y + variable x を返します:

DECLARE x INT64 DEFAULT 0;
SET x = 10;

WITH Numbers AS (SELECT 50 AS y)
SELECT (y+x) AS result FROM Numbers;

+--------+
| result |
+--------+
| 60     |
+--------+

システム変数を参照する

複数ステートメント クエリでシステム変数を参照できます。

次のクエリは、デフォルトのタイムゾーンを返します。

BEGIN
  SELECT @@time_zone AS default_time_zone;
END;

+-------------------+
| default_time_zone |
+-------------------+
| UTC               |
+-------------------+

システム変数は DDL クエリと DML クエリで使用できます。 たとえば、テーブルの作成時と更新時にシステム変数 @@time_zone を使用する方法は次のとおりです。

BEGIN
  CREATE TEMP TABLE MyTempTable
  AS SELECT @@time_zone AS default_time_zone;
END;

BEGIN
  CREATE OR REPLACE TABLE MyDataset.MyTable(default_time_zone STRING)
  OPTIONS (description = @@time_zone);
END;

BEGIN
  UPDATE MyDataset.MyTable
  SET default_time_zone = @@time_zone
  WHERE TRUE;
END;

DDL および DML クエリでシステム変数を使用できない場合があります。たとえば、システム変数をプロジェクト名、データセット、テーブル名として使用することはできません。テーブルパスに @@dataset_id システム変数を含めようとすると、エラーが発生します。

BEGIN
  CREATE TEMP TABLE @@dataset_id.MyTempTable (id STRING);
END;

マルチステートメント クエリで一時テーブルを使用する

一時テーブルを使用すると、中間結果をテーブルに保存できます。一時テーブルは BigQuery によって管理されるため、データセット内で保存または維持する必要はありません。一時テーブルのストレージに対して料金が発生します。

マルチステートメント クエリで一時テーブルを作成して参照できます。一時テーブルが不要になった場合は、手動で削除してストレージ費用を最小限に抑えることができます。または、BigQuery が 24 時間後に削除するのを待ちます。

一時テーブルを作成する

CREATE TABLE ステートメントを使用すると、複数ステートメント クエリの一時テーブルを作成できます。 次の例では、クエリの結果を保存する一時テーブルを作成し、サブクエリで一時テーブルを使用します。

-- Find the top 100 names from the year 2017.
CREATE TEMP TABLE top_names(name STRING)
AS
 SELECT name
 FROM `bigquery-public-data`.usa_names.usa_1910_current
 WHERE year = 2017
 ORDER BY number DESC LIMIT 100
;
-- Which names appear as words in Shakespeare's plays?
SELECT
 name AS shakespeare_name
FROM top_names
WHERE name IN (
 SELECT word
 FROM `bigquery-public-data`.samples.shakespeare
);

TEMP、TEMPORARY キーワード以外は、CREATE TABLE の構文と同じです。

一時テーブルを作成するときは、テーブル名にプロジェクトまたはデータセット修飾子を使用しないでください。テーブルは、特別なデータセットに自動的に作成されます。

一時テーブルを参照する

現在のマルチステートメント クエリの間、一時テーブルは名前で参照できます。これには、マルチステートメント クエリ内のプロシージャによって作成された一時テーブルも含まれます。一時テーブルを共有することはできません。一時テーブルは、ランダムに生成された名前の非表示の _script% データセット内に存在します。非表示のデータセットを一覧表示する方法については、データセットの一覧表示をご覧ください。

一時テーブルを削除する

複数ステートメント クエリが完了する前に一時テーブルを完全に削除するには、DROP TABLE ステートメントを使用します。

CREATE TEMP TABLE table1(x INT64);
SELECT * FROM table1;  -- Succeeds
DROP TABLE table1;
SELECT * FROM table1;  -- Results in an error

複数ステートメント クエリが終了した後、一時テーブルは最長で 24 時間存在します。

一時テーブルのデータを表示する

一時テーブルを作成したら、テーブルの構造とテーブル内のデータを表示できます。テーブルの構造とデータを表示する手順は次のとおりです。

  1. Google Cloud コンソールで [エクスプローラ] ページに移動します。

    エクスプローラに移動

  2. [クエリ履歴] をクリックします。

  3. 一時テーブルを作成したクエリを選択します。

  4. [宛先テーブル] 行の [一時テーブル] をクリックします。

_SESSION を使用して一時テーブルを修飾する

デフォルト データセットとともに一時テーブルを使用する場合、非修飾テーブル名は一時テーブル（存在する場合）またはデフォルト データセットのテーブルを参照します。例外は CREATE TABLE ステートメントです。TEMP キーワードや TEMPORARY キーワードは存在する場合のみ、ターゲット テーブルが一時テーブルと見なされます。

たとえば、次のマルチステートメント クエリについて考えてみます。

-- Create table t1 in the default dataset
CREATE TABLE t1 (x INT64);

-- Create temporary table t1.
CREATE TEMP TABLE t1 (x INT64);

-- This statement selects from the temporary table.
SELECT * FROM t1;

-- Drop the temporary table
DROP TABLE t1;

-- Now that the temporary table is dropped, this statement selects from the
-- table in the default dataset.
SELECT * FROM t1;

一時テーブルを参照していることを明示的に示すには、テーブル名に _SESSION 修飾子を指定します。

-- Create a temp table
CREATE TEMP TABLE t1 (x INT64);

-- Create a temp table using the `_SESSION` qualifier
CREATE TEMP TABLE _SESSION.t2 (x INT64);

-- Select from a temporary table using the `_SESSION` qualifier
SELECT * FROM _SESSION.t1;

存在しない一時テーブルのクエリに _SESSION 修飾子を使用すると、複数ステートメント クエリは、テーブルが存在しないことを示すエラーをスローします。たとえば、t3 という一時テーブルが存在しない場合、デフォルト データセットに t3 というテーブルが存在する場合でも、マルチステートメント クエリはエラーをスローします。

一時テーブルでないテーブルを作成するために _SESSION を使用することはできません。

CREATE TABLE _SESSION.t4 (x INT64);  -- Fails

複数ステートメントのクエリジョブに関する情報を収集する

複数ステートメント クエリジョブには、実行された複数ステートメント クエリに関する情報が含まれます。ジョブデータに対して実行できる一般的なタスクとして、複数ステートメント クエリで最後に実行されたステートメントを返すもの、または複数ステートメント クエリで実行されたすべてのステートメントを返すものがあります。

最後に実行されたステートメントを返す

jobs.getQueryResults メソッドは、複数ステートメント クエリで実行された最後のステートメントのクエリ結果を返します。ステートメントが実行されなかった場合、結果は返されません。

実行したすべてのステートメントを返す

複数ステートメント クエリ内のすべてのステートメントの結果を取得するには、子ジョブを列挙し、それぞれに対して jobs.getQueryResults を呼び出します。

子ジョブを列挙する

他のクエリと同様に、jobs.insert を使用して BigQuery でマルチステートメント クエリが実行され、マルチステートメント クエリはクエリテキストとして指定されます。複数ステートメント クエリを実行すると、子ジョブと呼ばれる追加のジョブが複数ステートメント クエリのステートメントごとに作成されます。複数ステートメント クエリの子ジョブを列挙するには、jobs.list を呼び出し、複数ステートメント クエリジョブ ID を parentJobId パラメータとして渡します。

複数ステートメント クエリをデバッグする

複数ステートメント クエリのデバッグのヒントをいくつか紹介します。

  • ブール値の条件が true であることのアサーションを行うには、ASSERT ステートメントを使用します。

  • BEGIN...EXCEPTION...END を使用してエラーを検出し、エラー メッセージとスタック トレースを表示します。

  • 中間結果を表示するには、SELECT FORMAT("....") を使用します。

  • Google Cloud コンソールで複数ステートメント クエリを実行すると、複数ステートメント クエリ内の各ステートメントの出力を表示できます。bq コマンドライン ツールの bq query コマンドを実行すると、マルチステートメント クエリの実行時に各ステップの結果も表示されます。

  • Google Cloud Console では、Query Editor 内の個々のステートメントを選択して実行できます。

権限

テーブル、モデル、その他のリソースへのアクセス権は、実行時にチェックされます。ステートメントが実行されない場合、または式が評価されない場合、BigQuery は、複数ステートメント クエリを実行するユーザーが参照しているリソースにアクセスできるかどうかをチェックしません。

マルチステートメント クエリでは、各式またはステートメントの権限は個別に検証されます。例:

SELECT * FROM dataset_with_access.table1;
SELECT * FROM dataset_without_access.table2;

クエリを実行するユーザーに table1 へのアクセス権はあっても table2 へのアクセス権がない場合、最初のクエリは成功し、2 番目のクエリは失敗します。マルチステートメント クエリジョブ自体も失敗します。

セキュリティの制約

複数ステートメント クエリでは、動的 SQL を使用して、実行時に SQL ステートメントをビルドできます。これは簡便な方法ですが、不正使用の可能性が高まります。たとえば、次のクエリを実行すると、テーブル パラメータが不適切にフィルタリングされ、意図しないテーブルにアクセスして実行される可能性があるため、SQL インジェクションに対してセキュリティ上の脅威となりえます。

-- Risky query vulnerable to SQL injection attack.
EXECUTE IMMEDIATE CONCAT('SELECT * FROM SensitiveTable WHERE id = ', @id);

テーブル内の機密データの公開や漏洩、または DROP TABLE などのコマンドを実行してテーブル内のデータを削除することを回避するために、BigQuery の動的手続き型ステートメントは、SQL インジェクション攻撃への露出を低減するための次のような複数のセキュリティ対策をサポートしています。

  • EXECUTE IMMEDIATE ステートメントでは、クエリ パラメータと変数で展開されたクエリに複数の SQL ステートメントを埋め込めません。
  • 次のコマンドは動的に実行されません。 BEGIN/END、CALL、CASE、IF、LOOP、WHILE、EXECUTE IMMEDIATE

構成フィールドの制限

次のジョブ構成クエリ フィールドは、マルチステートメント クエリに設定できません。

  • clustering
  • create_disposition
  • destination_table
  • destination_encryption_configuration
  • range_partitioning
  • schema_update_options
  • time_partitioning
  • user_defined_function_resources
  • write_disposition

料金

マルチステートメント クエリの料金には、クエリ（オンデマンド課金モデルを使用している場合）と一時テーブルのストレージの料金が含まれます。予約を使用している場合、クエリの使用量は予約料金の対象になります。

オンデマンド クエリのサイズの計算

オンデマンド課金を使用する場合、BigQuery はマルチステートメント クエリの実行中に処理されたバイト数に基づいてマルチステートメント クエリに対して課金します。

マルチステートメント クエリで処理されるバイト数を見積もるには、ドライランを実行します。

複数ステートメントのクエリには次の料金が適用されます。

  • DECLARE: DEFAULT 式で参照されるテーブルに対してスキャンされたバイトの合計。テーブルを参照していない DECLARE ステートメントに費用は発生しません。

  • SET: 式で参照されるテーブルに対してスキャンされたバイトの合計。テーブルを参照していない SET ステートメントに費用は発生しません。

  • IF: 条件式で参照されるテーブルに対してスキャンされたバイトの合計。テーブル参照のない IF 条件式には費用は発生しません。IF ブロック内のステートメントが実行されていない場合、費用は発生しません。

  • WHILE: 条件式で参照されるテーブルに対してスキャンされたバイトの合計。条件式にテーブルを参照していない WHILE ステートメントで費用は発生しません。WHILE ブロック内のステートメントが実行されていない場合、費用は発生しません。

  • CONTINUE または ITERATE: 関連費用はありません。

  • BREAK または LEAVE: 関連費用はありません。

  • BEGIN または END: 関連費用はありません。

複数ステートメント クエリが失敗した場合、失敗までのステートメントの費用は引き続き適用されます。失敗したステートメントには課金されません。

たとえば、次のサンプルコードには、各ステートメントの前に、各ステートメントで発生する費用（該当する場合）を説明するコメントが含まれています。

-- No cost, since no tables are referenced.
DECLARE x DATE DEFAULT CURRENT_DATE();
-- Incurs the cost of scanning string_col from dataset.table.
DECLARE y STRING DEFAULT (SELECT MAX(string_col) FROM dataset.table);
-- Incurs the cost of copying the data from dataset.big_table.  Once the
-- table is created, you are not charged for storage while the rest of the
-- multi-statement query runs.
CREATE TEMP TABLE t AS SELECT * FROM dataset.big_table;
-- Incurs the cost of scanning column1 from temporary table t.
SELECT column1 FROM t;
-- No cost, since y = 'foo' doesn't reference a table.
IF y = 'foo' THEN
  -- Incurs the cost of scanning all columns from dataset.other_table, if
  -- y was equal to 'foo', or otherwise no cost since it is not executed.
  SELECT * FROM dataset.other_table;
ELSE
  -- Incurs the cost of scanning all columns from dataset.different_table, if
  -- y was not equal to 'foo', or otherwise no cost since it is not executed.
  UPDATE dataset.different_table
  SET col = 10
  WHERE true;
END IF;
-- Incurs the cost of scanning date_col from dataset.table for each
-- iteration of the loop.
WHILE x < (SELECT MIN(date_col) FROM dataset.table) DO
  -- No cost, since the expression does not reference any tables.
  SET x = DATE_ADD(x, INTERVAL 1 DAY);
  -- No cost, since the expression does not reference any tables.
  IF true THEN
    -- LEAVE has no associated cost.
    LEAVE;
  END IF;
  -- Never executed, since the IF branch is always taken, so does not incur
  -- a cost.
  SELECT * FROM dataset.big_table;
END WHILE;

詳細については、クエリサイズの計算をご覧ください。

ストレージの料金

マルチステートメント クエリによって作成された一時テーブルに対して料金が発生します。TABLE_STORAGE ビューまたは TABLE_STORAGE_USAGE_TIMELINE ビューを使用して、これらの一時テーブルで使用されているストレージを確認できます。一時テーブルは、ランダムに生成された名前の非表示の _script% データセット内に存在します。

割り当て

複数ステートメント クエリ割り当てについては、割り当てと上限をご覧ください。

マルチステートメント クエリの数を表示する

アクティブなマルチステートメント クエリの数は、INFORMATION_SCHEMA.JOBS_BY_PROJECT ビューを使用して表示できます。次の例では、INFORMATION_SCHEMA.JOBS_BY_PROJECT ビューを使用して、前日のマルチステートメント クエリの数を表示しています。

SELECT
  COUNT(*)
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND CURRENT_TIMESTAMP()
AND job_type = "QUERY"
AND state = 'RUNNING'
AND statement_type = 'SCRIPT'

INFORMATION_SCHEMA.JOBS のマルチステートメント クエリの詳細については、マルチステートメント クエリジョブをご覧ください。