Cloud Storage データのクエリ

BigQuery では、次の形式の Cloud Storage データのクエリがサポートされています。

• カンマ区切り値（CSV）
• JSON（改行区切り）
• Avro
• ORC
• Parquet
• Datastore エクスポート
• Firestore エクスポート

BigQuery では、次のストレージ クラスの Cloud Storage データにクエリを実行できます。

• Standard
• Nearline
• Coldline
• アーカイブ

Cloud Storage 外部データソースに対してクエリを実行するには、データの Cloud Storage URI パスを指定し、データソースを参照するテーブルを作成します。Cloud Storage のデータソースの参照に使用するテーブルは、永続テーブルまたは一時テーブルです。

Cloud Storage に保存されているデータに対してクエリを実行する場合は、必ずデータセットと Cloud Storage バケットの場所に関する考慮事項を参照してください。

Cloud Storage URI の取得

Cloud Storage データソースを使用して外部テーブルを作成するには、Cloud Storage URI を指定する必要があります。

Cloud Storage URI は、バケット名とオブジェクト（ファイル名）で構成されます。たとえば、Cloud Storage バケットの名前が mybucket でデータファイルの名前が myfile.csv の場合、バケットの URI は gs://mybucket/myfile.csv になります。データが複数のファイルに分かれている場合は、URI にワイルドカードを使用できます。詳細については、Cloud Storage のリクエスト URI をご覧ください。

BigQuery は、最初のダブル スラッシュ以降に複数の連続スラッシュが含まれるソース URI をサポートしていません。Cloud Storage では、オブジェクト名に複数の連続スラッシュ（"/"）文字を含めることができます。一方、BigQuery では、複数の連続スラッシュは単一のスラッシュに変換されます。たとえば、gs://bucket/my//object//name というソース URI は Cloud Storage では有効ですが、BigQuery では機能しません。

Cloud Storage URI を取得するには:

1. Cloud Storage Console を開きます。

   Cloud Storage Console

2. ソースデータを含むオブジェクト（ファイル）の場所に移動します。

3. Cloud Storage Console の上部に、オブジェクトのパスが表示されます。URI を作成するには、gs://bucket/file を適切なパス（例: gs://mybucket/myfile.json）に置き換えます。bucket は Cloud Storage バケット名で、file はデータを含むオブジェクト（ファイル）の名前です。

注: gsutil ls コマンドを使用してバケットやオブジェクトを一覧表示することもできます。

外部の永続テーブルと一時テーブル

永続テーブルまたは一時テーブルを使用すると、BigQuery で外部のデータソースに対してクエリを行うことができます。永続テーブルは、データセット内に作成され、外部データソースにリンクされるテーブルです。テーブルは永続的であるため、アクセス制御を行い、基礎となる外部データソースにアクセスできる他のユーザーとテーブルを共有できます。テーブルに対するクエリはいつでも実行できます。

一時テーブルを使用して外部データソースに対してクエリを実行する場合には、クエリを含むコマンドを送信し、外部データソースにリンクする一時テーブルを作成します。一時テーブルを使用する場合、BigQuery データセット内にはテーブルを作成しません。テーブルはデータセットに永続的に保存されないため、このテーブルを他のユーザーと共有することはできません。一時テーブルを使用して外部データソースに対するクエリ行なう方法は、外部データに 1 回限りのアドホック クエリを実行する場合、あるいは抽出、変換、読み込み（ETL）処理を行う場合に便利です。

外部の永続テーブルを使用して Cloud Storage データに対してクエリを実行する

必要な権限とスコープ

永続テーブルを使用して Cloud Storage の外部データに対してクエリを実行する場合、プロジェクト レベル以上でクエリジョブを実行する権限、外部データを指すテーブルを作成できる権限、テーブルにアクセスできる権限が必要です。外部データが Cloud Storage に保存されている場合、Cloud Storage バケット内のデータに対するアクセス権も必要です。

BigQuery の権限

