BigQuery UDFでJSONのデータを扱う #GoogleCloud

BigQueryで、カラムにJSON文字列で格納されたデータを扱います。

データ例(tab区切り):

id  family
1   {"father":"taro","children":[{"name":"ichiro","age":7},{"name":"jiro","age":6},{"name":"saburo","age":5}]}
2   {"father":"masayuki","children":[{"name":"genzaburo","age":19},{"name":"genjiro","age":17}]}
3   {"father":"Micheal","children":[{"name":"John","age":10},{"name":"Paul","age":9},{"name":"George","age":8}]}

JSON関数

BigQueryのクエリにはJSONを扱う関数が用意されています。
https://cloud.google.com/bigquery/query-reference#jsonfunctions

関数を使うとJSONPathライクな表現でJSON内のデータを抽出できます。

例：fatherを抽出

SELECT id,  JSON_EXTRACT_SCALAR(family, '$.father') as children FROM [test.families]

例：長男の名前を抽出

SELECT id,  JSON_EXTRACT_SCALAR(family, '$.children[1].name') as children FROM [test.families]

UDF

例えば、末っ子の名前一覧を取得したいとします。

JSONPathだと、$.children[(@.length-1)].nameの様に書けるのですが、これはBigQueryだとエラーになります。
リファレンスによると残念ながら"bracket notation is not supported. "とのことです。

そこでUDF(User Defined Functions)使ってみます。
https://cloud.google.com/bigquery/user-defined-functions

UDFを使うと、行の抽出ロジックをjavascriptで書くことが出来ます。

BigQuery UIのクエリ入力エリア右上のタブでUDF入力エリアを表示できます。

UDF:

bigquery.defineFunction(
  'getYoungestChildName', // 関数名
  ['id', 'family'], // 入力カラム名
  [{name: 'id', type: 'INTEGER'},{name: 'youngest_child_name', type: 'STRING'}], // 出力スキーマ定義
  function(row, emit) {
    var family = JSON.parse(row.family);
    var name = family.children.length > 0 ? family.children[family.children.length - 1].name : null;
    emit({id:row.id, youngest_child_name:　name});
  }
);

UDFで定義されたfunctionは1行を受け取って、何らかの処理（変換や抽出）を行って1 or 0行を返します。
引数に渡されるemit関数を呼んで結果行を返します。行を結果に含めない場合はemitを呼ばなければOKです。

Query:

SELECT id,  youngest_child_name FROM (
  getYoungestChildName([test.families])
)

上の例ではgetYoungestChildNameパラメータにテーブルを渡していますが、サブクエリを渡すことも出来ます。

SELECT id,  youngest_child_name FROM (
  getYoungestChildName(select * from [test.families])
)

抽出できました！
＼(^o^)／

応用

この記事の例でもう少し汎用的にやるのなら、末っ子情報をJSONで返して、呼び出し側でJSON関数で扱える様にしてもいいかもしれません。

UDF:

bigquery.defineFunction(
  'getYoungestChild',
  ['id', 'family'],
  [{name: 'id', type: 'INTEGER'},{name: 'youngest_child', type: 'STRING'}],
  function(row, emit) {
    var family = JSON.parse(row.family);
    var child = family.children.length > 0 ? family.children[family.children.length - 1] : null;
    emit({id:row.id, youngest_child:　JSON.stringify(child)});
  }
);

Query:

SELECT 
  id,  
  JSON_EXTRACT_SCALAR(youngest_child, '$.name') as name, 
  JSON_EXTRACT_SCALAR(youngest_child, '$.age') as age, 
FROM (
  getYoungestChild([test.families])
)

感想

UDFはクエリのスカラー関数を自分で定義できるものかな、と勝手に想像していたのですがちょっと違ってました。
とは言え、スカラー関数相当の処理は（少し面倒ですが）実現できるし、他に出来ることも多いので、とても有用だと思いました。

UDFによってMap-ReduceのMap処理に相当するものをjavascriptで記述できて、処理は勝手に大量のGoogleサーバーに分散されて短時間で完了します。
ある程度のMap-ReduceはBigQueryだけで完結させることができそうです。