🎱
BigQueryのテーブル関数をDataformで定義する
モチベーション
BigQuery、Dataform、Google Data Studio 関連案件にて、DataStudioのメールフィルタ機能 を用いて、アクセス者のメールアドレスに応じて動的にデータ出し分けをしていたが、カスタムクエリの本数増加、複雑化などでカスタムクエリのソース(SQL)もgit管理対象にしてメンテナンス性を向上したく、テーブル関数のDataformでの定義方法を検証した時のメモ。
参考ドキュメント
- BigQueryのテーブル関数
- Dataform hasOutput
検証内容
Dataformにてテーブル関数を作成する
BigQueryのテーブル関数のドキュメント内のSQL例にemailの引数を追加する形のテーブル関数を追加。
definitions/names_by_year.sqlx
config {
hasOutput: true
}
CREATE OR REPLACE TABLE FUNCTION ${self()}(y INT64, email STRING)
AS
SELECT year, name, SUM(number) AS total, email
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE year = y
GROUP BY year, name
Google Data Studioのカスタムクエリにてテーブル関数を利用する
Google Data Studioのデータソース作成画面で以下のようなBigQueryカスタムクエリ内容で上記のテーブル関数を呼び出せることを確認できた。
SELECT * FROM dataform.names_by_year(1950, @DS_USER_EMAIL)
（おまけ）Dataform内でテーブル関数を再利用する
Dataform管理で作成したテーブル関数をDataform内で再利用できるか検証してみた。
以下のようなSQLXファイルでテーブル関数を用いてテーブルを作れることも確認。
definitions/name_by_year_limit5.sqlx
config {
type: "table"
}
SELECT * FROM ${ref('names_by_year')}(1950, "foo@example.com") LIMIT 5
まとめ
Data Studioのカスタムクエリの本数増加時、複雑化した際にはBigQueryのテーブル関数とDataformのhasOutputを使うと、git管理対象のSQLボリュームを増やせてメンテナンス性を向上できそうなことがわかった。Dataformとても便利です。
Discussion