クエリ結果の書き込み

このドキュメントでは、クエリ結果の書き込みまたは保存の方法について説明します。

一時テーブルと永続テーブル

BigQuery は、すべてのクエリ結果を永続テーブルまたは一時テーブルに保存します。

  • 一時テーブルは、特別なデータセットに保存されたランダムな名前のテーブルです。一時テーブルは、クエリ結果をキャッシュに保存するために使用されます。一時テーブルの存続期間は約 24 時間です。一時テーブルは共有できず、標準のリストやその他のテーブル操作の方法を使用して表示することもできません。一時テーブルの保管には料金はかかりません。

  • 永続テーブルは、ユーザーがアクセス可能なデータセットに含まれる新規または既存のテーブルです。新しいテーブルにクエリ結果を書き込んだ場合、そのデータの保管に対して料金がかかります。永続テーブルにクエリ結果を書き込む場合、クエリ対象のテーブルは、宛先テーブルが含まれるデータセットと同じロケーションに存在する必要があります。

必要な権限

クエリ結果をテーブルに書き込むには、少なくとも次の権限が付与されている必要があります。

  • 新しいテーブルを作成するための bigquery.tables.create 権限
  • 新しいテーブルへのデータの書き込み、テーブルの上書き、テーブルへのデータの追加を行うための bigquery.tables.updateData 権限
  • クエリジョブを実行するための bigquery.jobs.create 権限

クエリ対象のデータにアクセスするために、bigquery.tables.getData などの権限も必要になる場合があります。

bigquery.tables.create 権限および bigquery.tables.updateData 権限はいずれも、事前定義された以下の Cloud IAM の役割に含まれています。

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

bigquery.jobs.create 権限は、事前定義された以下の Cloud IAM の役割に含まれています。

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

また、bigquery.datasets.create 権限を持つユーザーがデータセットを作成すると、そのデータセットに対する bigquery.dataOwner アクセス権がユーザーに付与されます。bigquery.dataOwner アクセス権があれば、データセット内でテーブルを作成および更新できます。

BigQuery での Cloud IAM の役割と権限については、事前定義された役割と権限をご覧ください。

クエリ結果を永続テーブルに書き込む

永続テーブルにクエリ結果を書き込む際は、新しいテーブルの作成、既存テーブルへの結果の追加、既存のテーブルの上書きを行うことができます。クエリ結果を永続テーブルに書き込むには、次の方法があります。

  • GCP Console または従来の BigQuery ウェブ UI を使用する
  • コマンドライン ツールの bq query コマンドを使用する
  • jobs.insert API メソッドを呼び出して query ジョブを構成する
  • クライアント ライブラリを使用する

クエリ結果の書き込み

永続テーブルにクエリ結果を書き込むには次のようにします。

Console

  1. GCP Console で BigQuery ウェブ UI を開きます。

    GCP Console に移動する

  2. ナビゲーション パネルの [リソース] セクションでプロジェクトを展開し、データセットを選択します。

  3. クエリエディタが表示されていない場合は、ウィンドウの右上にある [エディタを表示] をクリックします。

  4. [クエリエディタ] テキスト領域に有効な SQL クエリを入力します。

  5. エディタの下にある [展開] をクリックし、[クエリの設定] を選択します。

    クエリの設定

  6. [クエリ結果の宛先テーブルを設定する] チェックボックスをオンにします。

    宛先の設定

  7. [送信先] セクションの [プロジェクト名] と [データセット名] でテーブルを作成するプロジェクトとデータセットをそれぞれ選択し、[テーブル名] に作成するテーブルの名前を設定します。

  8. [宛先テーブルの書き込み設定] セクションで、次のいずれかを選択します。

    • [Write if empty] - テーブルが空の場合にのみ、クエリ結果をテーブルに書き込みます。
    • [Append to table] - クエリ結果を既存のテーブルに追加します。
    • [テーブルを上書きする] - 既存のテーブルにクエリ結果を同じ名前で上書きします。
  9. (省略可)[処理を行うロケーション] で [自動選択] をクリックし、ロケーションを選択します。

  10. [実行] をクリックします。これにより、指定したテーブルにクエリ結果を書き込むクエリジョブが作成されます。

