セッションでクエリを記述する
このドキュメントでは、BigQuery セッションでクエリを記述する方法について説明します。BigQuery セッションの概要をすでに理解し、セッションでクエリを実行する方法を知っているユーザーを対象としています。
セッションには状態が保存されます。セッションで作成された状態は、セッション全体を通して維持され、使用できます。1 つのクエリエントリに一時テーブルを作成した場合、セッションの残りの部分では、その一時テーブルを他のクエリエントリで使用できます。
セッションには、セッション変数、セッション システム変数、複数ステートメント クエリ、複数ステートメント トランザクションのサポートが含まれています。
以下の手順を行う前に、セッションでの操作に必要な権限があることを確認してください。
セッションでシステム変数を使用する
セッション レベルのデータは、次のシステム変数で設定または取得できます。
@@dataset_id
: 現在のプロジェクト内にあるデフォルト データセットの ID。システム変数の@@dataset_project_id
と@@dataset_id
を組み合わせて設定して使用できます。@@dataset_project_id
: クエリで使用されるデータセットのデフォルト プロジェクトの ID。このシステム変数が設定されていない場合、またはNULL
に設定されている場合は、クエリ実行プロジェクトが使用されます。システム変数の@@dataset_project_id
と@@dataset_id
を組み合わせて設定して使用できます。@@query_label
: セッションに割り当てるジョブラベル。ラベルは、セッション内の特定のクエリだけでなく、セッション全体で使用できます。@@session_id
: 現在のセッションの ID。@@time_zone
: タイムゾーンが引数として指定されていない場合に、タイムゾーンに依存する SQL 関数で使用するデフォルトのタイムゾーン。
これらのシステム変数は、セッション中はいつでも使用できます。また、残りのセッションのスコープに含まれます。これらの変数は定義しませんが、SET
ステートメントで新しい値を割り当てることができます。
セッション内の変数の最大サイズは 1 MB で、セッション内のすべての変数の最大サイズは 10 MB です。
セッションにラベルを割り当てる
セッションにジョブラベルを割り当てることができます。 この操作を行うと、セッション内の以降のすべてのクエリがラベルに割り当てられます。セッション中、ラベルはいつでも使用でき、残りのセッションのスコープに含まれます。割り当てたジョブラベルは監査ログに表示されます。
セッションで変数を使用する
セッション レベルのデータは、変数を使用して作成、設定、取得できます。セッション中、変数はいつでも使用でき、残りのセッションのスコープに含まれます。
- セッション スコープの変数を作成するには、
BEGIN...END
ブロックの外部でDECLARE
ステートメントを使用します。 - 作成後にセッション スコープの変数を設定するには、
SET
ステートメントを使用します。 BEGIN...END
ブロック内で宣言された変数は、セッション スコープの変数ではありません。- セッション スコープの変数は
BEGIN...END
ブロック内で参照できます。 - セッション スコープの変数は
BEGIN...END
ブロック内で設定できます。
セッション内の変数の最大サイズは 1 MB で、セッション内のすべての変数の最大サイズは 10 MB です。
セッションで一時テーブルを使用する
一時テーブルを使用すると、テーブルに中間結果を保存できます。一時テーブルはセッション レベルで表示されるため、データセット内での保存や管理の必要はありません。セッションが終了すると自動的に削除されます。セッションがアクティブである間は、一時テーブルのストレージに対して課金されます。一時テーブルの詳細については、マルチステートメント クエリを操作するをご覧ください。
セッションで一時的な関数を使用する
一時テーブルはセッション レベルで表示されるため、データセット内での保存や管理の必要はありません。セッションが終了すると自動的に削除されます。
セッションで複数ステートメント クエリを操作する
セッションでは、GoogleSQL 複数ステートメント クエリを使用できます。スクリプトには、各スクリプトの一時テーブルとシステム変数を含めることができます。セッション変数と一時テーブルはスクリプトから参照できます。スクリプトで宣言されているすべてのトップレベル変数は、セッション変数でもあります。
セッション内の複数クエリで複数ステートメント トランザクションを実行する
セッション内の複数のクエリで、複数ステートメント トランザクションを実行できます。次に例を示します。
次のクエリはトランザクションを開始します。
BEGIN TRANSACTION
トランザクションの中で、次のクエリは Flights
という一時テーブルを作成し、このテーブルのデータを返します。このクエリには 2 つのステートメントが含まれます。
CREATE TEMP TABLE Flights(total INT64) AS SELECT * FROM UNNEST([10,23,3,14,55]) AS a;
SELECT * FROM Flights;
次のクエリは、トランザクションを commit します。
COMMIT
Flights
テーブルに影響するアクティブなトランザクションを確認できます。
WITH running_transactions AS (
SELECT DISTINCT transaction_id
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
EXCEPT DISTINCT
SELECT transaction_id FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
AND statement_type = "COMMIT_TRANSACTION"
OR statement_type = "ROLLBACK_TRANSACTION"
)
SELECT
jobs.transaction_id AS transaction_id,
project_id,
user_email,
session_info.session_id,
query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS jobs, running_transactions
WHERE
creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
AND destination_table = ("Flights")
AND jobs.transaction_id = running_transactions.transaction_id;
進行中のトランザクションをキャンセルする場合、bigquery.admin
ロールがあれば、Cloud Shell または API 呼び出しで、トランザクションに関連付けられたセッション ID を使用してロールバック ステートメントを発行できます。トランザクションに関連付けられたセッション ID を使用してクエリを実行すると、結果にセッション ID が含まれます。
セッションの例
これは、Google Cloud コンソールのセッション ワークフローの例です。
Google Cloud コンソールで新しいエディタタブを開き、セッションを作成します。
エディタタブで、次のクエリを追加します。
CREATE TEMP TABLE Flights(total INT64) AS SELECT * FROM UNNEST([10,23,3,14,55]) AS a; SELECT * FROM Flights;
クエリを実行します。
Flights
という一時テーブルが作成され、すべてのデータが返されます。+-------+ | total | +-------+ | 55 | | 23 | | 3 | | 14 | | 10 | +-------+
エディタタブのコンテンツを削除し、次のクエリを追加します。
SELECT * FROM Flights LIMIT 2;
クエリを実行します。2 つのレコードの結果が返されます。前のクエリを削除しても、クエリからの情報は現在のセッションに保存されます。
+-------+ | total | +-------+ | 55 | | 23 | +-------+
エディタタブのコンテンツを削除し、次のクエリを追加します。
DECLARE x INT64 DEFAULT 10; SELECT total * x AS total_a FROM Flights LIMIT 2; BEGIN SET x = 100; SELECT total * x AS total_b FROM Flights LIMIT 2; END; SELECT total * x AS total_c FROM Flights LIMIT 2;
クエリを実行します。セッション スコープの変数
x
は、Flights
テーブルに対して返される結果の数を制限するために使用されます。この変数がBEGIN...END
ステートメントの外部で宣言され、BEGIN...END
ステートメント内で設定され、再度BEGIN...END
外で参照された場合に、この変数にスコープが与える影響に注意してください。+---------+ | total_a | +---------+ | 550 | | 230 | +---------+ +---------+ | total_b | +---------+ | 5500 | | 2300 | +---------+ +---------+ | total_c | +---------+ | 5500 | | 2300 | +---------+
エディタタブのコンテンツを削除し、次のクエリを追加します。
SELECT STRING(TIMESTAMP "2008-12-20 15:30:00+00", @@time_zone) AS default_time_zone; SET @@time_zone = "America/Los_Angeles"; SELECT STRING(TIMESTAMP "2008-12-20 15:30:00+00", @@time_zone) AS new_time_zone;
クエリを実行します。セッション スコープのシステム変数
@@time_zone
は、タイムスタンプをタイムゾーンに割り当てるために使用されます。最初のステートメントは、デフォルトのタイムゾーン(この例ではUTC
)を持つタイムスタンプを返します。次のステートメントで、@@time_zone
を新しい値に割り当てます。3 番目のステートメントは、新しいタイムゾーンを持つタイムスタンプを返します。+-------------------------------+ | default_time_zone | +-------------------------------+ | 2008-12-20 15:30:00+00 | +-------------------------------+ +-------------------------------+ | new_time_zone | +-------------------------------+ | 2008-12-20 07:30:00-08 | +-------------------------------+