概要

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-bigquery¹）
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.sql²に保存すれば動く。ややこしい部分はドキュメントのURLをコメントで残したのでそっちも見るとよいかも。

./macros/tvf.sql

{%- 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='...'だけ注意。

./models/stg_dates.sql

/* {{
  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ができればOK³。

他のmodelから参照

あるmodelから他のmodelを参照するときには{{ ref('model') }}を使うが、これはtable functionも同様。試しにstg_datesの土日限定版をweekend_datesとして作成してみる。

./models/weekend_dates.sql

/* {{
  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 Graph⁴もちゃんと作成されるよ。

懸念

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を使う必要がある。

所感

軽く懸念点は挙げたけど、まあまあ使えそうな感触。必要にせまられたら使ってみるかも。

ちなみに動作確認したPythonのversionは3.10.9、dbt-coreは1.3.1、dbt-bigqueryは1.3.0。当時まだPython3.11がサポートされておらずちょっとハマった（ここで確認）。 ↩
tvfはtable-valued functionの略。 ↩
まだ削除していなければ、dbt initで作成されたexample以下のmodelも作成されるけど気にしない方向で。 ↩
dbt docs generateからのdbt docs serveで見られる。 ↩