宛先テーブルを指定せずにクエリを実行した場合は、エディタの下にある [結果を保存する] ボタンをクリックすると、キャッシュに保存された結果テーブルを永続テーブルにコピーできます。

DDL

データ定義言語(DDL)ステートメントでは、標準 SQL クエリ構文を使用してテーブルの作成と変更ができます。

詳細については、CREATE TABLE ステートメントのページと、既存のテーブルからの新しいテーブルの作成CREATE TABLE の例をご覧ください。

従来の UI

  1. 従来の BigQuery ウェブ UI に移動します。
    従来の BigQuery ウェブ UI に移動

  2. [COMPOSE QUERY] ボタンをクリックします。

  3. [New Query] テキスト領域に有効な SQL クエリを入力します。

  4. [Show Options] をクリックします。

  5. [Destination Table] セクションで、[Select Table] をクリックします。

  6. [Select Destination Table] ダイアログで次の操作を行います。

    1. [Project] で、データセットとテーブルを保存するプロジェクトを選択します。

    2. [Dataset] で、テーブルを保存するデータセットを選択します。

    3. [Table ID] フィールドにテーブル名を入力します。この名前はコピー先データセット内で一意である必要があります。テーブル名は最長 1,024 文字で、a~z、A~Z、0~9、_(アンダースコア文字)のみを使用できます。

    4. [OK] をクリックします。

  7. [Destination Table] セクションの [Write Preference] で、次のいずれかを選択します。

    • [Write if empty] - テーブルが空の場合にのみ、クエリ結果をテーブルに書き込みます。
    • [Append to table] - クエリ結果を既存のテーブルに追加します。
    • [Overwrite table] - 既存のテーブルにクエリ結果を同じ名前で上書きします。
  8. (省略可)[Processing Location] で [Unspecified] をクリックし、データのロケーションを選択します。

  9. [Run query] をクリックします。これにより、指定したテーブルにクエリ結果を書き込むクエリジョブが作成されます。

宛先テーブルを指定せずにクエリを実行した場合は、結果ウィンドウの [Save as Table] ボタンをクリックすると、一時テーブルを永続テーブルにコピーできます。

CLI

クエリ結果に基づいて永続テーブルを作成するには、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 フラグが指定されているため、クエリ結果が宛先テーブルに追加されます。

bq query \
--append \
--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 のリファレンス ドキュメントをご覧ください。

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")

q := client.Query("SELECT 17 as my_col")
q.Location = "US" // Location must match the dataset(s) referenced in query.
q.QueryConfig.Dst = client.Dataset(destDatasetID).Table(destTableID)
job, err := q.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}
if err := status.Err(); err != nil {
	return err
}
it, err := job.Read(ctx)
for {
	var row []bigquery.Value
	err := it.Next(&row)
	if err == iterator.Done {
		break
	}
	if err != nil {
		return err
	}
	fmt.Println(row)
}

Java

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Java の設定手順に従ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。

クエリの結果を永続テーブルに保存するには、QueryJobConfiguration宛先テーブルを目的の TableId に設定します。

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
// String destinationDataset = 'my_destination_dataset';
// String destinationTable = 'my_destination_table';
String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
QueryJobConfiguration queryConfig =
    // Note that setUseLegacySql is set to false by default
    QueryJobConfiguration.newBuilder(query)
        // Save the results of the query to a permanent table.
        .setDestinationTable(TableId.of(destinationDataset, destinationTable))
        .build();

// Print the results.
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  for (FieldValue val : row) {
    System.out.printf("%s,", val.toString());
  }
  System.out.printf("\n");
}

Python

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Python の設定手順に従ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。

クエリ結果を永続テーブルに保存するには、QueryJobConfig を作成し、宛先を目的の TableReference に設定します。そのジョブ構成を query メソッドに渡します。

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'your_dataset_id'

job_config = bigquery.QueryJobConfig()
# Set the destination table
table_ref = client.dataset(dataset_id).table('your_table_id')
job_config.destination = table_ref
sql = """
    SELECT corpus
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY corpus;
"""

# Start the query, passing in the extra configuration.
query_job = client.query(
    sql,
    # Location must match that of the dataset(s) referenced in the query
    # and of the destination table.
    location='US',
    job_config=job_config)  # API request - starts the query

query_job.result()  # Waits for the query to finish
print('Query results loaded to table {}'.format(table_ref.path))

