スクリプトとストアド プロシージャ

スクリプトは、1 回のリクエストで実行できる SQL ステートメントの集まりです。スクリプトでは変数と制御フロー ステートメントを使用できますが、それぞれ副作用を及ぼす可能性があります。スクリプトを使用すると、次のことができます。

  • 共有状態の複数のクエリを順番に実行します。
  • テーブルの作成や削除などの管理タスクを自動化します。
  • IFWHILE などのプログラミング構造を使用して、より複雑なロジックを実装します。

プロシージャは、SQL ステートメント内から呼び出すことができる永続スクリプトです。プロシージャは、入力引数を受け取り、出力として値を返すことができます。

次の例では、変数を設定し、INSERT ステートメントを実行して、結果を書式設定されたテキスト文字列として表示するスクリプトを示しています。このスクリプトは、Google Cloud Console や bq コマンドライン ツールの使用など、クエリと同じ方法で実行できます。また、クエリとしてスクリプトを保存することもできます。

DECLARE id STRING;
SET id = GENERATE_UUID();

INSERT INTO mydataset.customers (customer_id)
   VALUES(id);

SELECT FORMAT("Created customer ID %s", id);

同じスクリプトをプロシージャに変換した例を示します。

CREATE OR REPLACE PROCEDURE mydataset.create_customer()
BEGIN
  DECLARE id STRING;
  SET id = GENERATE_UUID();
  INSERT INTO mydataset.customers (customer_id)
    VALUES(id);
  SELECT FORMAT("Created customer %s", id);
END

上記の例では、プロシージャの名前は mydataset.create_customer で、プロシージャの本文は BEGINEND ステートメントの間にあります。

プロシージャを呼び出すには、CALL ステートメントを使用します。

CALL mydataset.create_customer();

スクリプトの作成

スクリプトは、1 つ以上の SQL ステートメントをセミコロンで区切って構成します。任意の有効な SQL ステートメントをスクリプトで使用できます。スクリプトには、スクリプト文を含めることができます。これにより、変数を使用できるようになるか、SQL ステートメントで制御フローを実装できます。次の例では、変数を宣言し、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

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

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

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

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

変数

変数は、スクリプトの先頭、または BEGIN ブロックの先頭で宣言する必要があります。スクリプトの先頭で宣言された変数は、スクリプト全体のスコープに含まれます。BEGIN ブロック内で宣言された変数には、そのブロックのスコープがあります。対応する END ステートメントの後に、スコープの範囲外になります。

DECLARE x INT64;

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

-- Here you can reference x, but not y

一時テーブル

一時テーブルを使用すると、中間結果をテーブルに保存できます。この一時テーブルはセッション レベルで存在するため、データセット内での保存や管理の必要はありません。このスクリプトの完了後、しばらくすると自動的に削除されます。

次の例では、クエリの結果を保存する一時テーブルを作成し、サブクエリで一時テーブルを使用します。

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

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

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

一時スクリプトの名前は、現在のスクリプトの期間の間だけ参照できます。これには、スクリプト内のプロシージャによって作成された一時テーブルも含まれます。一時テーブルは共有できず、標準のリストやその他のテーブル操作の方法を使用して表示することもできません。

スクリプトが終了した後、一時テーブルは最長で 24 時間存在します。指定した名前を使用して保存されませんが、代わりにランダムな名前が割り当てられます。テーブルの構造とデータを表示するには、BigQuery コンソールに移動して [クエリ履歴] をクリックし、一時テーブルを作成したクエリを選択します。次に、[宛先テーブル] 行の [一時テーブル] をクリックします。一時テーブルの保管には料金はかかりません。

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

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

デフォルト データセットとともに一時テーブルを使用する場合、非修飾テーブル名は一時テーブル(存在する場合)またはデフォルト データセットのテーブルを参照します。例外は 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

スクリプトのデバッグ

以下に、スクリプトとストアド プロシージャをデバッグする際のヒントを示します。

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

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

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

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

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

プロシージャの作成

プロシージャを作成するには、CREATE PROCEDURE ステートメントを使用します。プロシージャの本文は、BEGINEND 文の間にあります。

CREATE PROCEDURE dataset_name.procedure_name
BEGIN
-- statements here
END

パラメータ

プロシージャでは、名前付きパラメータのリストを取得できます。各パラメータにはデータ型があります。

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING)
BEGIN
DECLARE id STRING;
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

プロシージャには出力パラメータを含めることができます。出力パラメータは、プロシージャから値を返しますが、プロシージャの入力はできません。出力パラメータを作成するには、パラメータの名前の前に OUT キーワードを使用します。

たとえば、このバージョンのプロシージャでは、id パラメータを使用して新規顧客 ID を返します。

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING, OUT id STRING)
BEGIN
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

このプロシージャを呼び出すには、変数を使用して出力値を取得する必要があります。

--- Create a new customer record.
DECLARE id STRING;
CALL mydataset.create_customer("alice",id);

--- Display the record.
SELECT * FROM temp.customers
WHERE customer_id = id;

プロシージャには、入出力パラメータを含めることもできます。入出力パラメータは、プロシージャから値を返し、プロシージャの入力も受け入れます。入出力パラメータを作成するには、パラメータの名前の前に INOUT キーワードを使用します。詳細については、引数モードをご覧ください。

料金

オンデマンド課金の場合、BigQuery がスクリプトの実行中に処理したバイト数に基づいてスクリプト化の料金が課金されます。

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