BigQueryのテーブル参照数を数える ( 日付別テーブル ワイルドカード対応 )

Followしていた[GCPUGのMedium](https://medium.com/google-cloud-jp)で、興味深い記事を見つけました。

- [INFORMATION_SCHEMA.JOBS_BY_* ビューで BigQuery のテーブルの利用状況を把握する](https://medium.com/google-cloud-jp/bigquery-table-usage-investigation-836c31eafe4b)

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

- [GCP公式ドキュメント](https://cloud.google.com/bigquery/docs/information-schema-intro)

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

考え方としては、

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

で、、サクッとやってみてすぐに分かったのですが、、、[日付別テーブルで管理](https://qiita.com/aki3061/items/3eaa57e700ac084d33d8#%E6%97%A5%E4%BB%98%E5%88%A5%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB)しているテーブルのワイルドカードでのアクセスがうまくJOINできない😿

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

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

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

```sql
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
```