標準 SQL のスクリプト

ベータ版

この機能はプレリリース版の状態で、変更やサポート制限が行われる可能性があります。詳細については、プロダクトのリリース ステージをご覧ください。

BigQuery スクリプト

BigQuery スクリプトを使用すると、1 回のリクエストで複数のステートメントを BigQuery に送信して、変数を使用したり、IF、WHILE などの制御フロー ステートメントを使用できます。たとえば、変数を宣言し、値を代入すると、第 3 の場所にあるステートメントからその変数を参照できます。

BigQuery のスクリプトは、順番に実行される SQL ステートメントのリストです。SQL ステートメント リストは、セミコロンで区切られた有効な BigQuery ステートメントのリストです。

例:

    -- Declare a variable to hold names as an array.
    DECLARE top_names ARRAY<STRING>;
    -- Build an array of the top 100 names from the year 2017.
    SET top_names = (
      SELECT ARRAY_AGG(name ORDER BY number DESC LIMIT 100)
      FROM `bigquery-public-data`.usa_names.usa_1910_current
      WHERE year = 2017
    );
    -- Which names appear as words in Shakespeare's plays?
    SELECT
      name AS shakespeare_name
    FROM UNNEST(top_names) AS name
    WHERE name IN (
      SELECT word
      FROM `bigquery-public-data`.samples.shakespeare
    );
    

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

jobs.getQueryResults がスクリプトで呼び出されると、スクリプトで実行する最後の SELECT、DML、または DDL ステートメントのクエリ結果が返されます。上記のステートメントが実行されていない場合、クエリ結果は返されません。スクリプト内のすべてのステートメントの結果を取得するには、子ジョブを列挙し、それぞれに対して jobs.getQueryResults を呼び出します。

BigQuery では、CREATE TEMP FUNCTION ステートメントが含まれていない場合、複数のステートメントを含むリクエストは、最後にクエリ ステートメントを含むスクリプトとして解釈されます。たとえば、以下はスクリプトと見なされません。

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

    SELECT Add(3, 4);
    

DECLARE

構文

    DECLARE variable_name[, ...] [variable_type] [DEFAULT expression];
    

variable_name は、有効な識別子である必要があります。また、variable_type は BigQuery の任意のタイプです。

説明

指定された型の変数を宣言します。たとえば DEFAULT 句が指定された場合、変数は式の値で初期化されます。DEFAULT 句が指定されない場合、変数は値 NULL で初期化されます。

[variable_type] を省略する場合、DEFAULT 句を指定する必要があります。変数の型は、DEFAULT 句の式の型によって推定されます。

変数の宣言は、スクリプトの先頭、他のステートメントの前、または BEGIN が宣言されたブロックの先頭に置きます。変数名は大文字と小文字を区別しません。

複数の変数名を 1 つの DECLARE ステートメント内に記述できます。ただし、variable_type と expression は 1 つだけです。

現在のブロックまたは格納中のブロックですでに宣言されている変数と同じ名前の変数を宣言するとエラーになります。

たとえば DEFAULT 句が存在する場合、式の値は指定された型に強制型変換できる必要があります。同じブロックまたは格納中のブロック内ですでに宣言されているその他の変数を参照できます。

変数の最大サイズは 1 MB で、スクリプトで使用されるすべての変数の最大サイズは 10 MB です。

例

次の例では、変数 x を INT64 として値 NULL で初期化しています。

    DECLARE x INT64;
    

次の例では、変数 d を DATE として現在の日付の値で初期化しています。

    DECLARE d DATE DEFAULT CURRENT_DATE();
    

次の例では、x、y、z を INT64 として値 0 で初期化しています。

    DECLARE x, y, z INT64 DEFAULT 0;
    

次の例では、dataset1.products テーブルの任意の項目に対応する item という名前の変数を宣言しています。item の型はテーブル スキーマから推測されます。

    DECLARE item DEFAULT (SELECT item FROM dataset1.products LIMIT 1);
    

SET

構文

    SET name = expression;
    

    SET (variable_name_1, variable_name_2, …, variable_name_n) =
      (expression_1, expression_2, …, expression_n);
    

