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 バケットの場所に関する考慮事項を参照してください。

始める前に

このドキュメントの各タスクを実行するために必要な権限をユーザーに与える Identity and Access Management（IAM）のロールを付与します。タスクの実行に必要な権限（存在する場合）は、タスクの「必要な権限」セクションに記載されています。

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 を取得するには:

Cloud Storage Console を開きます。

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

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

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

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

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

必要な権限

永続テーブルを使用して Cloud Storage の外部データに対してクエリを実行するには、次のことを行うための権限が必要です。

プロジェクトレベル以上でクエリジョブを実行する。
外部データを指すテーブルを作成する。
そのテーブルにアクセスする。

外部データが Cloud Storage に保存されている場合は、データが入っているバケットにアクセスする権限も必要です。

BigQuery で外部テーブルを作成してクエリを実行する権限

BigQuery で外部テーブルを作成してクエリを実行するには、次の IAM 権限が必要です。

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

以下の各 IAM 事前定義ロールには、BigQuery で外部テーブルを作成してクエリを実行するために必要な権限が含まれています。

roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin（bigquery.jobs.create 権限を含む）
roles/bigquery.user（bigquery.jobs.create 権限を含む）
roles/bigquery.jobUser（bigquery.jobs.create 権限を含む）

また、bigquery.datasets.create 権限がある場合は、作成したデータセットに外部テーブルを作成してアクセスできます。それでも、データを照会するには bigquery.jobs.create 権限が必要です。

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

Cloud Storage バケット内の外部データに対してクエリを実行する権限

Cloud Storage バケット内の外部データに対してクエリを実行するには、次の IAM 権限が必要です。

storage.objects.get
storage.objects.list（URI ワイルドカードを使用する場合に必要）

IAM 事前定義ロール roles/storage.objectViewer には、Cloud Storage バケット内の外部データに対してクエリを実行するために必要なすべての権限が含まれています。

Compute Engine インスタンスのアクセススコープ

Compute Engine インスタンスから、Cloud Storage ソースにリンクされている外部テーブルにクエリを実行する必要がある場合は、インスタンスに少なくとも Cloud Storage の読み取り専用アクセススコープが必要です（https://www.googleapis.com/auth/devstorage.read_only）。

このスコープにより、Cloud Storage などの Google Cloud プロダクトに対する Compute Engine インスタンスのアクセスが制御されます。インスタンス上で実行されるアプリケーションは、インスタンスにアタッチしたサービスアカウントを使用して Google Cloud APIs を呼び出します。

デフォルトの Compute Engine サービスアカウントとして実行するように Compute Engine インスタンスを設定すると、インスタンスにはいくつかのデフォルトのスコープがデフォルトで付与され、https://www.googleapis.com/auth/devstorage.read_only スコープを含みます。

代わりにカスタムサービスアカウントでインスタンスを設定する場合は、https://www.googleapis.com/auth/devstorage.read_only スコープをインスタンスに明示的に付与してください。

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

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

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

Cloud コンソールの使用
bq mk コマンドの使用
tables.insert API メソッドを使用する際に ExternalDataConfiguration を作成する
CREATE EXTERNAL TABLE データ定義言語（DDL）ステートメントを実行する
クライアントライブラリを使用する

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

永続外部テーブルを作成する場合は、次の方法でスキーマを指定できます。

明示的なテーブルスキーマを指定します。
スキーマの自動検出を使用します。

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

Console

Cloud コンソールで、[BigQuery] ページを開きます。

BigQuery に移動

[エクスプローラ] パネルでプロジェクトを開いて、データセットを選択します。
アクション オプションを開いて、[テーブルを作成] をクリックします。
[テーブルの作成] ページの [ソース] セクションで、次の操作を行います。
- [テーブルの作成元] で [Google Cloud Storage] を選択します。
- [GCS バケットからファイルを選択] フィールドで、ファイルまたは Cloud Storage バケットを参照するか、「Cloud Storage URI」を入力します。Cloud コンソールで複数の URI を指定することはできませんが、ワイルドカードはサポートされています。Cloud Storage バケットは、作成するテーブルを含むデータセットと同じロケーションに存在する必要があります。
- [ファイル形式] でデータの形式を選択します。
[テーブルの作成] ページの [送信先] セクションで、次の操作を行います。
- [データセット] で、該当するデータセットを選択します。
- [テーブルタイプ] が [外部テーブル] に設定されていることを確認します。
- [テーブル名] フィールドに、BigQuery で作成するテーブルの名前を入力します。
[スキーマ] セクションで、スキーマの自動検出を有効にするか、スキーマを手動で指定できます。
- スキーマの自動検出を有効にするには、[自動検出] オプションを選択します。
- 手動でスキーマを指定するには、[自動検出] オプションを選択せずに、次のいずれかを行います。
  - [テキストとして編集] を有効にし、テーブルスキーマを JSON 配列として入力します。
