Cloud Storage 外部テーブルを作成する

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

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

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

  • Standard
  • Nearline
  • Coldline
  • Archive

Cloud Storage 外部テーブルにクエリを実行するには、外部テーブルと Cloud Storage ファイルの両方に対する権限が必要です。可能であれば、BigLake テーブルを使用することをおすすめします。BigLake テーブルではアクセス権の委任が可能であるため、Cloud Storage データのクエリには BigLake テーブルに対する権限のみが必要です。

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

始める前に

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

必要なロール

外部テーブルを作成するには、bigquery.tables.create BigQuery Identity and Access Management（IAM）権限が必要です。

この権限は、次の Identity and Access Management 事前定義ロールに含まれています。

  • BigQuery データ編集者（roles/bigquery.dataEditor）
  • BigQuery データオーナー（roles/bigquery.dataOwner）
  • BigQuery 管理者（roles/bigquery.admin）

データを含む Cloud Storage バケットにアクセスするには、次の権限も必要です。

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

Identity and Access Management 事前定義ロールの Cloud Storage ストレージ管理者（roles/storage.admin）にはこれらの権限が含まれています。

これらのロールのいずれかのプリンシパルでない場合は、アクセス権の付与または外部テーブルの作成を管理者に依頼してください。

BigQuery での Identity and Access Management のロールと権限の詳細については、事前定義ロールと権限をご覧ください。

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

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

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

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

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

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

パーティション分割されていないデータに外部テーブルを作成する

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

次のオプションのいずれかを選択します。

コンソール

  1. [BigQuery] ページに移動します。

    BigQuery に移動

  2. 左側のペインで、 [エクスプローラ] をクリックします。

    エクスプローラ ペインのボタンがハイライト表示されている。

    左側のペインが表示されていない場合は、 左側のペインを開くをクリックしてペインを開きます。

  3. [エクスプローラ] ペインでプロジェクトを開き、[データセット] をクリックして、データセットを選択します。

  4. アクション オプションを開いて、[テーブルを作成] をクリックします。

  5. [送信元] セクションで、次の詳細を指定します。

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

    2. [Select file from GCS bucket or use a URI pattern] で、使用するバケットとファイルを参照して選択するか、gs://bucket_name/[folder_name/]file_name の形式でパスを入力します。

      Google Cloud コンソールで複数の URI を指定することはできませんが、ワイルドカード文字のアスタリスク（*）を 1 つ指定することで複数のファイルを選択できます。例: gs://mybucket/file_name*。詳細については、Cloud Storage の URI でのワイルドカードの使用をご覧ください。

      Cloud Storage バケットは、作成するテーブルを含むデータセットと同じロケーションに存在する必要があります。

    3. [ファイル形式] で、ファイルと一致する形式を選択します。

  6. [送信先] セクションで、次の詳細を指定します。

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

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

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

    4. [テーブルタイプ] で [外部テーブル] を選択します。

  7. [スキーマ] セクションで、スキーマの自動検出を有効にするか、ソースファイルがある場合はスキーマを手動で指定できます。ソースファイルがない場合は、スキーマを手動で指定する必要があります。

    • スキーマの自動検出を有効にするには、[自動検出] オプションをオンにします。

    • 手動でスキーマを指定するには、[自動検出] オプションをオフにしておきます。[テキストとして編集] を有効にし、テーブル スキーマを JSON 配列として入力します。

  8. スキーマと一致しない追加の列値を持つ行を無視するには、[詳細オプション] セクションを開いて [不明な値] を選択します。

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

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

SQL

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

  1. Google Cloud コンソールで、[BigQuery] ページに移動します。

    [BigQuery] に移動

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

    CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
  OPTIONS (
    format ="TABLE_FORMAT",
    uris = ['BUCKET_PATH'[,...]]
    );

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

    • PROJECT_ID: テーブルを作成するプロジェクトの名前（例: myproject）
    • DATASET: テーブルを作成する BigQuery データセットの名前（例: mydataset）
    • EXTERNAL_TABLE_NAME: 作成するテーブルの名前（例: mytable）
    • TABLE_FORMAT: 作成するテーブルの形式（例: PARQUET）
    • BUCKET_PATH: 外部テーブルのデータを含む Cloud Storage バケットへのパス（['gs://bucket_name/[folder_name/]file_name'] 形式）。

      パスにワイルドカードとしてアスタリスク（*）を 1 つ使用して、バケットから複数のファイルを選択することもできます。例: ['gs://mybucket/file_name*']詳細については、Cloud Storage の URI でのワイルドカードのサポートをご覧ください。

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

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

      • ['gs://bucket/path1/myfile.csv']
      • ['gs://bucket/path1/*.csv']
      • ['gs://bucket/path1/*', 'gs://bucket/path2/file00*']

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

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

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

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