説明

変数が指定された式の値を持つように設定するか、または複数の式の結果に基づいて複数の変数を同時に設定します。

SET スクリプト本文内のどこにでも記述できます。

例

次の例では、変数 x の値を 5 に設定しています。

    SET x = 5;
    

次の例では、a の値を 4、b の値を 'foo'、c の値を false にそれぞれ設定しています。

    SET (a, b, c) = (1 + 3, 'foo', false);
    

次の例では、クエリの結果を複数の変数に代入しています。まず、2 つの変数 target_word と corpus_count を宣言します。次に、SELECT AS STRUCT クエリの結果を 2 つの変数に割り当てます。クエリの結果は、2 つのフィールドを持つ STRUCT を含む 1 つの行です。最初の要素は最初の変数に割り当てられ、2 番目の要素は 2 番目の変数に割り当てられます。

    DECLARE target_word STRING DEFAULT 'methinks';
    DECLARE corpus_count, word_count INT64;

    SET (corpus_count, word_count) = (
      SELECT AS STRUCT COUNT(DISTINCT corpus), SUM(word_count)
      FROM `bigquery-public-data`.samples.shakespeare
      WHERE LOWER(word) = target_word
    );

    SELECT
      FORMAT('Found %d occurrences of "%s" across %d Shakespeare works',
             word_count, target_word, corpus_count) AS result;
    

このステートメント リストは、次の文字列を出力します。

Found 151 occurrences of "methinks" across 38 Shakespeare works

BEGIN

構文

    BEGIN
      sql_statement_list
    END;
    

説明

BEGIN は、宣言された変数が対応する END まで存在するステートメントのブロックを開始します。sql_statement_list は、それぞれがセミコロンで終わるゼロ個以上の SQL ステートメントのリストです。

変数宣言は、ブロックの先頭で、その他のタイプのステートメントよりも前に配置する必要があります。ブロック内で宣言された変数は、そのブロック内とネストされたブロック内でのみ参照できます。同じブロックまたは外側のブロックで宣言されている変数と同じ名前の変数を宣言するとエラーになります。

ブロックや条件文（BEGIN/END、IF/ELSE/END IF、WHILE/END WHILE など）の最大のネストレベルは 50 です。

例

次の例では、変数 x のデフォルト値が 10 と宣言されています。そしてブロックが開始され、変数 y の値が x（つまり 10）に割り当てられています。この値が戻り値になります。次に、END ステートメントがブロックの最後となり、変数 y のスコープもここで終了となります。最後に、x の値が返されます。

    DECLARE x INT64 DEFAULT 10;
    BEGIN
      DECLARE y INT64;
      SET y = x;
      SELECT y;
    END;
    SELECT x;
    

BEGIN...EXCEPTION

構文

    BEGIN
      sql_statement_list
    EXCEPTION WHEN ERROR THEN
      sql_statement_list
    END;
    

説明

BEGIN...EXCEPTION はステートメントのブロックを実行します。ステートメントのいずれかにエラーが発生した場合、スクリプトは残りのブロックをスキップし、EXCEPTION 句のステートメントを実行します。

EXCEPTION 句内では、次の EXCEPTION システム変数を使用することでエラーの詳細にアクセスできます。

名前	型	説明
@@error.formatted_stack_trace	STRING	@@error.stack_trace のコンテンツは人が読める文字列として表現されます。この値は表示を目的に使用され、予告なく変更されることがあります。プログラムによるエラーのスタック トレースへのアクセスには、代わりに @@error.stack_trace を使用してください。
@@error.message	STRING	人が読めるエラー メッセージを指定します。
@@error.stack_trace	1 をご覧ください。	配列の各要素は、エラーの時点で実行しているステートメントまたはプロシージャ コールに対応し、現在実行中のスタック フレームが先頭に表示されます。各フィールドの意味の定義は、次のとおりです。 行 / 列: スタック フレームの行番号と列番号を 1 から開始して指定します。フレームがプロシージャの本文内で発生した場合、line 1 column 1 はプロシージャの先頭にある BEGIN キーワードに対応します。 ロケーション: フレームがプロシージャ本文内にある場合、[project_name].[dataset_name].[procedure_name] 形式でプロシージャのフルネームを指定します。フレームがトップレベル スクリプト内の場所を参照している場合、このフィールドは NULL になります。 ファイル名: 将来の使用のために予約されています。常に NULL です。
@@error.statement_text	STRING	エラーの原因となったステートメントのテキストを指定します。

