BigQueryのパーティションとクラスタ化の設定をコンソールからまとめて確認する

データアナリティクス事業本部、池田です。
BigQueryでテーブルを作成してパーティショニングしたり、 クラスタリングしたりすることがありました。 私は自分を全く信頼していないので、正しく設定できているか必ず最後にチェックするのですが、 同じリージョン内ならまとめてSQLで確認できるようなので、その方法です。

テーブルの作成

今回の確認対象となるデータセットと各種テーブルを作成します。
全てUSリージョンで作成します。

データセット

データセットはSQLで作成できないようなので、ここだけ bqコマンド で作成しました。

bq --location=US mk --dataset partitioned_tables
bq --location=US mk --dataset clustered_tables
bq --location=US mk --dataset normal_tables

（BigQueryのコンソールからプロジェクトを選択して、「データセットを作成」を実行でも良いです。）

↓ Cloud Shell から実行しました。

（2021/04/01追記）
アップデートでデータセットもSQLで作成できるようになりました。
【 Spring forward with BigQuery user-friendly SQL 】

テーブル

テーブルは↓のSQLで、 パーティション分割テーブル、 クラスタ化されたテーブル、パーティション分割もクラスタ化もしないテーブルを それぞれ作成しました。

-- パーティション分割テーブル
CREATE TABLE partitioned_tables.partitioned_by_date (
    col1_string STRING,
    col2_date DATE,
    col3_int INT64
)
PARTITION BY col2_date; -- 日付で日単位で分割

CREATE TABLE partitioned_tables.partitioned_by_int (
    col1_string STRING,
    col2_date DATE,
    col3_int INT64
)
PARTITION BY RANGE_BUCKET(col3_int, -- 整数で分割
                          GENERATE_ARRAY(0, 1000, 100)); -- 0以上1000未満の範囲を100ごとに分割

CREATE TABLE partitioned_tables.partitioned_by_ingestion_time (
    col1_string STRING,
    col2_date DATE,
    col3_int INT64
)
PARTITION BY DATE(_PARTITIONTIME); -- 取り込みタイムスタンプで日単位で分割

-- クラスタ化されたテーブル
CREATE TABLE clustered_tables.clustered (
    col1_string STRING,
    col2_date DATE,
    col3_int INT64
)
CLUSTER BY col2_date, col1_string; -- 2カラムでクラスタ化

-- パーティション分割もクラスタ化もしないテーブル
CREATE TABLE normal_tables.normal (
    col1_string STRING,
    col2_date DATE,
    col3_int INT64
);

↓最終的にこんな感じ。

コンソール画面から確認する

設定を確認する数が多くなければ、コンソールからさくっと確認してしまうのが楽だと思います。
テーブルの詳細タブから、パーティションとクラスタ化の設定を確認することができます。
↓日付分割テーブルの場合。

↓クラスタ化したテーブルの場合。

↓ちなみにパーティション分割テーブルはアイコンがパカっとしてます。

SQLでリージョン内をまとめて確認する

INFORMATION_SCHEMA の COLUMNS ビュー には列情報が入ってるので、 パーティションとクラスタ化の列として使用されているか参照することができます。
【 COLUMNS ビュー 】
また、FROM句で指定する際に、データセットではなく リージョン修飾子 を用いることで、データセットをまたいでまとめて参照できます。

↓USリージョン内を確認するSQL

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    ORDINAL_POSITION,
    COLUMN_NAME,
    DATA_TYPE,
    IS_PARTITIONING_COLUMN,
    CLUSTERING_ORDINAL_POSITION
FROM
    region-us.INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA IN ('partitioned_tables', 'clustered_tables', 'normal_tables')
AND (IS_PARTITIONING_COLUMN = 'YES' OR CLUSTERING_ORDINAL_POSITION IS NOT NULL)
ORDER BY
    TABLE_SCHEMA,
    TABLE_NAME,
    ORDINAL_POSITION;

（ TABLE_SCHEMA IN の部分で今回検証用に作成したデータセットだけを指定しています。）

↓結果

TABLE_SCHEMA,TABLE_NAME,ORDINAL_POSITION,COLUMN_NAME,DATA_TYPE,IS_PARTITIONING_COLUMN,CLUSTERING_ORDINAL_POSITION
clustered_tables,clustered,1,col1_string,STRING,NO,2
clustered_tables,clustered,2,col2_date,DATE,NO,1
partitioned_tables,partitioned_by_date,2,col2_date,DATE,YES,
partitioned_tables,partitioned_by_ingestion_time,,_PARTITIONTIME,TIMESTAMP,YES,
partitioned_tables,partitioned_by_int,3,col3_int,INT64,YES,

• IS_PARTITIONING_COLUMN ：列がパーティショニング列かどうか
• CLUSTERING_ORDINAL_POSITION ：テーブルのクラスタリング列内の列の 1 から始まるオフセット。テーブルがクラスタ化テーブルでない場合、値は NULL

取り込み時間分割テーブル （ _PARTITIONTIME ）の場合は、 ORDINAL_POSITION 列（テーブル内の列番号）にNULLが入るようですね。

必要な権限について

リージョン修飾子で INFORMATION_SCHEMA 内のオブジェクト（今回は COLUMNS ）を参照しようとした場合に 以下のようなエラーになることがありました。
Access Denied: Table {プロジェクト名}:region-us.INFORMATION_SCHEMA.COLUMNS: User does not have permission to query table {プロジェクト名}:region-us.INFORMATION_SCHEMA.COLUMNS.
（私の場合はGCPプロジェクトのオーナーでコンソールにつないでいましたがそんな感じになりました。）

ドキュメント によると必要な権限は bigquery.tables.get と bigquery.tables.list らしいので、 それらを明示的に付与することで、SQLを実行できるようになりました。
↓ロールを作成して…

↓IAMに付与。

おわりに

リージョン修飾子が便利でした。

関連情報/参考にさせていただいたページ

• COLUMNS ビュー
• 必要な権限
• リージョン修飾子
• コマンドライン ツール リファレンス