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 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 サービスアカウントの詳細については、サービスアカウントをご覧ください。

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

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

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

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

コンソール

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

[BigQuery] に移動
[エクスプローラ] ペインでプロジェクトを開いて、データセットを選択します。
アクション オプションを開き、[テーブルを作成] をクリックします。
[送信元] セクションで、次の詳細を指定します。
1. [テーブルの作成元] で [Google Cloud Storage] を選択します。
2. GCS バケットからファイルを選択するか、URI パターンを使用するには、使用するバケットとファイルを参照して選択するか、gs://bucket_name/[folder_name/]file_name の形式でパスを入力します。
  
  Google Cloud コンソールで複数の URI を指定することはできませんが、ワイルドカードとしてアスタリスク（*）を 1 つ指定することで複数のファイルを選択できます。たとえば、gs://mybucket/file_name* のようにします。詳細については、Cloud Storage の URI でのワイルドカードのサポートをご覧ください。
  
  Cloud Storage バケットは、作成するテーブルを含むデータセットと同じロケーションに存在する必要があります。
3. [ファイル形式] で、ファイルと一致する形式を選択します。
[宛先] セクションで、次の詳細を指定します。
1. [プロジェクト] で、テーブルを作成するプロジェクトを選択します。
2. [データセット] で、テーブルを作成するデータセットを選択します。
3. [テーブル] に、作成するテーブルの名前を入力します。
4. [テーブルタイプ] で [外部テーブル] を選択します。
[スキーマ] セクションで、スキーマの自動検出を有効にするか、ソースファイルがある場合はスキーマを手動で指定できます。ソースファイルがない場合は、スキーマを手動で指定する必要があります。
- スキーマの自動検出を有効にするには、[自動検出] オプションをオンにします。
- 手動でスキーマを指定するには、[自動検出] オプションをオフにしておきます。[テキストとして編集] を有効にし、テーブルスキーマを JSON 配列として入力します。
スキーマと一致しない追加の列値を持つ行を無視するには、[詳細オプション] セクションを開いて [不明な値] を選択します。
[テーブルを作成] をクリックします。

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

SQL

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

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

[BigQuery] に移動
クエリエディタで次のステートメントを入力します。
```
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 リソースパスをご覧ください。
[実行] をクリックします。

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

例

次の例では、スキーマの自動検出を使用して、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 に対する認証を行うには、アプリケーションのデフォルト認証情報を設定します。詳細については、ローカル開発環境の認証を設定するをご覧ください。

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

# 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 パーティション分割データに外部テーブルを作成するには、次のいずれかのオプションを選択します。

コンソール

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

[BigQuery] に移動
[エクスプローラ] ペインでプロジェクトを開いて、データセットを選択します。
（アクションを表示）をクリックしてから、[テーブルを作成] をクリックします。[テーブルを作成] ペインが開きます。
[送信元] セクションで、次の詳細を指定します。

[テーブルの作成元] で [Google Cloud Storage] を選択します。
[Select file from Cloud Storage bucket] に、ワイルドカードを使用して Cloud Storage フォルダへのパスを入力します。たとえば、my_bucket/my_files* のようにします。Cloud Storage バケットは、作成、追加、または上書きするテーブルを含むデータセットと同じロケーションに存在している必要があります。
[ファイル形式] リストでファイル形式を選択します。
[ソースデータパーティショニング] チェックボックスをオンにして、[ソース URI の接頭辞を選択] に Cloud Storage URI の接頭辞を入力します。たとえば、gs://my_bucket/my_files のようにします。
[パーティション推論モード] セクションで、次のいずれかのオプションを選択します。
- 種類を自動的に推測します: パーティションスキーマ検出モードを AUTO に設定します。
- すべての列は文字列です: パーティションスキーマ検出モードを STRINGS に設定します。
- 独自に指定します: パーティションスキーマ検出モードを CUSTOM に設定し、パーティションキーのスキーマ情報を手動で入力します。詳細については、カスタムパーティションキースキーマを指定するをご覧ください。
省略可: このテーブルのすべてのクエリでパーティションフィルタを必須にするには、[パーティションフィルタを要求] チェックボックスをオンにします。パーティションフィルタを要求すると、コストが削減され、パフォーマンスが向上する場合があります。詳細については、クエリ内のパーティションキーに対する必須の述語フィルタをご覧ください。

[宛先] セクションで、次の詳細を指定します。
1. [プロジェクト] で、テーブルを作成するプロジェクトを選択します。
2. [データセット] で、テーブルを作成するデータセットを選択します。
3. [テーブル] に、作成するテーブルの名前を入力します。
4. [テーブルタイプ] で [外部テーブル] を選択します。
[スキーマ] セクションでスキーマ定義を入力します。
スキーマの自動検出を有効にするには、[自動検出] を選択します。
スキーマと一致しない追加の列値を持つ行を無視するには、[詳細オプション] セクションを開いて [不明な値] を選択します。
[テーブルを作成] をクリックします。

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

GitHub で表示フィードバック

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 ステートメントを使用してテーブルを更新します。

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

[BigQuery] に移動
クエリエディタで次のステートメントを入力します。
```
CREATE OR REPLACE EXTERNAL TABLE
  `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
  WITH CONNECTION `REGION.CONNECTION_ID`
  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: 使用する接続の名前
- 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 を設定する必要があります。
[実行] をクリックします。

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

bq

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

変更するテーブルの要素を記述する外部テーブル定義を生成します。
```
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: 作成するテーブル定義ファイルの名前。
新しい外部テーブルの定義を使用してテーブルを更新します。
```
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 のリソースパスを取得するには:

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

Cloud Storage Console
ソースデータを含むオブジェクト（ファイル）の場所に移動します。
オブジェクトの名前をクリックします。

[オブジェクトの詳細] ページが開きます。
[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 のエクスポートにリンクする外部テーブルを作成する場合、ワイルドカードとしてアスタリスクは使用できません。

制限事項

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

次のステップ

外部テーブルについて確認する。
BigLake テーブルについて確認する。

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

始める前に

必要なロール

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

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

コンソール

SQL

bq

API

Java

Node.js

Python

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

コンソール

SQL

bq

API

Java

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

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

SQL

bq

Cloud Storage リソースパス

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

制限事項

次のステップ

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