【BigQuery】ユーザー定義関数の使い方！UDF(User Defined Function)を紹介します！

SQL
2021.04.16

今回はBigQueryで利用できるユーザー定義関数(User Defined Function)について紹介したいと思います。

正直言ってUDFは知らないと分析ができないと言った類のテクニックではありませんが、使えれば複雑な記述を簡潔化でき「可読性」≒「メンテナンス性」の向上が劇的に見込まれます。

特に複雑な処理を何度も行う場合にUDFの偉大さを痛感することになると思います。

ad

UDFとは

UDF(User Defined Function)とはその名の通り「ユーザーが自由に関数を定義できる機能」です。

COUNT()やREPLACE()のような関数を自分で作れるようなイメージです。

私の場合は特に「URL文字列の分解処理」や「日時のフォーマット処理」、「定数管理」などの汎用性が高くて煩雑な処理を定義して使い回すことが多いです。

UDFの永続性

UDFには「永続的」なものと「一時的」なものの２種類があります。

一時的UDF

一時的なUDFはそのQUERY(主にSELECT文)の中でしか使用しない機能を定義します。

そのQUERYでしか使用しないようなマニアックな機能だったり、その場でしか使用しない一時的な機能を提供します。

# 対象日付を指定する
CREATE TEMP FUNCTION target_date() AS (
         CURRENT_DATE('Asia/Tokyo')
        #  DATE_ADD(CURRENT_DATE('Asia/Tokyo'), INTERVAL -1 DAY)
        #  DATE('2021-04-17')
);

select target_date()


こちらの例では現在日付を取得する関数ですが、動作検証をする際はコメントを活きにしてテストします。

永続型UDF

永続的なUDFはどのQUERYからもアクセスできる機能を定義します。
主に汎用的に使用する共通機能を定義することが多いです。

CREATE OR REPLACE FUNCTION で指定したデータセットにファンクションが作成されます。
作成されたファンクションはどのSQL文からも呼び出しが可能です。

CREATE OR REPLACE FUNCTION `functions.unifyTodofuken`(td STRING) AS (
CASE
    WHEN td LIKE "北海%" OR td LIKE "札幌%" THEN
        "北海道"
    WHEN td LIKE "青森%" THEN
        "青森県"
    WHEN td LIKE "岩手%" THEN
        "岩手県"
    WHEN td LIKE "埼玉%" OR td LIKE "さいたま%" THEN
        "埼玉県"
    WHEN td LIKE "東京%" THEN
        "東京都"
    WHEN td LIKE "神奈川%" OR td LIKE "横浜%" THEN
        "神奈川県"
    WHEN td LIKE "福岡%" OR td LIKE "博多%" THEN
        "福岡県"
    ELSE "その他"
END
);
SELECT function.unifyTodofuken('北海道'),function.unifyTodofuken('青森'),function.unifyTodofuken('さいたま')

UDFの定義言語

UDFの実装はSQLだけではなくJavaScriptでの定義が可能です。

実行処理の速度としてはSQL文の方が高速なようですが、SQLでは実現できないような複雑な処理はJavaScriptで実装するのが良いでしょう。

SQL型のUDF

CREATE TEMP FUNCTION get_month_date(date DATE) AS (
    DATE(
         EXTRACT(YEAR FROM date)
        ,EXTRACT(MONTH FROM date)
        ,1
    )
);

SELECT get_month_date('2021-11-05')

JavaScript型のUDF

変数を利用したロジックが必要だたり、SQLにはない関数を利用する場合などはJavaScriptを利用したUDFを利用します。
特に複雑な処理を実装する場合に力を発揮するUDFです。

こちらの例では

CREATE TEMPORARY FUNCTION  decode_url(x STRING)
RETURNS STRING LANGUAGE js AS """
  try{
    return decodeURIComponent(x);
  }catch(e){
    return x;
  }
""";

select  decode_url('%E4%BB%95%E4%BA%8B%E3%81%AE%E8%AA%AC%E6%98%8E%E6%9B%B8%E3%80%9C%E3%81%82%E3%81%AA%E3%81%9F%E3%81%AF%E4%BB%8A%E3%81%A9%E3%82%93%E3%81%AA%E3%82%B2%E3%83%BC%E3%83%A0%E3%82%92%E3%81%97%E3%81%A6%E3%81%84%E3%82%8B%E3%81%AE%E3%81%8B%E3%80%9C')

ad

コメント