標準 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
);

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 で初期化されます。

変数の宣言は、スクリプトの先頭、他のステートメントの前、または 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;

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 の値が （つまり 10）に割り当てられています。この値が戻り値になります。次に、END ステートメントがブロックの最後となり、変数 y のスコープもここで終了となります。最後に、 の値が返されます。

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

END

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

IF

構文

IF condition THEN
  [if_statement_list]
[ELSE
  else_statement_list
]
END IF;

説明

condition が true の場合は、if_statement_list が実行されます。それ以外の場合は、else_statement_list が実行されます（オプションの ELSE が指定されている場合）。

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

例

次の例では、INT64 変数 target_product_id のデフォルト値が 103 と宣言されています。この宣言によりテーブル dataset.products が 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));
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 ステートメントを使用してループを作成しています。ループは、 が 10 以上になるまで実行されます。ループが終了すると、 の値が出力されます。

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 が宣言されています。次に、ループを開始します。ループ内では、ランダムなブール値が に割り当てられています。次に、 が 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 が宣言されています。次に、ループを開始します。ループ内では、ランダムなブール値が に割り当てられています。次に、 が 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 と同義。

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;