こんにちは！がく＠ちゅらデータエンジニアです。
はっぴばーすでーとぅみーー、はっぴばーすでーとぅみーー
天命を知る年齢に達してしまいました・・・が、天命？ナニソレオイシイノ？
馬力あふれる50代を生きていきたいと思います！
こちらの12日目の記事になります。
Frosty Friday Week4 HARD JSON
半構造データのJSONですね！Snowflakeでは、JSON形式もサポートしてますし、とても便利な仕組みが用意されており、比較的容易に扱うことが出来ます
※なので、HARDレベルかなぁ・・・と思ったりします（笑
Week4のチャレンジ内容
こちらに書いてある「ヒント」が結構重要なことを言ってるのが分かるのは、解いてる最中でした（笑
環境設定
いつものように実行環境を設定していきましょう
use role sysadmin;
use warehouse gaku_wh; -- 任意のWHにしてくださいね
use database frosty_friday;
create or replace schema week4;
今回もスキーマは、week4 ってのにしています。
ステージの作成
create or replace stage week4_ext_stage
URL='s3://frostyfridaychallenges/challenge_4/';
今回も、凄くシンプルなStage定義です。※業務でやる場合はもっとちゃんと作ったほうがいいとはおもいます。
ls @week4_ext_stage;
listでもlsでもOK
jsonファイルが一つありますね、こちらを取り込んで、処理をしていきます。
select $1,$2 from @week4_ext_stage;
こんな感じの出力が得られます。
これから分かることは
- 中身はjson（拡張子が間違っている場合もあるから念の為中身も調べる）
- 大外が「[]」で括られている
こちらにありますが
「JSON パーサーに、外側の括弧（つまり [ ]）を削除するように指示するブール値。」
を指定しないとうまく取り込めません
create or replace file format week4_json_file_format
type = JSON
STRIP_OUTER_ARRAY = TRUE -- JSON パーサーに、外側の括弧（つまり [ ]）を削除するように指示するブール値。
;
こちらを使ってSELECTします
select
$1::variant as v
from @week4_ext_stage/Spanish_Monarchs.json
(file_format => 'week4_json_file_format')
;
ちなみに、STRIP_OUTER_ARRAYを指定しないと
create or replace file format week4_json_file_format_non_strip
type = JSON
-- STRIP_OUTER_ARRAY = TRUE -- JSON パーサーに、外側の括弧（つまり [ ]）を削除するように指示するブール値。
;
select
$1::variant as v
from @week4_ext_stage/Spanish_Monarchs.json
(file_format => 'week4_json_file_format_non_strip')
;
こんな感じに読み込めて、うまくいきません・・・・
データの取込
create or replace table week4_raw (
v variant
, file_name string
, file_row_number number
);
とテーブルを作り
copy into week4_raw
from (
select
$1::variant as v
, metadata$filename as file_name
, metadata$file_row_number as file_row_number
from
@week4_ext_stage/Spanish_Monarchs.json
(file_format => 'week4_json_file_format')
)
;
データを取り込みました。
次に半構造化データの入れ子を解いて、整形していきます。
利用するのは
- LATERAL FLATTEN
- ROW_NUMBER() OVER
- v:"Era"
- ::string
の4つのテクニックですね
LATERAL FLATTEN
複合値を複数の行にフラット化（展開）します。
最初の取込では、2行です。
そして、トップレベルの要素に、Houses があるので、これをキーに、フラット化しました。
select
value
from
week4_raw
, LATERAL FLATTEN(v:"Houses") as h
;
次は、Monarchesをキーに、フラット化します。
対象は、Housesでフラット化した h
select
h.value as h_value
, m.value as m_value
from
week4_raw
, LATERAL FLATTEN(v:"Houses") as h
, LATERAL FLATTEN(h.value:"Monarchs") as m
;
こんな感じにフラット化されます。
ROW_NUMBER() OVER
ウィンドウ関数（ランク関連）で、ウィンドウパーティション内の各業に一意の番号を返します。
RANK（）だと判断対象のカラムの値が同値の場合は、1,1,3,4,5などと値が発行されるのですが、
ROW_NUMBER()だと同値でも別のランクが振り分けられます。1,2,3,4,5
ROW_NUMBER() OVER (ORDER BY m.value:"Birth"::date) as ID
こちらの意図は、誕生日で古いものから順番に採番します。
半構造化データのクエリに関しては、上記に詳しいです。
コロン記法
VARIANT 列名と任意の第1レベル要素の間にコロン : を挿入します。 <列>:<第1レベル要素>
ドット記法
<列>:<第1レベル要素>.<第2レベル要素>.<第3レベル要素>
という形で階層を追って取り込めます。
最終的な答えはこちら
create or replace view spanish_monarches_view
as
select
ROW_NUMBER() OVER (ORDER BY m.value:"Birth"::date) as ID
, m.index + 1 as INTER_HOUSE_ID
, v:"Era"::string as ERA
, h.value:"House"::string as HOUSE
, m.value:"Name"::string as NAME
, m.value:"Nickname"[0]::string as NICKNAME_1
, m.value:"Nickname"[1]::string as NICKNAME_2
, m.value:"Nickname"[3]::string as NICKNAME_3
, m.value:"Birth"::date as BIRTH
, m.value:"Place of Birth"::string as PLACE_OF_BIRTH
, m.value:"Start of Reign"::date as START_OF_REIGN
, m.value:"Consort\\/Queen Consort"[0]::string as CONSORT_OR_QUEEN_CONSORT_1
, m.value:"Consort\\/Queen Consort"[1]::string as CONSORT_OR_QUEEN_CONSORT_2
, m.value:"Consort\\/Queen Consort"[2]::string as CONSORT_OR_QUEEN_CONSORT_3
, m.value:"End of Reign"::date as END_OF_REIGN
, m.value:"Duration"::string as DURATION
, m.value:"Death"::date as DEATH
, m.value:"Age at Time of Death"::string as AGE_AT_TIME_OF_DEATH_YEARS
, m.value:"Place of Death"::string as PLACE_OF_DEATH
, m.value:"Burial Place"::string as BURIAL_PLACE
from
week4_raw
, LATERAL FLATTEN(v:"Houses") as h
, LATERAL FLATTEN(h.value:"Monarchs") as m
;
select * from spanish_monarches_view;
まとめ
今回はJSONファイルを扱いました。
HARDとありますが、個人的にはIntermediate（中級）かな・・・
半構造化の取り扱いのきほんのきみたいな問題だったかなーとおもいました
こちらで解法を公開しています
Discussion