[テーブルを作成] をクリックします。

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

SQL

永続外部テーブルを作成するには、CREATE EXTERNAL TABLE DDL ステートメントを実行します。スキーマは明示的に指定できます。スキーマを指定しない場合、BigQuery はスキーマの自動検出を使用して、外部データからスキーマを推測します。

次の例では、スキーマの自動検出を使用して、Cloud Storage に格納された CSV ファイルにリンクする sales という名前の外部テーブルを作成しています。

Cloud Console で、[BigQuery] ページに移動します。

BigQuery に移動

クエリエディタで次のステートメントを入力します。

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales
  OPTIONS (
  format = 'CSV',
  uris = ['gs://mybucket/sales.csv']);

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

クエリの実行方法については、インタラクティブクエリの実行をご覧ください。

次の例では、スキーマを明示的に指定し、CSV ファイルの最初の行をスキップしています。

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales (
  Region STRING,
  Quarter STRING,
  Total_Sales INT64
) OPTIONS (
    format = 'CSV',
    uris = ['gs://mybucket/sales.csv'],
    skip_leading_rows = 1);

bq

外部テーブルを作成するには、--external_table_definition フラグを指定して bq mk コマンドを使用します。このフラグには、テーブル定義ファイルへのパスまたはインラインテーブル定義が含まれます。

オプション 1: テーブル定義ファイル

bq mkdef コマンドを使用してテーブル定義ファイルを作成し、次のようにファイルパスを bq mk コマンドに渡します。

bq mkdef --source_format=SOURCE_FORMAT \
  BUCKET_URL > DEFINITION_FILE

bq mk --table \
  --external_table_definition=DEFINITION_FILE \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

ここで

SOURCE_FORMAT は、外部データソースの形式です（例: CSV）。
BUCKET_URI は、使用する Cloud Storage URI です。
DEFINITION_FILE は、ローカルマシン上のテーブル定義ファイルのパスです。
DATASET_NAME は、テーブルを含むデータセットの名前です。
TABLE_NAME は、作成するテーブルの名前です。
SCHEMA は、JSON スキーマファイルのパスを指定するか、field:data_type,field:data_type,... の形式でスキーマを指定します。

例:

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def

bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

スキーマの自動検出を使用するには、mkdef コマンドで --autodetect=true フラグを設定し、スキーマを省略します。

bq mkdef --source_format=CSV --autodetect=true \
  gs://mybucket/sales.csv > mytable_def

bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable

オプション 2: インラインテーブルの定義

テーブル定義ファイルを作成する代わりに、テーブル定義を bq mk コマンドに直接渡します。

bq mk --table \
  --external_table_definition=@SOURCE_FORMAT=BUCKET_URI \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

ここで

SOURCE_FORMAT は、外部データソースの形式です（例: CSV）。
BUCKET_URI は、使用する Cloud Storage URI です。
DATASET_NAME は、テーブルを含むデータセットの名前です。
TABLE_NAME は、作成するテーブルの名前です。
SCHEMA は、JSON スキーマファイルのパスを指定するか、field:data_type,field:data_type,... の形式でスキーマを指定します。スキーマの自動検出を使用するには、この引数を省略します。

例:

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def
bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

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());
    }
  }
}

Node.js

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

