2024.01.26

dbt run-operation を利用して、全テーブル・全カラムを対象に指定文字列を含むレコードを調査

背景

特定の文字列が DB の全テーブル・全カラムの中に含まれているか知りたいという調査依頼に dbt run-operation を利用して対応した。

仕様

本番 DB のデータを BigQuery のデータセットに Datastream で同期している。このデータセットにあるすべてのテーブルを対象に、特定の文字列を含んだレコードが存在するか調べる。レコードが存在する場合は、そのレコードを抽出する。

実装

実行環境

BigQuery
dbt: 1.5.0

dbt macro

調査用のマクロを 2 つ作成する。

指定したデータセットとテーブルの全カラムを対象に指定した文字列が含まれているか調査するマクロ

macros/run_operation/search_all_columns_in_table.sql

search_all_columns_in_all_tables で呼び出しているマクロ。

log | dbt Developer Hub の第二引数に True を渡すことで、標準出力に テーブル名: 検索条件に合致したレコード数 を出力する。

#standardSQL

{% macro search_all_columns_in_table(dataset_id, table_id) -%}
  {% set search_query -%}
    select
      count(*) as contains_record_count
    from `<project_id>.{{ dataset_id }}.{{ table_id }}`
    where
      contains_substr(
        `<project_id>.{{ dataset_id }}.{{ table_id }}`
        , '(C) 検索したい文字列'
      )
      -- MEMO: 検索したい文字列が複数ある場合は、or で条件を追加する
      -- or contains_substr(
      --   `<project_id>.{{ dataset_id }}.{{ table_id }}`
      --   , '(C) 検索したい文字列 2'
      -- )
  {%- endset %}

  {%- set search_query_result = run_query(search_query) -%}

  {% if execute -%}
    {%- set contains_record_count = search_query_result.columns[0].values()[0] -%}
  {% else %}
    {%- set contains_record_count = [] -%}
  {%- endif %}

  {{ log(table_id ~ ": " ~ contains_record_count, True) }}
{%- endmacro %}

macros/run_operation/search_all_columns_in_table.yml

search_all_columns_in_all_tables のドキュメント。

version: 2

macros:
  - name: search_all_columns_in_table
    description: >
      {{ doc('search_all_columns_in_table_description') }}
    arguments:
      - name: dataset_id
        type: BigQuery Dataset ID
        description: データセットの名前

      - name: table_id
        type: BigQuery Table ID
        description: テーブルまたはビューの名前

指定したデータセットの全テーブル・全カラムを対象に指定した文字列が含まれているか調査するマクロ

macros/run_operation/search_all_columns_in_all_tables.sql

上の search_all_columns_in_table を中で呼び出している。

#standardSQL

{% macro search_all_columns_in_all_tables() -%}
  -- MEMO: ここで dataset_id を設定
  -- * 引数で間違えた dataset_id を渡して、大量の課金されるバイト数が多いクエリを実行するのが怖いため
  {%- set dataset_id = '(A) 検索対象のデータセット名' -%}

  {% set table_ids_query -%}
    select
      table_name as table_id
    from `<project_id>.<region>.INFORMATION_SCHEMA.TABLES`
    where
      table_schema = '{{ dataset_id }}'
      -- MEMO: (B) 絞りこみたい条件を追加
      -- * table_name で、テーブルを絞り込める
      --
      -- and table_name not in (
      --   'large_table_name'
      --   , 'unnecessary_table_name'
      -- )
    order by
      table_name asc
  {%- endset %}

  {%- set table_ids_query_results = run_query(table_ids_query) -%}

  {% if execute -%}
    {%- set table_ids = table_ids_query_results.columns[0].values() -%}
  {% else %}
    {%- set table_ids = [] -%}
  {%- endif %}

  {% for table_id in table_ids -%}
    {{ search_all_columns_in_table(dataset_id, table_id) }}
  {% endfor %}
{%- endmacro %}

macros/run_operation/search_all_columns_in_all_tables.yml

search_all_columns_in_all_tables のドキュメント。

version: 2

macros:
  - name: search_all_columns_in_all_tables
    description: >
      {{ doc('search_all_columns_in_all_tables_description') }}

dbt docs

description で参照していた 2 つのドキュメントは、 Markdown で詳しく説明したいので別ファイルに切り出した。

search_all_columns_in_all_tables_description
search_all_columns_in_table_description

macros/run_operation/_run_operation.md

下の調査と同じ内容

{% docs search_all_columns_in_all_tables_description %}

# 指定したデータセットの全テーブル・全カラムを対象に指定した文字列が含まれているか調査

## 注意点

レコードの多いテーブルに対して、クエリを実行すると費用がかさむため、
必ず `課金されるバイト数` を確認してから
`dbt run-operation search_all_columns_in_all_tables` を実行する。

## 調査

### ブランチを切る

1. `main` ブランチから、ブランチを切る

### 課金されるバイト数 を確認

