この記事について

FirestoreのデータをBigQueryに流し
DataPortalで閲覧したい場合にハマったことについての記事です。

ハマったこと

dataに含まれる指定のkeyに紐づくvalueを取得すること。

CollectionからBigQueryへExportされるテーブルのスキーマは以下になります。
Firestore上のDocumentはdataにjson形式で型はStringで格納されています。

例としてBigQueryへ流す対象のCollectionに含まれるDocumentの内容は以下とします。

このDocument情報はBigQuery上ではdataFieldに保存されます。

{"defense": 250,"name": "スライム","power": 200}

powerだけを取得したい場合Queryを作る場合以下で済むんですが・・・

SELECT JSON_EXTRACT(data, '$.power') FROM table_name

Queryと同じようにDataPortal上でdataからpowerを抽出し新しくFieldを追加しようとするもJSON_EXTRACTはサポートされてない・・・

DataPortalでサポートされている関数の一つであるREGEXP_EXTRACTを使いdataから正規表現で指定Keyである"power"のvalueを取得する。

REGEXP_EXTRACTはBigQueryでもサポートされているのでQueryを書くと以下になります。

SELECT REGEXP_EXTRACT(data,'"power":+([^,]+)') FROM table_name

今回はハマったことに対してサポートしてる関数を使用し正規表現で指定のkeyに紐づくvalueを取得したんですがもっと簡単な方法あるぞ！とご存じの方いらっしゃいましたらぜひご共有ください