このページは Cloud Translation API によって翻訳されました。

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

マルチステートメントクエリは、共有状態で順番に実行できる SQL ステートメントの集合です。

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

マルチステートメントクエリはストアドプロシージャで頻繁に使用され、プロシージャ言語ステートメントをサポートします。これにより、変数の定義、制御フローの実装などを行うことができます。マルチステートメントクエリには、テーブルやテーブルデータの作成や変更などの副作用がある DDL ステートメントと DML ステートメントを含めることができます。

マルチステートメントクエリの作成、実行、保存

マルチステートメントクエリは、セミコロンで区切られた 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 コンソールや bq コマンドラインツールを使用して）実行できます。

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

マルチステートメントクエリで読み取られるバイト数を見積もるには、ドライランを検討してください。マルチステートメントクエリのドライランは、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     |
+--------+

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

一時テーブルを使用すると、中間結果をテーブルに保存できます。一時テーブルは 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 時間存在します。

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

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

Google Cloud コンソールで、[BigQuery] ページを開きます。

BigQuery に移動
左側のペインで、 [エクスプローラ] をクリックします。

左側のペインが表示されていない場合は、 左側のペインを開くをクリックしてペインを開きます。
[エクスプローラ] ペインで、[ジョブの履歴] をクリックします。
[個人履歴] タブまたは [プロジェクト履歴] タブで、一時テーブルを作成したクエリをクリックします。
次に、[宛先テーブル] 行の [一時テーブル] をクリックします。

注: マルチステートメントクエリで使用される一時テーブルの名前は保持されません。 Google Cloud コンソールでは、ランダムな名前が付けられます。

`_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 コンソールでは、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 のマルチステートメントクエリの詳細については、マルチステートメントクエリジョブをご覧ください。