FollowしていたGCPUGのMediumで、興味深い記事を見つけました。
おお、こういったデータセットやジョブのメタデータがQueryで取得できたんですね！知りませんでした。
これに触発されて、参照されたテーブルをさっと確認して、参照されていないテーブルをリストして、カオスになった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