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

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 により不要なファイルの読み取りが行われなくなるため、パフォーマンスを向上できます。

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

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

このオプションを有効にした場合、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 コマンドライン ツールを使用する。
  • クライアント ライブラリを使用する。

Console

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

    [BigQuery] ページに移動

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

  3. アクション オプションを展開し、[開く] をクリックします。

  4. 詳細パネルで [テーブルを作成] をクリックします。

  5. [テーブルの作成] ページの [ソース] セクションで、次の操作を行います。

    1. [テーブルの作成元] プルダウン リストから [Google Cloud Storage] を選択します。
    2. [GCS バケットからファイルを選択] フィールドに、ワイルドカード形式を使用して Cloud Storage フォルダのパスを入力します。例: my_bucket/my_files*
    3. [ファイル形式] プルダウン リストからファイル形式を選択します。
    4. [ソースデータ パーティショニング] チェックボックスをオンにします。
    5. [ソース URI の接頭辞を選択] フィールドに、Cloud Storage URI の接頭辞を入力します。例: gs://my_bucket/my_files
    6. [パーティション推論モード] を選択します。[独自指定] を選択した場合、パーティション キーのスキーマ情報を入力します。
    7. (省略可)このテーブルに対するすべてのクエリで述語フィルタを使用する必要がある場合は、[パーティション フィルタを要求] チェックボックスをオンにします。詳細については、クエリ内のパーティション キーに対する必須の述語フィルタをご覧ください。
  6. [テーブルの作成] ページの [送信先] セクションで、次の操作を行います。

    1. プロジェクト名とデータセット名を選択します。
    2. [テーブルタイプ] プルダウン リストで、[外部テーブル] を選択します。
    3. [テーブル名] フィールドに、外部テーブルの名前を入力します。
  7. [テーブルの作成] ページの [スキーマ] セクションで、スキーマ情報を入力します。BigQuery では、一部の形式に対するスキーマの自動検出がサポートされています。詳細については、スキーマの自動検出の使用をご覧ください。

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

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

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