¹: @@error.stack_trace の型は ARRAY<STRUCT<line INT64, column INT64, filename STRING, location STRING>> です。

BigQuery はエラー メッセージをいつでも修正できるため、@@error.message の使用にあたっては、エラー メッセージが常に同じである、または一定のパターンであることを想定しないでください。エラー メッセージからテキストを抽出してエラーの場所情報を取得せず、代わりに @@error.stack_trace と @@error.statement_text を使用してください。

例外ハンドラによってスローされる（そして、処理されない）例外を処理するには、個別の例外ハンドラを使用して外側のブロック内にあるブロックをラップする必要があります。

外側のブロックに別の例外ハンドラを使用する方法は次のとおりです。

    BEGIN
      BEGIN
        ...
      EXCEPTION WHEN ERROR THEN
        SELECT 1/0;
      END;
    EXCEPTION WHEN ERROR THEN
      -- The exception thrown from the inner exception handler lands here.
    END;
    

BEGIN...EXCEPTION ブロックは、他の BEGIN ブロック同様に DECLARE ステートメントもサポートします。BEGIN ブロックで宣言されている変数は、BEGIN セクションでのみ有効で、ブロックの例外ハンドラでは使用できません。

例

    CREATE OR REPLACE PROCEDURE dataset1.proc1() BEGIN
      SELECT 1/0;
    END;

    CREATE OR REPLACE PROCEDURE dataset1.proc2() BEGIN
      CALL dataset1.proc1();
    END;

    BEGIN
      CALL dataset1.proc2();
    EXCEPTION WHEN ERROR THEN
      SELECT
        @@error.message,
        @@error.stack_trace,
        @@error.statement_text,
        @@error.formatted_stack_trace;
    END;
    

この例では、ゼロ除算のエラーが発生した場合、スクリプト全体が終了するのではなく、BigQuery が dataset1.proc1() と dataset1.proc2() を終了し、例外ハンドラで SELECT ステートメントを実行します。例外ハンドラが実行されると、変数の値は次のようになります。

変数	値
@@error.message	"Query error: division by zero: 1 / 0 at <project>.dataset1.proc1:2:3]"
@@error.stack_trace	[ STRUCT(2 AS line, 3 AS column, NULL AS filename, "<project>.dataset1.proc1:2:3" AS location), STRUCT(2 AS line, 3 AS column, NULL AS filename, "<project>.dataset1.proc2:2:3" AS location), STRUCT(10 AS line, 3 AS column, NULL AS filename, NULL AS location), ]
@@error.statement_text	"SELECT 1/0"
@@error.formatted_stack_trace	"At <project>.dataset1.proc1[2:3]\nAt <project>.dataset1.proc2[2:3]\nAt [10:3]"

END

BEGIN で開始されたブロックを終了します。

IF

構文

    IF condition THEN [sql_statement_list]
    [ELSEIF condition THEN sql_statement_list]
    [ELSEIF condition THEN sql_statement_list]...
    [ELSE  sql_statement_list]
    END IF;
    

説明

条件が true の場合は最初の sql_statement_list を、条件が一致しない場合はオプションの ELSE sql_statement_list を実行します。

ブロックや条件文（BEGIN/END、IF/ELSE/END IF、WHILE/END WHILE など）の最大のネストレベルは 50 です。

例

