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

BigQuery は、デフォルトの Hive パーティショニングレイアウトを使用して、Cloud Storage に保存されている Avro、Parquet、ORC、JSON、CSV 形式の外部パーティションデータのクエリをサポートしています。Hive パーティショニングのサポートは、テーブル定義ファイルで適切なオプションを設定することにより有効化されます。テーブルの作成と変更には、Cloud Console、bq コマンドラインツール、BigQuery API を使用できます。

マネージドパーティション分割テーブルでのクエリの手順については、パーティション分割テーブルの概要をご覧ください。

制限事項

Hive パーティショニングのサポートは、すべての URI において、パーティションエンコードの直前に共通のソース URI プレフィックスがあるものと想定して構築されています。例: gs://BUCKET/PATH_TO_TABLE/。
Hive パーティション分割テーブルのディレクトリ構造では、同じパーティショニングキーが同じ順序で表示され、テーブルごとに最大 10 個のパーティションキーがあると想定されます。
データはデフォルトの Hive パーティショニングレイアウトに従う必要があります。
Hive パーティショニングキーと基になるファイル内の列は重複できません。
Cloud Storage に保存されている外部データソースのクエリにはすべての制限が適用されます。
サポートは標準 SQL に対してのみです。

サポートされるデータレイアウト

データはデフォルトの Hive パーティション分割レイアウトに従う必要があります。たとえば、次のファイルはデフォルトのレイアウトに従っています。すなわち、Key-Value ペアがディレクトリとして = 記号のセパレータでレイアウトされていて、パーティションキーが常に同じ順序になっています。

gs://myBucket/myTable/dt=2019-10-31/lang=en/foo
gs://myBucket/myTable/dt=2018-10-31/lang=fr/bar

この例で共通のソース URI プレフィックスは、gs://myBucket/myTable です。

サポートされないデータレイアウト

ディレクトリパス内でパーティションキー名がエンコードされていない場合、パーティションスキーマの検出は失敗します。たとえば、パーティションキー名をエンコードしない次のパスを考えてみましょう。

gs://myBucket/myTable/2019-10-31/en/foo

スキーマの順序が一貫していないファイルも検出に失敗します。たとえば、逆パーティションキーエンコーディングを使用した次の 2 つのファイルについて考えてみましょう。

gs://myBucket/myTable/dt=2019-10-31/lang=en/foo
gs://myBucket/myTable/lang=fr/dt=2018-10-31/bar

これらのファイルの場合、パーティションスキーマの検出は失敗します。

パーティションスキーマ検出モード

Cloud Storage からデータをクエリする場合、Hive パーティションキーは通常の列として表示されます。BigQuery では、以下の 3 つの Hive パーティションスキーマ検出モードがサポートされています。

AUTO: キー名と型は自動検出されます。次の型を検出できます: STRING、INTEGER、DATE、TIMESTAMP。
STRINGS: キー名は自動的に STRING 型に変換されます。
CUSTOM: パーティションキースキーマはソース URI プレフィックスで指定されたとおりにエンコードされます。

カスタムパーティションキースキーマの指定

CUSTOM スキーマを使用するには、ソース URI プレフィックスフィールドでスキーマを指定する必要があります。CUSTOM スキーマを使用すると、各パーティションキーのタイプを指定できます。値は、指定された型で有効に解析される必要があります。そうでない場合、クエリは失敗します。

たとえば、source_uri_prefix フラグを gs://myBucket/myTable/{dt:DATE}/{val:STRING} に設定すると、BigQuery は val を STRING として処理し、dt を DATE として処理し、gs://myBucket/myTable を一致したファイルのソース URI プレフィックスとして使用します。

パーティションのプルーニング

BigQuery は、パーティションキーのクエリ述部を使用して、可能な場合はパーティションをプルーンします。これにより、BigQuery により不要なファイルの読み取りが行われなくなるため、パフォーマンスを向上できます。

クエリ内のパーティションキーに対する必須の述語フィルタ

外部パーティション分割テーブルを作成する場合は、HivePartitioningOptions で requirePartitionFilter オプションを有効にして、パーティションキーに対して述語フィルタの使用を要求できます。

