FollowしていたGCPUGのMediumで、興味深い記事を見つけました。

INFORMATION_SCHEMA.JOBS_BY_* ビューで BigQuery のテーブルの利用状況を把握する

おお、こういったデータセットやジョブのメタデータがQueryで取得できたんですね！知りませんでした。

GCP公式ドキュメント

これに触発されて、参照されたテーブルをさっと確認して、参照されていないテーブルをリストして、カオスになったBigQueryを整理する際に使えたらいいなと、思いました。

考え方としては、

テーブルの一覧を myDataset.INFORMATION_SCHEMA.TABLES から取得して、これに、上記のQiitaの記事で記載の参照数をLEFT JOINしてやれば参照のないテーブルが見つかるなと思った次第です。

で、、サクッとやってみてすぐに分かったのですが、、、日付別テーブルで管理しているテーブルのワイルドカードでのアクセスがうまくJOINできない😿

そこでいろいろSQLを頑張って、以下のような順番で実現してみました。

myDataset.INFORMATION_SCHEMA.TABLES を使ってテーブル一覧 tables を取得
テーブル一覧 tables の中から、_YYYYMMDD となっているテーブルを探してそれに対しては、_YYYYMM, _YYYY といった具合でワイルドカードアクセスできるテーブル名を縦持ちで table_seqとして作成
table_seqのままだと作ったテーブル名が重複が多いので、null除去と、ユニーク化するために、table_listを作成
一方、参照テーブルと参照数を reference_jobs で出すが、参照テーブル名では、末尾が * のものがあれば * を削除
1と2をLEFT JOINするが、更に、table_YYYYMMDD 由来の table名を、table_に集約・集計

なお、myDataset を確認したいデータセットで指定してください。31日前までのJOBを集計します。INFORMATION_SCHEMA.JOBS_BY_*には 180 日間の履歴が残っているようですが、絞ることでコスト削減できます。

with tables as (
SELECT
table_catalog
, table_schema
, table_name
, table_type
, creation_time
FROM
  myDataset.INFORMATION_SCHEMA.TABLES
)
, table_seq as (
select table_catalog, table_schema, table_name, table_type, creation_time from tables
UNION ALL SELECT table_catalog, table_schema, IF (REGEXP_CONTAINS(table_name, '_[0-9]{8}'), SUBSTR(table_name, 0, CHAR_LENGTH(table_name)-1), NULL), table_type, creation_time from tables
UNION ALL SELECT table_catalog, table_schema, IF (REGEXP_CONTAINS(table_name, '_[0-9]{8}'), SUBSTR(table_name, 0, CHAR_LENGTH(table_name)-2), NULL), table_type, creation_time from tables
UNION ALL SELECT table_catalog, table_schema, IF (REGEXP_CONTAINS(table_name, '_[0-9]{8}'), SUBSTR(table_name, 0, CHAR_LENGTH(table_name)-3), NULL), table_type, creation_time from tables
UNION ALL SELECT table_catalog, table_schema, IF (REGEXP_CONTAINS(table_name, '_[0-9]{8}'), SUBSTR(table_name, 0, CHAR_LENGTH(table_name)-4), NULL), table_type, creation_time from tables
UNION ALL SELECT table_catalog, table_schema, IF (REGEXP_CONTAINS(table_name, '_[0-9]{8}'), SUBSTR(table_name, 0, CHAR_LENGTH(table_name)-5), NULL), table_type, creation_time from tables
UNION ALL SELECT table_catalog, table_schema, IF (REGEXP_CONTAINS(table_name, '_[0-9]{8}'), SUBSTR(table_name, 0, CHAR_LENGTH(table_name)-6), NULL), table_type, creation_time from tables
UNION ALL SELECT table_catalog, table_schema, IF (REGEXP_CONTAINS(table_name, '_[0-9]{8}'), SUBSTR(table_name, 0, CHAR_LENGTH(table_name)-7), NULL), table_type, creation_time from tables
UNION ALL SELECT table_catalog, table_schema, IF (REGEXP_CONTAINS(table_name, '_[0-9]{8}'), SUBSTR(table_name, 0, CHAR_LENGTH(table_name)-8), NULL), table_type, creation_time from tables
)
, table_list as (
select table_catalog, table_schema, table_name, table_type, min(creation_time) as creation_time
from table_seq
group by table_catalog, table_schema, table_name, table_type
)
,
reference_jobs as
(
select
  t.project_id
, t.dataset_id
, REGEXP_REPLACE(t.table_id, '\\*$' , '') as table_id
, max(j.start_time) as last_query_date
, count(1) as ref_nums -- テーブルの参照回数を取得します。
from
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT j
    -- referenced_tables カラムの配列を行へ変換します。
    cross join
      unnest(j.referenced_tables) t
where
  -- creation_time がパーティショニング カラムとなっているため、
  -- コスト節約のために、このカラムでの絞り込みを行うことを推奨します。
  DATE_DIFF(current_date(), DATE(j.creation_time) , DAY) < 31
and j.job_type = 'QUERY' -- クエリ ジョブのみを検索対象とします。
and j.statement_type = 'SELECT' -- select クエリのみを検索対象とします。
and j.state = 'DONE' -- 実行が完了したジョブのみを検索対象とします。
and j.error_result is null -- 正常終了したジョブのみを検索対象とします。
-- 参照テーブル（プロジェクト ID、データセット ID、テーブル ID）ごとに
-- グループ化して集計します。
group by
  t.project_id
, t.dataset_id
, 3
)

SELECT
l.table_catalog as project_name
, l.table_schema as dataset_name
, REGEXP_REPLACE(l.table_name, '_[0-9]*$' , '_') as table_name
, min(l.creation_time) as first_createion_time
, max(j.last_query_date) as last_query_date
, sum(IFNULL(j.ref_nums, 0)) as ref_nums
from table_list as l
left join reference_jobs as j
  ON l.table_schema = j.dataset_id AND l.table_name = j.table_id
group by project_name, dataset_name, table_name
order by ref_nums DESC