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

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

Console

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

BigQuery に移動
[エクスプローラ] パネルでプロジェクトを展開し、データセットを選択します。
[アクションを表示] をクリックし、[開く] をクリックします。
詳細パネルで [ テーブルを作成] をクリックします。
[テーブルを作成] ページで、次の詳細を指定します。

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

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

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

外部のパーティション分割テーブルの更新

外部の永続テーブルを更新する場合、パーティションキーは不変である必要があります。