サイズの大きいクエリ結果を書き込む

通常、クエリには最大レスポンス サイズがあります。実行しようとしているクエリの結果がそのサイズを超過する可能性がある場合は、以下を行うことができます。

  • 標準 SQL では、クエリ結果用の宛先テーブルを指定します。
  • レガシー SQL では、宛先テーブルを指定し、allowLargeResults オプションを設定します。

サイズの大きいクエリ結果を格納する宛先テーブルを指定した場合、そのデータの保管に対して料金がかかります。

制限事項

レガシー SQL では、サイズの大きい結果を書き込む際に以下の制限があります。

  • 宛先テーブルを指定する必要があります。
  • トップレベルに ORDER BYTOP、または LIMIT 句は指定できません。指定した場合、クエリ出力の並列計算ができなくなり、allowLargeResults を使用するメリットがなくなります。
  • ウィンドウ関数は、PARTITION BY 句と組み合わせた場合にのみ、サイズの大きいクエリ結果を返すことができます。

レガシー SQL を使用してサイズの大きい結果を書き込む

レガシー SQL を使用してサイズの大きい結果セットを書き込むには次のようにします。

Console

  1. GCP Console で BigQuery ウェブ UI を開きます。
    GCP Console に移動する

  2. [クエリを新規作成] をクリックします。

  3. [クエリエディタ] テキスト領域に有効な SQL クエリを入力します。#legacySQL 接頭辞を使用するか、クエリ設定で [レガシー SQL を使用] がオンになっていることを確認します。

  4. [展開] クリックし、[クエリの設定] を選択します。

    クエリの設定

  5. [送信先] で [クエリ結果の宛先テーブルを設定する] をオンにします。

    宛先の設定

  6. [プロジェクト名] で、宛先テーブルを作成するプロジェクトを選択します。

  7. [データセット名] で、テーブルを保存するデータセットを選択します。

  8. [テーブル名] フィールドにテーブル名を入力します。

  9. 既存のテーブルにサイズの大きい結果セットを書き込む場合は、[宛先テーブルの書き込み設定] オプションを使用して、宛先テーブルの書き込み処理を制御できます。

    • [空の場合に書き込む] - テーブルが空の場合にのみ、クエリ結果をテーブルに書き込みます。
    • [テーブルに追加する] - クエリ結果を既存のテーブルに追加します。
    • [テーブルを上書きする] - 既存のテーブルにクエリ結果を同じ名前で上書きします。

    テーブルを上書きする

  10. [結果サイズ] で [大容量の結果を許可する(サイズ制限なし)] をオンにします。

    クエリ結果のサイズ

  11. (省略可)[処理を行うロケーション] で [自動選択] をクリックし、データのロケーションを選択します。

    クエリ処理のロケーション

  12. [保存] をクリックしてクエリの設定を更新します。

  13. [実行] をクリックします。これにより、指定したテーブルにサイズの大きい結果セットを書き込むクエリジョブが作成されます。

従来の UI

  1. BigQuery ウェブ UI に移動します。
    BigQuery ウェブ UI に移動

  2. [COMPOSE QUERY] ボタンをクリックします。

  3. [New Query] テキスト領域に有効な BigQuery SQL クエリを入力します。#legacySQL 接頭辞を使用するか、クエリ オプションで [Use Legacy SQL] がオンになっていることを確認します。

  4. [Show Options] をクリックします。

  5. [Destination Table] で [Select Table] をクリックします。

  6. [Select Destination Table] ダイアログで次の操作を行います。

    1. [Project] で、宛先テーブルを作成するプロジェクトを選択します。

    2. [Dataset] で、そのテーブルを格納するデータセットを選択します。

    3. [Table ID] フィールドにテーブル名を入力します。

    4. [OK] をクリックします。

  7. 既存のテーブルにサイズの大きい結果セットを書き込む場合は、[Write Preference] オプションを使用して、宛先テーブルに対する書き込み処理を制御できます。

    • [Write if empty] - テーブルが空の場合にのみ、クエリ結果をテーブルに書き込みます。
    • [Append to table] - クエリ結果を既存のテーブルに追加します。
    • [Overwrite table] - 既存のテーブルにクエリ結果を同じ名前で上書きします。
  8. [Results Size] で [Allow Large Results] をオンにします。

    サイズの大きい結果を許可するオプション

  9. (省略可)[Processing Location] で [Unspecified] をクリックし、データのロケーションを選択します。

  10. [Run Query] をクリックします。これにより、指定したテーブルにサイズの大きい結果セットを書き込むクエリジョブが作成されます。