2. 全テーブルを対象にクエリを実行した場合、どれくらい `課金されるバイト数` がかかるか確認する
  1. `(A) 検索対象のデータセット名` を、検索したいデータセット名に変更
  2. `課金されるバイト数` と `テーブル 別 課金されるバイト数` のクエリを実行
  3. `total_logical_bytes` が多いテーブルがあれば、除いて実行することを検討する

#### 課金されるバイト数

```sql
select
  sum(total_logical_bytes) as total_logical_bytes
from `<project_id>.<region>.INFORMATION_SCHEMA.TABLE_STORAGE`
where
  table_schema = '(A) 検索対象のデータセット名'
```

#### テーブル 別 課金されるバイト数

```sql
select
  table_name
  , total_logical_bytes
from `<project_id>.<region>.INFORMATION_SCHEMA.TABLE_STORAGE`
where
  table_schema = '(A) 検索対象のデータセット名'
order by
  total_logical_bytes desc
```

### search_all_columns_in_all_tables のマクロを変更

3. `(A) 検索対象のデータセット名` を、検索したいデータセット名に変更
4. `(B) 絞りこみたい条件を追加` の下に、絞り込みたい条件があれば追加
  1. レコード数の多いテーブル、検索する必要のないテーブルを除くなど

### search_all_columns_in_table のマクロを変更

5. `(C) 検索したい文字列` を変更
  1.  検索したい文字列が複数ある場合は、`or` で条件を追加する

### プルリクを作成

6. 1~5 の対応後、プルリクエストを作成し、レビューを依頼
7. レビューが問題なければ、`$ dbt run-operation search_all_columns_in_all_tables` を実行
8. 標準出力に `テーブル名: 検索条件に合致したレコード数` が出力される
  1. `検索結果に合致したレコード数` が `0` 以外の場合、`テーブル名` に `(C) 検索したい文字列が含まれている` ので、9 に進む
