Snowlfakeでの半構造化データのSelectのやり方まとめ
前置き
こんにちは。株式会社GENDAのデータエンジニアのこみぃです。
Snowflakeはjsonなどの半構造化データを直接insertしておけるデータウェアハウスなので、何も考えずにデータを突っ込んでおいてあとから考える、ということができます。
ただ、半構造化データの抽出にはちょっとだけコツが必要だったので、今回はそのお話でも。
キーが決まっている単純なjsonの場合
一番簡単なのは、単純にキーが決まっているjsonからデータを取り出す場合です。
例えばこんなデータ（わかりにくいですが2行のレコードにjsonが入っています）
+--------------------+
| DATA |
|--------------------|
| { |
| "id": 1, |
| "text": "test 1" |
| } |
| { |
| "id": 2, |
| "text": "test 2" |
| } |
+--------------------+
キーに対してのアクセスは : を使うことでできます。
select data:id as id, data:text as text from sample_table_01;
+----+----------+
| ID | TEXT |
|----+----------|
| 1 | "test 1" |
| 2 | "test 2" |
+----+----------+
簡単ですね！
BigQueryで言うjson_extractはsnowflakeだと : でサクッと書けます
配列を含むjsonの場合
jsonの中に配列が入っている場合、その中身を展開して複数の列に分ける方が良いことが多いです。
例えば以下のようなデータ
+------------------------+
| DATA |
|------------------------|
| { |
| "id": 1, |
| "item": [ |
| { |
| "item_id": 1, |
| "text": "test 1" |
| }, |
| { |
| "item_id": 2, |
| "text": "test 2" |
| }, |
| { |
| "item_id": 3, |
| "text": "test 3" |
| } |
| ] |
| } |
| { |
| "id": 2, |
| "item": [ |
| { |
| "item_id": 4, |
| "text": "test 4" |
| }, |
| { |
| "item_id": 5, |
| "text": "test 5" |
| }, |
| { |
| "item_id": 6, |
| "text": "test 6" |
| } |
| ] |
| } |
+------------------------+
この場合は LATERAL FLATTEN を使います。
select
t1.data as record_data,
t1.data:id as record_id,
f1.value as item_json,
f1.value:item_id as item_id,
f1.value:text as item_text
from
sample_table t1,
lateral flatten(t1.data:item) f1
;
LATERAL FLATTEN は半構造化データの中身を展開してjoinしたいときのために使うすごくすごい関数です。
実際にはjson部分は不要なのでサブクエリにしてくるむことになります。具体的にはこんな感じ
select
record_id,
item_id,
item_text
from
(
select
t1.data as record_data,
t1.data:id as record_id,
f1.value as item_json,
f1.value:item_id as item_id,
f1.value:text as item_text
from
sample_table t1,
lateral flatten(t1.data:item) f1
)
;
実行結果はこんな感じで、しっかりと分かれて抽出できています。
+-----------+---------+-----------+
| RECORD_ID | ITEM_ID | ITEM_TEXT |
|-----------+---------+-----------|
| 1 | 1 | "test 1" |
| 1 | 2 | "test 2" |
| 1 | 3 | "test 3" |
| 2 | 4 | "test 4" |
| 2 | 5 | "test 5" |
| 2 | 6 | "test 6" |
+-----------+---------+-----------+
キーそのものがIDなどを示している場合
jsonの中には、キーがそのまま何かしらのカラムである場合があります。
例えば以下のようなデータ
+----+-------------------------------------+
| ID | DATA |
|----+-------------------------------------|
| 1 | { |
| | "a": { |
| | "column_a": { |
| | "id": 1, |
| | "text": "test text test text" |
| | } |
| | }, |
| | "b": { |
| | "column_a": { |
| | "id": 2, |
| | "text": "test text 2" |
| | } |
| | } |
| | } |
| 2 | { |
| | "c": { |
| | "column_a": { |
| | "id": 3, |
| | "text": "test text test 3" |
| | } |
| | }, |
| | "d": { |
| | "column_a": { |
| | "id": 4, |
| | "text": "test text 4" |
| | } |
| | } |
| | } |
+----+-------------------------------------+
このデータではDATAのカラムの中のjsonの1段階目のキー（"a","b","c","d"）が固有のIDになります。
データを参照する時に t1:data:col_name のようにキーを指定してselectすることができないので、少しテクいやり方が必要です。
具体的にはこういうクエリを書く必要があります。
select
t1.id as record_id,
t1.data as record_data,
f1.value as unit_id,
GET(record_data, unit_id) as unit_data
from
sample_table t1,
lateral flatten(OBJECT_KEYS(t1.data)) f1
;
やっていることとしては
- OBJECT_KEYSでキーを取得
- lateral flattenでキーをもとに半構造化データを展開
- keyをvalueで取り出して列におさめる
- GETでレコードのdataの中身を改めて取得
すごく、すごいややこしいです。。。
こちらもjson部分は抽出結果としては要らないはずなんで、実際にはこんな感じにサブクエリでくるむことになりますね。
select
record_id,
unit_id,
unit_data
from
(
select
t1.id as record_id,
t1.data as record_data,
f1.value as unit_id,
GET(record_data, unit_id) as unit_data
from
sample_table t1,
lateral flatten(OBJECT_KEYS(t1.data)) f1
)
;
クエリの結果はこんな感じで、目的にしている抽出ができてそうなことがわかります。
+-----------+---------+-----------------------------------+
| RECORD_ID | UNIT_ID | UNIT_DATA |
|-----------+---------+-----------------------------------|
| 1 | "a" | { |
| | | "column_a": { |
| | | "id": 1, |
| | | "text": "test text test text" |
| | | } |
| | | } |
| 1 | "b" | { |
| | | "column_a": { |
| | | "id": 2, |
| | | "text": "test text 2" |
| | | } |
| | | } |
| 2 | "c" | { |
| | | "column_a": { |
| | | "id": 3, |
| | | "text": "test text test 3" |
| | | } |
| | | } |
| 2 | "d" | { |
| | | "column_a": { |
| | | "id": 4, |
| | | "text": "test text 4" |
| | | } |
| | | } |
+-----------+---------+-----------------------------------+
本日のまとめ
本日のまとめはこんな感じでしょうか
- Snowflakeには半構造化データを安心して突っ込んでおこう
- 抽出にあたっては以下を駆使していこう！
- :
- LATERAL FLATTEN
- OBJECT_KEYS
- GET
結びの言葉
今回恥ずかしながら自分で調べて数時間かかったので、未来の自分も含めて、同じ作業をする皆様の時間を短縮できるようにまとめておいた次第です。
最後に一つ宣伝を。
私が所属する株式会社GENDAでは一緒に働く仲間をすごく真剣に求めています。
興味がありましたらぜひお気軽にお声おかけください。
本日はこのあたりで。
それじゃあ、バイバイ！
Discussion