GMOアドマーケティングのT.Oです。
BigQueryでユーザー定義関数を作成し、利用する手順についてご紹介します。

BigQueryでのユーザー定義関数の概要

BigQueryのユーザー定義関数（以下、UDFと略します）には一時的UDFと永続的UDFの2種類があります。
一時的UDFの場合、BigQueryのクエリを実行する時にしか参照できず、他のBigQueryの利用者とUDFを共有することはできません。
一方、永続的UDFとする場合は、他のBigQueryの利用者と共有することができます。

UDFにはSQLまたはJavaScriptを使用することができます。
どのようにUDFを定義するか詳細については標準SQLユーザー定義関数を参照してください。

UDFを使ってみる

例としてユーザーエージェントの文字列からOSやバージョンを判定するためのUDFを作成します。

（前準備）
UDFにはJavaScriptが利用できるので、ユーザーエージェントの判定をするためのライブラリであるwootheeのJavaScript版woothee-jsを使用することにします。
woothee-jsをダウンロードし、release以下に格納されているwoothee.jsをGoogle Cloud Storageのバケットにアップします。

前準備が完了すると、バケットにアップしたwoothee.jsを参照するUDFを作成することができます。

一時UDFの実装例

"CREATE TEMPORARY FUNCTION"を使い、一時UDFを作成、動作確認用SQLで一時UDFを呼びます。

CREATE TEMPORARY FUNCTION ua_classifier(user_agent STRING)
# 返り値として構造体を利用できます
RETURNS STRUCT<category STRING, name STRING, os STRING, version STRING, vendor STRING, os_version STRING> 
LANGUAGE js AS """
  # woothee.jsに定義された関数を呼び出し
  obj = woothee.parse(user_agent); 
  return obj;
"""
# OPTIONSで外部ファイルとしてCloud StroageにアップしたJavaScriptを指定できます
OPTIONS (
  # hogehoge部分にはwoothee.jsをアップしたCloud Storageのバケット名を指定します
  library="gs://{hogehoge}/woothee.js"
);
# 動作確認用SQLで一時UDFを呼ぶ
SELECT ua_classifier("Mozilla/5.0 (iPhone; CPU iPhone OS 14_0 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.0 Mobile/15E148 Safari/604.1") AS ua ;

実行例）

永続UDFの実装例

"CREATE OR REPLACE FUNCTION"を使い、永続UDFを作成します。

# bqhoge部分にはBigQueryのリソースのデータセット名を指定します。このデータセット以下に永続UDFが生成されます
CREATE OR REPLACE FUNCTION `{bqhoge}.ua_classifier`(user_agent STRING) 
# 返り値として構造体を利用できます
RETURNS STRUCT<category STRING, name STRING, os STRING, version STRING, vendor STRING, os_version STRING> 
LANGUAGE js AS """
  # woothee.jsに定義された関数を呼び出し
  obj = woothee.parse(user_agent); 
  return obj;
"""
# OPTIONSで外部ファイルとしてCloud StroageにアップしたJavaScriptを指定できます
OPTIONS (
  # hogehoge部分にはwoothee.jsをアップしたCloud Storageのバケット名を指定します
  library="gs://{hogehoge}/woothee.js"
);

上記の実装例をBigQueryで実行すると以下のように該当のデータセット名以下にua_classifierという名称で永続UDFが生成されます。
この状態になっていれば永続UDFを他のユーザーと共有することが可能です。

永続UDFの生成例）

永続UDFの確認用SQL例）

SELECT `{bqhoge}.ua_classifier`(“Mozilla/5.0 (iPhone; CPU iPhone OS 14_0 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.0 Mobile/15E148 Safari/604.1”) AS ua ;

永続UDFの実行例）

UDFに説明を追加する

UDFの使い方などの説明を追加することができます。
BigQueryで生成した永続UDFを選択します。

説明の部分にある鉛筆アイコンを選択します。説明の編集画面が表示されます。説明を追加し「更新」を選択します。

まとめ

今回はJavaScriptでBigQueryのUDFを作成し、利用する手順についてご紹介しました。
UDFを利用することで標準のSQL関数だけでは実現が難しい機能を実装できます。

明日は、yoshishinさんによる「新型MacBookAir M1チップ(Apple Silicon)でRails開発環境を構築する」です。
引き続き、GMOアドマーケティング Advent Calendar 2020 をお楽しみください！