BigQuery で外部テーブルを作成およびクエリするには、少なくとも以下の権限が必要です。

• bigquery.tables.create
• bigquery.tables.getData
• bigquery.jobs.create

bigquery.tables.create 権限および bigquery.tables.getData 権限はいずれも、事前定義された以下の IAM ロールに含まれています。

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

次の事前定義済みの IAM ロールには bigquery.jobs.create 権限が含まれています。

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

また、bigquery.datasets.create 権限を持つユーザーがデータセットを作成すると、そのデータセットに対する bigquery.dataOwner アクセス権がユーザーに付与されます。bigquery.dataOwner アクセスを使用すると、ユーザーはデータセット内に外部テーブルを作成できますが bigquery.jobs.create データを照会するには権限が必要です。

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

Cloud Storage の権限

Cloud Storage バケット内の外部データに対してクエリを行なうには、storage.objects.get 権限が付与されている必要があります。URI のワイルドカードを使用する場合は storage.objects.list 権限も必要です。

事前定義の IAM ロール storage.objectViewer が付与されると、storage.objects.get 権限と storage.objects.list 権限が両方とも与えられます。

Compute Engine インスタンスのスコープ

Compute Engine インスタンスを作成するときに、インスタンスに対するスコープのリストを指定できます。このスコープは、Cloud Storage など Google Cloud プロダクトに対するインスタンスのアクセスを制御します。VM 上で実行されるアプリケーションは、サービス アカウントを使用して Google Cloud API を呼び出します。

Compute Engine インスタンスを Compute Engine のデフォルトのサービス アカウントとして実行するように設定するときに、そのサービス アカウントが Cloud Storage データソースにリンクされた外部テーブルにアクセスする場合、インスタンスに Cloud Storage への読み取り専用アクセス権が必要です。Compute Engine のデフォルトのサービス アカウントには、https://www.googleapis.com/auth/devstorage.read_only スコープが自動的に付与されます。固有のサービス アカウントを作成する場合は、インスタンスに Cloud Storage 読み取り専用スコープを適用してください。

Compute Engine インスタンスへのスコープの適用方法については、インスタンスのサービス アカウントとアクセス スコープを変更するをご覧ください。Compute Engine サービス アカウントの詳細については、サービス アカウントをご覧ください。

外部の永続テーブルを作成してクエリを実行する

外部のデータソースにリンクされた永続テーブルは、次の方法で作成します。

• Cloud Console または従来の BigQuery ウェブ UI を使用する
• bq コマンドライン ツールの mk コマンドを使用する
• tables.insert API メソッドを使用する際に ExternalDataConfiguration を作成
• クライアント ライブラリの使用

永続テーブルを使用して外部データソースに対してクエリを実行するには、BigQuery データセットに外部データソースにリンクするテーブルを作成します。データは BigQuery テーブルに保存されません。テーブルは永続的であるため、アクセス制御を行い、基礎となる外部データソースにアクセスできる他のユーザーとテーブルを共有できます。

BigQuery で永続外部テーブルを作成するときに、次の 3 つの方法でスキーマ情報を指定できます。

• tables.insert API メソッドを使用して永続外部テーブルを作成するには、スキーマ定義と ExternalDataConfiguration を含むテーブル リソースを作成します。autodetect パラメータを true に設定して、サポートされているデータソースのスキーマ自動検出を有効にします。
• bq コマンドライン ツールを使用して永続外部テーブルを作成する場合は、テーブル定義ファイルを使用するか、独自のスキーマ ファイルを作成して使用するか、bq ツールを使用してスキーマをインライン入力します。スキーマの自動検出がサポートされているデータソースに対しては、テーブル定義ファイルの作成時にこの機能を有効にできます。
• Console または従来の BigQuery ウェブ UI を使用して永続外部テーブルを作成する場合は、テーブル スキーマを手動で入力するか、サポートされているデータソースに対するスキーマの自動検出を利用できます。

外部テーブルを作成するには:

