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を使ってみるといいと思います。

---

株式会社grasys(グラシス)は、技術が好きで一緒に夢中になれる仲間を募集しています。

grasysは、大規模・高負荷・高集積・高密度なシステムを多く扱っているITインフラの会社です。Google Cloud (GCP)、Amazon Web Services (AWS)、Microsoft Azureの最先端技術を活用してクラウドインフラやデータ分析基盤など、ITシステムの重要な基盤を設計・構築し、改善を続けながら運用しています。

お客様の課題解決をしながら技術を広げたい方、攻めのインフラ技術を習得したい方、とことん技術を追求したい方にとって素晴らしい環境が、grasysにはあります。
お気軽にご連絡ください。

株式会社grasys | 採用情報