例

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

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_PATH > DEFINITION_FILE

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

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

  • 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 リソースパスをご覧ください。

  • 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

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

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_PATH \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

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

  • 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 リソースパスをご覧ください。

  • 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 メソッドを呼び出して、Table リソースに ExternalDataConfiguration を作成します。

schema プロパティを指定するか autodetect プロパティを true に設定して、サポートされているデータソースのスキーマの自動検出を有効にします。

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

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

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

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

from google.cloud import bigquery

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

# TODO(developer): Set table_id to the ID of the table to create.
table_id = "your-project.your_dataset.your_table_name"

# TODO(developer): Set the external source format of your table.
# Note that the set of allowed values for external data sources is
# different than the set used for loading data (see :class:`~google.cloud.bigquery.job.SourceFormat`).
external_source_format = "AVRO"

# TODO(developer): Set the source_uris to point to your data in Google Cloud
source_uris = [
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/a-twitter.avro",
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/b-twitter.avro",
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/c-twitter.avro",
]

# Create ExternalConfig object with external source format
external_config = bigquery.ExternalConfig(external_source_format)
# Set source_uris that point to your data in Google Cloud
external_config.source_uris = source_uris

# TODO(developer) You have the option to set a reference_file_schema_uri, which points to
# a reference file for the table schema
reference_file_schema_uri = "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/b-twitter.avro"

external_config.reference_file_schema_uri = reference_file_schema_uri

table = bigquery.Table(table_id)
# Set the external data configuration of the table
table.external_data_configuration = external_config
table = client.create_table(table)  # Make an API request.

print(
    f"Created table with external source format {table.external_data_configuration.source_format}"
)

パーティション分割テーブルに外部テーブルを作成する

Cloud Storage にある Hive パーティション分割データに外部テーブルを作成できます。外部パーティション分割テーブルを作成した後に、パーティション キーを変更することはできません。パーティション キーを変更するには、テーブルを再作成する必要があります。

Hive パーティション分割データに外部テーブルを作成するには、次のいずれかのオプションを選択します。

コンソール

  1. Google Cloud コンソールで、[BigQuery] に移動します。

    [BigQuery] に移動

  2. 左側のペインで、 [エクスプローラ] をクリックします。
  3. [エクスプローラ] ペインでプロジェクトを開き、[データセット] をクリックして、データセットを選択します。
  4. [アクション] をクリックしてから、[テーブルを作成] をクリックします。これで、[テーブルを作成] ペインが開きます。
  5. [送信元] セクションで、次の詳細を指定します。
    1. [テーブルの作成元] で [Google Cloud Storage] を選択します。
    2. [Select file from Cloud Storage bucket] に、ワイルドカードを使用して Cloud Storage フォルダへのパスを入力します。例: my_bucket/my_files*。Cloud Storage バケットは、作成、追加、または上書きするテーブルを含むデータセットと同じロケーションに存在している必要があります。
    3. [ファイル形式] リストでファイル形式を選択します。
    4. [ソースデータ パーティショニング] チェックボックスをオンにして、[ソース URI の接頭辞を選択] に Cloud Storage URI の接頭辞を入力します。たとえば、gs://my_bucket/my_files のようにします。
    5. [パーティション推論モード] セクションで、次のいずれかのオプションを選択します。
      • 種類を自動的に推測します: パーティション スキーマ検出モードを AUTO に設定します。
      • すべての列は文字列です: パーティション スキーマ検出モードを STRINGS に設定します。
      • 独自に指定します: パーティション スキーマ検出モードを CUSTOM に設定し、パーティション キーのスキーマ情報を手動で入力します。詳細については、カスタム パーティション キー スキーマを指定するをご覧ください。
    6. 省略可: このテーブルのすべてのクエリでパーティション フィルタを必須にするには、[パーティション フィルタを要求] チェックボックスをオンにします。パーティション フィルタを要求すると、コストが削減され、パフォーマンスが向上する場合があります。詳細については、クエリ内のパーティション キーに対する必須の述語フィルタをご覧ください。
  6. [送信先] セクションで、次の詳細を指定します。
    1. [プロジェクト] で、テーブルを作成するプロジェクトを選択します。
    2. [データセット] で、テーブルを作成するデータセットを選択します。
    3. [テーブル] に、作成するテーブルの名前を入力します。
    4. [テーブルタイプ] で [外部テーブル] を選択します。
  7. [スキーマ] セクションでスキーマ定義を入力します。
  8. スキーマの自動検出を有効にするには、[自動検出] を選択します。
  9. スキーマと一致しない追加の列値を持つ行を無視するには、[詳細オプション] セクションを開いて [不明な値] を選択します。
  10. [テーブルを作成] をクリックします。