GitHub で表示フィードバック

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryExternalGCSPerm() {
  // Queries an external data source using a permanent table

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";

  // Configure the external data source
  const dataConfig = {
    sourceFormat: 'CSV',
    sourceUris: ['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],
    // Optionally skip header row
    csvOptions: {skipLeadingRows: 1},
  };

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    externalDataConfiguration: dataConfig,
  };

  // Create an external table linked to the GCS file
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);

  // Example query to find states starting with 'W'
  const query = `SELECT post_abbr
  FROM \`${datasetId}.${tableId}\`
  WHERE name LIKE 'W%'`;

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(query);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  console.log(rows);
}

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 = bigquery.DatasetReference(project, 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 で外部テーブルに対してクエリを実行するには、次の IAM 権限が必要です。

bigquery.tables.getData
bigquery.jobs.create

以下の各 IAM 事前定義ロールには、一時テーブルを使用して BigQuery の外部テーブルに対してクエリを実行するために必要な権限が含まれています。

roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin（bigquery.jobs.create 権限を含む）
roles/bigquery.user（bigquery.jobs.create 権限を含む）
roles/bigquery.jobUser（bigquery.jobs.create 権限を含む）

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

Cloud Storage バケット内の外部データに対してクエリを実行する権限

Cloud Storage バケット内の外部データに対してクエリを実行するには、次の IAM 権限が必要です。

storage.objects.get
storage.objects.list（URI ワイルドカードを使用する場合に必要）

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

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）。
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）。
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());
    }
  }
}

Node.js

GitHub で表示フィードバック

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryExternalGCSTemp() {
  // Queries an external data source using a temporary table.

  const tableId = 'us_states';

  // Configure the external data source
  const externalDataConfig = {
    sourceFormat: 'CSV',
    sourceUris: ['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],
    // Optionally skip header row.
    csvOptions: {skipLeadingRows: 1},
    schema: {fields: schema},
  };

  // Example query to find states starting with 'W'
  const query = `SELECT post_abbr
  FROM \`${tableId}\`
  WHERE name LIKE 'W%'`;

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    query,
    tableDefinitions: {[tableId]: externalDataConfig},
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  console.log(rows);
}

Python

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 にワイルドカードを追加するには、ベース名にアスタリスク（*）を追加します。

例:

次のワイルドカード URI は、接頭辞 gs://mybucket/fed-samples/fed-sample で始まるすべてのフォルダ内のすべてのファイルを選択します。
```
gs://mybucket/fed-samples/fed-sample*
```
次のワイルドカード URI は、fed-samples というフォルダと fed-samples のサブフォルダにある .csv 拡張子を持つファイルのみを選択します。
```
gs://mybucket/fed-samples/fed-sample/*.csv
```
次のワイルドカード URI は、fed-samples という名前のフォルダで、fed-sample*.csv という命名パターンのファイルを選択します。この例では、fed-samples のサブフォルダ内のファイルは選択されません。
```
gs://mybucket/fed-samples/fed-sample*.csv
```

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

バケット内のオブジェクト（ファイル名）について使用できるワイルドカードは 1 つのみです。ワイルドカードは、オブジェクト名の中や末尾に使用できます。バケット名にワイルドカードを付けることはできません。ソース URI では複数のワイルドカードを使用できません。たとえば、パス gs://mybucket/fed-*/temp/*.csv は無効です。

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 を使用する場合や Google Cloud CLI でデフォルトのプロジェクトを設定する場合、このフラグは不要です。
dataset は、外部の永続テーブルが保存されているデータセットの名前です。
table_name は、外部の永続テーブルの名前です。

クエリに _FILE_NAME 疑似列に対するフィルタ述語がある場合、BigQuery は、フィルタに一致しないファイルの読み取りをスキップしようとします。_FILE_NAME 疑似列を使用してクエリ述語を構築する場合、疑似列を使用して取り込み時間パーティション分割テーブルに対するクエリを実行する場合と同様の推奨事項が適用されます。

Cloud Storage データのクエリ

始める前に

Cloud Storage URI の取得

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

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

必要な権限

BigQuery で外部テーブルを作成してクエリを実行する権限

Cloud Storage バケット内の外部データに対してクエリを実行する権限

Compute Engine インスタンスのアクセス スコープ

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

Console

SQL

bq

API

Java

Node.js

Python

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

必要な権限

BigQuery で外部テーブルに対してクエリを実行する権限

Cloud Storage バケット内の外部データに対してクエリを実行する権限

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

bq

API

Java

Node.js

Python

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

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

_FILE_NAME 疑似列

Compute Engine インスタンスのアクセススコープ