BigQueryテーブルをパーティション化して処理データ量を1/260に削減する!

まとめ

1ヶ月分のデータのうち2時間のタイムフレームを対象とするSelectクエリのデータ処理量は適切にパーティショニングされたテーブルでは非パーティショニングテーブルに比べ1/260に削減された。

データセットによっては巨大なデータが知らないうちに処理されBigQueryコスト大幅UPの原因になるため、データのインポートやテーブル作成の際は積極的にパーティショニングを有効化していきたい。

また、パーティションキーのタイムスタンプでSelect対象を絞り込んでもデータ処理量削減につながらないため同時にパーティションキーでタイムフレームを絞るように注意したい。

テストデータセット

New York Cityタクシー乗車記録を利用する。データはParquet形式で提供され、今回利用した2022年1月のデータ量は展開後におよそ343.03 MBとなった。
https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page

インポートオプション

インポート時にテーブルをパーティショニングするには --time_partitioning_fieldと --time_partitioning_typeオプションを利用する。

--time_partitioning_field

パーティションのキーを指定する。

--time_partitioning_field tpep_pickup_datetime

--time_partitioning_type

有効な値は DAY、HOUR、MONTH、または YEAR。

インポートコマンド

time_partitioning_typeごとに異なるBigQueryテーブルを作成した。

"tpep_pickup_datetime"カラムをパーティションキーとしtime_partitioning_type を"HOUR"とにする場合

bq load --source_format=PARQUET --time_partitioning_field tpep_pickup_datetime --time_partitioning_type HOUR test.yellow_tripdata_2022-01 ./yellow_tripdata_2022-01.parquet

比較用Selectクエリ

from google.cloud import bigquery
#クライアント作成
client = bigquery.Client()
#クエリ
dataset_name="test"
table_name="yellow_tripdata_2022-01"
query = """
    SELECT tpep_pickup_datetime,trip_distance,fare_amount
    FROM `{}.{}`
""".format(dataset_name,table_name)
#クエリ実行
query_job = client.query(query)

クエリ実行結果

--time_partitioning_type HOUR

このクエリを実行すると、148.17 KB が処理されます

--time_partitioning_type DAY

このクエリを実行すると、991.27 KB が処理されます。

--time_partitioning_type MONTH

このクエリを実行すると、37.6 MB が処理されます。

パーティショニングなし

1ヶ月分のデータをインポートしたのでtime_partitioning_type=MONTHと同じ処理対象データ量になる。

このクエリを実行すると、37.6 MB が処理されます。

　パーティションキーを自動で使ってくれないパターン

2022-01-01 12:０0から14:00の間にタクシーを降りた人の乗車日時、乗車距離、運賃(チップ等含まず)を取得したい場合のSQLクエリは以下のように書ける。

SELECT tpep_pickup_datetime,trip_distance,fare_amount FROM `test2.yellow_tripdata_2022-01` WHERE tpep_dropoff_datetime between  "2022-01-01 12:00:00" and "2022-01-01 14:00:00" 

ここで、"tpep_dropoff_datetime"カラムはパーティションキーではないことに注意する。これだとテーブル全体が処理対象とされてしまいBigQueryが読み込むデータ量が大きくなってしまう。

このクエリを実行すると、75.19 MB が処理されます。

タクシーの移動距離にもよるが乗車時間はたとえば最大6時間と仮定できるなら"tpep_pickup_datetime"カラムで乗車時間を絞るとパーティションキーが活用され処理対象データ量が1/156に減る。これで処理時間とコスト削減につながる。

SELECT tpep_pickup_datetime,trip_distance,fare_amount FROM `test2.yellow_tripdata_2022-01` WHERE tpep_dropoff_datetime between  "2022-01-01 12:00:00" and "2022-01-01 14:00:00" and tpep_pickup_datetime between  "2022-01-01 08:00:00" and "2022-01-01 14:00:00"

このクエリを実行すると、492.41 KB が処理されます。