概要
BigQueryならJSONでネストされたデータであってもロードして、さらに簡単に正規化できる。
例えば、APIで取得したデータがJSON形式でネストされている場合でもひとまずBigQueryのテーブルに読み込み、BigQueryのUNNEST関数を使うことで簡単にネスト解除し正規化することができる。
JSONデータをBigqueryに読み込む
Cloud StorageやGCEインスタンスに置いたJSONファイルをBigQueryに簡単に読み込むことができる。
厳密にはファイル形式はJSON Linesとのこと。
JSON Linesは、JSONを改行で区切って、1つのファイルにまとめたもの。
今回読み込むJSONがこちら。
{ "metrics": [ { "metric_name": "traffic_in", "data": [ { "group": { "store_name": "Tokyo" }, "next_level": [ { "group": { "from": { "gregorian": "2023-01-10" } }, "next_level": [ { "value": 210, "group": { "start": "12:00" } }, { "value": 220, "group": { "start": "13:00" } }, { "value": 230, "group": { "start": "14:00" } } ] } ] }, { "group": { "store_name": "Osaka" }, "next_level": [ { "group": { "from": { "gregorian": "2023-01-10" } }, "next_level": [ { "value": 310, "group": { "start": "12:00" } }, { "value": 320, "group": { "start": "13:00" } }, { "value": 330, "group": { "start": "14:00" } } ] } ] }, { "group": { "store_name": "Fukuoka" }, "next_level": [ { "group": { "from": { "gregorian": "2023-01-10" } }, "next_level": [ { "value": 110, "group": { "start": "12:00" } }, { "value": 120, "group": { "start": "13:00" } }, { "value": 130, "group": { "start": "14:00" } } ] } ] } ] }, { "metric_name": "traffic_out", "data": [ { "group": { "store_name": "Tokyo" }, "next_level": [ { "group": { "from": { "gregorian": "2023-01-10" } }, "next_level": [ { "value": 210, "group": { "start": "12:00" } }, { "value": 220, "group": { "start": "13:00" } }, { "value": 230, "group": { "start": "14:00" } } ] } ] }, { "group": { "store_name": "Osaka" }, "next_level": [ { "group": { "from": { "gregorian": "2023-01-10" } }, "next_level": [ { "value": 310, "group": { "start": "12:00" } }, { "value": 320, "group": { "start": "13:00" } }, { "value": 330, "group": { "start": "14:00" } } ] } ] }, { "group": { "store_name": "Fukuoka" }, "next_level": [ { "group": { "from": { "gregorian": "2023-01-10" } }, "next_level": [ { "value": 110, "group": { "start": "12:00" } }, { "value": 120, "group": { "start": "13:00" } }, { "value": 130, "group": { "start": "14:00" } } ] } ] } ] } ] }
これをJSON Lines形式、つまり改行を取り除いてからBQに読み込む。
読み込むには以下のクエリを実行する。
LOAD DATA OVERWRITE mydataset.mytable FROM FILES ( format = 'JSON', uris = ['gs://bucket/path/file.json']);
すると、ネストされた状態でBigQueryにロードされていることがわかる。
ちなみにSQLクエリだけでなく、bqコマンドやpythonライブラリも用意されている。
Cloud Storage からの JSON データの読み込み | BigQuery | Google Cloud
ネストされたデータを、ネスト解除する
次にネストされたデータを解除したい。
BigQueryではFROM句にカンマ区切りでテーブル名を記述すると、CROSS JOINとなる。
UNNEST関数を使用してCROSS JOINすると、一般的なCROSS JOINではなく、UNNEST関数の対象となるネストされた行に対して、ネストされた行の各値が結合される。
やってみないとイメージがわからないと思うので、実際にやってみることおススメします。
SELECT m.metric_name as metric, n1.group.from.gregorian, n2.group.start, d.group.store_name, n2.value FROM `プロジェクト名.データセット名.テーブル名`, unnest(metrics) as m, unnest(m.data) as d, unnest(d.next_level) as n1, unnest(n1.next_level) as n2 order by m.metric_name, d.group.store_name, n1.group.from.gregorian, n2.group.start
これでネスト解除され、1行に1レコードが記載される。
以上
