【GCP】BQで有効期限有無でテーブルを抽出

目的

bigqueryので有効期限ありなしのテーブルを抽出したい。

背景

アドホックなデータ分析などの一時利用向けに
自由にテーブルを作成できるデータセットを、組織内で公開している。
データセット自体には
デフォルトでライフサイクル（テーブルが削除されるまでの期間）を設定しているが
有効期限はテーブル単位で任意に変更できてしまうため、
運用者の気づかぬ内に無期限テーブルができてしまう。
定期的にテーブルをクリーニングする際に有効期限有無で対象を選別するのに利用するクエリ。

方法

有効期限ありテーブルを抽出

SELECT distinct
    table_name
FROM
    プロジェクト名.データセット名.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE
    option_name = 'expiration_timestamp'
order by
    1
;

有効期限なしテーブルを抽出

(
    SELECT distinct
        table_name
    FROM
        プロジェクト名.データセット名.INFORMATION_SCHEMA.TABLE_OPTIONS
) EXCEPT DISTINCT(
    SELECT distinct
        table_name
    FROM
        プロジェクト名.データセット名.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
        option_name = 'expiration_timestamp'
)
order by
    1
;