BigQuery のビューで参照しているビュー・テーブルを確認して、変更してよいか判断する

BigQuery のビューで参照しているビュー・テーブルを見て、変更して良いか確認したい場面

dbt のモデルを変更したときに、参照しているクエリに影響が出ないようにしたい。

• 変更例
  • モデルを削除
  • モデル名を変更
  • モデルの定義、抽出条件を変更
  • カラム名、カラムの定義を変更

影響のあるクエリを抽出するために、SQL のコードの中に dbt で変更予定のモデル名やカラム名が含まれているビューがあるか検索する。 もし影響のあるクエリが存在すれば、dbt のモデルの変更に合わせて、参照しているクエリに影響が出ないようにクエリを修正する。

特定の文字列が SQL に含まれているビューを検索

<> で囲っている部分は、実行環境に合わせて置き換える。

• <project_id>: 検索対象のプロジェクト
• <dataset_id>: 任意のデータセット名
• <region>: 検索対象のプロジェクトの region
  • region が asia-northeast1 の場合は、region-asia-northeast1 のデータセットを参照

<project_id>.<dataset_id>.<project_id>_tables の作成

<project_id>.<dataset_id>.<project_id>_tables というビューの名前で保存。

TABLES ビュー | BigQuery | Google Cloud で、抽出できるカラムを確認できます。

#standardSQL

with views as (
  select
    concat(table_catalog, '.', table_schema, '.', table_name) as view_id
    , table_catalog
    , table_schema
    , table_name
    , table_type
    , ddl
    , creation_time
  from `<project_id>.region-<region>.INFORMATION_SCHEMA.TABLES`
)

select
  *
from views
order by
  creation_time desc

BigQuery のビューで参照している dbt のモデルを探す

hogehoge という dbt のモデルを参照しているクエリを探したい場合。

SELECT
  view_id
FROM `<project_id>.<dataset_id>.<project_id>_tables`
where
  regexp_contains(ddl, r'hogehoge')

＊ SQL の中で hogehoge という文字列を含んでいるクエリも抽出されます。

詳細を確認

上記クエリでビューが見つかった場合は、詳細を見てみる。

SELECT
  *
FROM `<project_id>.<dataset_id>.<project_id>_tables`
where
  regexp_contains(ddl, r'hogehoge')

参考

• BigQuery INFORMATION_SCHEMA の概要 | Google Cloud
• TABLES ビュー | BigQuery | Google Cloud