【BigQuery】UDFを使って多次元のJSON配列の要素数を取得する

 

UDFとは何か？

 

UDFとはCREATE FUNCTION ステートメントを使用して定義、作成、および登録を行うユーザー定義関数のことです。UDFはSQLだけでなくJavascriptとかも書けます、これが何に役に立つのかというとテーブルのデータが整形されておらずJSONでぶち込まれていた場合、UDFを使って簡単に処理できるので、SQLで縦持ちとか横持ちとかにしてガシャガシャやる手間が省けます。

 

 

UDFでJSON配列の長さを取得する

 

例えばこういった感じのJSON配列が文字型でテーブルに格納されていた場合、普通のSQLの関数で文字型として格納されている多次元のJSON配列の要素数を取得することは非常に困難を極めます。

 

テーブル名：regist_data

segment	date	data
A	2019-01-01	[segment:a,data:{{ “Name” : “Joe”, “Age” : 22, “Address” : “Tokyo”, “EMail” : “a1@mail.com” } , { “Name” : “Tom”, “Age” : 25, “Address” : “Osaka”, “EMail” : “a2@mail.com” } , { “Name” : “Mike”, “Age” : 30, “Address” : “Sanfrancisco”, “EMail” : “a3@mail.com” }}]
B	2019-01-02	[segment: b,data:{{ “Name” : “Joe”, “Age” : 20, “Address” : “Hongkong”, “EMail” : “b1@mail.com” } , { “Name” : “Tom”, “Age” : 25, “Address” : “Paris”, “EMail” : “b2@mail.com” } , { “Name” : “Mike”, “Age” : 33, “Address” : “Sanfrancisco”, “EMail” : “b3@mail.com” }}]
C	2019-01-01	[segment:c,data:{{ “Name” : “Joe”, “Age” : 27, “Address” : “Osaka”, “EMail” : “c1@mail.com” } , { “Name” : “Tom”, “Age” : 29, “Address” : “Paris”, “EMail” : “c2@mail.com” } , { “Name” : “Mike”, “Age” : 36, “Address” : “London”, “EMail” : “c3@mail.com” }}]
D	2019-01-01	[segment:d,data:{{ “Name” : “Joe”, “Age” : 20, “Address” : “Tokyo”, “EMail” : “d1@mail.com” } , { “Name” : “Tom”, “Age” : 21, “Address” : “Paris”, “EMail” : “d2@mail.com” } , { “Name” : “Mike”, “Age” : 40, “Address” : “Sanfrancisco”, “EMail” : “d3@mail.com” }}]

 

こういうときにUDFを使うと入れ子になって複雑なJSON配列の要素数を一発で集計することが可能です。UDFの使い方は以下のような感じです。”””で囲まれた部分にSQLなりJavascriptで処理を記述します。

 

 

-- 関数を定義する
CREATE TEMP FUNCTION JsonArrayLength(json_array STRING)
RETURNS INT64 LANGUAGE js AS """
var arr = JSON.parse(json_array);　
return arr.length;　
""";

-- JSON配列の要素数を取得する
SELECT
  user_id,
  created_datetime,
  JsonArrayLength(data) AS data_length
FROM
  regist_data

 

実行結果

segment	date	data	data_length
A	2019-01-01	[segment:a,data:{{ “Name” : “Joe”, “Age” : 22, “Address” : “Tokyo”, “EMail” : “a1@mail.com” } , { “Name” : “Tom”, “Age” : 25, “Address” : “Osaka”, “EMail” : “a2@mail.com” } , { “Name” : “Mike”, “Age” : 30, “Address” : “Sanfrancisco”, “EMail” : “a3@mail.com” }}]	3
B	2019-01-02	[segment: b,data:{{ “Name” : “Joe”, “Age” : 20, “Address” : “Hongkong”, “EMail” : “b1@mail.com” } , { “Name” : “Tom”, “Age” : 25, “Address” : “Paris”, “EMail” : “b2@mail.com” } , { “Name” : “Mike”, “Age” : 33, “Address” : “Sanfrancisco”, “EMail” : “b3@mail.com” }}]	3
C	2019-01-01	[segment:c,data:{{ “Name” : “Joe”, “Age” : 27, “Address” : “Osaka”, “EMail” : “c1@mail.com” } , { “Name” : “Tom”, “Age” : 29, “Address” : “Paris”, “EMail” : “c2@mail.com” } , { “Name” : “Mike”, “Age” : 36, “Address” : “London”, “EMail” : “c3@mail.com” }}]	3
D	2019-01-01	[segment:d,data:{{ “Name” : “Joe”, “Age” : 20, “Address” : “Tokyo”, “EMail” : “d1@mail.com” } , { “Name” : “Tom”, “Age” : 21, “Address” : “Paris”, “EMail” : “d2@mail.com” } , { “Name” : “Mike”, “Age” : 40, “Address” : “Sanfrancisco”, “EMail” : “d3@mail.com” }}]	3

 

 

 

終わり

 

UDFについては自分の会社だと使用に関して賛否両論あるみたいですが、知っておくとむっちゃ便利で集計にさく時間も節約できるので使えるに越したことはないかなと思います。ていうかこれがないとJSON形式のデータとか来たら中身取り出してるだけで日が暮れますよね笑

 

処理速度に関しては遅くなるみたいですが、Bigqueryなので基本そこまで気にしなくていいかなと思います。前職は単にテーブルをJOINでガシャン！してただけだったからBigQueryつーかSQLで集計始めると思ったより奥が深いぞい・・・

 

ちなみに分析用SQLの話だと↓の参考書が非常に役立っているので、UDFとか分析関数とか使うような業務をされている人には非常にオススメです。