🍊
dbt の Cross-database macros について
この記事はdbt Advent Calendar 2024 15日目 シリーズ2の記事です。
はじめに
データ分析やETLツールとして広く使われている dbt は、異なるデータベース間でコードを再利用できる仕組みを提供しています。その中でも「Cross-database macros」は、データベース固有のSQL方言を抽象化し、共通の機能を実現するための強力なツールです。この記事では、Cross-database macrosの概要とその実際の活用例を通じて、その価値を解説します。
Cross-database macrosとは？
Cross-database macrosは、異なるデータベースアダプタやSQL方言の違いを抽象化し、共通のマクロを提供する仕組みです。この仕組みにより、同じコードを異なるデータベース環境で再利用することが可能になります。
例えば、BigQueryやSnowflake、PostgreSQLといった複数のデータベースにおいて、日付や文字列操作の関数が異なる場合でも、Cross-database macrosを使えば、{{ dbt.current_timestamp() }} のような統一された関数で操作を行うことができます。
誰にとって有益なのか？
Cross-database macrosは、以下の3つのユーザーグループにとって特に有益です。
1. パッケージのメンテナ
パッケージのメンテナであれば、Cross-database macrosを利用することで、特定のデータベースに依存しないコードを書くことができます。これにより、作成したパッケージが多くのデータベースで利用可能となり、ユーザー層の拡大が期待できます。
2. アダプタのメンテナ
アダプタ（例えばBigQueryやSnowflakeのドライバ）を管理している場合、Cross-database macrosを実装・テストすることで、対応するパッケージの幅を広げることが可能です。この取り組みにより、アダプタを利用するユーザーにより多くの選択肢を提供できます。
3. エンドユーザー
エンドユーザーにとっては、特別な設定やカスタマイズなしに、多くのパッケージやアダプタが「そのまま動く」環境が整います。異なるデータベース間での移行や、マルチデータベース環境での運用が容易になるのも大きな利点です。
Cross-database macrosの活用例
以下に、Cross-database macrosを活用した具体例をいくつか挙げます。
1. 現在日時
現在日時が必要な場合
{{ dbt.current_timestamp() }}
これにより、BigQueryでは current_timestamp()、PostgreSQLでは now() が呼び出されます。
2. 指定されたデータ型に値をキャストする
異なるデータベースでの cast 処理
{{ dbt.cast("column_1", api.Column.translate_type("string")) }}
{{ dbt.cast("column_2", api.Column.translate_type("integer")) }}
{{ dbt.cast("'2016-03-09'", api.Column.translate_type("date")) }}
サンプル出力（BigQuery）
cast(column_1 as string)
cast(column_2 as INT64)
cast('2016-03-09' as date)
サンプル出力（PostgreSQL）
cast(column_1 as TEXT)
cast(column_2 as INT)
cast('2016-03-09' as date)
Cross-database macrosを導入するメリット
- 異なるデータベース間での移行する場合
- マルチデータベース環境での運用する場合
正直エンドユーザーの自分としては、上記のメリットに該当しなければ見慣れているBigQuery：current_timestamp()を{{ dbt.current_timestamp() }}にわざわざ書き換えるメリットはない気がする。
あと、パッケージやアダプタ、サンプルリポジトリはCross-database macrosを使っているので、習得するとそれらのコードを読むのが楽になるくらいですかね、、、
おわりに
Cross-database macrosは、dbtの柔軟性と拡張性を象徴する機能のひとつです。これを活用することで、異なるデータベース間でのプロジェクト運用が格段に効率化されます。
調べた結果、本番導入は断念。
補足
Cross-database macros早見表(BigQuery)
データ型関数（Data type functions）
| BigQuery | dbt |
|---|---|
| bigint | {{ dbt.type_bigint() }} |
| boolean | {{ dbt.type_boolean() }} |
| FLOAT64 | {{ dbt.type_float() }} |
| INT64 | {{ dbt.type_int() }} |
| numeric | {{ dbt.type_numeric() }} |
| string | {{ dbt.type_string() }} |
| timestamp | {{ dbt.type_timestamp() }} |
| current_timestamp() | {{ dbt.current_timestamp() }} |
セット関数（Set functions）
| BigQuery | dbt |
|---|---|
| except distinct | {{ dbt.except() }} |
| intersect distinct | {{ dbt.intersect() }} |
配列関数（Array functions）
| BigQuery | dbt |
|---|---|
| array_concat(array_column, [ element_column ]) | {{ dbt.array_append("array_column", "element_column") }} |
| array_concat(array_column, [ 5 ]) | {{ dbt.array_append("array_column", "5") }} |
| array_concat(array_column, [ 'blue' ]) | {{ dbt.array_append("array_column", "'blue'") }} |
| array_concat(array_column_1, array_column_2) | {{ dbt.array_concat("array_column_1", "array_column_2") }} |
| [ column_1 , column_2 , column_3 ] | {{ dbt.array_construct(["column_1", "column_2", "column_3"]) }} |
| ARRAY<integer>[] | {{ dbt.array_construct([], "integer") }} |
| [ 1 , 2 , 3 , 4 ] | {{ dbt.array_construct([1, 2, 3, 4]) }} |
| [ 'blue' , 'green' ] | {{ dbt.array_construct(["'blue'", "'green'"]) }} |
文字列関数（String functions）
| BigQuery | dbt |
|---|---|
| column_1 || column_2 | {{ dbt.concat(["column_1", "column_2"]) }} |
| year_column || '-' || month_column || '-' || day_column | {{ dbt.concat(["year_column", "'-'" , "month_column", "'-'" , "day_column"]) }} |
| first_part_column || '.' || second_part_column | {{ dbt.concat(["first_part_column", "'.'" , "second_part_column"]) }} |
| first_part_column || ',' || second_part_column | {{ dbt.concat(["first_part_column", "','" , "second_part_column"]) }} |
| to_hex(md5(cast(column as string))) | {{ dbt.hash("column") }} |
| to_hex(md5(cast('Pennsylvania' as string))) | {{ dbt.hash("'Pennsylvania'") }} |
| length(column) | {{ dbt.length("column") }} |
| strpos(text_column,substring_column) | {{ dbt.position("substring_column", "text_column") }} |
| strpos(text_column,'-') | {{ dbt.position("'-'", "text_column") }} |
| replace(string_text_column,old_chars_column,new_chars_column) | {{ dbt.replace("string_text_column", "old_chars_column", "new_chars_column") }} |
| replace(string_text_column,'-','_') | {{ dbt.replace("string_text_column", "'-'", "'_'") }} |
| case when length_column = 0 then '' else substr(string_text_column,-1 * (length_column)) end | {{ dbt.right("string_text_column", "length_column") }} |
| case when 3 = 0 then '' else substr(string_text_column,-1 * (3)) end | {{ dbt.right("string_text_column", "3") }} |
| split(column_to_split,delimiter_column)[safe_offset(0)] | {{ dbt.split_part(string_text='column_to_split', delimiter_text='delimiter_column', part_number=1) }} |
| split('1|2|3', '|' )[safe_offset(0)] | {{ dbt.split_part(string_text="'1|2|3'", delimiter_text="'|'", part_number=1) }} |
文字列リテラル関数（String literal functions）
| BigQuery | dbt |
|---|---|
| they're | {{ dbt.escape_single_quotes("they're") }} |
| ain't ain't a word | {{ dbt.escape_single_quotes("ain't ain't a word") }} |
| select '''Pennsylvania''' | select {{ dbt.string_literal("Pennsylvania") }} |
集計およびウィンドウ関数（Aggregate and window functions）
| BigQuery | dbt |
|---|---|
| any_value(column_name) | {{ dbt.any_value("column_name") }} |
| logical_or(boolean_column) | {{ dbt.bool_or("boolean_column") }} |
| logical_or(integer_column = 3) | {{ dbt.bool_or("integer_column = 3") }} |
| logical_or(string_column = 'Pennsylvania') | {{ dbt.bool_or("string_column = 'Pennsylvania'") }} |
| logical_or(column1 = column | {{ dbt.bool_or("column1 = column2") }} |
| string_agg(column_to_agg, ',' order by order_by_column limit 10) | {{ dbt.listagg(measure="column_to_agg", delimiter_text="','", order_by_clause="order by order_by_column", limit_num=10) }} |
型変換関数（Cast functions）
| BigQuery | dbt |
|---|---|
| cast(column_1 as string) | {{ dbt.cast("column_1", api.Column.translate_type("string")) }} |
| cast(column_2 as INT64) | {{ dbt.cast("column_2", api.Column.translate_type("integer")) }} |
| cast('2016-03-09' as date) | {{ dbt.cast("'2016-03-09'", api.Column.translate_type("date")) }} |
| cast(boolean_column_name as string) | {{ dbt.cast_bool_to_text("boolean_column_name") }} |
| cast(false as string) | {{ dbt.cast_bool_to_text("false") }} |
| cast(true as string) | {{ dbt.cast_bool_to_text("true") }} |
| cast(0 = 1 as string) | {{ dbt.cast_bool_to_text("0 = 1") }} |
| cast(1 = 1 as string) | {{ dbt.cast_bool_to_text("1 = 1") }} |
| cast(null as string) | {{ dbt.cast_bool_to_text("null") }} |
| safe_cast(column_1 as string) | {{ dbt.safe_cast("column_1", api.Column.translate_type("string")) }} |
| safe_cast(column_2 as INT64) | {{ dbt.safe_cast("column_2", api.Column.translate_type("integer")) }} |
| safe_cast('2016-03-09' as date) | {{ dbt.safe_cast("'2016-03-09'", api.Column.translate_type("date")) }} |
日付および時刻関数（Date and time functions）
| BigQuery | dbt |
|---|---|
| to_date('2023-10-04', 'YYYY-MM-DD') | {{ dbt.date(2023, 10, 4) }} |
| datetime_add(cast( '2016-03-09' as datetime), interval 1 day) | {{ dbt.dateadd(datepart="day", interval=1, from_date_or_timestamp="'2016-03-09'") }} |
| datetime_add(cast( '2016-03-09' as datetime),interval -2 month) | {{ dbt.dateadd(datepart="month", interval=-2, from_date_or_timestamp="'2016-03-09'") }} |
| datetime_diff(cast(column_2 as datetime),cast(column_1 as datetime),day) | {{ dbt.datediff("column_1", "column_2", "day") }} |
| datetime_diff(cast('2016-03-09' as datetime),cast(column as datetime),month) | {{ dbt.datediff("column", "'2016-03-09'", "month") }} |
| datetime_diff(cast(column as datetime),cast('2016-03-09' as datetime),year) | {{ dbt.datediff("'2016-03-09'", "column", "year") }} |
| timestamp_trunc(cast(updated_at as timestamp),day) | {{ dbt.date_trunc("day", "updated_at") }} |
| timestamp_trunc(cast(updated_at as timestamp),month) | {{ dbt.date_trunc("month", "updated_at") }} |
| timestamp_trunc(cast('2016-03-09' as timestamp),year) | {{ dbt.date_trunc("year", "'2016-03-09'") }} |
| cast(datetime_add(cast(datetime_add(cast( timestamp_trunc(cast(created_at as timestamp),month) as datetime),interval 1 month)as datetime), interval -1 day) as date) | {{ dbt.last_day("created_at", "month") }} |
| cast(datetime_add(cast( datetime_add(cast( timestamp_trunc(cast('2016-03-09' as timestamp),year) as datetime), interval 1 year) as datetime),interval -1 day) as date) | {{ dbt.last_day("'2016-03-09'", "year") }} |
参考
Discussion