BigQueryのユーザー定義関数(UDF)を使ってみる
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の確認用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の使い方などの説明を追加することができます。
BigQueryで生成した永続UDFを選択します。
説明の部分にある鉛筆アイコンを選択します。説明の編集画面が表示されます。説明を追加し「更新」を選択します。
まとめ
今回はJavaScriptでBigQueryのUDFを作成し、利用する手順についてご紹介しました。
UDFを利用することで標準のSQL関数だけでは実現が難しい機能を実装できます。
明日は、yoshishinさんによる「新型MacBookAir M1チップ(Apple Silicon)でRails開発環境を構築する」です。
引き続き、GMOアドマーケティング Advent Calendar 2020 をお楽しみください!
Author And Source
この問題について(BigQueryのユーザー定義関数(UDF)を使ってみる), 我々は、より多くの情報をここで見つけました https://qiita.com/tfactory/items/c879e1bf18ede3473ed7著者帰属:元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .