このドキュメントでは、クエリ結果の書き込みまたは保存の方法について説明します。
一時テーブルと永続テーブル
BigQuery は、すべてのクエリ結果を永続テーブルまたは一時テーブルに保存します。
BigQuery は、永続的なテーブルに書き込まれていない一時テーブルを使用してクエリ結果をキャッシュに保存します。テーブルは、特別なデータセット内に作成され、ランダムに名前が付けられます。独自に使用するために、一時テーブルを作成することもできます。詳細については、一時テーブルをご覧ください。
クエリの終了後、一時テーブルは最大 24 時間存在します。テーブルの構造とデータを表示するには、BigQuery コンソールに移動して [クエリ履歴] をクリックし、一時テーブルを作成したクエリを選択します。次に、[宛先テーブル] 行の [一時テーブル] をクリックします。
一時テーブルに対するクエリや一時テーブルの共有はできません。また、標準リストやその他のテーブル操作メソッドを使用しても、テーブルは表示されません。一時テーブルの保管には料金はかかりません。
永続テーブルは、ユーザーがアクセス可能なデータセットに含まれる新規または既存のテーブルです。新しいテーブルにクエリ結果を書き込んだ場合、そのデータの保管に対して料金がかかります。永続テーブルにクエリ結果を書き込む場合、クエリ対象のテーブルは、宛先テーブルが含まれるデータセットと同じロケーションに存在する必要があります。
必要な権限
クエリ結果をテーブルに書き込むには、少なくとも次の権限が付与されている必要があります。
- 新しいテーブルを作成するための
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 のロールと権限について詳しくは、事前定義ロールと権限をご覧ください。
クエリ結果を永続テーブルに書き込む
永続テーブルにクエリ結果を書き込む際は、新しいテーブルの作成、既存テーブルへの結果の追加、既存のテーブルの上書きを行うことができます。クエリ結果を永続テーブルに書き込むには、次の方法があります。
- Cloud Console を使用する。
bq
コマンドライン ツールのbq query
コマンドを使用する。jobs.insert
API メソッドを呼び出してquery
ジョブを構成する。- クライアント ライブラリを使用する。
クエリ結果の書き込み
クエリ結果を永続テーブルに書き込むには、次の手順を使用します。費用を抑えるために、クエリを実行する前にデータをプレビューできます。
Console
Cloud Console で [BigQuery] ページを開きます。
[エクスプローラ] パネルでプロジェクトを展開し、データセットを選択します。
クエリエディタが表示されていない場合は、ウィンドウの右上にある [エディタを表示] をクリックします。
[クエリエディタ] テキスト領域に有効な SQL クエリを入力します。
[展開] をクリックして、[クエリ オプション] を選択します。
[クエリ結果の宛先テーブルを設定する] チェックボックスをオンにします。
[送信先] セクションの [プロジェクト名] と [データセット名] でテーブルを作成するプロジェクトとデータセットをそれぞれ選択し、[テーブル名] に作成するテーブルの名前を設定します。
[宛先テーブルの書き込み設定] セクションで、次のいずれかを選択します。
- [空の場合に書き込む] - テーブルが空の場合にのみ、クエリ結果をテーブルに書き込みます。
- [テーブルに追加する] - クエリ結果を既存のテーブルに追加します。
- [テーブルを上書きする] - 既存のテーブルにクエリ結果を同じ名前で上書きします。
(省略可)[処理を行うロケーション] で [自動選択] をクリックし、ロケーションを選択します。
[クエリを実行] をクリックします。これにより、指定したテーブルにクエリ結果を書き込むクエリジョブが作成されます。
宛先テーブルを指定せずにクエリを実行した場合は、エディタの下にある [結果を保存する] ボタンをクリックすると、キャッシュに保存された結果テーブルを永続テーブルにコピーできます。
SQL
データ定義言語(DDL)ステートメントでは、標準 SQL クエリ構文を使用してテーブルの作成と変更ができます。
詳細については、CREATE TABLE
ステートメントのページと、既存のテーブルからの新しいテーブルの作成の CREATE TABLE
の例をご覧ください。
bq
クエリ結果に基づいて永続テーブルを作成するには、bq query
コマンドを入力して、--destination_table
フラグを指定します。標準 SQL 構文を使用するには、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
は標準 SQL 構文のクエリです。
書き込み処理フラグが指定されていない場合は、デフォルトの動作として、テーブルが空の場合にのみ結果が書き込まれます。テーブルが存在していて空でない場合は、「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 のリファレンス ドキュメントをご覧ください。
Java
このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Java の設定手順を実施してください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。
クエリの結果を永続テーブルに保存するには、QueryJobConfiguration で宛先テーブルを目的の TableId に設定します。
Node.js
このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Node.js の設定手順を実施してください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。
Python
このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Python の手順に従って設定を行ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。
クエリ結果を永続テーブルに保存するには、QueryJobConfig を作成し、宛先を目的の TableReference に設定します。そのジョブ構成を query メソッドに渡します。サイズの大きいクエリ結果を書き込む
通常、クエリには最大レスポンス サイズがあります。実行しようとしているクエリの結果がそのサイズを超過する可能性がある場合は、以下のいずれかを行うことができます。
- 標準 SQL では、クエリ結果用の宛先テーブルを指定します。
- レガシー SQL では、宛先テーブルを指定し、
allowLargeResults
オプションを設定します。
サイズの大きいクエリ結果を格納する宛先テーブルを指定した場合、そのデータの保管に対して料金がかかります。
制限事項
レガシー SQL では、サイズの大きい結果を書き込む際に以下の制限があります。
- 宛先テーブルを指定する必要があります。
- トップレベルに
ORDER BY
、TOP
、またはLIMIT
句は指定できません。指定した場合、クエリ出力の並列計算ができなくなり、allowLargeResults
を使用するメリットがなくなります。 - ウィンドウ関数は、
PARTITION BY
句と組み合わせた場合にのみ、サイズの大きいクエリ結果を返すことができます。
レガシー SQL を使用してサイズの大きい結果を書き込む
レガシー SQL を使用してサイズの大きい結果セットを書き込むには次のようにします。
Console
Cloud Console で [BigQuery] ページを開きます。
[クエリを新規作成] をクリックします。
[クエリエディタ] テキスト領域に有効な SQL クエリを入力します。
#legacySQL
接頭辞を使用するか、クエリ設定で [レガシー SQL を使用] がオンになっていることを確認します。[展開] クリックし、[クエリの設定] を選択します。
[送信先] で [クエリ結果の宛先テーブルを設定する] をオンにします。
[プロジェクト名] で、宛先テーブルを作成するプロジェクトを選択します。
[データセット名] で、テーブルを保存するデータセットを選択します。
[テーブル名] フィールドにテーブル名を入力します。
既存のテーブルにサイズの大きい結果セットを書き込む場合は、[宛先テーブルの書き込み設定] オプションを使用して、宛先テーブルの書き込み処理を制御できます。
- 空の場合に書き込む: テーブルが空の場合にのみ、クエリ結果をテーブルに書き込みます。
- テーブルに追加する: クエリ結果を既存のテーブルに追加します。
- テーブルを上書きする: 既存のテーブルにクエリ結果を同じ名前で上書きします。
[結果サイズ] で [大容量の結果を許可する(サイズ制限なし)] をオンにします。
(省略可)[処理を行うロケーション] で [自動選択] をクリックし、データのロケーションを選択します。
[保存] をクリックしてクエリの設定を更新します。
[実行] をクリックします。これにより、指定したテーブルにサイズの大きい結果セットを書き込むクエリジョブが作成されます。
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 のリファレンス ドキュメントをご覧ください。
Java
サイズの大きい結果を書き込めるようにするには、QueryJobConfiguration で allow large results を true
に設定し、宛先テーブルを目的の TableId に設定します。
Node.js
このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Node.js の設定手順を実施してください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。
Python
このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Python の手順に従って設定を行ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。
Cloud Console からクエリ結果をダウンロードして保存する
Cloud Console を使用して SQL クエリを実行した後、結果を別の場所に保存できます。Cloud Console を使用して、クエリ結果をローカル ファイル、スプレッドシート、ドライブにダウンロードできます。bq
コマンドライン ツールや API では、ローカル ファイル、スプレッドシート、ドライブへの結果保存はサポートされていません。
制限事項
クエリ結果のダウンロードと保存には以下の制限があります。
- クエリ結果は、CSV 形式または改行区切りの JSON 形式でのみローカルにダウンロードできます。
- ネストされたデータや繰り返しデータを含むクエリ結果は、CSV 形式ではダウンロードできません。
- ネストされたデータや繰り返しデータを含むクエリ結果は、スプレッドシートには保存できません。
- Cloud Console を使用してクエリ結果をドライブに保存するには、結果セットを 1 GB 以下にし、行数を 16,000 行以下にする必要があります。結果がこれらの上限を超える場合は、テーブルに保存できます。
- クエリ結果はドライブに保存する場合は、CSV または改行区切りの JSON 形式にする必要があります。
クエリ結果をローカル ファイルにダウンロードする
bq
コマンドライン ツールまたは API では、クエリ結果をローカル ファイルにダウンロードできません。
クエリ結果を CSV または改行区切りの JSON ファイルとしてダウンロードするには Cloud Console を使用します。
Console
Cloud Console で [BigQuery] ページを開きます。
[クエリを新規作成] をクリックします。
[クエリエディタ] テキスト領域に有効な SQL クエリを入力します。
(省略可)処理を行うロケーションを変更するには、[展開] をクリックして、[クエリの設定] を選択します。[処理を行うロケーション] で [自動選択] をクリックし、データのロケーションを選択します。
[実行] をクリックします。
結果が返されたら、[結果を保存する] をクリックして、結果を保存する形式とロケーションを選択します。
このファイルは、ブラウザのデフォルトのダウンロード場所にダウンロードされます。
クエリ結果をドライブに保存する
クエリ結果をドライブに保存することは、bq
コマンドライン ツールや API ではサポートされていません。
クエリ結果をドライブに保存するには、Cloud Console を使用します。
Console
Cloud Console で [BigQuery] ページを開きます。
[クエリエディタ] テキスト領域に有効な SQL クエリを入力します。
[実行] をクリックします。
結果が返されたら、[結果を保存する] をクリックします。
[CSV(Google ドライブ)] または [JSON(Google ドライブ)] を選択します。ドライブに結果を保存する場合、ロケーションは選択できません。結果は常にルートのマイドライブに保存されます。
結果がドライブに保存されるまで数分かかることがあります。結果が保存されると、ファイル名(
bq-results-[TIMESTAMP]-[RANDOM_CHARACTERS].[CSV or JSON]
の形式)を含むポップアップ メッセージが表示されます。ポップアップ メッセージで、[開く] をクリックしてファイルを開くか、ドライブに移動して [マイドライブ] をクリックします。
クエリ結果をスプレッドシートに保存する
クエリ結果をスプレッドシートに保存することは、bq
コマンドライン ツールや API ではサポートされていません。
クエリ結果をスプレッドシートに保存するには、Cloud Console を使用します。
Console
Cloud Console で [BigQuery] ページを開きます。
[クエリを新規作成] をクリックします。
[クエリエディタ] テキスト領域に有効な SQL クエリを入力します。
(省略可)処理を行うロケーションを変更するには、[展開] をクリックして、[クエリの設定] を選択します。[処理を行うロケーション] で [自動選択] をクリックし、データのロケーションを選択します。
[実行] をクリックします。
結果が返されたら、[結果を保存する] をクリックして、[Google スプレッドシート] を選択します。
必要に応じて、プロンプトに従ってご自分の Google アカウントにログインし、[許可する] をクリックして、ドライブの
MY Drive
フォルダへのデータの書き込み権限を BigQuery に付与します。プロンプトに従って設定すると、「BigQuery Client Tools connected to your Google Account」という件名のメールが届きます。このメールに、付与した権限に関する情報と、その権限を削除する手順が記載されています。
結果が保存されると、Cloud Console のクエリ結果の下に、
Saved to Sheets as "results-20190225-103531. Open
のようなメッセージが表示されます。メッセージ内のリンクをクリックしてスプレッドシートに結果を表示するか、My Drive
フォルダに移動してファイルを手動で開きます。スプレッドシートに保存したクエリ結果のファイル名は
results-[DATE]
で始まります([DATE]
はYYYYMMDD
形式で表記された当日の日付)。