このオプションを有効にした場合、WHERE 句を指定しないで外部パーティション分割テーブルにクエリを実行すると、次のエラーが発生します。Cannot query over table <table_name> without a filter over column(s) <partition key names> that can be used for partition elimination。

Hive パーティション分割データ用の外部テーブルの作成

Hive パーティション分割データ用の外部テーブルは、次の方法で作成できます。

Cloud Console を使用する。
bq コマンドラインツールを使用する。
クライアントライブラリを使用する。

コンソール

Cloud Console で [BigQuery] ページを開きます。

[BigQuery] ページに移動
[エクスプローラ] パネルでプロジェクトを展開し、データセットを選択します。

注: デフォルトでは、プレビューリリースの Cloud Console が表示されます。[PREVIEW 機能を非表示] をクリックして一般提供の Cloud Console に移動した場合は、ナビゲーションパネルの [リソース] セクションでプロジェクトを展開し、データセットを選択します。
[テーブルを作成] をクリックして、新しいテーブルを作成します。
[テーブルの作成] ページの [ソース] セクションで、次の操作を行います。
1. [テーブルの作成元] プルダウンリストから [Cloud Storage] を選択します。
2. [Cloud Storage バケットからファイルを選択] フィールドに、ワイルドカード形式を使用して Cloud Storage フォルダのパスを入力します。例: my_bucket/my_files*
3. [ファイル形式] プルダウンリストからファイル形式を選択します。
4. [ソースデータパーティショニング] チェックボックスをオンにします。
5. [ソース URI の接頭辞を選択] フィールドに、Cloud Storage URI の接頭辞を入力します。例: gs://my_bucket/my_files
6. [パーティション推論モード] を選択します。[独自指定] を選択した場合、パーティションキーのスキーマ情報を入力します。
7. 省略可: このテーブルに対するすべてのクエリで述語フィルタを使用する必要がある場合は、[パーティションフィルタを要求] チェックボックスをオンにします。詳細については、クエリ内のパーティションキーに対する述語フィルタの要求をご覧ください。
[テーブルの作成] ページの [出力先] セクションで、次の操作を行います。
1. プロジェクト名とデータセット名を選択します。
2. [テーブルタイプ] プルダウンリストで、[外部テーブル] を選択します。
3. [テーブル名] フィールドに、外部テーブルの名前を入力します。
[テーブルの作成] ページの [スキーマ] セクションで、スキーマ情報を入力します。BigQuery は一部の形式に対するスキーマの自動検出をサポートしています。詳細については、スキーマの自動検出の使用をご覧ください。
[テーブルを作成] をクリックします。

bq

自動パーティションキー検出を設定するには、--hive_partitioning_mode フラグを AUTO に設定します。例:

bq mkdef --source_format=ORC --hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
GCS_URIS > TABLE_DEF_FILE

文字列型のパーティションキー検出を設定するには、--hive_partitioning_mode フラグを STRINGS に設定します。例:

bq mkdef --source_format=PARQUET --hive_partitioning_mode=STRINGS \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
GCS_URIS > TABLE_DEF_FILE

--source_uri_prefix フラグを使用してカスタムパーティションキースキーマを指定するには、--hive_partitioning_mode フラグを CUSTOM に設定します。例:

bq mkdef --source_format=NEWLINE_DELIMITED_JSON --hive_partitioning_mode=CUSTOM \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX/CUSTOM_SCHEMA_ENCODING \
GCS_URIS FILE_SCHEMA > TABLE_DEF_FILE

--hive_partitioning_source_uri_prefix フラグの場合、<var>GCS_URI_SHARED_PREFIX</var> の部分の直後に引数の <var>CUSOM_SCHEMA_ENCODING</var> の部分を指定します。次に例を示します。

GCS_URI_SHARED_PREFIX/{KEY1:TYPE1}/{KEY2:TYPE2}/...

例:

--hive_partitioning_source_uri_prefix=gs://myBucket/myTable/{dt:DATE}/{val:STRING}

クエリの実行時に述語フィルタの使用を要求するには、bq mkdef コマンドに --require_hive_partition_filter=True を追加します。例:

bq mkdef --source_format=ORC --hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
--require_hive_partition_filter=True \
GCS_URIS > TABLE_DEF_FILE

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

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