SQL

CREATE EXTERNAL TABLE DDL ステートメントを使用します。

次の例では、Hive パーティション キーの自動検出を使用します。

CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
WITH PARTITION COLUMNS
OPTIONS (
format = 'SOURCE_FORMAT',
uris = ['GCS_URIS'],
hive_partition_uri_prefix = 'GCS_URI_SHARED_PREFIX',
require_hive_partition_filter = BOOLEAN);

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

  • SOURCE_FORMAT: 外部データソースの形式（PARQUET など）
  • GCS_URIS: ワイルドカードを使用した Cloud Storage フォルダのパス
  • GCS_URI_SHARED_PREFIX: ワイルドカードを使用しないソース URI 接頭辞
  • BOOLEAN: クエリ時に述語フィルタを要求するかどうか。このフラグは省略可能です。デフォルト値は false です。

次の例では、WITH PARTITION COLUMNS 句を使用してカスタム Hive パーティションのキーとタイプを一覧表示しています。

CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
WITH PARTITION COLUMNS (PARTITION_COLUMN_LIST)
OPTIONS (
format = 'SOURCE_FORMAT',
uris = ['GCS_URIS'],
hive_partition_uri_prefix = 'GCS_URI_SHARED_PREFIX',
require_hive_partition_filter = BOOLEAN);

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

  • PARTITION_COLUMN_LIST: Cloud Storage フォルダのパスと同じ順序のリスト。形式:
KEY1 TYPE1, KEY2 TYPE2

次の例では、外部パーティション分割テーブルを作成します。スキーマの自動検出を使用して、ファイル スキーマと Hive パーティショニング レイアウトの両方を検出します。外部パスが gs://bucket/path/field_1=first/field_2=1/data.parquet の場合、パーティション列は field_1（STRING）と field_2（INT64）として検出されます。

CREATE EXTERNAL TABLE dataset.AutoHivePartitionedTable
WITH PARTITION COLUMNS
OPTIONS (
uris = ['gs://bucket/path/*'],
format = 'PARQUET',
hive_partition_uri_prefix = 'gs://bucket/path',
require_hive_partition_filter = false);

次の例では、パーティション列を明示的に指定することで外部パーティション分割テーブルを作成します。この例は、外部ファイルのパスのパターンが gs://bucket/path/field_1=first/field_2=1/data.parquet であることを前提としています。

CREATE EXTERNAL TABLE dataset.CustomHivePartitionedTable
WITH PARTITION COLUMNS (
field_1 STRING, -- column order must match the external path
field_2 INT64)
OPTIONS (
uris = ['gs://bucket/path/*'],
format = 'PARQUET',
hive_partition_uri_prefix = 'gs://bucket/path',
require_hive_partition_filter = false);

bq

まず、bq mkdef コマンドを使用してテーブル定義ファイルを作成します。

bq mkdef \
--source_format=SOURCE_FORMAT \
--hive_partitioning_mode=PARTITIONING_MODE \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
--require_hive_partition_filter=BOOLEAN \
 GCS_URIS > DEFINITION_FILE

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

  • SOURCE_FORMAT: 外部データソースの形式。例: CSV。
  • PARTITIONING_MODE: Hive パーティショニング モード。次の値のいずれかを使用できます。
    • AUTO: キー名と型を自動的に検出します。
    • STRINGS: キー名を自動的に文字列に変換します。
    • CUSTOM: ソース URI 接頭辞でキースキーマをエンコードします。
  • GCS_URI_SHARED_PREFIX: ソース URI 接頭辞。
  • BOOLEAN: クエリ時に述語フィルタを要求するかどうかを指定します。このフラグは省略可能です。デフォルト値は false です。
  • GCS_URIS: ワイルドカード形式を使用する Cloud Storage フォルダのパス。
  • DEFINITION_FILE: ローカルマシン上のテーブル定義ファイルへのパス。