Console

1. Cloud Console で [BigQuery] ページを開きます。
   [BigQuery] ページに移動
2. ナビゲーション パネルの [リソース] セクションでプロジェクトを展開し、データセットを選択します。
3. ウィンドウの右側にある [テーブルを作成] をクリックします。

4. [テーブルの作成] ページの [ソース] セクションで、次の操作を行います。

   • [テーブルの作成元] で [Google Cloud Storage] を選択します。

   • [Cloud Storage バケットからファイルを選択] フィールドで、ファイルまたは Cloud Storage バケットを参照するか、「Cloud Storage URI」を入力します。Cloud Console で複数の URI を指定することはできませんが、ワイルドカードはサポートされています。Cloud Storage バケットは、作成するテーブルを含むデータセットと同じロケーションに存在している必要があります。

     

   • [ファイル形式] でデータの形式を選択します。Cloud Storage の外部データに有効なフォーマットは次のとおりです。

     • カンマ区切り値（CSV）
     • JSON（改行区切り）
     • Avro
     • Datastore バックアップ（Firestore でも使用）

5. [テーブルの作成] ページの [送信先] セクションで、次の操作を行います。

   • [データセット名] で、適切なデータセットを選択します。

     

   • [テーブルタイプ] が [外部テーブル] に設定されていることを確認します。

   • [テーブル名] に、BigQuery で作成するテーブルの名前を入力します。

6. [Schema] セクションでスキーマ定義を入力します。

   • JSON または CSV ファイルの場合、[自動検出] オプションをオンにするとスキーマの自動検出を有効にできます。Datastore エクスポート、Firestore エクスポート、Avro ファイルには、[自動検出] を使用できません。これらのファイル形式のスキーマ情報は、自己記述型のソースデータから自動的に取得されます。
   • CSV ファイルと JSON ファイルの場合、次の方法でスキーマ情報を手動で入力できます。
     • [テキストとして編集] を有効にし、テーブル スキーマを JSON 配列として入力します。注: 既存のテーブルのスキーマを JSON 形式で表示するには、bq コマンドライン ツールに「bq show --format=prettyjson dataset.table」と入力します。
     • [フィールドを追加] を使用して、スキーマを手動で入力します。

7. [テーブルを作成] をクリックします。

永続テーブルが作成されると、ネイティブの BigQuery テーブルの場合と同じようにクエリを実行できます。クエリの完了後は、結果を CSV または JSON としてエクスポート、テーブルとして保存、Google スプレッドシートに保存できます。

従来の UI

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

2. ナビゲーション パネルで、データセットにカーソルを合わせて下矢印アイコン をクリックし、[Create new table] をクリックします。

3. [テーブルの作成] ページの [ソースデータ] セクションで、次の操作を行います。

   • [Location] で [Cloud Storage] を選択し、ソース フィールドに Cloud Storage URI を入力します。Cloud Storage URI にはワイルドカードを使用できます。
   • [ファイル形式] でデータの形式を選択します。Cloud Storage に有効なフォーマットは次のとおりです。
     • カンマ区切り値（CSV）
     • JSON（改行区切り）
     • Avro
     • Datastore バックアップ（Firestore でも使用）

4. [テーブルの作成] ページの [宛先テーブル] セクションで、次の操作を行います。

   • [Table name] で、該当するデータセットを選択します。テーブル名のフィールドで、BigQuery で作成する永続テーブルの名前を入力します。
   • [Table type] が [External table] に設定されていることを確認します。

5. [Schema] セクションで、スキーマ情報を入力します。

   • JSON または CSV ファイルの場合、[自動検出] オプションをオンにしてスキーマの自動検出を有効にできます。Datastore エクスポート、Firestore エクスポート、Avro ファイルには、[自動検出] を使用できません。これらのファイル形式のスキーマ情報は、自己記述型のソースデータから自動的に取得されます。

   • CSV または JSON スキーマ情報を手動で入力するには、次の操作を行います。

     • [Edit as text] をクリックして、テーブル スキーマを JSON 形式で入力する。
     • [Add Field] を使用して、スキーマを手動で入力する。