9. 下記クエリを実行して、対象のレコードを確認する
  1. `< 8.1 のテーブル名 >`、`(C) 検索したい文字列` は実際の値で書き換える
  2. [contains_substr](https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#contains_substr) を利用すると、テーブルの全カラムを対象に `(C) 検索したい文字列` が含まれているか調べられる

```sql
select
  *
from `<project_id>.<dataset_id>.< 8.1 のテーブル名 >`
where
  contains_substr(
    `<project_id>.<dataset_id>.< 8.1 のテーブル名 >`
    , '(C) 検索したい文字列'
  )
```

10. 調査完了後、プルリクエストを閉じる

{% enddocs %}

{% docs search_all_columns_in_table_description %}

# 指定したデータセットとテーブルの全カラムを対象に指定した文字列が含まれているか調査

`search_all_columns_in_all_tables` で参照しているマクロ。

＊ このマクロを単体で使うことはない。

{% enddocs %}

調査

上で作成した今回の調査用の dbt のマクロを dbt run-operation で、実行して対象のレコードを検索する。

注意点

レコードの多いテーブルに対して、クエリを実行すると費用がかさむため、必ず 課金されるバイト数 を確認してから dbt run-operation search_all_columns_in_all_tables を実行する。

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

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

調査の流れ

ブランチを切る

main ブランチから、ブランチを切る

課金されるバイト数を確認

全テーブルを対象にクエリを実行した場合、どれくらい 課金されるバイト数 がかかるか確認する
1. (A) 検索対象のデータセット名 を、検索したいデータセット名に変更
2. 課金されるバイト数 と テーブル別課金されるバイト数 のクエリを実行
3. total_logical_bytes が多いテーブルがあれば、除いて実行することを検討する

TOTAL_LOGICAL_BYTES INT64 テーブルまたはマテリアライズドビューの論理（非圧縮）バイトの合計数

TABLE_STORAGE ビュー | BigQuery | Google Cloud

TOTAL_LOGICAL_BYTES ≒ 課金されるバイト数 の認識。

課金されるバイト数

select
  sum(total_logical_bytes) as total_logical_bytes
from `<project_id>.<region>.INFORMATION_SCHEMA.TABLE_STORAGE`
where
  table_schema = '(A) 検索対象のデータセット名'

テーブル別課金されるバイト数

select
  table_name
  , total_logical_bytes
from `<project_id>.<region>.INFORMATION_SCHEMA.TABLE_STORAGE`
where
  table_schema = '(A) 検索対象のデータセット名'
order by
  total_logical_bytes desc

search_all_columns_in_all_tables のマクロを変更

(A) 検索対象のデータセット名 を、検索したいデータセット名に変更
(B) 絞りこみたい条件を追加 の下に、絞り込みたい条件があれば追加
1. レコード数の多いテーブル、検索する必要のないテーブルを除くなど

search_all_columns_in_table のマクロを変更

(C) 検索したい文字列 を変更
1. 検索したい文字列が複数ある場合は、or で条件を追加する

プルリクを作成

1~5 の対応後、プルリクエストを作成し、レビューを依頼
レビューが問題なければ、$ dbt run-operation search_all_columns_in_all_tables を実行
標準出力に テーブル名: 検索条件に合致したレコード数 が出力される
1. 検索結果に合致したレコード数 が 0 以外の場合、テーブル名 に (C) 検索したい文字列が含まれている ので、9 に進む
下記クエリを実行して、対象のレコードを確認する
1. < 8.1 のテーブル名 >、(C) 検索したい文字列 は実際の値で書き換える
2. contains_substr を利用すると、テーブルの全カラムを対象に (C) 検索したい文字列 が含まれているか調べられる

select
  *
from `<project_id>.<dataset_id>.< 8.1 のテーブル名 >`
where
  contains_substr(
    `<project_id>.<dataset_id>.< 8.1 のテーブル名 >`
    , '(C) 検索したい文字列'
  )

調査完了後、プルリクエストを閉じる

実行例

実際に実行すると標準出力に、テーブル名: 検索条件に合致したレコード数 が出力される。検索結果から、baz, foo のテーブルに、検索条件に合致したレコードが存在することがわかった。

$ dbt run-operation search_all_columns_in_all_tables
HH:MM:SS  Running with dbt=1.5.0
HH:MM:SS  Found 999 models, 9999 tests, 999 snapshots, 999 analyses, 999 macros, 0 operations, 99 seed files, 999 sources, 99 exposures, 99 metrics, 0 groups
HH:MM:SS  bar: 0
HH:MM:SS  baz: 3
HH:MM:SS  foo: 2

<project_id>.<dataset_id>.baz, <project_id>.<dataset_id>.foo を指定し、 (C) 検索したい文字列 を実際に検索したい文字列に書き換えて実行すると、検索したい文字列を含んだレコードを見つけることができる。

select
  *
from `<project_id>.<dataset_id>.baz`
where
  contains_substr(
    `<project_id>.<dataset_id>.baz`
    , '(C) 検索したい文字列'
  )

所感

今回の対応で、同じような調査依頼が来た時に一瞬で調べられるようになって良かった。

レコードが多いテーブルは、課金されるバイト数 も多くなるので、できるだけこういった調査は行いたくない。

dbt の macro で、dataset_id や where の絞り込み部分を dbt run-operation の引数で渡すことを最初は考えた。ただ、typo やレコード数の多いテーブルに気づかずに実行などで、費用がかさむのが怖いのでやめました。こういった費用がかさみそうな調査は、実行するコードを共有し、レビューを通して問題ないことを確認してから、実行する必要があると判断しました。

hene.dev

dbt run-operation を利用して、全テーブル・全カラムを対象に指定文字列を含むレコードを調査

dbt run-operation を利用して、全テーブル・全カラムを対象に指定文字列を含むレコードを調査

背景

仕様

実装

実行環境

dbt macro

指定したデータセットとテーブルの全カラムを対象に指定した文字列が含まれているか調査するマクロ

macros/run_operation/search_all_columns_in_table.sql

macros/run_operation/search_all_columns_in_table.yml

指定したデータセットの全テーブル・全カラムを対象に指定した文字列が含まれているか調査するマクロ

macros/run_operation/search_all_columns_in_all_tables.sql

macros/run_operation/search_all_columns_in_all_tables.yml

dbt docs

macros/run_operation/_run_operation.md

調査

注意点

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

調査の流れ

ブランチを切る

課金されるバイト数を確認

課金されるバイト数

テーブル別課金されるバイト数

search_all_columns_in_all_tables のマクロを変更

search_all_columns_in_table のマクロを変更

プルリクを作成

実行例

所感

参考

関連記事

dbt run-operation を利用して、全テーブル・全カラムを対象に指定文字列を含むレコードを調査

dbt run-operation を利用して、全テーブル・全カラムを対象に指定文字列を含むレコードを調査

背景

仕様

実装

実行環境

dbt macro

指定したデータセットとテーブルの全カラムを対象に指定した文字列が含まれているか調査 するマクロ

macros/run_operation/search_all_columns_in_table.sql

macros/run_operation/search_all_columns_in_table.yml

指定したデータセットの全テーブル・全カラムを対象に指定した文字列が含まれているか調査 するマクロ

macros/run_operation/search_all_columns_in_all_tables.sql

macros/run_operation/search_all_columns_in_all_tables.yml

dbt docs

macros/run_operation/_run_operation.md

調査

注意点

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

調査の流れ

ブランチを切る

課金されるバイト数 を確認

課金されるバイト数

テーブル 別 課金されるバイト数

search_all_columns_in_all_tables のマクロを変更

search_all_columns_in_table のマクロを変更

プルリクを作成

実行例

所感

参考

関連記事

指定したデータセットとテーブルの全カラムを対象に指定した文字列が含まれているか調査するマクロ

指定したデータセットの全テーブル・全カラムを対象に指定した文字列が含まれているか調査するマクロ

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

課金されるバイト数を確認

テーブル別課金されるバイト数