クエリ結果の書き込み

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

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

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

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

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

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

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

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

必要な権限

永続テーブルにクエリ結果を書き込むために必要な権限は、データの書き込み処理によって異なります。

新しいテーブルにクエリ結果を書き込む権限

新しいテーブルにクエリ結果を書き込む場合は、データセット レベルの WRITER アクセス権が付与されているか、bigquery.tables.create 権限を含むプロジェクト レベルの IAM 役割が割り当てられている必要があります。次の事前定義されたプロジェクト レベルの IAM 役割には、bigquery.tables.create 権限が含まれています。

また、bigquery.user 役割には bigquery.datasets.create 権限が含まれているため、bigquery.user 役割に割り当てられたユーザーは、自分が作成した任意のデータセット内にテーブルを作成できます。bigquery.user 役割に割り当てられているユーザーがデータセットを作成すると、そのユーザーには、作成したデータセットへの OWNER アクセス権が付与されます。 データセットへの OWNER アクセス権が付与されたユーザーは、そのデータセットと、そこに含まれるすべてのテーブルを完全に制御できます。

BigQuery での IAM 役割と権限の詳細については、アクセス制御をご覧ください。データセット レベルの役割の詳細については、データセットに対する基本の役割をご覧ください。

データを上書きまたは追加する権限

クエリ結果を使用して既存のテーブルを上書きする、または既存のテーブルにデータを追加する場合は、データセット レベルで WRITER アクセス権が付与されているか、bigquery.tables.updateData 権限を含むプロジェクト レベルの IAM 役割が割り当てられている必要があります。次の事前定義されたプロジェクト レベルの IAM 役割には、bigquery.tables.updateData 権限が含まれています。

また、bigquery.user 役割には bigquery.datasets.create 権限が含まれているため、bigquery.user 役割に割り当てられたユーザーは、自分がデータセット内に作成した任意のテーブルにデータを上書きまたは追加できます。bigquery.user 役割に割り当てられているユーザーがデータセットを作成すると、そのユーザーには、作成したデータセットへの OWNER アクセス権が付与されます。データセットへの OWNER アクセス権が付与されたユーザーは、そのデータセットと、そこに含まれるすべてのテーブルを完全に制御できます。

BigQuery での IAM 役割と権限の詳細については、アクセス制御をご覧ください。データセット レベルの役割の詳細については、データセットに対する基本の役割をご覧ください。

クエリ結果の書き込み

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

Console

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

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

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

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

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

    クエリの設定

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

    宛先の設定

  7. [送信先] セクションで、テーブルを作成する適切な [プロジェクト名] と [データセット名] を選択し、[テーブル名] を選択します。

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

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

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

クエリを実行する前に宛先テーブルを指定するのを忘れた場合は、エディタの下の [ビューを保存] ボタンをクリックして一時テーブルを永続テーブルにコピーできます。

従来の UI

オプション 1: DDL ステートメントを使用する

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

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

オプション 2: 従来のウェブ 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 データ一般公開データセットからデータを取得します。

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

bq --location=US query --destination_table myotherproject:mydataset.mytable --append --use_legacy_sql=false 'SELECT name,number FROM `bigquery-public-data.usa_names.usa_1910_current` WHERE gender = "M" ORDER BY number DESC'

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 BYTOPLIMIT 句は指定できません。指定した場合、クエリ出力の並列計算ができなくなり、allowLargeResults を使用するメリットがなくなります。
  • ウィンドウ関数は、PARTITION BY 句と組み合わせた場合にのみ、サイズの大きいクエリ結果を返すことができます。

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

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

Console

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

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

  3. クエリエディタのテキスト領域に、有効な BigQuery 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 - テーブルが空の場合にのみ、クエリ結果をテーブルに書き込みます。
    • [テーブルに追加する] - クエリ結果を既存のテーブルに追加します。
    • Overwrite table - 既存のテーブルにクエリ結果を同じ名前で上書きします。
  8. [Results Size] で [Allow Large Results] をオンにします。

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

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

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

コマンドライン

--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_NAME] --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 データ一般公開データセットからデータを取得します。このクエリは例を示すことのみを目的とします。実際に返される結果セットは最大レスポンス サイズを超えません。

bq --location=US 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 --location=US 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 --location=US 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 ジョブを構成し、configuration.query.allowLargeResults プロパティの値を true に設定します。configuration.query.destinationTable プロパティを使用して宛先テーブルを指定します。既存の宛先テーブルに対する書き込み処理を制御するには、configuration.query.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

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

サイズの大きい結果を書き込めるようにするには、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 を開きます。
    BigQuery ウェブ UI に移動

  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 を開きます。

    BigQuery ウェブ UI に移動

  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 を開きます。
    BigQuery ウェブ UI に移動

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

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

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

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

    クエリの設定

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

    宛先の設定

  7. [送信先] セクションで、テーブルを作成する適切な [プロジェクト名] と [データセット名] を選択し、[テーブル名] を選択します。

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

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

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

クエリを実行する前に宛先テーブルを指定するのを忘れた場合は、エディタの下の [ビューを保存] ボタンをクリックして一時テーブルを永続テーブルにコピーできます。

従来の UI

オプション 1: DDL ステートメントを使用する

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

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

オプション 2: 従来のウェブ 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 - テーブルが空の場合にのみ、クエリ結果をテーブルに書き込みます。
    • [テーブルに追加する] - クエリ結果を既存のテーブルに追加します。
    • 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 データ一般公開データセットからデータを取得します。

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

bq --location=US query --destination_table myotherproject:mydataset.mytable --append --use_legacy_sql=false 'SELECT name,number FROM `bigquery-public-data.usa_names.usa_1910_current` WHERE gender = "M" ORDER BY number DESC'

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. 結果が保存されると、Console の 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 アカウントにログインし、[Allow] をクリックして、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 のサポートページをご覧ください。