クエリ結果の書き込み
このドキュメントでは、一時テーブルや永続テーブルにクエリ結果を書き込む方法について説明します。
一時テーブルと永続テーブル
BigQuery は、すべてのクエリ結果を永続テーブルまたは一時テーブルに保存します。
BigQuery は、永続的なテーブルに書き込まれていない一時テーブルを使用してクエリ結果をキャッシュに保存します。テーブルは、特別なデータセット内に作成され、ランダムに名前が付けられます。また、マルチステートメント クエリとセッション内で使用する一時テーブルを作成することもできます。
クエリの終了後、一時テーブルは最大 24 時間存在します。テーブルの構造とデータを表示するには、BigQuery コンソールに移動して [個人履歴] をクリックし、一時テーブルを作成したクエリを選択します。次に、[宛先テーブル] 行の [一時テーブル] をクリックします。
一時テーブルデータへのアクセスは、クエリジョブを作成したユーザーまたはサービス アカウントに限定されます。
一時テーブルは共有できず、標準のリストやその他のテーブル操作の方法を使用して表示することもできません。一時テーブルは、クエリ対象の 1 つまたは複数のテーブルと同じリージョンに作成されます。
永続テーブルは、ユーザーがアクセス可能なデータセットに含まれる新規または既存のテーブルです。新しいテーブルにクエリ結果を書き込んだ場合、そのデータの保管に対して料金がかかります。永続テーブルにクエリ結果を書き込む場合、クエリ対象のテーブルは、宛先テーブルが含まれるデータセットと同じロケーションに存在する必要があります。
必要な権限
クエリ結果をテーブルに書き込むには、少なくとも次の権限が付与されている必要があります。
- 新しいテーブルを作成するための
bigquery.tables.create
権限 - 新しいテーブルへのデータの書き込み、テーブルの上書き、テーブルへのデータの追加を行うための
bigquery.tables.updateData
権限 - クエリジョブを実行するための
bigquery.jobs.create
権限
クエリ対象のデータにアクセスするために、bigquery.tables.getData
などの権限も必要になる場合があります。
次の事前定義済みの IAM ロールには bigquery.tables.create
権限と bigquery.tables.updateData
権限の両方が含まれています。
bigquery.dataEditor
bigquery.dataOwner
bigquery.admin
次の事前定義済みの IAM ロールには bigquery.jobs.create
権限が含まれています。
bigquery.user
bigquery.jobUser
bigquery.admin
また、bigquery.datasets.create
権限を持つユーザーがデータセットを作成すると、そのデータセットに対する bigquery.dataOwner
アクセス権がユーザーに付与されます。bigquery.dataOwner
アクセス権があれば、データセット内でテーブルを作成および更新できます。
BigQuery での IAM のロールと権限については、事前定義ロールと権限をご覧ください。
クエリ結果を永続テーブルに書き込む
永続テーブルにクエリ結果を書き込む際は、新しいテーブルの作成、既存テーブルへの結果の追加、既存のテーブルの上書きを行うことができます。
クエリ結果の書き込み
クエリ結果を永続テーブルに書き込むには、次の手順を使用します。費用を抑えるために、クエリを実行する前にデータをプレビューできます。
コンソール
Google Cloud コンソールで [BigQuery] ページを開きます。
[エクスプローラ] パネルでプロジェクトを開いて、データセットを選択します。
有効な SQL クエリを入力します。
[展開] をクリックして、[クエリ オプション] を選択します。
[クエリ結果の宛先テーブルを設定する] オプションを選択します。
[送信先] セクションで、テーブルを作成するデータセットを選択し、テーブル ID を選択します。
[宛先テーブルの書き込み設定] セクションで、次のいずれかを選択します。
- [空の場合に書き込む] - テーブルが空の場合にのみ、クエリ結果をテーブルに書き込みます。
- [テーブルに追加する] - クエリ結果を既存のテーブルに追加します。
- [テーブルを上書きする] - 既存のテーブルにクエリ結果を同じ名前で上書きします。
省略可: [データのロケーション] で、ロケーションを選択します。
クエリの設定を更新するには、[保存] をクリックします。
[実行] をクリックします。これにより、指定したテーブルにクエリ結果を書き込むクエリジョブが作成されます。
宛先テーブルを指定せずにクエリを実行した場合は、エディタの下にある [結果を保存する] ボタンをクリックすると、キャッシュに保存された結果テーブルを永続テーブルにコピーできます。
SQL
次の例では、CREATE TABLE
ステートメントを使用して、一般公開 bikeshare_trips
テーブルのデータから trips
テーブルを作成します。
Google Cloud コンソールで [BigQuery] ページに移動します。
クエリエディタで次のステートメントを入力します。
CREATE TABLE mydataset.trips AS ( SELECT bike_id, start_time, duration_minutes FROM bigquery-public-data.austin_bikeshare.bikeshare_trips );
[
実行] をクリックします。
クエリの実行方法については、インタラクティブ クエリを実行するをご覧ください。
詳細については、既存のテーブルから新しいテーブルを作成するをご覧ください。
bq
-
In the Google Cloud console, activate Cloud Shell.
At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
クエリ結果に基づいて永続テーブルを作成するには、
bq query
コマンドを入力して、--destination_table
フラグを指定します。GoogleSQL 構文を使用するには、use_legacy_sql=false
フラグを指定します。デフォルト プロジェクト以外のプロジェクトにあるテーブルにクエリ結果を書き込むには、project_id:dataset
の形式でプロジェクト ID をデータセット名に追加します。省略可:
--location
フラグを指定して、その値を使用するロケーションに設定します。既存の宛先テーブルに対する書き込み処理を制御するには、次のオプション フラグのいずれかを指定します。
--append_table
: 宛先テーブルが存在する場合、クエリ結果がそのテーブルに追加されます。--replace
: 宛先テーブルが存在する場合、そのテーブルはクエリ結果で上書きされます。bq --location=location query \ --destination_table project_id:dataset.table \ --use_legacy_sql=false 'query'
次のように置き換えます。
location
は、クエリの処理に使用するロケーションの名前です。--location
フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値をasia-northeast1
に設定します。ロケーションのデフォルト値は、.bigqueryrc
ファイルを使用して設定できます。project_id
はプロジェクト ID です。dataset
は、クエリ結果を書き込むテーブルを含むデータセットの名前です。table
は、クエリ結果を書き込むテーブルの名前です。query
は、GoogleSQL 構文のクエリです。書き込み処理フラグが指定されていない場合は、デフォルトの動作として、テーブルが空の場合にのみ結果が書き込まれます。テーブルが存在していて空でない場合は、「BigQuery error in query operation: Error processing job
project_id:bqjob_123abc456789_00000e1234f_1': Already Exists: Table project_id:dataset.table
」というエラーが返されます。例:
次のコマンドを入力すると、
mydataset
内のmytable
という宛先テーブルにクエリ結果が書き込まれます。このデータセットはデフォルト プロジェクトにあります。コマンドに書き込み処理フラグは指定されていないため、宛先テーブルは新規または空である必要があります。それ以外の場合は、Already exists
エラーが返されます。このクエリは、USA Name Data 一般公開データセットからデータを取得します。bq query \ --destination_table mydataset.mytable \ --use_legacy_sql=false \ 'SELECT name, number FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = "M" ORDER BY number DESC'
クエリ結果を使用して
mydataset
内のmytable
という名前の宛先テーブルを上書きするには、次のコマンドを入力します。このデータセットはデフォルト プロジェクトにあります。このコマンドには--replace
フラグが指定されているため、宛先テーブルが上書きされます。bq query \ --destination_table mydataset.mytable \ --replace \ --use_legacy_sql=false \ 'SELECT name, number FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = "M" ORDER BY number DESC'
mydataset
内のmytable
という名前の宛先テーブルにクエリ結果を追加するには、次のコマンドを入力します。このデータセットはデフォルト プロジェクトではなくmy-other-project
にあります。このコマンドには--append_table
フラグが指定されているため、クエリ結果が宛先テーブルに追加されます。bq query \ --append_table \ --use_legacy_sql=false \ --destination_table my-other-project:mydataset.mytable \ 'SELECT name, number FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = "M" ORDER BY number DESC'
上記のそれぞれの例では、次のような出力が生成されます。読みやすくするために、出力の一部のみを示します。
Waiting on bqjob_r123abc456_000001234567_1 ... (2s) Current status: DONE +---------+--------+ | name | number | +---------+--------+ | Robert | 10021 | | John | 9636 | | Robert | 9297 | | ... | +---------+--------+
API
クエリ結果を永続テーブルに保存するには、jobs.insert
メソッドを呼び出して query
ジョブを構成し、destinationTable
プロパティの値を含めます。既存の宛先テーブルに対する書き込み処理を制御するには、writeDisposition
プロパティを構成します。
クエリジョブの処理を行うロケーションを制御するには、ジョブリソースの jobReference
セクションにある location
プロパティを指定します。
Go
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある Go の設定手順を完了してください。詳細については、BigQuery Go API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
Java
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある Java の設定手順を完了してください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
クエリの結果を永続テーブルに保存するには、QueryJobConfiguration で宛先テーブルを目的の TableId に設定します。
Node.js
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある Node.js の設定手順を完了してください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
Python
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある Python の設定手順を完了してください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
クエリ結果を永続テーブルに保存するには、QueryJobConfig を作成し、宛先を目的の TableReference に設定します。そのジョブ構成を query メソッドに渡します。サイズの大きいクエリ結果を書き込む
通常、クエリには最大レスポンス サイズがあります。実行しようとしているクエリの結果がそのサイズを超過する可能性がある場合は、以下のいずれかを行うことができます。
- GoogleSQL では、クエリ結果の宛先テーブルを指定します。
- レガシー SQL では、宛先テーブルを指定し、
allowLargeResults
オプションを設定します。
サイズの大きいクエリ結果を格納する宛先テーブルを指定した場合、そのデータの保管に対して料金がかかります。
制限事項
レガシー SQL では、サイズの大きい結果を書き込む際に以下の制限があります。
- 宛先テーブルを指定する必要があります。
- トップレベルに
ORDER BY
、TOP
、またはLIMIT
句は指定できません。指定した場合、クエリ出力の並列計算ができなくなり、allowLargeResults
を使用するメリットがなくなります。 - ウィンドウ関数は、
PARTITION BY
句と組み合わせた場合にのみ、サイズの大きいクエリ結果を返すことができます。
レガシー SQL を使用してサイズの大きい結果を書き込む
レガシー SQL を使用してサイズの大きい結果セットを書き込むには次のようにします。
コンソール
Google Cloud コンソールで、[BigQuery] ページを開きます。
[クエリを新規作成] をクリックします。
[クエリエディタ] テキスト領域に有効な SQL クエリを入力します。
#legacySQL
接頭辞を使用するか、クエリ設定で [レガシー SQL を使用] がオンになっていることを確認します。[展開] クリックし、[クエリの設定] を選択します。
[送信先] で [クエリ結果の宛先テーブルを設定する] をオンにします。
[Dataset] で、そのテーブルを格納するデータセットを選択します。
[Table ID] フィールドにテーブル名を入力します。
既存のテーブルにサイズの大きい結果セットを書き込む場合は、[宛先テーブルの書き込み設定] オプションを使用して、宛先テーブルの書き込み処理を制御できます。
- 空の場合に書き込む: テーブルが空の場合にのみ、クエリ結果をテーブルに書き込みます。
- テーブルに追加する: クエリ結果を既存のテーブルに追加します。
- テーブルを上書きする: 既存のテーブルにクエリ結果を同じ名前で上書きします。
[結果サイズ] で [大容量の結果を許可する(サイズ制限なし)] をオンにします。
省略可: [データのロケーション] で、データのロケーションを選択します。
[保存] をクリックしてクエリの設定を更新します。
[実行] をクリックします。これにより、指定したテーブルにサイズの大きい結果セットを書き込むクエリジョブが作成されます。
bq
--allow_large_results
フラグと --destination_table
フラグを使用して、サイズの大きい結果セットを保持する宛先テーブルを作成します。--allow_large_results
オプションはレガシー SQL にのみ適用されるため、--use_legacy_sql=true
フラグも指定する必要があります。デフォルト プロジェクト以外のプロジェクトにあるテーブルにクエリ結果を書き込むには、PROJECT_ID:DATASET
の形式でプロジェクト ID をデータセット名に追加します。--location
フラグを指定して、その値をロケーションに設定します。
既存の宛先テーブルに対する書き込み処理を制御するには、次のオプション フラグのいずれかを指定します。
--append_table
: 宛先テーブルが存在する場合、クエリ結果がそのテーブルに追加されます。--replace
: 宛先テーブルが存在する場合、そのテーブルはクエリ結果で上書きされます。
bq --location=location query \ --destination_table PROJECT_ID:DATASET.TABLE \ --use_legacy_sql=true \ --allow_large_results "QUERY"
次のように置き換えます。
LOCATION
は、クエリの処理に使用するロケーションの名前です。--location
フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値をasia-northeast1
に設定します。.bigqueryrc
ファイルを使用してロケーションのデフォルト値を設定できます。PROJECT_ID
はプロジェクト ID です。DATASET
は、クエリ結果を書き込むテーブルを含むデータセットの名前です。TABLE
は、クエリ結果を書き込むテーブルの名前です。QUERY
は、レガシー SQL 構文のクエリです。
例:
次のコマンドを入力すると、mydataset
内の mytable
という名前の宛先テーブルにサイズの大きいクエリの結果が書き込まれます。このデータセットはデフォルト プロジェクトにあります。コマンドに書き込み処理フラグは指定されていないため、宛先テーブルは新規または空である必要があります。それ以外の場合は、Already exists
エラーが返されます。このクエリは、USA Name Data 一般公開データセットからデータを取得します。このクエリは例を示すことのみを目的とします。実際に返される結果セットが最大レスポンス サイズを超えることはありません。
bq query \
--destination_table mydataset.mytable \
--use_legacy_sql=true \
--allow_large_results \
"SELECT
name,
number
FROM
[bigquery-public-data:usa_names.usa_1910_current]
WHERE
gender = 'M'
ORDER BY
number DESC"
大きなクエリ結果を使用して、mydataset
の mytable
という名前の宛先テーブルを上書きするには、次のコマンドを入力します。このデータセットはデフォルト プロジェクトではなく myotherproject
にあります。このコマンドには --replace
フラグが指定されているため、宛先テーブルが上書きされます。
bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=true \
--allow_large_results \
"SELECT
name,
number
FROM
[bigquery-public-data:usa_names.usa_1910_current]
WHERE
gender = 'M'
ORDER BY
number DESC"
mydataset
内の mytable
という名前の宛先テーブルにサイズの大きいクエリの結果を追加するには、次のコマンドを入力します。このデータセットはデフォルト プロジェクトではなく myotherproject
にあります。このコマンドには --append_table
フラグが指定されているため、クエリ結果が宛先テーブルに追加されます。
bq query \
--destination_table myotherproject:mydataset.mytable \
--append_table \
--use_legacy_sql=true \
--allow_large_results \
"SELECT
name,
number
FROM
[bigquery-public-data:usa_names.usa_1910_current]
WHERE
gender = 'M'
ORDER BY
number DESC"
API
サイズの大きいクエリ結果を宛先テーブルに書き込むには、jobs.insert
メソッドを呼び出して query
ジョブを構成し、allowLargeResults
プロパティの値を true
に設定します。destinationTable
プロパティを使用して宛先テーブルを指定します。既存の宛先テーブルに対する書き込み処理を制御するには、writeDisposition
プロパティを構成します。
ジョブリソースの jobReference
セクションにある location
プロパティでロケーションを指定します。
Go
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある Go の設定手順を完了してください。詳細については、BigQuery Go API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
Java
サイズの大きい結果を書き込めるようにするには、QueryJobConfiguration で allow large results を true
に設定し、宛先テーブルを目的の TableId に設定します。
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある Java の設定手順を完了してください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
Node.js
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある Node.js の設定手順を完了してください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
Python
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートにある Python の設定手順を完了してください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。
BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。
Google Cloud コンソールからクエリ結果をダウンロードして保存する
Google Cloud コンソールを使用して SQL クエリを実行した後、結果を別の場所に保存できます。Google Cloud コンソールを使用して、クエリ結果をローカル ファイル、Google スプレッドシート、または Google ドライブにダウンロードできます。最初にクエリ結果を列で並べ替えると、その順序はダウンロードしたデータに保存されます。bq コマンドライン ツールや API で結果をローカル ファイル、Google スプレッドシート、または Google ドライブに保存することはできません。
制限事項
クエリ結果のダウンロードと保存には以下の制限があります。
- クエリ結果は、CSV 形式または改行区切りの JSON 形式でのみローカルにダウンロードできます。
- ネストされたデータや繰り返しデータを含むクエリ結果は、Google スプレッドシートには保存できません。
- Google Cloud コンソールを使用してクエリ結果を Google ドライブに保存するには、結果セットが 1 GB 以下である必要があります。結果がそれより大きい場合は、代わりにテーブルに保存できます。
- クエリ結果をローカル CSV ファイルに保存する場合、最大ダウンロード サイズは 10 MB です。最大ダウンロード サイズは、
tabledata.list
メソッドのレスポンスで返される各行のサイズに基づいており、クエリ結果のスキーマによって異なります。ダウンロードされる CSV ファイルのサイズは一定でないため、ダウンロード サイズの上限を下回ることがあります。 - クエリ結果は Google ドライブに保存する場合は、CSV または改行区切りの JSON 形式にする必要があります。
次のステップ
- プログラムでテーブルを JSON ファイルにエクスポートする方法を確認する。
- クエリジョブの割り当てについて確認する。
- BigQuery のストレージの料金について確認する。