BigQueryでのJSONロード
概要
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レコードが記載される。
以上