BigQueryのテーブル関数の使い道を考えてみる

概要

Google BigQueryに、「テーブル関数（TVF）」という機能がプレビューで公開されてたので試しました。
https://cloud.google.com/bigquery/docs/reference/standard-sql/table-functions

パラメータを受け取って、関数内で実行したクエリの結果を返してくるといった機能みたいです。
パラメータを持ったViewみたいなものでしょうか。

今回使うデータセット、テーブル

以降の例にあるデータセット、テーブルは、私の個人プロジェクトにある以下のものを利用しています。
データセット：truefly
テーブル：truefly.census
※「令和２年国勢調査　人口速報集計」（総務省統計局） を加工して作成しています。

テーブル関数の作り方

CREATE OR REPLACE TABLE FUNCTIONステートメントで、テーブル関数を作成できます。
試しに、都道府県名をパラメータとして受け取り、市区町村の人口を出力する関数を作ってみます。

CREATE OR REPLACE TABLE FUNCTION truefly.get_city_population(prefecture_name STRING) AS
SELECT
    city,
    population,
    male_population,
    female_population,
FROM
    truefly.census
WHERE
    prefecture = prefecture_name

作成されました。UDFと同じアイコンですね。

執筆(2021/07/17)時点では、CREATE TEMPORARY TABLE FUNCTIONステートメントで、SQL実行時に定義することはできませんでした。
WITH句でやればOKということですね。

テーブル関数の実行

FROM句にtruefly.get_city_population('都道府県名')を書くことで呼び出しができます。
他のテーブルとJOINもできるようですが、今回は単体で埼玉県の市区町村の人口を取得してみます。

SELECT * FROM truefly.get_city_population('埼玉県') ORDER BY population DESC LIMIT 5

このようにtruefly.censusテーブルから取得した結果が出力されます。

city	population	male_population	female_population
埼玉県	7346836	3651934	3694902
さいたま市	1324591	652965	671626
川口市	594461	299265	295196
川越市	354680	177393	177287
所沢市	342535	168168	174367

そして試して気が付きました。
パラメータを単にそのままWHERE句にいれるだけなら、わざわざテーブル関数など介さず、そのままテーブル参照でええやん、と。

テーブル関数の有効的な活用方法を検討する

テーブル関数を活用方法を見出すために、もっと複雑なテーブル関数を実装してみます。

テーブルの結合条件をパラメータで渡すテーブル関数

例えば、特定の都道府県の人口よりも多い人口の都道府県を取得したいとき、
truefly.censusと、特定の都道府県のtruefly.censusを比較して値をとってくることになるので、SQLはやや複雑になります。
これをテーブル関数にするとこんな感じ。

CREATE OR REPLACE TABLE FUNCTION truefly.get_larger_population(prefecture_name STRING) AS
SELECT
    a.prefecture,
    a.population,
FROM
    truefly.census a
    INNER JOIN truefly.census b
        ON a.population > b.population
        AND b.city = prefecture_name
WHERE
    -- 都道府県単位の人口を取得
    a.city != '全国'
    AND a.prefecture = a.city

テーブル関数から、埼玉県よりも人口の多い都道府県を取得してみます。

SELECT * FROM truefly.get_larger_population('埼玉県') ORDER BY population

prefecture	population
東京都	14064696
神奈川県	9240411
大阪府	8842523
愛知県	7546192

このようにJOINの結合条件となっている値をパラメータで外から渡すことで、シンプルな関数を呼び出すだけで実現できました。

取得する項目をパラメータで渡すテーブル関数

今回のサンプルテーブル、truefly.censusには、

• 人口：population
• 男性人口：male_population
• 女性人口：female_population
  といった項目があります。この項目をパラメータで指定して、都道府県毎のランキングを返すテーブル関数を作ってみます。

CREATE OR REPLACE TABLE FUNCTION truefly.get_ranking(class STRING) AS
SELECT
    RANK() OVER(ORDER BY value DESC) AS rank,
    prefecture,
    value,
FROM (
    SELECT
        prefecture,
        CASE
            WHEN class = '人口' THEN population
            WHEN class = '男性人口' THEN male_population
            WHEN class = '女性人口' THEN female_population
            ELSE population
        END AS value
    FROM
        truefly.census
    WHERE
        -- 都道府県単位の人口を取得
        city != '全国'
        AND prefecture = city
)       
ORDER BY
    rank

呼び出すときはどの項目のランキングがほしいかをパラメータで指定します。
今回は3項目だけなので、それぞれ上位3件を取得してみます。

SELECT * FROM (SELECT '1.人口' AS class, * FROM truefly.get_ranking('人口') LIMIT 3) UNION ALL
SELECT * FROM (SELECT '2.男性人口' AS class, * FROM truefly.get_ranking('男性人口')  LIMIT 3) UNION ALL
SELECT * FROM (SELECT '3.女性人口' AS class, * FROM truefly.get_ranking('女性人口')  LIMIT 3)
ORDER BY 1

class	rank	prefecture	value
1.人口	1	東京都	14064696
1.人口	2	神奈川県	9240411
1.人口	3	大阪府	8842523
2.男性人口	1	東京都	6902188
2.男性人口	2	神奈川県	4587059
2.男性人口	3	大阪府	4236023
3.女性人口	1	東京都	7162508
3.女性人口	2	神奈川県	4653352
3.女性人口	3	大阪府	4606500

当然全体の人口が多い都道府県が、男女別でも上位でした。
このようにパラメータでどの値をとってくるか指定することで、
内部でSQLを共通化して、実装の手間を省くことができるんじゃないかなと思いました。

まとめ

以上、BigQueryのテーブル関数について、使い道を検討してみました。

公式ドキュメントにあるような、単にWHERE句で書ける条件をパラメータで渡すといった使い方よりは、
JOINの結合条件や、SELECT句のCASE文内の条件をパラメータで渡して、中身のSQLは共通処理としてテーブル関数化する、
といった使い方で有効活用ができそうです。

個人的には、テーブルをSELECTした結果を元に、スカラー値を返してくれるUDFが実現できると嬉しいんですが、いつかできるようになるのかなあ。