BigQuery の BigLake テーブルと Hive パーティショニングを利用して無駄なファイルの読み込みを避けてみた

Google Cloud データエンジニアのはんざわです。

BigQuery を利用していると Cloud Storage に保存されているファイルを読み込みたいといったケースはよくあると思います。
BigQuery では、外部テーブルや BigLake の機能を利用することで、BigQuery から Cloud Storage のデータを直接読み込むことができます。
しかし、Cloud Storage に保存されているデータ量が多い場合、本来であれば不要なファイルも読み込まれてしまうことで無駄な課金が発生したり、パフォーマンスが悪くなったりする恐れがあります。
BigQuery には、そのような問題を解決する機能として、Hive パーティショニングが用意されています。

本ブログでは、Hive パーティショニングを実際に試してみて、その内容を紹介したいと思います。

Hive パーティションとは？

https://cloud.google.com/bigquery/docs/hive-partitioned-queries?hl=ja

通常のテーブルにおけるパーティション分割は、特定の列の値を基準に１つのテーブルを内部的に細かく分割します。これにより、クエリがスキャンする範囲を減らし、コスト削減とパフォーマンスの向上に繋がります。

一方で、Hive パーティショニングは、Hive 形式で分けられている Cloud Storage のディレクトリ構造を利用してデータを効率的に分割する仕組みです。このディレクトリ構造を利用することで、クエリ実行時に指定されたパーティションキーに基づいて必要なデータだけをスキャンするため、スキャン量を大幅に削減できます。

Hive 形式とは、Key-Value のペアが等号記号（=）で繋がっているレイアウトです。この形式では、Cloud Storage 上のパスに基づいてパーティションが管理されます。Hive 形式の例は以下のとおりです。

gs://my_bucket/my_table/dt=2025-01-01/my_filename
gs://my_bucket/my_table/dt=2025-01-02/my_filename

この例の場合、パーティションキーに dt を使用し、2025-01-01 や 2025-01-02 を指定することで、そのパス配下のファイルのみをスキャンの対象にできます。

上記のように、Hive 形式のパスにおいてパーティションキーが常に同じ順序で記述されていれば、Hive パーティショニングを利用できます。一方で、以下のようにパス内でパーティションキーの順序が異なる場合、Hive パーティショニングを利用できないので、注意が必要です。

gs://my_bucket/my_table/dt=2025-01-01/lang=en/my_filename
gs://my_bucket/my_table/lang=ja/dt=2025-01-01/my_filename

Hive パーティションを試してみた

【事前準備】Cloud Storage バケットの作成とサンプルデータの準備

まず初めに、BigLake テーブルが参照する Cloud Storage バケットを作成します。

$ gcloud storage buckets create gs://cm-hanzawa-hive \
      --location=us-central1 \
      --soft-delete-duration=0 \
      --uniform-bucket-level-access

次に、BigQuery のサンプルデータを作成した Cloud Storage バケットにエクスポートします。
エクスポート先のパスは、gs://cm-hanzawa-hive/worldpop/ が共通部分のパスであり、dt=yyyy-mm-dd がパーティションキーとして機能する Hive 形式のパス構造になっています。この構造により、dt 列の値を基準に効率的にデータを管理できます。

/* 2010-01-01 のデータを Cloud Storage にエクスポート */
EXPORT DATA
OPTIONS (
    uri = 'gs://cm-hanzawa-hive/worldpop/dt=2010-01-01/*.json',
    format = 'JSON'
) AS (
  SELECT
    *
  FROM
    `bigquery-public-data.worldpop.population_grid_1km`
  WHERE
    last_updated = '2010-01-01'
);

/* 2011-01-01 のデータを Cloud Storage にエクスポート */
EXPORT DATA
OPTIONS (
    uri = 'gs://cm-hanzawa-hive/worldpop/dt=2011-01-01/*.json',
    format = 'JSON'
) AS (
  SELECT
    *
  FROM
    `bigquery-public-data.worldpop.population_grid_1km`
  WHERE
    last_updated = '2011-01-01'
);

エクスポートした後のバケット内のディレクトリ構成は、以下のようになっています。

gs://cm-hanzawa-hive/worldpop/dt=2010-01-01/*.json
gs://cm-hanzawa-hive/worldpop/dt=2011-01-01/*.json

【事前準備】BigLake テーブルの作成

次に、BigLake テーブルを作成します。

BigQuery から Cloud Storage のファイルを直接参照する場合、従来の外部テーブルを使用する方法と BigLake テーブルを使用する方法の２つがあります。現在は、BigLake テーブルの使用が推奨されているため、特別な理由がない限りこちらを使用することをおすすめします。

BigLake テーブルの使用が推奨されている理由や作成手順の詳細は、以下のブログで解説しています。ぜひ参考にしてください。

https://dev.classmethod.jp/articles/gcp-bigquery-biglake/

まずは、BigQuery Connection を作成します。

$ bq mk --connection \
      --location=us-central1 \
      --connection_type=CLOUD_RESOURCE \
      biglake_connection

次に、BigQuery Connection を構成しているサービスアカウントが事前に作成した Cloud Storage のサンプルデータを参照できるように権限を振ります。具体的には、BigQuery Connection のサービスアカウント bqcx-868908576898-83wo@gcp-sa-bigquery-condel.iam.gserviceaccount.com にバケット単位 gs://cm-hanzawa-hive/ でオブジェクト閲覧者 roles/storage.objectViewer の権限を振ります。

$ bq show --format json --connection us-central1.biglake_connection | jq -r .cloudResource.serviceAccountId

> bqcx-868908576898-83wo@gcp-sa-bigquery-condel.iam.gserviceaccount.com

