BigQuery にて 遂に永続UDFサポート（ベータ版）
投稿者：常田
ようやくBigQueryではユーザ定義関数が永続化がサポートされます。
多くの独自の関数が事前に用意されているため利用の頻度は少ないかもしれませんがデータ加工が必要な場合などにSQL関数では手間になる処理や出来ない処理を独自の関数定義を行うことで対応することが出来ます。
これまでもユーザ定義関数( User Defined Function : UDF )を利用することが出来ましたが、一時的に作成する Temp Function でした。これは実行するSQLと同時に送付する必要があり少し利用に際しては手間な場面が多くありました。
利用方法
BigQuery UIから普通に利用することが出来ます
CREATE OR REPLACE FUNCTION `nic-tranning-enablement.car_data.length`(a STRING) RETURNS STRING LANGUAGE js AS """
return a.length;;
""";
作成が出来るとデータセットの列にアイコンが表示されていることが確認できました。
またUIとしてもいくつか永続UDFのためのものが追加されています。作成後にUI上で編集が出来るの便利で使いやすそうです。
JSON型のパース
参考記事にも紹介されていますが、JSON型の処理をパースしてBigQuery構造体に変換することも可能です。これらはUDFを利用すると非常に簡単に行うことが出来ます。
CREATE OR REPLACE FUNCTION `nic-tranning-enablement.car_data.ParseJsonAsStruct`(json_str STRING) RETURNS STRUCT<type STRING, coordinates ARRAY<STRING>> LANGUAGE js AS """
var obj = JSON.parse(json_str);
return obj;
""";
これは次のようなデータが入っているカラムに対して処理を行うことが出来ます。
{"type":"Polygon","coordinates":[[[140.3422452007195,29.7935644381794],[140.3414870097204,29.7935644381757],[140.3410480570383,29.79402040176603],[140.3416399780816,29.79460333953882],[140.3426442486108,29.79453985143566],[140.3426708518048,29.79403771680564],[140.3422452007195,29.7935644381794]]]}
速度
想像していたとおりですが事前に登録してる永続UDFを利用すると速度的にも有利になりそうです。まだベータ版なのでパフォーマンスについては正しくないかもしれませんが永続UDFの場合には処理がかなり早いです（またSQLに対してもキャッシュが有効になるなど利点が多そうです。
SELECT length(city_name), city_name FROM `nic-tranning-enablement.car_data.land_tokyo_min` LIMIT 1000
- クエリ完了（経過時間: 0.4 秒、処理されたバイト数: 139.5 KB）
- 連続して実行した際には キャッシュが有効 する
CREATE TEMP FUNCTION tmp_length(a STRING) RETURNS STRING LANGUAGE js AS """
return a.length;;
""";
SELECT tmp_length(city_name), city_name FROM `nic-tranning-enablement.car_data.land_tokyo_min` LIMIT 1000
- クエリ完了（経過時間: 1.7 秒、処理されたバイト数: 139.5 KB）
- 連続して実行された際にもキャシュされず常に実行される
まとめ
ビジネスロジックを持つアプリケーションの場合従来ながらにUDFを活用しているケースでBigQueryでは使い勝手面で諦めていたケースなどはこれでだいぶ実用的になりそうな気がします。特に、アプリケーションなどがODBC経由で接続している場合にこれまでの一時ユーザ定義関数は利用できなかったために諦めていたこともあるかと思います。業務ではよく日付処理などがUDFで使われますがそういった場合でも共有化しておくことで再利用可能な部品としても利用できそうですね。