CLI

--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"

大きなクエリ結果を使用して、mydatasetmytable という名前の宛先テーブルを上書きするには、次のコマンドを入力します。このデータセットはデフォルト プロジェクトではなく 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 フラグが指定されているため、クエリ結果が宛先テーブルに追加されます。

bq query \
--destination_table myotherproject:mydataset.mytable \
--append \
--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 のリファレンス ドキュメントをご覧ください。

q := client.Query(
	"SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus;")
q.UseLegacySQL = true
q.AllowLargeResults = true
q.QueryConfig.Dst = client.Dataset(dstDatasetID).Table(dstTableID)
job, err := q.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}
if err := status.Err(); err != nil {
	return err
}
it, err := job.Read(ctx)
for {
	var row []bigquery.Value
	err := it.Next(&row)
	if err == iterator.Done {
		break
	}
	if err != nil {
		return err
	}
	fmt.Println(row)
}

Java

サイズの大きい結果を書き込めるようにするには、QueryJobConfigurationallow large resultstrue に設定し、宛先テーブルを目的の TableId に設定します。

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
// String destinationDataset = 'my_destination_dataset';
// String destinationTable = 'my_destination_table';
String query = "SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus;";
QueryJobConfiguration queryConfig =
    // To use legacy SQL syntax, set useLegacySql to true.
    QueryJobConfiguration.newBuilder(query)
        .setUseLegacySql(true)
        // Save the results of the query to a permanent table.
        .setDestinationTable(TableId.of(destinationDataset, destinationTable))
        // Allow results larger than the maximum response size.
        // If true, a destination table must be set.
        .setAllowLargeResults(true)
        .build();

// Print the results.
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  for (FieldValue val : row) {
    System.out.printf("%s,", val.toString());
  }
  System.out.printf("\n");
}

Python

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Python の設定手順に従ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'your_dataset_id'

job_config = bigquery.QueryJobConfig()
# Set use_legacy_sql to True to use legacy SQL syntax.
job_config.use_legacy_sql = True
# Set the destination table
table_ref = client.dataset(dataset_id).table("your_table_id")
job_config.destination = table_ref
job_config.allow_large_results = True
sql = """
    SELECT corpus
    FROM [bigquery-public-data:samples.shakespeare]
    GROUP BY corpus;
"""
# Start the query, passing in the extra configuration.
query_job = client.query(
    sql,
    # Location must match that of the dataset(s) referenced in the query
    # and of the destination table.
    location="US",
    job_config=job_config,
)  # API request - starts the query

query_job.result()  # Waits for the query to finish
print("Query results loaded to table {}".format(table_ref.path))

クエリ結果をダウンロードして保存する

SQL クエリの実行後、結果をローカルマシンのファイルにダウンロードできます。また、結果を Google ドライブ、Google スプレッドシートまたは BigQuery の永続的なテーブルに保存することもできます。

制限事項

クエリ結果のダウンロードと保存には以下の制限があります。

  • 従来の BigQuery ウェブ UI の場合、クエリ結果はローカル ファイルまたは Google スプレッドシートにダウンロードできます。結果を Google ドライブにダウンロードするには、GCP Console を使用してください。
  • 従来の BigQuery ウェブ UI を使用してクエリ結果をダウンロードするには、結果セットに含まれている行が 16,000 行未満であり、かつサイズが 10 MB 以下である必要があります。結果が 10 MB を超えているか 16,000 行以上ある場合は、テーブルにその結果を保存できます。
  • クエリ結果は、CSV 形式または改行区切りの JSON 形式でのみローカルにダウンロードできます。
  • ネストされたデータや繰り返しデータを含むクエリ結果は、CSV 形式ではダウンロードできません。
  • ネストされたデータや繰り返しデータを含むクエリ結果は、Google スプレッドシートには保存できません。
  • 従来の BigQuery ウェブ UI を使用してクエリ結果を Google スプレッドシートに保存する場合は、結果セットに含まれている行が 16,000 行未満であり、かつサイズが 10 MB 以下である必要があります。結果が 10 MB を超えているか 16,000 行以上ある場合は、代わりにその結果をテーブルに保存できます。
  • コマンドライン ツールまたは API では、結果をローカル ファイル、Google スプレッドシートまたは Google ドライブに保存することはできません。
  • GCP Console を使用してクエリ結果を Google ドライブに保存するには、結果セットが 1 GB 以下である必要があります。結果が 1 GB を超える場合は、テーブルに保存します。
  • クエリ結果は Google ドライブに保存する場合は、CSV または改行区切りの JSON 形式にする必要があります。