$ gcloud storage buckets add-iam-policy-binding gs://cm-hanzawa-hive \
      --member=serviceAccount:bqcx-868908576898-83wo@gcp-sa-bigquery-condel.iam.gserviceaccount.com \
      --role=roles/storage.objectViewer

最後に、Hive パーティショニングを有効にした BigLake テーブルを作成します。

BigLake テーブルで Hive パーティショニングを利用するには、WITH PARTITION COLUMNS でパーティションキーのカラム名と型を指定し、hive_partition_uri_prefix で共通部分のパスを指定する必要があります。この例では、パーティションキーとして dt に DATE 型を指定し、共通部分のパスには gs://cm-hanzawa-hive/worldpop/ を設定しています。なお、パーティションキーは前述した Hive 形式に従う必要がありますが、自動検出させることも可能です。

CREATE OR REPLACE EXTERNAL TABLE `hive.worldpop` (
    country_name STRING,
    geo_id STRING,
    population FLOAT64,
    longitude_centroid FLOAT64,
    latitude_centroid FLOAT64,
    alpha_3_code STRING,
    geog GEOGRAPHY,
    last_updated DATE
)
WITH PARTITION COLUMNS
(
    dt DATE
)
WITH CONNECTION `us-central1.biglake_connection`
OPTIONS (
    format = 'JSON',
    hive_partition_uri_prefix = 'gs://cm-hanzawa-hive/worldpop/',
    uris = ['gs://cm-hanzawa-hive/worldpop/*']
);

参考：CREATE EXTERNAL TABLE statement

BigLake テーブルを参照してみる

まずは、パーティショニングフィルタを含めずに BigLake テーブルをフルスキャンしてみます。

SELECT
  COUNT(*)
FROM
  `hive.worldpop`

-- 実行結果: 437975328

実行した結果は以下のとおりです。
実行時間が 50 秒でスキャンデータ量が 158.27 GB という結果になりました。

次に、dt = '2011-01-01' のパーティショニングフィルタを含めた状態でクエリを実行してみます。

SELECT
  COUNT(*)
FROM
  `hive.worldpop`
WHERE
  dt = '2011-01-01'

-- 実行結果: 218986953

実行した結果は以下のとおりです。
実行時間が 20 秒でスキャンデータ量が 79.14 GB という結果になり、フルスキャンした結果と比べて実行時間もスキャン量も大幅に削減されていることが分かります。

おまけ

ここからは、おまけとして Hive パーティショニングを利用する際に考えられる特殊なケースについて検証を行います。
検証内容は以下の２つです。

1. 存在しないパーティションの値を指定してみる
2. 読み込み範囲外に無効なファイルを配置してみる

1. 存在しないパーティションの値を指定してみる

まずは、パーティションキーに存在しない値を指定してみたいと思います。
事前準備でエクスポートした結果、以下のようなバケットのパスの構成になっています。

gs://cm-hanzawa-hive/worldpop/dt=2010-01-01/*.json
gs://cm-hanzawa-hive/worldpop/dt=2011-01-01/*.json

この構成であれば、dt のパーティションキーに 2010-01-01 や 2011-01-01 の値を指定することでその配下のファイルのみがスキャンされます。

では、これら以外の存在しない日付（例：1111-11-11）を指定した場合の挙動を確認してみます。

SELECT
  COUNT(*)
FROM
  `hive.worldpop`
WHERE
  dt = '1111-11-11'

> 0

実行した結果は、以下のとおりです。
クエリ自体は問題なく成功しましたが、実行時間もスキャン量も 0 となっていました。

2. スキャンの範囲外に無効なファイルを配置してみる

検証したい内容のイメージとして、以下のような構成になります。

  gs://cm-hanzawa-hive/worldpop/dt=2010-01-01/*.json
  gs://cm-hanzawa-hive/worldpop/dt=2011-01-01/*.json
+ gs://cm-hanzawa-hive/worldpop/dt=2012-01-01/sample.png

新たに dt=2012-01-01 のディレクトリを作成し、そこに png ファイルを配置します。
この状態でフルスキャンすると、BigQuery が解析できないファイル（この例では PNG ファイル）が含まれるため、エラーが発生します。BigQuery は指定されたデータ形式（この場合は JSON）に基づいてファイルを解析するため、形式が異なるファイルが含まれるとクエリが失敗します。

では、2011-01-01 など他の日付を指定し、その配下のファイルのみをスキャンした場合でもエラーになるのか確認してみたいと思います。

-- パーティションを指定せずにフルスキャン
SELECT
  COUNT(*)
FROM
  `hive.worldpop`

-- エラー発生
> Error while reading table: hive.worldpop, error message: JSON parsing error in row starting at position 0: Parser terminated before end of string File: gs://cm-hanzawa-hive/worldpop/dt=2012-01-01/sample.png

-- パーティションで 2011-01-01 を指定
SELECT
  COUNT(*)
FROM
  `hive.worldpop`
WHERE
  dt = '2011-01-01'

-- 正常に終了
> 218986953

結果からもわかるように、パーティションで指定された範囲内に無効な形式のファイルが含まれている場合、エラーが発生します。しかし、パーティションでスキャン範囲を限定することで、範囲外に無効なファイルが存在していてもエラーを回避することができます。

まとめ

本ブログでは、BigQuery の Hive パーティショニングを活用することで、Cloud Storage 上の不要なファイルのスキャンを回避し、コスト削減やパフォーマンス向上を図る方法を紹介しました。
Hive パーティショニングは、大量のデータを扱う場面で特に有効な機能です。適切に活用することで、Cloud Storage のスキャン量を削減し、BigQuery のクエリコストを最適化できます。

本ブログが参考になれば幸いです。