6. [Options] セクションで該当する項目を選択し、[Create Table] をクリックします。

永続テーブルが作成されると、ネイティブの BigQuery テーブルの場合と同じようにクエリを実行できます。クエリの完了後は、結果を CSV または JSON としてエクスポート、テーブルとして保存、Google スプレッドシートに保存できます。

bq

bq コマンドライン ツールでテーブルを作成するには、bq mk コマンドを使用します。bq コマンドライン ツールを使用して外部データソースにリンクするテーブルを作成するには、以下を使用してテーブルのスキーマを識別します。

• テーブル定義ファイル（ローカルマシンに保存）
• インライン スキーマの定義
• JSON スキーマ ファイル（ローカルマシンに保存）

テーブル定義ファイルを使用して Cloud Storage データソースにリンクされた永続テーブルを作成するには、次のコマンドを入力します。

bq mk \
--external_table_definition=definition_file \
dataset.table

ここで

• definition_file は、ローカルマシン上のテーブル定義ファイルのパスです。
• dataset は、テーブルを含むデータセットの名前です。
• table は、作成するテーブルの名前です。

たとえば、mytable_def という名前のテーブル定義ファイルを使用して、mytable という名前の永続テーブルを作成するコマンドは次のとおりです。

bq mk --external_table_definition=/tmp/mytable_def mydataset.mytable

インライン スキーマ定義を使用して、外部データソースにリンクする永続テーブルを作成するには、次のコマンドを入力します。

bq mk \
--external_table_definition=schema@source_format=Cloud Storage URI \
dataset.table

ここで

• schema は、field:data_type,field:data_type という形式のスキーマ定義です。
• source_format は CSV、NEWLINE_DELIMITED_JSON、AVRO、または DATASTORE_BACKUP です（DATASTORE_BACKUP は Filestore でも使用されます）。
• Cloud Storage URI は、使用する Cloud Storage URI です。
• dataset は、テーブルを含むデータセットの名前です。
• table は、作成するテーブルの名前です。

たとえば、次のコマンドは、スキーマ定義 Region:STRING,Quarter:STRING,Total_sales:INTEGER を使用して、Cloud Storage に格納された CSV ファイルにリンクする sales という名前の永続テーブルを作成します。

bq mk \
--external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
mydataset.sales

JSON スキーマ ファイルを使用して、外部データソースにリンクする永続テーブルを作成するには、次のコマンドを入力します。

bq mk \
--external_table_definition=schema@source_format=Cloud Storage URI \
dataset.table

ここで

• schema は、ローカルマシン上の JSON スキーマ ファイルのパスです。
• source_format は CSV、NEWLINE_DELIMITED_JSON、AVRO、DATASTORE_BACKUP です（DATASTORE_BACKUP は Firestore でも使用されます）。
• Cloud Storage URI は、使用する Cloud Storage URI です。
• dataset は、テーブルを含むデータセットの名前です。
• table は、作成するテーブルの名前です。

たとえば、次のコマンドは、スキーマ ファイル /tmp/sales_schema.json を使用して、Cloud Storage に格納された CSV ファイルにリンクする sales という名前の一時テーブルを作成します。

bq mk \
--external_table_definition=/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv \
mydataset.sales

永続テーブルが作成されると、ネイティブの BigQuery テーブルの場合と同じようにクエリを実行できます。クエリの完了後は、結果を CSV または JSON としてエクスポート、テーブルとして保存、Google スプレッドシートに保存できます。

API

tables.insert API メソッドを使用する際に ExternalDataConfiguration を作成します。schema プロパティを指定するか autodetect プロパティを true に設定して、サポートされているデータソースのスキーマの自動検出を有効にします。

Java

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

GitHub で表示 フィードバック

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source using a permanent table
public class QueryExternalGCSPerm {

