hene

hene.dev

(Last updated on )

パーティション分割テーブル・ワイルドカードテーブルの範囲を絞って ci の dbt test のコストを削減

パーティション分割テーブル・ワイルドカードテーブルの範囲を絞って ci の dbt test のコストを削減

DBT_TARGET の値によって、絞る範囲を変更する。

  • prod: モニタリング用
    • 3か月 + 1日前 ~ 昨日
  • ci: CI 用
    • 2日前 + 1日前 ~ 昨日
  • dev: 開発用
    • 3か月 + 1日前 ~ 昨日

手順

  • dbt_project.yml で vars を設定
  • macro で範囲を絞る
  • vars を上書き
  • GitHub Actions で dbt test を実行

dbt_project.yml で vars を設定

デフォルトは、3か月 + 1日前 ~ 昨日 で絞る。

dbt_project.yml

  • filter_max_date: 昨日の日付
  • filter_min_date: 3か月 + 1日前の日付
# 略

vars:
    filter_max_date: "date_sub(current_date('Asia/Tokyo'), interval 1 day)"
    filter_min_date: "date_sub(current_date('Asia/Tokyo'), interval 3 month)"

macro で範囲を絞る

query_source_partitioned_table

パーティション分割テーブルの絞り込み。

  • partition_column_name で絞り込む

macros/query/query_source_partitioned_table.sql

#standardSQL

{% macro query_source_partitioned_table(source_name, table_name, partition_column_name) -%}
(
  select
    *
  from {{ source(source_name, table_name) }}
  where
    {{ partition_column_name }} between
    date_sub(
      {{ var('filter_min_date') }}
      , interval 1 day
    )
    and {{ var('filter_max_date') }}
)
{%- endmacro %}

macros/query/query_source_partitioned_table.yml

version: 2

macros:
  - name: query_source_partitioned_table
    description: >
      {{ doc("query_source_partitioned_table_description") }}
    arguments:
      - name: source_name
        type: source name
        description: dbt の source 名

      - name: table_name
        type: table
        description: テーブル名

      - name: partition_column_name
        type: column
        description: パーティション分割テーブルで分割しているカラム名

macros/query/_query.md

<!-- 略 -->

{% docs query_source_partitioned_table_description %}

# パーティション分割テーブルを参照するときに使う macro