次の例では、INT64 変数 target_product_id のデフォルト値が 103 と宣言されています。この宣言によりテーブル dataset.products が product_id 列を持つ行を含み target_product_id の値と一致するかどうかがチェックされます。一致する場合は、プロダクトが見つかったという文字列と default_product_id の値が出力されます。一致しない場合は、プロダクトが見つからなかったという文字列と default_product_id の値が出力されます。

    DECLARE target_product_id INT64 DEFAULT 103;
    IF EXISTS (SELECT 1 FROM dataset.products
               WHERE product_id = target_product_id) THEN
      SELECT CONCAT('found product ', CAST(target_product_id AS STRING));
      ELSEIF EXISTS (SELECT 1 FROM dataset.more_products
               WHERE product_id = target_product_id) THEN
      SELECT CONCAT('found product from more_products table',
      CAST(target_product_id AS STRING));
    ELSE
      SELECT CONCAT('did not find product ', CAST(target_product_id AS STRING));
    END IF;
    

ループ

LOOP

構文

    LOOP
      sql_statement_list
    END LOOP;
    

説明

BREAK または LEAVE ステートメントでループが終了するまで、sql_statement_list が実行されます。sql_statement_list は、それぞれがセミコロンで終わるゼロ個以上の SQL ステートメントのリストです。

例

次の例では、変数 x のデフォルト値が 0 と宣言されています。次に、LOOP ステートメントを使用してループを作成しています。ループは、x が 10 以上になるまで実行されます。ループが終了すると、x の値が出力されます。

    DECLARE x INT64 DEFAULT 0;
    LOOP
      SET x = x + 1;
      IF x >= 10 THEN
        LEAVE;
      END IF;
    END LOOP;
    SELECT x;
    

この例では、次を出力します。

+----+
    | x  |
    +----+
    | 10 |
    +----+

WHILE

構文

    WHILE boolean_expression DO
      sql_statement_list
    END WHILE;
    

ブロックや条件文（BEGIN/END、IF/ELSE/END IF、WHILE/END WHILE など）の最大のネストレベルは 50 です。

説明

boolean_expression が true の場合は、sql_statement_list が実行されます。boolean_expression は、ループのイテレーションごとに評価されます。

BREAK

説明

現在のループを終了します。

BREAK をループの外側で使用するとエラーが発生します。

例

次の例では、変数 heads と heads_count が宣言されています。次に、ループを開始します。ループ内では、ランダムなブール値が heads に割り当てられています。次に、heads が true の場合に「Heads!」（コインの表）と出力され、heads_count が増分されます。true でない場合は「Tails!」（コインの裏）と出力され、ループが終了します。コインのフリップが表になった回数を表す文字列が出力されます。

    DECLARE heads BOOL;
    DECLARE heads_count INT64 DEFAULT 0;
    LOOP
      SET heads = RAND() < 0.5;
      IF heads THEN
        SELECT 'Heads!';
        SET heads_count = heads_count + 1;
      ELSE
        SELECT 'Tails!';
        BREAK;
      END IF;
    END LOOP;
    SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');
    

LEAVE

BREAK と同義。

CONTINUE

説明

現在のループ内の次のステートメントをスキップし、ループの先頭に戻ります。

CONTINUE をループの外側で使用するとエラーが発生します。

例

次の例では、変数 heads と heads_count が宣言されています。次に、ループを開始します。ループ内では、ランダムなブール値が heads に割り当てられています。次に、heads が true の場合に「Heads!」（コインの表）と出力され、heads_count が増分され、ループの最初に戻ります（その他のステートメントは飛ばされる）。true でない場合は「Tails!」（コインの裏）と出力され、ループが終了します。コインのフリップが表になった回数を表す文字列が出力されます。

    DECLARE heads BOOL;
    DECLARE heads_count INT64 DEFAULT 0;
    LOOP
      SET heads = RAND() < 0.5;
      IF heads THEN
        SELECT 'Heads!';
        SET heads_count = heads_count + 1;
        CONTINUE;
      END IF;
      SELECT 'Tails!';
      BREAK;
    END LOOP;
    SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');
    

ITERATE

CONTINUE と同義。

RAISE

構文

    RAISE [USING MESSAGE = message];
    

説明

USING MESSAGE = message が指定されると、必要に応じて特定のエラー メッセージを使用して、エラーを発生させます。

USING MESSAGE が指定されていない場合

RAISE ステートメントは EXCEPTION 句内でのみ使用する必要があります。RAISE ステートメントは検出された例外を再生成し、元のスタック トレースを保持します。