クエリ結果をローカル ファイルにダウンロードする

コマンドライン ツールまたは API では、クエリ結果をローカル ファイルにダウンロードできません。

ウェブ UI を使用してクエリ結果を CSV または改行区切りの JSON ファイルとしてダウンロードするには次のようにします。

Console

  1. GCP Console で BigQuery ウェブ UI を開きます。
    GCP Console に移動する

  2. [クエリを新規作成] をクリックします。

  3. [クエリエディタ] テキスト領域に有効な SQL クエリを入力します。

  4. (省略可)処理を行うロケーションを変更するには、[展開] をクリックして、[クエリの設定] を選択します。[処理を行うロケーション] で [自動選択] をクリックし、データのロケーションを選択します。

  5. [実行] をクリックします。

  6. 結果が返されたら、[結果を保存する] をクリックして、結果を保存する形式とロケーションを選択します。

    このファイルは、ブラウザのデフォルトのダウンロード場所にダウンロードされます。

従来の UI

  1. BigQuery ウェブ UI に移動します。
    BigQuery ウェブ UI に移動

  2. [COMPOSE QUERY] ボタンをクリックします。

  3. [New Query] テキスト領域に有効な SQL クエリを入力します。

  4. [Show Options] をクリックします。

  5. (省略可)[Processing Location] で [Unspecified] をクリックし、データのロケーションを選択します。

  6. [Run Query] をクリックします。

  7. 結果が返されたら、クエリ結果の上にある [Download as CSV] ボタンまたは [Download as JSON] ボタンをクリックします。

    ダウンロード ボタンと保存ボタンのスクリーンショット

    このファイルは、ブラウザのデフォルトのダウンロード場所にダウンロードされます。

クエリ結果を Google ドライブに保存する

コマンドライン ツール、API または従来の BigQuery ウェブ UI では、クエリ結果を Google ドライブに保存できません。

GCP Console を使用してクエリ結果を Google ドライブに保存するには、次の手順を行います。

Console

  1. GCP Console で BigQuery ウェブ UI を開きます。

    GCP Console に移動する

  2. [クエリエディタ] テキスト領域に有効な SQL クエリを入力します。

  3. [実行] をクリックします。

  4. 結果が返されたら、[結果を保存する] をクリックします。

    [結果を保存する] ボタンのスクリーンショット

  5. [CSV(Google ドライブ)] または [JSON(Google ドライブ)] を選択します。Google ドライブに結果を保存する場合、ロケーションは選択できません。結果は常にルートのマイドライブに保存されます。

  6. 結果が Google ドライブに保存されるまで数分かかることがあります。結果が保存されると、ファイル名を含むポップアップ メッセージが表示されます - bq-results-[TIMESTAMP]-[RANDOM_CHARACTERS].[CSV or JSON]

    [結果を保存する] ボタンのスクリーンショット

  7. ポップアップ メッセージで、[開く] をクリックしてファイルを開くか、Google ドライブに移動して [マイドライブ] をクリックします。

クエリ結果をテーブルに保存する

クエリ結果をテーブルとして保存するには次のようにします。