PARTITIONING_MODE が CUSTOM の場合、次の形式を使用して、ソース URI プレフィックスにパーティション キー スキーマを含めます。

--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX/{KEY1:TYPE1}/{KEY2:TYPE2}/...

テーブル定義ファイルを作成したら、bq mk コマンドを使用して外部テーブルを作成します。

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

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

  • DEFINITION_FILE: テーブル定義ファイルへのパス。
  • DATASET_NAME: テーブルを含むデータセットの名前。
  • TABLE_NAME: 作成するテーブルの名前。
  • SCHEMA: JSON スキーマ ファイルへのパスを指定するか、field:data_type,field:data_type,... 形式のスキーマを指定します。スキーマの自動検出を使用するには、この引数を省略します。

例

次の例では、AUTO Hive パーティショニング モードを使用します。

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=AUTO \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable \
  gs://myBucket/myTable/* > mytable_def

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

次の例では、STRING Hive パーティショニング モードを使用します。

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=STRING \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable \
  gs://myBucket/myTable/* > mytable_def

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

次の例では、CUSTOM Hive パーティショニング モードを使用します。

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=CUSTOM \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable/{dt:DATE}/{val:STRING} \
  gs://myBucket/myTable/* > mytable_def

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

API

BigQuery API を使用して Hive パーティショニングを設定するには、テーブル定義ファイルの作成時に、ExternalDataConfiguration オブジェクトに hivePartitioningOptions オブジェクトを含めます。

hivePartitioningOptions.mode フィールドを CUSTOM に設定した場合、hivePartitioningOptions.sourceUriPrefix フィールドのパーティション キースキーマを次のように入力します。gs://BUCKET/PATH_TO_TABLE/{KEY1:TYPE1}/{KEY2:TYPE2}/...

クエリの実行時に述語フィルタの使用を強制するには、hivePartitioningOptions.requirePartitionFilter フィールドを true に設定します。

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.ExternalTableDefinition;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.HivePartitioningOptions;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create external table using hive partitioning
public class SetHivePartitioningOptions {

  public static void main(String[] args) {
    // 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/hive-partitioning-samples/customlayout/*";
    String sourceUriPrefix =
        "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/{pkey:STRING}/";
    setHivePartitioningOptions(datasetName, tableName, sourceUriPrefix, sourceUri);
  }

  public static void setHivePartitioningOptions(
      String datasetName, String tableName, String sourceUriPrefix, String sourceUri) {
    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();

      // Configuring partitioning options
      HivePartitioningOptions hivePartitioningOptions =
          HivePartitioningOptions.newBuilder()
              .setMode("CUSTOM")
              .setRequirePartitionFilter(true)
              .setSourceUriPrefix(sourceUriPrefix)
              .build();

      TableId tableId = TableId.of(datasetName, tableName);
      ExternalTableDefinition customTable =
          ExternalTableDefinition.newBuilder(sourceUri, FormatOptions.parquet())
              .setAutodetect(true)
              .setHivePartitioningOptions(hivePartitioningOptions)
              .build();
      bigquery.create(TableInfo.of(tableId, customTable));
      System.out.println("External table created using hivepartitioningoptions");
    } catch (BigQueryException e) {
      System.out.println("External table was not created" + e.toString());
    }
  }
}

外部テーブルにクエリを実行する

詳細については、外部テーブルの Cloud Storage データにクエリを実行するをご覧ください。

外部テーブルを BigLake にアップグレードする

外部テーブルを接続に関連付けることで、Cloud Storage に基づくテーブルを BigLake テーブルにアップグレードできます。BigLake テーブルでメタデータ キャッシュを使用する場合、この設定を同時に指定できます。ソース形式やソース URI など、テーブルの詳細情報を取得するには、テーブル情報の取得をご覧ください。

外部テーブルを BigLake テーブルに更新するには、次のいずれかのオプションを選択します。

SQL

