概要
2021年9月に、BigQueryでtable functionという機能がGAになった。ざっくり説明すると引数をとれるviewで、以下のように使える。
-- 開始・終了日を指定して日付と曜日のテーブルを取得
CREATE OR REPLACE TABLE FUNCTION mydataset.dates(
start_dt DATE,
end_dt DATE
) AS (
SELECT
dt,
-- 0... Sun, 6... Sat
MOD(UNIX_DATE(dt)+4, 7) AS day_of_week,
FROM UNNEST(GENERATE_DATE_ARRAY(start_dt, end_dt)) AS dt
);
SELECT dt, day_of_week
FROM mydataset.dates("2023-01-01", "2023-01-08")
ORDER BY dt;
/*
+------------+-------------+
| dt | day_of_week |
+------------+-------------+
| 2023-01-01 | 0 |
| 2023-01-02 | 1 |
| 2023-01-03 | 2 |
| 2023-01-04 | 3 |
| 2023-01-05 | 4 |
| 2023-01-06 | 5 |
| 2023-01-07 | 6 |
| 2023-01-08 | 0 |
+------------+-------------+
*/
viewみたいなものならdbtで管理できてもよくない？と思ったけど結構難しかったのでやったことをメモしておく。目標はviewとほぼ同じ感覚でtable functionを利用可能にすること。
前提
この記事に書いてあることを再現するためには以下をやっておく。
- BigQueryを利用する権限がある（サービスアカウントでも
gcloud auth application-default loginでも） - dbtをインストール済み（まだならここを参考に
pip install dbt-bigquery1） -
dbt initでdbtプロジェクトを作成済み
こんな感じのディレクトリになるはず（.gitkeepや.gitignoreは省略）。
.
├── analyses
├── dbt_project.yml
├── macros
├── models
│ └── example
│ ├── my_first_dbt_model.sql
│ ├── my_second_dbt_model.sql
│ └── schema.yml
├── README.md
├── seeds
├── snapshots
└── tests
設定
materializationの設定
現状だとtable functionはサポートされていないので、どのようにCREATE TABLE FUNCTIONのDDLを実行するかdbtに教えないといけない。必要なことはcreating new materializationのページに書いてある。
結論、以下を./macros/tvf.sql2に保存すれば動く。ややこしい部分はドキュメントのURLをコメントで残したのでそっちも見るとよいかも。
{%- materialization tvf, adapter='bigquery' -%}
{%- set identifier = model['alias'] -%} -- overwrite identifier
-- modelに設定したparamsを取得
-- https://docs.getdbt.com/reference/dbt-jinja-functions/config
{%- set params = config.get('params', '') -%}
-- relationはdatabase(project), schema(dataset), identifierなどをまとめたclass
-- https://docs.getdbt.com/reference/dbt-classes#relation
-- typeは特定の値しか許されないからとりあえずviewにしてみた
{%- set relation = api.Relation.create(identifier=identifier, schema=schema, database=database, type="external") -%}
{% set funcname = "`" + ([relation.database, relation.schema, relation.identifier] | join(".")) + "`" %}
{% call statement('main') -%}
-- ↓↓↓↓↓ 実行するsqlここから ↓↓↓↓↓
CREATE OR REPLACE TABLE FUNCTION {{ funcname }}({{ params }})
AS (
{{ sql }} -- modelに記載するsql
)
-- ↑↑↑↑↑ 実行するsqlここまで ↑↑↑↑↑
{%- endcall %}
-- 作成したrelationを返す（そんなルールだと思っておけばよさげ）
{{ return({'relations': [relation]}) }}
{%- endmaterialization -%}
modelの設定
table functionsのmodelはほぼviewと同じように設定すればよい。冒頭のmaterialized='tvf'とparams='...'だけ注意。
/* {{
config(
materialized='tvf',
params='start_dt DATE, end_dt DATE',
)
}} コメントにする必要はないがsyntax highlightがうるさいから... */
SELECT
dt,
-- 0... Sun, 6... Sat
MOD(UNIX_DATE(dt)+4, 7) AS day_of_week,
FROM UNNEST(GENERATE_DATE_ARRAY(start_dt, end_dt)) AS dt
ここまできたらdbt runを実行してみる。stg_datesというtable functionができればOK3。
他のmodelから参照
あるmodelから他のmodelを参照するときには{{ ref('model') }}を使うが、これはtable functionも同様。試しにstg_datesの土日限定版をweekend_datesとして作成してみる。
/* {{
config(
materialized='tvf',
params='start_dt DATE, end_dt DATE',
)
}} コメントにする必要はないがsyntax highlightがうるさいから... */
SELECT
dt,
-- 0... Sun, 6... Sat
day_of_week,
FROM {{ ref("stg_dates") }}(start_dt, end_dt) -- 引数を渡せる
WHERE day_of_week IN (0, 6)
dbt runするとweekend_datesというtable functionが増えているはず。Lineage Graph4もちゃんと作成されるよ。
懸念
columnが自動で表示されない
table functionの場合、schema.ymlに明記しないとWeb UIのColumnsに何も表示されない。何故こうなるかというと、その辺りの情報をとってくるこのSQLにtable functionの情報が引っかからないから。
通常のview・table用のテストを使えない
dbtはデフォルトでunique not_null accepted_values relationshipsという4つのテストを準備しているが、そのままではtable functionに使うことはできない（引数を渡せないから）。代わりにcustom generic testを使う必要がある。
所感
軽く懸念点は挙げたけど、まあまあ使えそうな感触。必要にせまられたら使ってみるかも。