Console

  1. GCP Console で BigQuery ウェブ UI を開きます。

    GCP Console に移動する

  2. ナビゲーション パネルの [リソース] セクションでプロジェクトを展開し、データセットを選択します。

  3. クエリエディタが表示されていない場合は、ウィンドウの右上にある [エディタを表示] をクリックします。

  4. [クエリエディタ] テキスト領域に有効な SQL クエリを入力します。

  5. エディタの下にある [展開] をクリックし、[クエリの設定] を選択します。

    クエリの設定

  6. [クエリ結果の宛先テーブルを設定する] チェックボックスをオンにします。

    宛先の設定

  7. [送信先] セクションの [プロジェクト名] と [データセット名] でテーブルを作成するプロジェクトとデータセットをそれぞれ選択し、[テーブル名] に作成するテーブルの名前を設定します。

  8. [宛先テーブルの書き込み設定] セクションで、次のいずれかを選択します。

    • [空の場合に書き込む] - テーブルが空の場合にのみ、クエリ結果をテーブルに書き込みます。
    • [テーブルに追加する] - クエリ結果を既存のテーブルに追加します。
    • [テーブルを上書きする] - 既存のテーブルにクエリ結果を同じ名前で上書きします。
  9. (省略可)[処理を行うロケーション] で [自動選択] をクリックし、ロケーションを選択します。

  10. [実行] をクリックします。これにより、指定したテーブルにクエリ結果を書き込むクエリジョブが作成されます。

宛先テーブルを指定せずにクエリを実行した場合は、エディタの下にある [結果を保存する] ボタンをクリックすると、キャッシュに保存された結果テーブルを永続テーブルにコピーできます。

DDL

データ定義言語(DDL)ステートメントでは、標準 SQL クエリ構文を使用してテーブルの作成と変更ができます。

詳細については、CREATE TABLE ステートメントのページと、既存のテーブルからの新しいテーブルの作成CREATE TABLE の例をご覧ください。

従来の UI

  1. 従来の BigQuery ウェブ UI に移動します。
    従来の BigQuery ウェブ UI に移動

  2. [COMPOSE QUERY] ボタンをクリックします。

  3. [New Query] テキスト領域に有効な SQL クエリを入力します。

  4. [Show Options] をクリックします。

  5. [Destination Table] セクションで、[Select Table] をクリックします。

  6. [Select Destination Table] ダイアログで次の操作を行います。

    1. [Project] で、データセットとテーブルを保存するプロジェクトを選択します。

    2. [Dataset] で、テーブルを保存するデータセットを選択します。

    3. [Table ID] フィールドにテーブル名を入力します。この名前はコピー先データセット内で一意である必要があります。テーブル名は最長 1,024 文字で、a~z、A~Z、0~9、_(アンダースコア文字)のみを使用できます。

    4. [OK] をクリックします。

  7. [Destination Table] セクションの [Write Preference] で、次のいずれかを選択します。

    • [Write if empty] - テーブルが空の場合にのみ、クエリ結果をテーブルに書き込みます。
    • [Append to table] - クエリ結果を既存のテーブルに追加します。
    • [Overwrite table] - 既存のテーブルにクエリ結果を同じ名前で上書きします。
  8. (省略可)[Processing Location] で [Unspecified] をクリックし、データのロケーションを選択します。

  9. [Run query] をクリックします。これにより、指定したテーブルにクエリ結果を書き込むクエリジョブが作成されます。

宛先テーブルを指定せずにクエリを実行した場合は、結果ウィンドウの [Save as Table] ボタンをクリックすると、一時テーブルを永続テーブルにコピーできます。

CLI

クエリ結果に基づいて永続テーブルを作成するには、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 フラグが指定されているため、クエリ結果が宛先テーブルに追加されます。

bq query \
--append \
--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 のリファレンス ドキュメントをご覧ください。

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")

q := client.Query("SELECT 17 as my_col")
q.Location = "US" // Location must match the dataset(s) referenced in query.
q.QueryConfig.Dst = client.Dataset(destDatasetID).Table(destTableID)
job, err := q.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}
if err := status.Err(); err != nil {
	return err
}
it, err := job.Read(ctx)
for {
	var row []bigquery.Value
	err := it.Next(&row)
	if err == iterator.Done {
		break
	}
	if err != nil {
		return err
	}
	fmt.Println(row)
}

Java

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Java の設定手順に従ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。

クエリの結果を永続テーブルに保存するには、QueryJobConfiguration宛先テーブルを目的の TableId に設定します。

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
// String destinationDataset = 'my_destination_dataset';
// String destinationTable = 'my_destination_table';
String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
QueryJobConfiguration queryConfig =
    // Note that setUseLegacySql is set to false by default
    QueryJobConfiguration.newBuilder(query)
        // Save the results of the query to a permanent table.
        .setDestinationTable(TableId.of(destinationDataset, destinationTable))
        .build();