CREATE OR REPLACE EXTERNAL TABLE DDL ステートメントを使用してテーブルを更新します。

  1. Google Cloud コンソールで、[BigQuery] ページに移動します。

    [BigQuery] に移動

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

    CREATE OR REPLACE EXTERNAL TABLE
  `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
  WITH CONNECTION {`REGION.CONNECTION_ID` | DEFAULT}
  OPTIONS(
    format ="TABLE_FORMAT",
    uris = ['BUCKET_PATH'],
    max_staleness = STALENESS_INTERVAL,
    metadata_cache_mode = 'CACHE_MODE'
    );

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

    • PROJECT_ID: テーブルを含むプロジェクトの名前
    • DATASET: テーブルを含むデータセットの名前
    • EXTERNAL_TABLE_NAME: テーブルの名前
    • REGION: 接続を含むリージョン
    • CONNECTION_ID: 使用する接続の名前

      デフォルトの接続を使用するには、REGION.CONNECTION_ID を含む接続文字列ではなく、DEFAULT を指定します。

    • TABLE_FORMAT: テーブルで使用される形式

      テーブルの更新時にこれを変更することはできません。

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

      パスにワイルドカードとしてアスタリスク（*）を 1 つ使用して、バケットから複数のファイルを選択することもできます。例: ['gs://mybucket/file_name*']詳細については、Cloud Storage の URI でのワイルドカードのサポートをご覧ください。

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

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

      • ['gs://bucket/path1/myfile.csv']
      • ['gs://bucket/path1/*.csv']
      • ['gs://bucket/path1/*', 'gs://bucket/path2/file00*']

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

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

    • STALENESS_INTERVAL: キャッシュ内のメタデータをテーブルに対するオペレーションで使用するかどうかを指定します。また、オペレーションがキャッシュ内のメタデータを使用するためにその鮮度を指定します。

      メタデータ キャッシュに関する考慮事項の詳細については、メタデータ キャッシュによるパフォーマンスの向上をご覧ください。

      メタデータのキャッシュ保存を無効にするには、0 を指定します。これがデフォルトです。

      メタデータ キャッシュを有効にするには、30 分から 7 日の間で間隔リテラルの値を指定します。たとえば、4 時間のステイルネス間隔には INTERVAL 4 HOUR を指定します。この値を指定すると、キャッシュに保存されたメタデータが過去 4 時間以内に更新されていれば、テーブルに対するオペレーションはそのメタデータを使用します。キャッシュに保存されているメタデータがそれより古い場合、オペレーションは代わりに Cloud Storage からメタデータを取得します。

    • CACHE_MODE: メタデータ キャッシュを自動的に更新するか手動で更新するかを指定します。

      メタデータ キャッシュに関する考慮事項の詳細については、メタデータ キャッシュのパフォーマンスをご覧ください。

      AUTOMATIC に設定すると、メタデータ キャッシュがシステムで定義された間隔（通常は 30～60 分）で更新されます。

      自身で決めたスケジュールでメタデータ キャッシュを更新する場合は、MANUAL に設定します。この場合は、BQ.REFRESH_EXTERNAL_METADATA_CACHE システム プロシージャを呼び出してキャッシュを更新できます。

      STALENESS_INTERVAL が 0 より大きい値に設定されている場合は、CACHE_MODE を設定する必要があります。

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

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

bq

テーブルを更新するには、bq mkdef コマンドと bq update コマンドを使用します。

  1. 変更するテーブルの要素を記述する外部テーブル定義を生成します。

    bq mkdef --connection_id=PROJECT_ID.REGION.CONNECTION_ID \
--source_format=TABLE_FORMAT \
--metadata_cache_mode=CACHE_MODE \
"BUCKET_PATH" > /tmp/DEFINITION_FILE

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

    • PROJECT_ID: 接続を含むプロジェクトの名前。
    • REGION: 接続を含むリージョン。
    • CONNECTION_ID: 使用する接続の名前。
    • TABLE_FORMAT: テーブルで使用される形式。テーブルの更新時にこれを変更することはできません。

    • CACHE_MODE: メタデータ キャッシュを自動的に更新するか手動で更新するかを指定します。メタデータ キャッシュに関する考慮事項の詳細については、メタデータ キャッシュによるパフォーマンスの向上をご覧ください。

      AUTOMATIC に設定すると、メタデータ キャッシュがシステムで定義された間隔（通常は 30～60 分）で更新されます。

      自身で決めたスケジュールでメタデータ キャッシュを更新する場合は、MANUAL に設定します。この場合は、BQ.REFRESH_EXTERNAL_METADATA_CACHE システム プロシージャを呼び出してキャッシュを更新できます。

      STALENESS_INTERVAL が 0 より大きい値に設定されている場合は、CACHE_MODE を設定する必要があります。

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

      バケットから選択したファイルを制限するには、パスにワイルドカードとしてアスタリスク（*）を 1 つ指定します。例: gs://mybucket/file_name*詳細については、Cloud Storage の URI でのワイルドカードのサポートをご覧ください。

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

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

      • gs://bucket/path1/myfile.csv
      • gs://bucket/path1/*.csv
      • gs://bucket/path1/*,gs://bucket/path2/file00*

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

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

    • DEFINITION_FILE: 作成するテーブル定義ファイルの名前。

  2. 新しい外部テーブルの定義を使用してテーブルを更新します。

    bq update --max_staleness=STALENESS_INTERVAL \
--external_table_definition=/tmp/DEFINITION_FILE \
PROJECT_ID:DATASET.EXTERNAL_TABLE_NAME

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

    • STALENESS_INTERVAL: キャッシュ内のメタデータをテーブルに対するオペレーションで使用するかどうかを指定します。また、オペレーションがキャッシュ内のメタデータを使用するためにその鮮度を指定します。メタデータ キャッシュに関する考慮事項の詳細については、メタデータ キャッシュによるパフォーマンスの向上をご覧ください。

      メタデータのキャッシュ保存を無効にするには、0 を指定します。これがデフォルトです。

      メタデータ キャッシュを有効にするには、INTERVAL データ型ドキュメントで説明されている Y-M D H:M:S 形式を使用して、30 分から 7 日の間隔値を指定します。たとえば、4 時間のステイルネス間隔の場合、0-0 0 4:0:0 を指定します。この値を指定すると、キャッシュに保存されたメタデータが過去 4 時間以内に更新されていれば、テーブルに対するオペレーションはそのメタデータを使用します。キャッシュに保存されているメタデータがそれより古い場合、オペレーションは代わりに Cloud Storage からメタデータを取得します。

    • DEFINITION_FILE: 作成または更新したテーブル定義ファイルの名前。

    • PROJECT_ID: テーブルを含むプロジェクトの名前。

    • DATASET: テーブルを含むデータセットの名前

    • EXTERNAL_TABLE_NAME: テーブルの名前。

Cloud Storage リソースパス

Cloud Storage データソースに基づいて外部テーブルを作成する場合は、データのパスを指定する必要があります。

Cloud Storage のリソースパスには、バケット名とオブジェクト（ファイル名）が含まれます。たとえば、Cloud Storage バケットの名前が mybucket でデータファイルの名前が myfile.csv の場合、リソースパスは gs://mybucket/myfile.csv になります。

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

Cloud Storage のリソースパスを取得するには:

  1. Cloud Storage コンソールを開きます。

    Cloud Storage コンソール

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

  3. オブジェクトの名前をクリックします。

    [オブジェクトの詳細] ページが開きます。

  4. [gsutil URI] フィールドに表示されている値（gs:// で始まる）をコピーします。

Cloud Storage の URI でのワイルドカードのサポート

データが複数のファイルに分かれている場合は、ワイルドカードとしてアスタリスク（*）を使用して複数のファイルを選択できます。ワイルドカードとしてアスタリスクを使用する場合は、次のルールに従う必要があります。

  • アスタリスクは、オブジェクト名の中または末尾に使用できます。
  • 複数のアスタリスクは使用できません。たとえば、パス gs://mybucket/fed-*/temp/*.csv は無効です。
  • バケット名にはアスタリスクを使用できません。

例:

  • 次の例では、gs://mybucket/fed-samples/fed-sample で始まるすべてのフォルダ内のすべてのファイルを選択する方法を示します。

    gs://mybucket/fed-samples/fed-sample*

  • 次の例では、fed-samples というフォルダと fed-samples のサブフォルダにある .csv という拡張子のファイルのみを選択する方法を示します。

    gs://mybucket/fed-samples/*.csv

  • 次の例では、fed-samples という名前のフォルダで fed-sample*.csv という命名パターンのファイルを選択する方法を示します。この例では、fed-samples のサブフォルダ内のファイルは選択されません。

    gs://mybucket/fed-samples/fed-sample*.csv

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

Datastore または Firestore のエクスポートにリンクする外部テーブルを作成する場合、ワイルドカードとしてアスタリスクは使用できません。

料金

BigQuery リクエストには、次の Cloud Storage の検索料金とデータ転送料金が適用されます。

制限事項

外部テーブルに適用される制限事項については、外部テーブルの制限事項をご覧ください。

次のステップ