BigQueryでUDFを使用する

BigQueryはUDF(ユーザー定義の関数)をサポートしています。
UDFはJavaScriptとSQL式で記述する事が出来ます。

サポートされる UDF 言語

今回はデータ型がSTRINGのカラムにJSON形式のデータを入れているテーブルから、 UDFを使用してJSON形式のデータをパースして取得してみたいと思います。

使用するテーブルとデータは下記の通りです。

テーブル定義
name type
id INTEGER
user_name STRING
profile STRING
テーブル内容
id user_name profile
1 taro {“age”:29, “address”:{\“country\”:81, \“pref\”:\“北海道\“}}
2 jiro {“age”:24, “address”:{\“country\”:81, \“pref\”:\“東京\“}}
取得したい形式
id user_name age country pref
1 taro 29 81 北海道
2 jiro 24 81 東京 
標準SQLの関数でデータを取得する場合

上記のテーブルとデータであれば、UDFを使用せずに標準SQLの関数を 使用して取得する事もできます。

bq query \
  --use_legacy_sql=false \
  "SELECT
     id,
     name,
     age,
     JSON_EXTRACT_SCALAR(address, '$.country') AS country,
     JSON_EXTRACT(address, '$.pref') AS pref
   FROM (
         SELECT
           id,
           name,
           JSON_EXTRACT_SCALAR(profile, '$.age') AS age,
           JSON_EXTRACT(profile, '$.address') AS address
         FROM DATASET.TABLE
UDF内でJavaScriptの標準メソッドJSON.prase()を使用して取得する場合
bq query \
  --use_legacy_sql=false \
  "CREATE TEMPORARY FUNCTION
     parse(x STRING) RETURNS STRUCT<age INT64, address STRUCT<country INT64, pref STRING>> LANGUAGE js AS
     ' var row = JSON.parse(x); return row; ';
   SELECT
     id, name, profile.address.country, profile.address.pref
     FROM (
       SELECT
         id, name, parse(profile) as profile
       FROM DATASET.TABLE 
   )"

UDFの定義部分は下記になります。

  CREATE TEMPORARY FUNCTION
    parse(x STRING) RETURNS STRUCT<age INT64, address STRUCT<country INT64, pref STRING>> LANGUAGE js AS
    ' var row = JSON.parse(x); return row; ';

parseという関数を定義していて、引数で文字列xを渡すと、その文字列に対してJSON.parse()を実行し、 JSON文字列からオブジェクトに変換され、オブジェクトを返しています。 返されるオブジェクトはSTRUCTで指定してある各メンバー毎のデータ型に変換されます。

外部JSライブラリを使用する

UDFは外部JSライブラリを使用する事も出来ます。

標準メソッドであるJSON.parse()を再現しているjson2をincloudして、 json2のJSON.parse()を実行してみたいと思います。 json2はGCS上に配置している想定です。

json2

  CREATE TEMPORARY FUNCTION
    parse(x STRING) RETURNS STRUCT<age INT64, address STRUCT<country INT64, pref STRING>> LANGUAGE js AS
    ' var row = JSON.parse(x); return row; ';
  OPTIONS (
    library="gs://bucket/path/to/json2.js",
  )

標準SQLの関数では吸収出来ない処理がある場合などは、UDFを使ってみるといいと思います。