BigQueryのユーザー定義関数（UDF）を使ってみる

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

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

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

UDFにはSQLまたはJavaScriptを使用することができます。
どのようにUDFを定義するか詳細については[標準SQLユーザー定義関数](https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions?hl=ja)を参照してください。

# UDFを使ってみる

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

（前準備）
UDFにはJavaScriptが利用できるので、ユーザーエージェントの判定をするためのライブラリである[woothee](https://github.com/woothee)のJavaScript版[woothee-js](https://github.com/woothee/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 ;
```

実行例）
![query_result.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/36602/45f655b6-d1b3-b20d-3d61-13d94ed7578b.png)

## 永続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の生成例）
![ua_query_p2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/36602/0c03de1a-1678-78e8-6eff-fcc08ec75834.png)

永続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の実行例）
![query_result.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/36602/45f655b6-d1b3-b20d-3d61-13d94ed7578b.png)

# UDFに説明を追加する
UDFの使い方などの説明を追加することができます。
BigQueryで生成した永続UDFを選択します。
![description_p0.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/36602/d1d3c859-b62d-96ed-0a8e-0d4568db2635.jpeg)

説明の部分にある鉛筆アイコンを選択します。説明の編集画面が表示されます。説明を追加し「更新」を選択します。
![description_p1.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/36602/4bb04a46-f109-eed2-81aa-765e13e6d5c3.jpeg)

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

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