  public static void runQueryExternalGCSPerm() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query =
        String.format("SELECT * FROM %s.%s WHERE name LIKE 'W%%'", datasetName, tableName);
    queryExternalGCSPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSPerm(
      String datasetName, String tableName, String sourceUri, Schema schema, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the GCS file
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
      bigquery.create(TableInfo.of(tableId, externalTable));

      // Example query to find states starting with 'W'
      TableResult results = bigquery.query(QueryJobConfiguration.of(query));

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query on external permanent table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Python

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

GitHub で表示 フィードバック

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

# Configure the external data source
dataset_ref = client.dataset(dataset_id)
table_id = "us_states"
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
table = bigquery.Table(dataset_ref.table(table_id), schema=schema)
external_config = bigquery.ExternalConfig("CSV")
external_config.source_uris = [
    "gs://cloud-samples-data/bigquery/us-states/us-states.csv"
]
external_config.options.skip_leading_rows = 1  # optionally skip header row
table.external_data_configuration = external_config

# Create a permanent table linked to the GCS file
table = client.create_table(table)  # API request

# Example query to find states starting with 'W'
sql = 'SELECT * FROM `{}.{}` WHERE name LIKE "W%"'.format(dataset_id, table_id)

query_job = client.query(sql)  # API request

w_states = list(query_job)  # Waits for query to finish
print("There are {} states with names starting with W.".format(len(w_states)))

一時テーブルを使用して Cloud Storage データにクエリを実行する

永続テーブルを作成せずに外部データソースに対してクエリを行うには、以下を結合するコマンドを実行します。

• テーブル定義ファイルとクエリ
• クエリとインライン スキーマ定義
• クエリと JSON スキーマ定義ファイル

テーブル定義ファイルまたは指定したスキーマを使用して一時外部テーブルが作成され、そのテーブルに対してクエリが実行されます。一時テーブルを使用した外部データソースに対するクエリの実行は、bq コマンドライン ツールと API でサポートされています。

外部の一時テーブルを使用する場合は、BigQuery データセット内にテーブルが作成されません。テーブルはデータセットに永続的に保存されないため、このテーブルを他のユーザーと共有することはできません。外部データに対する 1 回限りのアドホック クエリを行う場合、または抽出、変換、読み込み（ETL）プロセスを行う場合は、一時テーブルを使用して外部データソースのクエリを行うと便利です。

必要な権限

一時テーブルを使用して Cloud Storage の外部データに対してクエリを実行する場合、プロジェクト レベル以上でクエリジョブを実行する権限が必要であり、外部データを指すテーブルを含むデータセットにアクセスする必要があります。Cloud Storage のデータに対してクエリを実行する場合は、データを含むバケットに対するアクセス権も必要です。

BigQuery の権限

一時テーブルを使用して BigQuery の外部テーブルに対してクエリを行うには、少なくとも以下の権限が必要です。

• bigquery.tables.getData
• bigquery.jobs.create

次の定義済みの IAM ロールには bigquery.tables.getData 権限が含まれています。

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

次の事前定義済みの IAM ロールには bigquery.jobs.create 権限が含まれています。

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

また、bigquery.datasets.create 権限を持つユーザーがデータセットを作成すると、そのデータセットに対する bigquery.dataOwner アクセス権がユーザーに付与されます。bigquery.dataOwner アクセス権により、ユーザーはデータセット内の外部テーブルを作成してアクセスできますが、データに対してクエリを行うにはさらに bigquery.jobs.create 権限が必要です。

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

Cloud Storage の権限

Cloud Storage バケット内の外部データに対してクエリを行なうには、storage.objects.get 権限が付与されている必要があります。URI のワイルドカードを使用する場合は storage.objects.list 権限も必要です。

事前定義の IAM ロール storage.objectViewer が付与されると、storage.objects.get 権限と storage.objects.list 権限が両方とも与えられます。

一時テーブルを作成してクエリを行う

bq コマンドライン ツール、API、クライアント ライブラリを使用して、外部データソースにリンクされた一時テーブルを作成してクエリを実行できます。

bq

外部データソースにリンクされている一時テーブルに対するクエリを行うには、--external_table_definition フラグを指定して bq query コマンドを実行します。bq コマンドライン ツールを使用して外部データソースにリンクする一時テーブルに対してクエリを実行するには、以下を使用してテーブルのスキーマを識別します。

• テーブル定義ファイル（ローカルマシンに保存）
• インライン スキーマの定義
• JSON スキーマ ファイル（ローカルマシンに保存）

（省略可）--location フラグを指定して、その値をロケーションに設定します。

テーブル定義ファイルを使用して、外部データソースにリンクする一時テーブルをクエリするには、次のコマンドを入力します。

bq --location=location query \
--external_table_definition=table::definition_file \
'query'

ここで

• location は、使用するロケーションの名前です。--location フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値を asia-northeast1 に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。
• table は、作成する一時テーブルの名前です。
• definition_file は、ローカルマシン上のテーブル定義ファイルのパスです。
• query は、一時テーブルに送信するクエリです。

たとえば、次のコマンドを実行すると、sales_def というテーブル定義ファイルを使用して sales という一時テーブルが作成され、クエリが実行されます。

bq query \
--external_table_definition=sales::sales_def \
'SELECT
  Region,
  Total_sales
FROM
  sales'

インライン スキーマ定義を使用して、外部データソースにリンクする一時テーブルに対してクエリを実行するには、次のコマンドを入力します。

bq --location=location query \
--external_table_definition=table::schema@source_format=Cloud Storage URI \
'query'

ここで

• location は、使用するロケーションの名前です。--location フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値を asia-northeast1 に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。
• table は、作成する一時テーブルの名前です。
• schema は、field:data_type,field:data_type という形式のインライン スキーマの定義です。
• source_format は CSV、NEWLINE_DELIMITED_JSON、AVRO、DATASTORE_BACKUP です（DATASTORE_BACKUP は Firestore でも使用されます）。
• Cloud Storage URI は、使用する Cloud Storage URI です。
• query は、一時テーブルに送信するクエリです。

たとえば、次のコマンドを実行すると、スキーマ定義 Region:STRING,Quarter:STRING,Total_sales:INTEGER を使用して、Cloud Storage に保存された CSV ファイルにリンクする一時テーブルが sales という名前で作成され、クエリが実行されます。

bq query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
'SELECT
  Region,
  Total_sales
FROM
  sales'

JSON スキーマ ファイルを使用して、外部のデータソースにリンクする一時テーブルにクエリを実行するには、次のコマンドを入力します。

bq --location=location query \
--external_table_definition=schema_file@source_format=Cloud Storage URI \
'query'

ここで

• location は、使用するロケーションの名前です。--location フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値を asia-northeast1 に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。
• schema_file は、ローカルマシン上の JSON スキーマ ファイルのパスです。
• source_format は CSV、NEWLINE_DELIMITED_JSON、AVRO、DATASTORE_BACKUP です（DATASTORE_BACKUP は Firestore でも使用されます）。
• Cloud Storage URI は、使用する Cloud Storage URI です。
• query は、一時テーブルに送信するクエリです。

たとえば、次のコマンドを実行すると、/tmp/sales_schema.json というスキーマ ファイルを使用して、Cloud Storage に保存された CSV ファイルにリンクするテーブルが sales という名前で作成され、クエリが行われます。

bq query
--external_table_definition=sales::/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv
'SELECT Region, Total_sales FROM sales'

API

• クエリジョブ構成を作成します。jobs.query と jobs.insert の呼び出しについては、データのクエリをご覧ください。

• ExternalDataConfiguration を作成して、外部データソースを指定します。

Java

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

GitHub で表示 フィードバック

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source using a temporary table
public class QueryExternalGCSTemp {

  public static void runQueryExternalGCSTemp() {
    // TODO(developer): Replace these variables before running the sample.
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query = String.format("SELECT * FROM %s WHERE name LIKE 'W%%'", tableName);
    queryExternalGCSTemp(tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSTemp(
      String tableName, String sourceUri, Schema schema, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      // Configure the external data source and query job.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              .addTableDefinition(tableName, externalTable)
              .build();

      // Example query to find states starting with 'W'
      TableResult results = bigquery.query(queryConfig);

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query on external temporary table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Python

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

GitHub で表示 フィードバック

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# Configure the external data source and query job.
external_config = bigquery.ExternalConfig("CSV")
external_config.source_uris = [
    "gs://cloud-samples-data/bigquery/us-states/us-states.csv"
]
external_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
external_config.options.skip_leading_rows = 1
table_id = "us_states"
job_config = bigquery.QueryJobConfig(table_definitions={table_id: external_config})

# Example query to find states starting with 'W'.
sql = 'SELECT * FROM `{}` WHERE name LIKE "W%"'.format(table_id)

query_job = client.query(sql, job_config=job_config)  # Make an API request.

w_states = list(query_job)  # Wait for the job to complete.
print("There are {} states with names starting with W.".format(len(w_states)))

外部でパーティションに分割されたデータのクエリ

外部でパーティションに分割された Cloud Storage データのクエリの手順を参照してください。

Cloud Storage の URI でのワイルドカードの使用

Cloud Storage のデータが、共通のベース名を共有する複数のファイルに分割されている場合は、テーブル定義ファイルの URI でワイルドカードを使用できます。テーブル定義ファイルを使用せずに外部テーブルを作成するときにもワイルドカードを使用できます。

Cloud Storage の URI にワイルドカードを追加するには、ベース名にアスタリスク（*）を追加します。たとえば、fed-sample000001.csv と fed-sample000002.csv という 2 つのファイルがある場合、バケット URI は gs://mybucket/fed-sample* になります。このワイルドカードを含む URI は、Cloud Console、従来のウェブ UI、bq コマンドライン ツール、API、クライアント ライブラリで使用できます。

注: 一部のプラットフォームでは bq コマンドライン ツールの使用時に、アスタリスクをエスケープしなければならない場合があります。

バケット内のオブジェクト（ファイル名）について使用できるワイルドカードは 1 つのみです。ワイルドカードは、オブジェクト名の中や末尾に使用できます。バケット名にワイルドカードを付けることはできません。

Google Datastore のエクスポートで指定できる URI は 1 つのみです。また、URI の末尾は .backup_info または .export_metadata である必要があります。

以下の場合、ワイルドカード文字（アスタリスク）は使用できません。

• Datastore または Firestore のエクスポートにリンクされる外部テーブルを作成する。
• Cloud Storage から Datastore または Firestore のエクスポート データを読み込む

_FILE_NAME 疑似列

外部データソースに基づくテーブルは、_FILE_NAME という名前の疑似列を提供します。この列には、行が属するファイルへの完全修飾パスが含まれます。この列は、Cloud Storage と Google ドライブに保存されている外部データを参照するテーブルでのみ使用できます。

列名 _FILE_NAME は予約されています。つまり、この名前を持つ列はどのテーブルにも作成できません。_FILE_NAME の値を選択するには、エイリアスを使用する必要があります。次の例のクエリでは、エイリアス fn を疑似列に割り当て、_FILE_NAME を選択しています。

bq query \
--project_id=project_id \
--use_legacy_sql=false \
'SELECT
   name,
   _FILE_NAME AS fn
 FROM
   `dataset.table_name`
 WHERE
   name contains "Alex"' 

ここで

• project_id は、有効なプロジェクト ID です。Cloud Shell を使用する場合や Cloud SDK でデフォルトのプロジェクトを設定する場合、このフラグは不要です。
• dataset は、外部の永続テーブルが保存されているデータセットの名前です。
• table_name は、外部の永続テーブルの名前です。