概要

BigQuery でのテーブル クラスタリングの機能

• クラスタ化テーブルの概要  |  BigQuery  |  Google Cloud

BiqQueryのテーブルは、insertした時間で、データをパーティションする機能があります。
それに加えて、テーブルのフィールドを使って、データをクラスター化（いわゆるindexのようなもの？）する機能があるみたいです。

ただ、一般的なRDBと違って、後から追加することができないようなので、運用はかなり大変そうです。

1. テーブルを新しく作り直す
2. 同じデータをinsertする
3. 旧テーブルと、新テーブルの両方に対してクエリを投げて、ちゃんとクラスターが効いているか（scan bytesが減っているか）確認する
4. OKなら、旧テーブルを消して、新テーブルの方式に変更する

みたいな手順が必要になってくる場合があります。

実施事項

1. まずは、audit logテーブルから、現在流れているクエリのコストの高いものを抽出しました。

SELECT
  JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson, "$.jobChange.job.jobConfig.queryConfig.query"),
  JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson, "$.jobChange.job.jobStats.queryStats.totalBilledBytes") AS totalBilledBytes,
  CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson, "$.jobChange.job.jobStats.queryStats.totalBilledBytes") AS INT64)/POWER(2, 30) AS totalBilledGigaBytes
FROM
  `project-name.dataset-name.cloudaudit_googleapis_com_data_access`
WHERE
  _PARTITIONDATE BETWEEN '2020-05-15' AND '2020-05-20'
  AND JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson, "$.jobChange.job.jobConfig.type") = "QUERY"
  AND JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson, "$.jobChange.job.jobConfig.queryConfig.statementType") = "SELECT"
ORDER BY
  totalBilledGigaBytes DESC
LIMIT 100

2. この結果を元に、テーブル クラスタリングを導入した方が良さそうなテーブルを決めました。

テーブル構成を決めるのにはいくつか検討が必要で

• クラスタ化テーブルの作成と使用  |  BigQuery  |  Google Cloud

最大 4 つのクラスタリング列を指定できます。
複数の列を指定する場合、列の順序によってデータの並べ替え方法が決まります。
たとえば、テーブルが列 a、b、c によってクラスタ化されている場合、データは同じ順序（列 a、列 b、列 c の順）で並べ替えられます。
ベスト プラクティスとして、最も頻繁にフィルタリングまたは集計される列を最初に置いてください。

上記の通り、４つまで、クラスタに使うフィールドを指定できて、よくスキャンされる順に設定する必要があります。
user_idやcreated_atなどが一般的になるのかなと思います。

以上になります。