BigQueryの永続的なユーザー定義関数でコード名称変換を実装する

背景

2019/07/01、BigQueryに永続的なユーザー定義関数（persistent user-defined functions）がベータ版として機能追加¹されました。
※追記：2019/11/22、GA²になりました。

永続的なユーザー定義関数³は、FUNCTIONをリソースとしてデータセット内に保存でき、クエリからFUNCTIONを呼び出せるのが特徴です。
それまでのUDFは一時的なものだったため、データセット内に保存できず、毎回クエリの先頭にFUNCTIONを記述する必要がありました。

やりたいこと

他のシステムのデータをBigQueryへ取り込んで分析する際、コードに対応する名称を付与したい場面があります。
これを、コードと名称を対応付けたテーブルのJOINではなく、永続的なユーザー定義関数で実装してみます。
更に、定期的にコードと名称の対応付けが変わることを想定し、その判断に日付項目を利用してみます。

実装

まずは、永続的なユーザー定義関数をデータセットfに作成します。

get_sales_statusファンクション

CREATE OR REPLACE FUNCTION
  f.get_sales_status(code STRING, target_date DATE)
  RETURNS STRING AS (
    CASE
    WHEN target_date <= '2019-03-31' THEN
      CASE code
        WHEN '10' THEN '新規受付'
        WHEN '20' THEN '入金済'
        WHEN '30' THEN '対応中'
        WHEN '40' THEN '注文取消'
        WHEN '50' THEN '発送済'
        WHEN '60' THEN '購入処理中'
        WHEN '70' THEN '決済処理中'
        WHEN '80' THEN '返品'
        WHEN '99' THEN 'テスト用'
        ELSE NULL
        END
    WHEN target_date <= '2020-03-31' THEN
      CASE code
        WHEN '10' THEN '新規受付'
        WHEN '11' THEN '予約受付'
        WHEN '20' THEN '入金済'
        WHEN '30' THEN '対応中'
        WHEN '40' THEN '注文取消'
        WHEN '50' THEN '発送済'
        WHEN '60' THEN '購入処理中'
        WHEN '70' THEN '決済処理中'
        WHEN '80' THEN '返品'
        ELSE NULL
        END
    ELSE
      CASE code
        WHEN '10' THEN '受付'
        WHEN '20' THEN '入金済'
        WHEN '30' THEN '対応中'
        WHEN '40' THEN '注文取消'
        WHEN '50' THEN '発送済'
        WHEN '60' THEN '購入処理中'
        WHEN '70' THEN '決済処理中'
        WHEN '80' THEN '返品'
        ELSE NULL
        END
    END
  )

このUDFをクエリから利用します。

UDFを利用するクエリ

WITH
  data AS (
  SELECT
    '10' AS sales_status,
    DATE '2019-03-31' AS process_date
  UNION ALL SELECT '11', '2019-03-31'
  UNION ALL SELECT '10', '2019-04-01'
  UNION ALL SELECT '11', '2019-04-01'
  UNION ALL SELECT '10', '2021-04-01'
  UNION ALL SELECT '11', '2021-04-01'
  )
SELECT
  process_date,
  sales_status,
  f.get_sales_status(sales_status, process_date) AS sales_status_name
FROM
  data

クエリの結果は以下のとおりです。日付項目の値により、名称の切り替えが実現できています。

行	process_date	sales_status	sales_status_name
1	2019-03-31	10	新規受付
2	2019-03-31	11	null
3	2019-04-01	10	新規受付
4	2019-04-01	11	予約受付
5	2021-04-01	10	受付
6	2021-04-01	11	null

おわりに

永続的なユーザー定義関数が追加されたことにより、各クエリで重複していたロジックが切り出せる点が便利です。
関数をどう管理するかという課題がありますが、ファイル化してバージョン管理したり、スプレッドシートから関数を生成する仕組みを用意することで、管理しやすくなると思います。

1. https://cloud.google.com/bigquery/docs/release-notes?hl=en#July_01_2019 ↩

2. https://cloud.google.com/bigquery/docs/release-notes?hl=en#November_22_2019 ↩

3. https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language?hl=ja#create_function_statement ↩