1.JSONとは（そもそも論）
「 J ava S cript O bject N otation」の略称
JavaScriptはプログラミング言語の1つで、Object Notationは人間が容易に読み書きしながらデータを簡単に扱えるようにフォーマットとし て構造化した記述方法。
JSONはテキストをベースにした、軽量なデータ交換をおこなうためのフォーマット。(JavaScriptの為に作られたので名前に入っているが、のちに他言語でも広く使われるようになった。)
WebAPIなど、値をサービス間で受け渡しする開発をする際は必ずと言っていいほど目にする。
そのため、S3になんとなく出力されたログファイルだったりが溜め込まれている現場においては、ELTの時代になって、よりSnowflakeでJSONを扱う方法について詳しく理解しておかなければならないと思い、
この記事を作成しております。
JSONの説明をした経緯
近年、分析開発の進化が進むにつれ、エンジニアの最初のスタートが分析側によっている方が多いことから
SWEの知識がないエンジニアが増えてきており、ソフトウェア開発をする上でWebAPIなどの連携で必ず目にする
JSONについて、理解ができていない方向けについて軽くどんなものかをお話ししました。
2.Hands-On形式で学ぶJSONデータの扱い方
まずは今回使用する各種DBやschema等を作成しておきます。
--Set context
create database films_db;
create schema films_db.films_schema;
USE ROLE SYSADMIN;
USE DATABASE FILMS_DB;
USE SCHEMA FILMS_SCHEMA;
create stage films_stage;
今回では、内部ステージにあるJSONをテーブルにINSERTするので
INSERTする際に必要なファイルフォーマットと
テーブルを作成していきます。
ファイルフォーマットが長く記載していますが、これは全てデフォルト値です。
JSONを扱う上で、オプションの理解は必要不可欠なので記載しております。
他のファイルフォーマットについてのオプションはドキュメントを参照ください
-- テーブル作成
CREATE OR REPLACE TABLE FILMS_ELT (
JSON_VARIANT VARIANT
);
-- JSONファイルフォーマット
CREATE OR REPLACE FILE FORMAT JSON_FILE_FORMAT
TYPE='JSON',
FILE_EXTENSION=NULL,
DATE_FORMAT='AUTO',
TIME_FORMAT='AUTO',
TIMESTAMP_FORMAT='AUTO',
BINARY_FORMAT='HEX',
TRIM_SPACE=FALSE,
NULL_IF='',
COMPRESSION='AUTO',
ENABLE_OCTAL=FALSE,
ALLOW_DUPLICATE=FALSE,
STRIP_OUTER_ARRAY=FALSE,
-- STRIP_OUTER_ARRAY=TRUE,
STRIP_NULL_VALUES=FALSE,
IGNORE_UTF8_ERRORS=FALSE,
REPLACE_INVALID_CHARACTERS=FALSE,
SKIP_BYTE_ORDER_MARK=TRUE;
-- insert
COPY INTO FILMS_ELT
FROM @FILMS_STAGE/films.json
FILE_FORMAT = JSON_FILE_FORMAT;
ここでファイルフォーマットのオプションで大事なものを抜粋して今回は説明します。
STRIP_OUTER_ARRAY
JSONデータをINSERTする際に、最外の配列を削除して、その中身を個別の行として扱うことができます。
例えば、以下のようなデータがあったとします。
これは、再外部の「 ［ ］ 」が一つの塊として表現されているので、もし、これをFALSEにしてしまうと、１レコードにまとめてJSONデータが格納されるのことになります。
[{"id":"pj38ntdhvn","title":"The Lord of the Rings: The Fellowship of the Ring","release_date":"2001-12-01","actors":["Elijah Wood", "Viggo Mortensen", "Sean Astin", "Ian McKellen"],"ratings":{"imdb_rating":8.9,"metacritic_rating_percentage":92}},
{"id":"lgzy6qo9oq","title":"There Will Be Blood","release_date":"2008-02-15","actors":["Daniel Day-Lewis","Paul Dano"],"ratings":{"imdb_rating":8.2,"metacritic_rating_percentage":93}},
{"id":"ix4mibgs8c","title":"Moonlight","release_date":"2016-09-02","actors":["Mahershala Ali","Trevante Rhodes","Ashton Sanders"],"ratings":{"imdb_rating":7.4,"metacritic_rating_percentage":99}},
{"id":"mz3ot1ljif","title":"Forest Gump","release_date":"1994-10-07","actors":["Tom Hanks","Robin Wright","Sally Field"],"ratings":{"imdb_rating":8.8,"metacritic_rating_percentage":82}},
{"id":"8m8ing5hbn","title":"Seven Samurai","release_date":"1954-04-26", "actors":["Toshiro Mifune","Takashi Shimura","Seiji Miyaguchi"],"ratings":{"imdb_rating":8.7,"metacritic_rating_percentage":98}}]
実際にそのままのファイルフォーマットでinsertしてみます。
COPY INTO FILMS_ELT
FROM @FILMS_STAGE/films.json
FILE_FORMAT = JSON_FILE_FORMAT;
するとこのように１レコードにまとまっていることがわかります。
次に、STRIP_OUTER_ARRAYをTRUEにしてみます。
一度insertしたテーブルに対しては、TRUNCATEしておきましょう。
すると再外部の要素が削除され、５レコードにinsertされていることがわかります。
DMLの実施
上で、レコードを分割にできても実際に、各項目がどのような
値が入っているかわかりづらい。
そのため、以下のように記述する
ちなみに、DATEについては初期の段階では文字列として扱われてしまうため、以下のように型変換する必要がある。
SELECT
json_variant:id as id,
json_variant:title as title,
json_variant:release_date AS release_date,
-- dateの型を変更する方法２
json_variant:release_date::date AS release_date_dd_cast,
-- dateの型を変更する方法２
to_date(json_variant:release_date) AS release_date_func_cast,
json_variant:actors AS actors,
-- 入れ子の値を取得する方法１
json_variant:actors[0] as first_actor,
json_variant:ratings AS ratings,
-- 入れ子の値を取得する方法２
json_variant:ratings.imdb_rating AS IMDB_rating
FROM FILMS_ELT
WHERE release_date >= date('2000-01-01');
出力結果
入れ子の値をさらに分割する
以下のようにそれぞれの入れ子の値をさらにレコードで分割させたい場合も存在すると思われる。
その際は、FROM句にFLATTEN関数を用いることで、さらに入れ子の値をレコードごとに分割させることができる。
SELECT
json_variant:title,
json_variant:release_date::date,
F.json_variant:ratings,
L.value
FROM FILMS_ELT F,
LATERAL FLATTEN(INPUT => F.json_variant:ratings) L
;
実行結果
3.まとめ
いかがだったでしょうか。SQLでこんなにも簡単にJSONのクレンジングを行うことができます。
近年はコンピューターの性能が向上し、比較的安価にメモリを使うことができるようになってから
ETL→ELTの流れがきております。
その恩恵もあり、DWHでの処理が多くなっているので、このような半構造化データをDWHでも扱えるようにしてきましょう。