外部テーブルの Cloud Storage データにクエリを実行する

このドキュメントでは、Cloud Storage 外部テーブルに保存されているデータに対してクエリを行う方法について説明します。

始める前に

Cloud Storage 外部テーブルがあることを確認します。

必要なロール

Cloud Storage 外部テーブルに対してクエリを行うには、次のロールを持っていることを確認してください。

• BigQuery データ閲覧者（roles/bigquery.dataViewer）
• BigQuery ユーザー（roles/bigquery.user）
• Storage オブジェクト閲覧者（roles/storage.objectViewer）

権限に応じて、これらのロールを自身に付与するか、これらのロールを付与するよう管理者に依頼します。ロールの付与の詳細については、リソースに対して付与可能なロールの表示をご覧ください。

外部テーブルのクエリに必要な BigQuery 権限を正確に確認するには、[必要な権限] セクションを開きます。

必要な権限

• bigquery.jobs.create
• bigquery.readsessions.create（BigQuery Storage Read API を使用してデータを読み取る場合にのみ必要）
• bigquery.tables.get
• bigquery.tables.getData

カスタムロールや他の事前定義ロールを使用して、これらの権限を取得することもできます。

外部の永続テーブルに対してクエリを行う

Cloud Storage 外部テーブルを作成すると、標準の BigQuery テーブルの場合と同じように GoogleSQL 構文を使用してクエリを行うことができます。例: SELECT field1, field2 FROM mydataset.my_cloud_storage_table;

外部の一時テーブルに対してクエリを行う

外部データに対する 1 回限りのアドホック クエリを行う場合、または抽出、変換、読み込み（ETL）プロセスを行う場合は、一時テーブルを使用して外部データソースのクエリを行うと便利です。

永続テーブルを作成せずに外部データソースに対してクエリを実行するには、一時テーブルに対してテーブル定義を指定し、コマンドまたは呼び出しでそのテーブル定義を使用して一時テーブルに対してクエリを実行します。テーブル定義は次のいずれかの方法で指定できます。

• テーブル定義ファイル
• インライン スキーマの定義
• JSON スキーマ ファイル

テーブル定義ファイルまたは指定したスキーマを使用して一時外部テーブルが作成され、そのテーブルに対してクエリが実行されます。

外部の一時テーブルを使用する場合は、BigQuery データセット内にテーブルが作成されません。テーブルはデータセットに永続的に保存されないため、このテーブルを他のユーザーと共有することはできません。

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=BUCKET_PATH \
'QUERY'

次のように置き換えます。

• LOCATION: ロケーション の名前。--location フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値を asia-northeast1 に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。
• TABLE: 作成する一時テーブルの名前。
• SCHEMA: field:data_type,field:data_type という形式のインライン スキーマの定義。
• SOURCE_FORMAT は、外部データソースの形式です（例: CSV）。

• BUCKET_PATH: テーブルのデータを含む Cloud Storage バケットへのパス（gs://bucket_name/[folder_name/]file_pattern 形式）。

  file_pattern にワイルドカードとしてアスタリスク（*）を 1 つ指定して、バケットから複数のファイルを選択することもできます。たとえば、gs://mybucket/file00*.parquet のようにします。詳細については、Cloud Storage の URI でのワイルドカードのサポートをご覧ください。

  複数のパスを指定して、uris オプションに複数のバケットを指定できます。

  次の例に、有効な uris 値を示します。

  • gs://bucket/path1/myfile.csv
  • gs://bucket/path1/*.parquet
  • gs://bucket/path1/file1*、gs://bucket1/path1/*

  複数のファイルをターゲットとする uris 値を指定する場合、それらのファイルはすべて互換性のあるスキーマを共有する必要があります。

  BigQuery での Cloud Storage URI の使用方法については、Cloud Storage リソースパスをご覧ください。

• 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=BUCKET_PATH \
'QUERY'

次のように置き換えます。

• LOCATION: ロケーション の名前。--location フラグは省略可能です。たとえば、BigQuery を東京リージョンで使用している場合は、このフラグの値を asia-northeast1 に設定します。.bigqueryrc ファイルを使用してロケーションのデフォルト値を設定できます。
• SCHEMA_FILE: ローカルマシン上の JSON スキーマ ファイルへのパス
• SOURCE_FORMAT は、外部データソースの形式です（例: CSV）。

• BUCKET_PATH: テーブルのデータを含む Cloud Storage バケットへのパス（gs://bucket_name/[folder_name/]file_pattern 形式）。

  file_pattern にワイルドカードとしてアスタリスク（*）を 1 つ指定して、バケットから複数のファイルを選択することもできます。たとえば、gs://mybucket/file00*.parquet のようにします。詳細については、Cloud Storage の URI でのワイルドカードのサポートをご覧ください。

  複数のパスを指定して、uris オプションに複数のバケットを指定できます。

  次の例に、有効な uris 値を示します。

  • gs://bucket/path1/myfile.csv
  • gs://bucket/path1/*.parquet
  • gs://bucket/path1/file1*、gs://bucket1/path1/*

  複数のファイルをターゲットとする uris 値を指定する場合、それらのファイルはすべて互換性のあるスキーマを共有する必要があります。

  BigQuery での Cloud Storage URI の使用方法については、Cloud Storage リソースパスをご覧ください。

• 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

API を使用してクエリを実行する手順は次のとおりです。

1. Job オブジェクトを作成します。
2. Job オブジェクトの configuration セクションに JobConfiguration オブジェクトを入力します。
3. JobConfiguration オブジェクトの query セクションに JobConfigurationQuery オブジェクトを入力します。
4. JobConfigurationQuery オブジェクトの tableDefinitions セクションに ExternalDataConfiguration オブジェクトを入力します。
5. クエリを非同期で実行するには jobs.insert メソッドを呼び出し、同期的にクエリを実行するには jobs.query メソッドを呼び出して、Job オブジェクトで渡します。

Java

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

BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。

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

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

BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。

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

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

BigQuery に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、クライアント ライブラリの認証を設定するをご覧ください。

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

_FILE_NAME 疑似列に対してクエリを行う

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

_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 疑似列を使用してクエリ述語を構築する場合、疑似列を使用して取り込み時間パーティション分割テーブルに対するクエリを実行する場合と同様の推奨事項が適用されます。

次のステップ

• BigQuery での SQL の使用について確認する。
• 外部テーブルについて確認する。
• BigQuery の割り当てについて確認する。