USING MESSAGE が指定されている場合

RAISE ステートメントが BEGIN...EXCEPTION ブロックの BEGIN セクションに含まれている場合、次のようになります。

• ハンドラが呼び出されます。

• @@error.message の値は、指定された message 文字列（message が NULL の場合は NULL になる可能性があります）と完全に一致します。

• スタック トレースは、RAISE ステートメントに設定されます。

RAISE ステートメントが BEGIN...EXCEPTION ブロックの BEGIN セクションに含まれていない場合、RAISE ステートメントは指定したエラー メッセージを表示し、スクリプトの実行を終了します。

RETURN

BigQuery スクリプトでは、RETURN は現在のスクリプトの実行を終了します。

CALL

構文

    CALL procedure_name (procedure_argument[, …])
    

説明

引数リストとともにプロシージャを呼び出します。procedure_argument は、変数または式とすることが可能です。OUT 引数または INOUT 引数は、引数として渡される変数ごとに適切な BigQuery のタイプが指定される必要があります。

同じ変数を、プロシージャの引数リストに OUT 引数または INOUT 引数として複数回指定することはできません。

プロシージャ呼び出しの最大深度は 50 フレームです。

例

次の例では、変数 retCode を宣言しています。次に、引数 'someAccountId' と retCode を渡し、データセット myDataset 内でプロシージャ updateSomeTables を呼び出します。最後に、retCode の値を返します。

    DECLARE retCode INT64;
    -- Procedure signature: (IN account_id STRING, OUT retCode INT64)
    CALL myDataset.UpdateSomeTables('someAccountId', retCode);
    SELECT retCode;
    

システム変数

システム変数を使用して、スクリプトの実行中に情報を確認できます。

名前	型	説明
@@current_job_id	STRING	現在実行中のジョブのジョブ ID。スクリプトのコンテキストでは、スクリプト全体ではなく、現在のステートメントを処理するジョブが返されます。
@@last_job_id	STRING	現在のスクリプトで実行された最新のジョブのジョブ ID。現在のジョブは含まれません。スクリプトに CALL ステートメントが含まれている場合、このジョブは別のプロシージャで作成された可能性があります。
@@project_id	STRING	現在のクエリを実行するために使用されるプロジェクトの ID。プロシージャのコンテキストでは、@@project_id はプロシージャを所有するプロジェクトではなく、スクリプトを実行しているプロジェクトを指します。
@@row_count	INT64	スクリプトで使用され、前のスクリプト文が DML の場合、その DML ステートメントの結果として変更、挿入、または削除された行数を指定します。前のステートメントが MERGE ステートメントの場合、@@row_count は挿入、削除された合計行数を表します。スクリプト内にない場合、この値は NULL になります。
@@script.bytes_billed	INT64	現在実行中のスクリプト ジョブでこれまでに請求された合計バイト数。スクリプト内にない場合、この値は NULL になります。
@@script.bytes_processed	INT64	現在実行中のスクリプト ジョブでこれまでに処理された合計バイト数。スクリプト内にない場合、この値は NULL になります。
@@script.creation_time	TIMESTAMP	現在実行中のスクリプト ジョブの作成時刻。スクリプト内にない場合、この値は NULL になります。
@@script.job_id	STRING	現在実行中のスクリプト ジョブのジョブ ID。スクリプト内にない場合、この値は NULL になります。
@@script.num_child_jobs	INT64	現在完了している子ジョブの数。スクリプト内にない場合、この値は NULL になります。
@@script.slot_ms	INT64	スクリプトがそれまでに使用したスロットのミリ秒数。スクリプト内にない場合、この値は NUL L です。

上記のシステム変数に加えて、スクリプト実行中に EXCEPTION システム変数を使用できます。例外ハンドラで使用されない場合、EXCEPTION システム変数は NULL になります。以下は EXCEPTION システム変数です。

• @@error.formatted_stack_trace
• @@error.message
• @@error.stack_trace
• @@error.statement_text

EXCEPTION システム変数の詳細については、BEGIN...EXCEPTION をご覧ください。