- [パーティション分割テーブルに対するクエリ | BigQuery | Google Cloud](https://cloud.google.com/bigquery/docs/querying-partitioned-tables?hl=ja)

## 用途

コスト削減

## 対応

`DBT_TARGET` で、絞り込む期間を調整。

`var('filter_min_date')` + 1 日前 >= `_table_suffix` >= `var('filter_max_date')` で絞り込んでいます。

＊ `$ dbt run 略 --vars '{"filter_min_date": "'\''2023-01-01'\''", "filter_max_date": "'\''2023-02-01'\''"}' --target ci` で、絞り込みを調整できます

DBT_TARGET | partition_column_name でレコードを絞り込み
--------- | ---------
prod | 3 ヶ月 + 1 日前 ~ 昨日
ci | 2 日 + 1 日前 ~ 昨日
dev | 3 ヶ月 + 1 日前 ~ 昨日

{% enddocs %}

<!-- 略 -->

モデルで query_source_partitioned_table のマクロを利用する

models/**/*.sql

#standardSQL

with source as (
  select
    -- 略
  from {{
    query_source_partitioned_table(
      '<source_name>'
      , '<table_name>'
      , '<partition_column_name>'
    )
  }}
)

-- 略

select
  *
from final

query_source_wildcard_table

ワイルドカードテーブルの絞り込み。

  • _table_suffix で絞り込む

macros/query/query_source_wildcard_table.sql

#standardSQL

{% macro query_source_wildcard_table(source_name, table_name) -%}
(
  select
    *
    , _table_suffix
  from {{ source(source_name, table_name) }}
  where
    _table_suffix between
    format_date(
      '%Y%m%d'
      , date_sub(
        {{ var('filter_min_date') }}
        , interval 1 day
      )
    )
    and format_date(
      '%Y%m%d'
      , {{ var('filter_max_date') }}
    )
)
{%- endmacro %}

macros/query/query_source_wildcard_table.yml

version: 2

macros:
  - name: query_source_wildcard_table
    description: >
      {{ doc("query_source_wildcard_table_description") }}
    arguments:
      - name: source_name
        type: source name
        description: dbt の source 名

      - name: table_name
        type: table
        description: テーブル名

macros/query/_query.md

<!-- 略 -->

{% docs query_source_wildcard_table_description %}

# ワイルドカードテーブルを参照するときに使う macro

- [ワイルドカード テーブルを使用した複数テーブルに対するクエリ  |  BigQuery  |  Google Cloud](https://cloud.google.com/bigquery/docs/querying-wildcard-tables?hl=ja)

## 用途

コスト削減

### 対応

`DBT_TARGET` で、絞り込む期間を調整。

`var('filter_min_date')` + 1 日前 >= `_table_suffix` >= `var('filter_max_date')` で絞り込んでいます。

＊ `$ dbt run 略 --vars '{"filter_min_date": "'\''2023-01-01'\''", "filter_max_date": "'\''2023-02-01'\''"}' --target ci` で、絞り込みを調整できます

DBT_TARGET | _table_suffix でレコードを絞り込み
--------- | ---------
prod | 3 ヶ月 + 1 日前 ~ 昨日
ci | 2 日 + 1 日前 ~ 昨日
dev | 3 ヶ月 + 1 日前 ~ 昨日

{% enddocs %}

<!-- 略 -->

モデルで query_source_wildcard_table のマクロを利用する

models/**/*.sql

#standardSQL

with source as (
  select
    -- 略
  from {{
    query_source_wildcard_table(
      '<source_name>'
      , '<table_name>'
    )
  }}
)

-- 略

select
  *
from final

vars を上書き

dbt_vars/ci.json

ci のみ、filter_min_date を上書き

{
  "filter_min_date": "date_sub(current_date('Asia/Tokyo'), interval 2 day)"
}

dbt_vars/prod.json, dbt_vars/dev.json

{}

GitHub Actions で dbt test を実行

.github/workflows/build-dbt-in-ci.yml

name: build-dbt-in-ci

on:
  workflow_dispatch:
  pull_request:
    paths:
      - 'analyses/**'
      - 'macros/**'
      - 'models/**'
      - 'seeds/**'

concurrency:
  group: ${{ github.workflow }}-${{ github.ref }}
  cancel-in-progress: true

env:
  # 略
  BIGQUERY_PROJECT_ID: <bigquery_project_id>
  DBT_STATE: ./ci_target
  DBT_DEFER: true
  DBT_PROFILES_DIR: ./.dbt
  DBT_TARGET: ci
  GCP_REGION: asia-northeast1
  TZ: Asia/Tokyo

jobs:
  build-dbt-in-ci:
    name: Build dbt in CI
    # 略
    steps:
      - name: Checkout
        uses: actions/checkout@v3

      # 略

      - name: Run dbt seed
        run: poetry run dbt seed --select +state:modified --vars "$(cat dbt_vars/${DBT_TARGET}.json)"

      - name: Run dbt run
        run: poetry run dbt run --select +state:modified --vars "$(cat dbt_vars/${DBT_TARGET}.json)"

      - name: Run dbt test
        run: poetry run dbt test --select state:modified --exclude tag:big_model --vars "$(cat dbt_vars/${DBT_TARGET}.json)"

おまけ

ローカル環境で試す。

$ dbt run +<model_name> --vars '{"filter_min_date": "'\''2023-01-01'\''", "filter_max_date": "'\''2023-03-01'\''"}' --target dev

参考

関連記事

← Back to home