[BigQuery] dbtでtable functionを管理する

## 概要
2021年9月に、BigQueryで[table function](https://cloud.google.com/bigquery/docs/reference/standard-sql/table-functions)という機能がGAになった。ざっくり説明すると引数をとれるviewで、以下のように使える。

```sql
-- 開始・終了日を指定して日付と曜日のテーブルを取得
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をインストール済み（まだなら[ここ](https://docs.getdbt.com/docs/get-started/pip-install)を参考に`pip install dbt-bigquery`[^version]）
* `dbt init`でdbtプロジェクトを作成済み

[^version]: ちなみに動作確認したPythonのversionは3.10.9、dbt-coreは1.3.1、dbt-bigqueryは1.3.0。当時まだPython3.11がサポートされておらずちょっとハマった（[ここ](https://docs.getdbt.com/faqs/core/install-python-compatibility)で確認）。

こんな感じのディレクトリになるはず（.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](https://docs.getdbt.com/guides/advanced/creating-new-materializations)のページに書いてある。
結論、以下を./macros/tvf.sql[^tvf]に保存すれば動く。ややこしい部分はドキュメントのURLをコメントで残したのでそっちも見るとよいかも。
[^tvf]: tvfはtable-valued functionの略。

```jinja:./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='...'`だけ注意。

```sql:./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[^example]。
[^example]: まだ削除していなければ、`dbt init`で作成されたexample以下のmodelも作成されるけど気にしない方向で。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/540947/294d59e7-b547-c9a5-a9a2-63bdc6c39179.png)


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

```sql:./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[^graph]もちゃんと作成されるよ。
[^graph]: `dbt docs generate`からの`dbt docs serve`で見られる。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/540947/6e6d6f1e-9234-ee1f-2e5d-f605c1d1a397.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/540947/c2c8b08e-e4e0-9635-b55f-4f46385a2a37.png)

## 懸念
### columnが自動で表示されない
table functionの場合、schema.ymlに明記しないとWeb UIのColumnsに何も表示されない。何故こうなるかというと、その辺りの情報をとってくる[このSQL](https://github.com/dbt-labs/dbt-bigquery/blob/cf794aaa44ad7b426fe0dec125c3d15df741ad73/dbt/include/bigquery/macros/catalog.sql)にtable functionの情報が引っかからないから。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/540947/60b1e387-cbcd-4cf9-c45d-87d09d605361.png)

### 通常のview・table用のテストを使えない
dbtはデフォルトで`unique` `not_null` `accepted_values` `relationships`という4つのテストを準備しているが、そのままではtable functionに使うことはできない（引数を渡せないから）。代わりに[custom generic test](https://docs.getdbt.com/guides/best-practices/writing-custom-generic-tests)を使う必要がある。

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