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

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

管理パーティション分割テーブルでのクエリの手順については、パーティション分割テーブルの操作をご覧ください。

制限事項

  • Hive パーティショニングのサポートは、パーティション エンコードの直前に終了するすべての URI の共通ソース URI プレフィックスを想定して構築されます。これは gs://<bucket>/<path_to_table>/. のように見えます。
  • Hive パーティション分割テーブルのディレクトリ構造では、同じパーティション キーが同じ順序で表示され、テーブルごとに最大 10 個のパーティション キーがあると想定されます。
  • データはデフォルトの Hive パーティショニング レイアウトに従う必要があります。
  • Hive パーティショニング キーと基になるファイル内の列は重複できません。
  • テーブルの作成と変更は現在、Cloud Console、bq コマンドライン ツール、REST API に限定されています。
  • Cloud Storage に保存されている外部データソースのクエリにはすべての制限が適用されます。
  • サポートは標準 SQL に対してのみです。

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

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

gs://bucket/table/dt=2019-10-31/lang=en/foo
gs://bucket/table/dt=2018-10-31/lang=fr/bar

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

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

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

gs://bucket/table/2019-10-31/en/foo

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

gs://bucket/table/dt=2019-10-31/lang=en/foo
gs://bucket/table/lang=fr/dt=2018-10-31/bar

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

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

  • AUTO: キーの名前と型は自動検出されます。次の型を検出できます。STRING、INTEGER、DATE、TIMESTAMP。
  • STRINGS: キー名は、STRING タイプで自動的に推測されます。
  • CUSTOM: パーティション キー スキーマがソース URI 接頭辞でエンコードされています。

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

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

たとえば、source_uri_prefix を gs://my_bucket/my_table/{dt:DATE}/{val:STRING} に設定すると val は STRING として処理され、dt は DATE として処理され、一致したファイルのソース URI 接頭辞として gs://my_bucket/my_table が抽出されます。

詳しくは、Cloud Console、bq コマンドライン ツール、REST API をご覧ください。

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

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。

パーティション分割テーブルを作成する際に、requirePartitionFilter オプションを追加する方法について詳しくは、bq コマンドライン ツールおよび REST API の手順をご覧ください。

Cloud Console を使用した HivePartitioningOptions の設定

Cloud Console で Hive パーティショニング オプションを設定する手順は次のとおりです。

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

    BigQuery ページに移動

  2. [リソース] ペインで、データセットを選択します。

  3. [テーブルを作成] ボタンをクリックして、新しいテーブルを作成します。

  4. [テーブルの作成元] プルダウンから [Cloud Storage] を選択します。

Hive パーティショニング オプション

  1. [Cloud Storage バケットからファイルを選択] フィールドにデータセットのファイルパスを入力し、次にプルダウンからそのファイルタイプを選択します。
  2. [ソースデータ パーティショニング] ボックスをオンにして、指定されたフィールドにソース URI プレフィックスを入力します。
  3. [パーティション推論モード] で、AUTO の場合は [種類を自動的に推測します] を選択し、STRINGS の場合は [すべての列は文字列です] を選択します。
  4. [表の種類] プルダウンから [外部テーブル] を選択します。

bq コマンドライン ツールを使用した HivePartitioningOptions の設定

HivePartitioningOptions は、テーブル定義ファイルの作成中に ExternalDataConfiguration オブジェクトに設定されます。

パーティション キーの型の自動検出のリクエスト

bq mkdef --source_format=ORC --hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=gcs_uri_shared_prefix \
gcs_uris >/tmp/table_def_file

文字列型パーティション キー検出のリクエスト

bq mkdef --source_format=PARQUET --hive_partitioning_mode=STRINGS \
--hive_partitioning_source_uri_prefix=gcs_uri_shared_prefix \
gcs_uris >/tmp/table_def_file

source_uri_prefix フィールドでエンコードされたカスタム パーティション キースキーマの指定

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 >/tmp/table_def_file

CUSTOM: パーティション キー スキーマはソース URI 接頭辞の直後にエンコードされます。次の形式を使用して --hive_partitioning_source_uri_prefix を指定してください。

--hive_partitioning_source_uri_prefix=gcs_uri_shared_prefix/{key1:TYPE1}/{key2:TYPE2}/{key3:TYPE3}

パーティション フィルタを必須にする

クエリの実行時に述語フィルタの使用を強制するには、上記の bq mkdef コマンドに --require_hive_partition_filter=True を追加します。この例では、AUTO パーティション キーの型の検出を使用します。

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 >/tmp/table_def_file

REST API を使用した HivePartitioningOptions の設定

テーブル定義ファイルの作成時に ExternalDataConfiguration に HivePartitioningOptions を設定することで Hive パーティショニングがサポートされます。

なお、hivePartitioningOptions.modeが CUSTOM に設定されている場合、hivePartitioningOptions.sourceUriPrefix を gs://bucket/path_to_table/{key1:TYPE1}/{key2:TYPE2}/{key3:TYPE3} に設定してパーティション キー スキーマを sourceUriPrefix 内でエンコードする必要があるので注意してください。

クエリの実行時に述語フィルタの使用を強制するには、hivePartitioningOptions.requirePartitionFilter を true に設定します。