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

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

Console

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

    BigQuery に移動

  2. [エクスプローラ] パネルでプロジェクトを展開し、データセットを選択します。
  3. [アクションを表示] をクリックし、[開く] をクリックします。
  4. 詳細パネルで [ テーブルを作成] をクリックします。
  5. [テーブルを作成] ページで、次の詳細を指定します。
    1. [ソース] セクションの [テーブルの作成元] で [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. (省略可)このテーブルのすべてのクエリでパーティション フィルタを要求するには、[パーティション フィルタを要求] チェックボックスをオンにします。パーティション フィルタを要求すると、コストが削減され、パフォーマンスが向上する可能性があります。詳細については、クエリ内のパーティション キーに対する必須の述語フィルタをご覧ください。
    2. [送信先] セクションで、次の詳細を指定します。
      1. [データセット名] に、テーブルを作成するデータセットを選択します。
      2. [テーブル] フィールドに、作成するテーブルの名前を入力します。
      3. [テーブルタイプ] フィールドが [外部テーブル] に設定されていることを確認します。
    3. [スキーマ] セクションでスキーマ定義を入力します。
    4. スキーマの自動検出を有効にするには、[自動検出] を選択します。
    5. [テーブルを作成] をクリックします。

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_MODECUSTOM の場合、次の形式を使用して、ソース 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 のリファレンス ドキュメントをご覧ください。

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

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

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