// Print the results.
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  for (FieldValue val : row) {
    System.out.printf("%s,", val.toString());
  }
  System.out.printf("\n");
}

Python

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Python の設定手順に従ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。

クエリ結果を永続テーブルに保存するには、QueryJobConfig を作成し、宛先を目的の TableReference に設定します。そのジョブ構成を query メソッドに渡します。

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'your_dataset_id'

job_config = bigquery.QueryJobConfig()
# Set the destination table
table_ref = client.dataset(dataset_id).table('your_table_id')
job_config.destination = table_ref
sql = """
    SELECT corpus
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY corpus;
"""

# Start the query, passing in the extra configuration.
query_job = client.query(
    sql,
    # Location must match that of the dataset(s) referenced in the query
    # and of the destination table.
    location='US',
    job_config=job_config)  # API request - starts the query

query_job.result()  # Waits for the query to finish
print('Query results loaded to table {}'.format(table_ref.path))

クエリ結果を Google スプレッドシートに保存する

コマンドライン ツールや API では、Google スプレッドシートに結果を保存できません。

ウェブ UI を使用してクエリ結果を Google スプレッドシートに保存するには、次の手順を行います。

Console

  1. GCP Console で BigQuery ウェブ UI を開きます。
    BigQuery ウェブ UI に移動

  2. [クエリを新規作成] をクリックします。

  3. [クエリエディタ] テキスト領域に有効な SQL クエリを入力します。

  4. (省略可)処理を行うロケーションを変更するには、[展開] をクリックして、[クエリの設定] を選択します。[処理を行うロケーション] で [自動選択] をクリックし、データのロケーションを選択します。

  5. [実行] をクリックします。

  6. 結果が返されたら、[結果を保存する] をクリックして、[Google スプレッドシート] を選択します。

  7. 必要に応じて、プロンプトに従ってご自分の Google アカウントにログインし、[許可する] をクリックして、Google ドライブの MY Drive フォルダへのデータの書き込み権限を BigQuery に付与します。

    プロンプトに従って設定すると、「BigQuery Client Tools connected to your Google Account」という件名のメールが届きます。このメールに、付与した権限に関する情報と、その権限を削除する手順が記載されています。

  8. 結果が保存されると、コンソールの BigQuery ウェブ UI のクエリ結果の下に、Saved to Sheets as "results-20190225-103531. Open というメッセージが表示されます。メッセージ内のリンクをクリックして Google スプレッドシートに結果を表示するか、My Drive フォルダに移動してファイルを手動で開きます。

    Google スプレッドシートに保存したクエリ結果のファイル名は results-[DATE] で始まります([DATE]YYYYMMDD 形式で表記された当日の日付)。

従来の UI

  1. BigQuery ウェブ UI に移動します。
    BigQuery ウェブ UI に移動

  2. [COMPOSE QUERY] ボタンをクリックします。

  3. [New Query] テキスト領域に有効な SQL クエリを入力します。

  4. [Show Options] をクリックします。

  5. (省略可)[Processing Location] で [Unspecified] をクリックし、データのロケーションを選択します。

  6. [Run Query] をクリックします。

  7. 結果が返されたら、クエリ結果の上にある [Save to Google Sheets] ボタンをクリックします。

    ダウンロード ボタンと保存ボタンのスクリーンショット

  8. 必要に応じて、プロンプトに従ってご自分の Google アカウントにログインし、[許可する] をクリックして、Google ドライブの MY Drive フォルダへのデータの書き込み権限を BigQuery に付与します。

    プロンプトに従って設定すると、「BigQuery Client Tools connected to your Google Account」という件名のメールが届きます。このメールに、付与した権限に関する情報と、その権限を削除する手順が記載されています。

  9. 結果が保存されると、従来の BigQuery ウェブ UI のクエリ結果の上に Results saved to Google Sheets. Click to view というメッセージが表示されます。メッセージ内のリンクをクリックして Google スプレッドシートに結果を表示するか、MY Drive フォルダに移動してファイルを手動で開きます。

    Google スプレッドシートに保存したクエリ結果のファイル名は results-[DATE] で始まります([DATE]YYYYMMDD 形式で表記された当日の日付)。

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

ご不明な点がありましたら、Google のサポートページをご覧ください。