パーティショニングとクラスタリング

サンドボックスで完走可・無料枠内。元テーブルは 16 列・約 7.5 GiB ですが、列を 4 つに絞って CTAS するため、処理量は数百 MB 〜 1 GB 程度に収まります。

1. まず、公開データから列を絞って 1 年分だけ取り出した「種テーブル」を作ります。starttime は非パーティションテーブルの列なので、WHERE で期間を絞っても元テーブルのスキャン量そのものは変わりません（これがこの章の出発点です）。実行前に dry run で確認しておきます。

   bq query --use_legacy_sql=false --dry_run '
   SELECT starttime, tripduration, start_station_name, usertype
   FROM `bigquery-public-data.new_york_citibike.citibike_trips`
   WHERE starttime BETWEEN DATETIME("2017-01-01") AND DATETIME("2017-12-31 23:59:59")'

   starttime（DATETIME）と tripduration（INT64）だけで 1 行あたり固定 16 論理バイト（公式の Data type sizes 表で各 8 バイト）。58,937,715 行ぶんで少なくとも約 900 MiB、文字列 2 列を含めるとさらに増えます。表示された見積もりバイト数を控えておいてください（この後の Before 値として使います）。

2. 種テーブルを CTAS で作成します。

   CREATE OR REPLACE TABLE handson_ch06.trips_flat AS
   SELECT
     starttime,
     tripduration,
     start_station_name,
     usertype
   FROM `bigquery-public-data.new_york_citibike.citibike_trips`
   WHERE starttime BETWEEN DATETIME("2017-01-01") AND DATETIME("2017-12-31 23:59:59");

3. この種テーブルから、パーティションなし版とパーティションあり版を作ります。以降はどちらも数百 MB 級の小さい表なので、何度作り直しても無料枠を圧迫しません。

   CREATE OR REPLACE TABLE handson_ch06.trips_no_partition AS
   SELECT * FROM handson_ch06.trips_flat;
   
   CREATE OR REPLACE TABLE handson_ch06.trips_daily
   PARTITION BY DATE(starttime)
   AS
   SELECT * FROM handson_ch06.trips_flat;

4. 同じ「1 日だけ」を対象にした集計クエリを、両方のテーブルに対して dry run します。

   -- Before: パーティションなし
   bq query --use_legacy_sql=false --dry_run '
   SELECT usertype, COUNT(*) AS trips, AVG(tripduration) AS avg_duration
   FROM handson_ch06.trips_no_partition
   WHERE DATE(starttime) = "2017-06-15"
   GROUP BY usertype'
   
   -- After: 日次パーティション
   bq query --use_legacy_sql=false --dry_run '
   SELECT usertype, COUNT(*) AS trips, AVG(tripduration) AS avg_duration
   FROM handson_ch06.trips_daily
   WHERE DATE(starttime) = "2017-06-15"
   GROUP BY usertype'

Before（trips_no_partition）は 365 日ぶん全体を毎回スキャンするため、見積もりバイト数は種テーブルの合計サイズとほぼ同じになります。After（trips_daily）は一致する 1 パーティションしかスキャンしないため、単純計算でおよそ 365 分の 1 に近づきます（1 日あたりの利用件数にはばらつきがあるため、実際の比率は多少前後します）。dry run の見積もりバイト数がこの通り大きく減ることを、自分の画面で確認してください。

後片付け: trips_flat と trips_no_partition はもう使わないため削除します。trips_daily はハンズオン 6-2 で使うため残します。

bq rm -f -t PROJECT_ID:handson_ch06.trips_flat
bq rm -f -t PROJECT_ID:handson_ch06.trips_no_partition

サンドボックスで完走可・無料枠内。CTAS 部分は dry run で確認できますが、クラスタリングの効果は「実行後の実処理バイト数」でしか確認できません（前節の図 6-4 の通りです）。実行するクエリも数百 MB 級なので、無料枠（クエリ処理 1 TB/月）には十分収まります。

1. クラスタ列に使う候補として、よく登場する乗車ステーションを確認します。

   SELECT start_station_name, COUNT(*) AS n
   FROM handson_ch06.trips_daily
   GROUP BY start_station_name
   ORDER BY n DESC
   LIMIT 5;

   出力された上位のステーション名を 1 つ選び、以降の STATION_NAME を実際の値に置き換えてください。

2. trips_daily と同じデータから、start_station_name でクラスタ化した版を作ります。パーティションは引き継ぎ、クラスタリングを追加する形です。

   CREATE OR REPLACE TABLE handson_ch06.trips_daily_clustered
   PARTITION BY DATE(starttime)
   CLUSTER BY start_station_name
   AS
   SELECT * FROM handson_ch06.trips_daily;

3. 半年分・特定ステーションだけを対象にした同一クエリを、クラスタリングなし版・あり版の両方に対して まず dry run します。

   bq query --use_legacy_sql=false --dry_run '
   SELECT DATE(starttime) AS d, COUNT(*) AS trips
   FROM handson_ch06.trips_daily
   WHERE DATE(starttime) BETWEEN "2017-01-01" AND "2017-06-30"
     AND start_station_name = "STATION_NAME"
   GROUP BY d
   ORDER BY d'

   2 つの dry run 結果を見比べると、見積もりバイト数はほぼ同じはずです。対象になるパーティション数（181 日ぶん）が同じで、クラスタリングによるブロック単位の絞り込みは実行前の見積もりに反映されないためです。

4. 次に dry run を外して実際に実行し、コンソールの「ジョブ情報」タブ（または bq show -j --format=prettyjson JOB_ID の statistics.query.totalBytesProcessed）で実処理バイト数を比較します。

   bq query --use_legacy_sql=false '
   SELECT DATE(starttime) AS d, COUNT(*) AS trips
   FROM handson_ch06.trips_daily_clustered
   WHERE DATE(starttime) BETWEEN "2017-01-01" AND "2017-06-30"
     AND start_station_name = "STATION_NAME"
   GROUP BY d
   ORDER BY d'

クラスタリングなし版は各パーティション内をまんべんなく読むため、実処理バイト数は dry run の見積もりとほぼ一致します。クラスタリングあり版は start_station_name でブロックが整列しているため、対象ステーションを含まないブロックが読み飛ばされ、実処理バイト数が見積もりより少なくなります。「dry run では差が見えないのに、実行後には差が出る」という図 6-4 の内容を、自分の実行結果で確認できたはずです。

後片付け: ここまでで作った演習データセットをまるごと削除します。

bq rm -r -f -d PROJECT_ID:handson_ch06