SQLのみで目的の処理を行うことが難しい場合に、SQLクエリの中からJavaScriptのコードを呼び出し、その結果を得ることができる。このようなケースはよくあるので、実際に使用しているサンプルコードと共に解説する。
CREATE [OR REPLACE] [TEMP | TEMPORARY] FUNCTION <project>.<dataset>.<function_name>(<param1_name> <param1_type>, ..., <paramN_name> <paramN_type>)
RETURNS <return_type>
LANGUAGE js
OPTIONS (<option_name> <option_value>, ...)
AS """
// JavaScript code here
""";参考：JavaScript UDF -Google Cloud BigQuery
JavaScript UDFの作成方法
各部分の解説
- CREATE [OR REPLACE]: このオプションを使用すると、もし同名の関数が存在した場合にそれを置き換えることができます。
- TEMP | TEMPORARY: このキーワードを使用すると、作成される関数はセッションのライフサイクルに束縛され、セッション終了時に自動的に削除されます。
- <project>.<dataset>.<function_name>: 関数の完全修飾名を指定します。プロジェクトやデータセットを省略すると、現在のデフォルトのプロジェクトやデータセットが使用されます。
- <param1_name> <param1_type>, …, <paramN_name> <paramN_type>: 関数に渡すパラメータとそのデータ型を定義します。
- RETURNS <return_type>: 関数の戻り値の型を指定します。
- LANGUAGE js: この関数がJavaScriptで書かれていることを指定します。
- OPTIONS (<option_name> <option_value>, …): 関数に適用する追加オプションを指定します。例えば、リソース消費の上限を設定することができます。
- AS “””: 関数の本体を三重引用符で囲んだJavaScriptコードとして定義します。
JavaScript UDFの使用例
ここでは当サイトのテーマである分析SQLを用いてレポート作成する一連の工程の中で、一般的なJavaScript UDFの呼び出し方法を元に解説する。UDFとは？の解説はここでは省略するが、一般的にこのようなUDFは同じデータセット内で呼び出せるように永続関数として定義して使用する。また、定義する際は再定義可能にするため、’OR REPLACE FUNCTION’でCREATEすることになる。
URLをデコードする
CREATE OR REPLACE FUNCTION `<project>.<dataset>.URL_Decode`(url STRING) RETURNS STRING LANGUAGE js AS R"""
var encodedURL = url;
var decodedURL = decodeURIComponent(encodedURL);
return decodedURL;
""";このBigQueryのコードは、JavaScriptを使ってカスタム関数を定義し、URLのデコードを行うものです。具体的な動作は次のとおりです。
### 関数の定義
– **関数名**: `URL_Decode`
– **入力パラメータ**: `url`（文字列型）
– **戻り値**: デコードされたURL（文字列型）
– **言語**: JavaScript（`LANGUAGE js`）### 処理内容
1. **変数の設定**:
– `encodedURL` に関数の引数として渡されたエンコードされたURLが格納されます。2. **URLデコード**:
– `decodeURIComponent()` 関数を使用して、エンコードされたURL（`encodedURL`）をデコードします。この関数はJavaScriptにおいてURLのコンポーネント（部分的なURL）をデコードするために用いられ、パーセントエンコーディングされた文字列を通常の文字列に戻します。3. **戻り値**:
– デコードされたURL（`decodedURL`）を返します。### 使用目的
この関数は、BigQueryでURLがエンコードされた状態で格納されている場合、その内容を読みやすい形にデコードするために使用されます。例えば、データ分析を行う際にURLパラメータから特定の情報を抽出する必要があるが、エンコードされていて読めない場合に役立ちます。### 実装の背景
BigQueryは標準的にURLデコードの関数を提供していないため、カスタム関数をJavaScriptで実装しています。BigQueryのUDF（ユーザー定義関数）を使うことで、SQLクエリ内で直接JavaScriptのコードを実行し、複雑な文字列操作や計算を行うことができます。この関数をデータセット内のクエリで使用することで、エンコードされたURLを透過的に扱いやすい形に変換し、分析やレポーティングの精度を高めることが可能になります。
SELECT
`<project>.<dataset>.URL_Decode`('%E6%98%8E%E6%97%A5%E3%81%AE%E5%A4%A9%E6%B0%97%E3%81%AF%E6%99%B4%E3%82%8C');
