派生先テーブルの参照回数も考慮して安全にテーブルを撤退する

3行まとめ

テーブルの撤退時にはテーブルの参照回数を見ることが多いと思いますが、テーブル単独の参照回数を見るだけだと不十分なことが多いです
派生先のテーブルの参照回数まで考慮すると、テーブルが撤退できるか安全に判断することができます
リネージ上の親子関係をWITH RECURSIVEで考慮しながら、累積参照回数をSQLで導出できるようにし、安全にテーブル撤退を判断できるようにしました

3行まとめ
背景: テーブルの撤退にはテーブル単独の参照回数を見るだけだと不十分
アイディア: 累積参照回数を計算する
実装
まとめ

背景: テーブルの撤退にはテーブル単独の参照回数を見るだけだと不十分

データエンジニアやアナリティクスエンジニアの仕事をしていると、データ利用者の認知負荷を下げたりテーブル管理コストを下げるために、利用されていないテーブルを定期的に撤退する機会があると思います*1。撤退したいテーブルが「データマートのテーブルで、このダッシュボードでしか使われていない!」と断定できる場合は容易に撤退できると思いますが、世の中そう簡単な場合ばかりとは限りません。

具体的な例を考えてみましょう。以下のようなテーブルのリネージがあった場合、テーブルAは2回しか参照されていません。他のテーブルと比べると圧倒的に参照回数が少ないため、テーブルAを撤退すればよいように思えます。

graph LR;
  A(A: 参照回数2回) --> B(B: 参照回数50回);
  A(A: 参照回数2回) --> D(D: 参照回数10回);
  B(B: 参照回数50回) --> C(C: 参照回数10回);
  D(D: 参照回数10回) --> E(E: 参照回数100回);

しかし、注意深くリネージを見てみると、テーブルAの派生先にテーブルBやテーブルEのような参照回数が多いテーブルがいることが分かります。テーブル単独の参照回数を元にテーブルを撤退すると、利用人数の多い箇所でテーブルが見れなくったりバッチが失敗する事故が起きてしまいます。

アイディア: 累積参照回数を計算する

こういった事故を防ぐためには、リネージ上のleafからrootに向かって累積の参照回数を計算すればよさそうです。先ほどのリネージであれば以下が累積の参照回数を計算したものになります。

graph LR;
  A(A: 累積参照回数172回) --> B(B: 参照回数60回);
  A(A: 累積参照回数172回) --> D(D: 累積参照回数110回);
  B(B: 累積参照回数60回) --> C(C: 累積参照回数10回);
  D(D: 累積参照回数110回) --> E(E: 累積参照回数100回);

対象が小さければこのように人間の頭で計算できますが、大抵の場合は対象がもっと大きいため、SQLで自動的に計算できるようにしましょう。

実装

テーブル間の親子関係を抽出する

先ほどまでのリネージをデータとして扱うための準備が必要です。dbtやdataformを使っているのであれば、refなどの結果を抽出してテーブル間のリネージを親子関係として抽出できると思います。よりシンプルにSQLを管理している組織であれば、INFORMATION_SCHEMA.JOBS_BY_PROJECTのreferenced_tablesなどから依存関係を抽出するのもありだと思います*2。

dbtを利用している場合はelementaryを使うのがオススメです。以下のようにSQLを使ってリネージを親子関係としてさっと抽出することができます。

SELECT
  unique_id AS parent,
  JSON_VALUE(depends_on) AS child,
FROM
  `my_project.my_elementary.dbt_models`,
  UNNEST(JSON_QUERY_ARRAY(depends_on_nodes)) AS depends_on

WITH RECURSIVEでテーブルの親子関係を辿る

親子関係は多段になることが普通なので、再帰でリネージを辿るようにします。BigQueryではWITH RECURSIVEで再帰を書くことができるので、それを利用します。

WITH RECURSIVE tmp AS (
  SELECT
    JSON_VALUE(depends_on) AS original,
    unique_id AS parent,
    JSON_VALUE(depends_on) AS child,
  FROM
    `my_project.my_elementary.dbt_models`,
    UNNEST(JSON_QUERY_ARRAY(depends_on_nodes)) AS depends_on
  ),
  rec AS (
  SELECT * FROM tmp
  UNION ALL (
    # tmp.child => tmp.parent => ... => rec.original
    SELECT
      # 最終的に親か判定したいもの
      rec.original,
      # 再帰の過程で登場する親
      tmp.parent,
      # 再帰で使わないので、NULLを入れておく
      NULL AS child,
    FROM
      rec
    INNER JOIN
      tmp
    ON
      rec.parent = tmp.child # 「現在の親が子どもになる」 = 「さらに親を辿る」形で再帰させる
    ) ),
dbt_model_relationship_tmp AS (
  SELECT
    original AS parent,
    parent AS child,  
  FROM
    rec
  GROUP BY
    parent, child
),
dbt_model_relationship AS (
  SELECT
    *
  FROM
    dbt_model_relationship_tmp
  UNION DISTINCT
  (
    # 利用回数を知りたいので、rootも含めておく
    SELECT
      parent,
      parent AS child
    FROM
      dbt_model_relationship_tmp
    GROUP BY
      parent
  )
  UNION DISTINCT
  (
    # 利用回数を知りたいので、leafも含めておく
    SELECT
      child AS parent,
      child,
    FROM
      dbt_model_relationship_tmp
    GROUP BY
      child
  )
)
SELECT
  *
FROM
  dbt_model_relationship

rootとleafに相当するところにも後段の参照回数を付与できるようにしたかったため、後段で若干アドホックなことをしています。もっといい感じにきっと書ける気がする。

WITH RECURSIVEで潜れる再帰の上限は500回と制限があるようですが、SQLでそれだけ多段の参照関係になるほうがおかしいので、まあ普通は気にしなくてよいはずです。再帰で木構造を辿る例としては以下が参考になりました。

テーブルの親子関係を考慮しながら、累積参照回数を計算する

ここまでくると後は簡単で、親子関係の子ども側のテーブルに参照回数を付与しておけば、BIツールなどで累積参照回数を参照できるようになります。

最終的なSQL(クリックで開きます)

WITH RECURSIVE tmp AS (
  SELECT
    JSON_VALUE(depends_on) AS original,
    unique_id AS parent,
    JSON_VALUE(depends_on) AS child,
  FROM
    `my_project.my_elementary.dbt_models`,  
    UNNEST(JSON_QUERY_ARRAY(depends_on_nodes)) AS depends_on
  ),
  rec AS (
  SELECT * FROM tmp
  UNION ALL (
    # tmp.child => tmp.parent => ... => rec.original
    SELECT
      # 最終的に親か判定したいもの
      rec.original,
      # 再帰の過程で登場する親
      tmp.parent,
      # 再帰で使わないので、NULLを入れておく
      NULL AS child,
    FROM
      rec
    INNER JOIN
      tmp
    ON
      rec.parent = tmp.child # 「現在の親が子どもになる」 = 「さらに親を辿る」形で再帰させる
    ) ),
dbt_model_relationship_tmp AS (
  SELECT
    original AS parent,
    parent AS child,  
  FROM
    rec
  GROUP BY
    parent, child
),
dbt_model_relationship AS (
  SELECT
    *
  FROM
    dbt_model_relationship_tmp
  UNION DISTINCT
  (
    # 利用回数を知りたいので、rootも含めておく
    SELECT
      parent,
      parent AS child
    FROM
      dbt_model_relationship_tmp
    GROUP BY
      parent
  )
  UNION DISTINCT
  (
    # 利用回数を知りたいので、leafも含めておく
    SELECT
      child AS parent,
      child,
    FROM
      dbt_model_relationship_tmp
    GROUP BY
      child
  )
),
queries_count_by_table_id AS (
  SELECT
    referenced_tables.project_id,
    referenced_tables.dataset_id,
    referenced_tables.table_id,
    user_email,
    COUNT(*) AS cnt
  FROM
    `my-project`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS jobs_by_project,
    UNNEST(referenced_tables) AS referenced_tables
  WHERE
    creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 93 DAY) # 適当に3ヶ月分の利用実績を見る
    AND jobs_by_project.project_id = referenced_tables.project_id
  GROUP BY 
    referenced_tables.project_id,
    referenced_tables.dataset_id,
    referenced_tables.table_id,
    user_email
)
SELECT 
  dbt_model_relationship.parent,
  parent_dbt_models.database_name AS parent_project_id,
  parent_dbt_models.schema_name AS parent_dataset_id,
  parent_dbt_models.name AS parent_table_id,

  child_dbt_models.database_name AS child_project_id,
  child_dbt_models.schema_name AS child_dataset_id,
  child_dbt_models.name AS child_table_id,

  queries_count_by_table_id.user_email AS user_email,
  queries_count_by_table_id.cnt AS cnt,
FROM
  dbt_model_relationship
INNER JOIN
  `my_project.my_elementary.dbt_models` AS parent_dbt_models
ON
  dbt_model_relationship.parent = parent_dbt_models.unique_id
INNER JOIN
  `my_project.my_elementary.dbt_models` AS child_dbt_models
ON
  dbt_model_relationship.child = child_dbt_models.unique_id
INNER JOIN
  queries_count_by_table_id
ON
  child_dbt_models.database_name = queries_count_by_table_id.project_id
  AND child_dbt_models.schema_name = queries_count_by_table_id.dataset_id
  AND child_dbt_models.name = queries_count_by_table_id.table_id