この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
大阪オフィスの玉井です。
下記のウェビナーを聴講したのですが（レポートも書いた）、Snowflakeの半構造化データ用のクエリや関数を試すのに丁度いいリソースが紹介されていました。
この記事では、それを使いつつ、半構造化データ用の関数をご紹介します。
準備
使用するリソース
やっておくこと
上記のGetting Startedを済ませておきます。
使用するデータ
準備を終えると、下記のテーブルが用意されます。
見ればわかる通り、JSONファイルがVARIANT型として、そのまま格納されています。JSONファイルの中身については、上記のリポジトリで確認することができます。
半構造化データ用の関数いろいろ
FLATTEN
Snowflake上の半構造化データの処理において、よく使用する関数です。
複合値を複数の行にフラット化（展開）します。
その名の通り、JSON等の半構造化データを、行列の形に展開する関数です。といっても、文章だけだとわかりづらいため、実際に関数を実行してみます。
例えば、事前に用意したテーブルに対して、下記のクエリを発行してみます。
SELECT
*
FROM
raw_device_data,
LATERAL FLATTEN(INPUT => raw:Views)
;結果は下記の通り。INPUTに指定した部分以下をバラしてくれます。今回指定しているのはARRAYです（後述）。
最初は、クエリ結果を見ても、ピンと来ないと思います。JSONを可視化してくれるツールで、JSONファイル自体と見比べると、わかりやすいです。
例えば、元々のテーブルのRAWの1行目は、下記のような構造になっています（わかりやすいように、最後まで展開していません）。
このJSONは、Viewsという配列が1個あり、そこに各オブジェクトがズラズラと並んでいる構造をしています。先程のクエリはINPUTにraw:Viewsと指定しているため、各配列の要素毎に行となってバラける結果となっています。要素毎にはINDEXがつけられ、VALUEというカラムには、配列の各オブジェクトがそれぞれ入ります。
ちなみに、FLATTENの前に記述しているLATERALとは何でしょうか。これはLATERAL結合といって、raw_device_dataの各行毎に、FLATTEN関数をかけ、その結果を結合しています（だからクエリ結果には、raw_device_dataの3カラムも存在する）。
詳しくは、下記の記事がめちゃくちゃわかりやすいので、一読してみてください。
OBJECT_KEYS
FLATTENを使って、各オブジェクト毎に行を分けることはできましたが、各オブジェクトは、まだそれぞれが半構造な形を保っており、このまま分析に使用するのは難しいです。
オブジェクトの値を取る方法は色々ありますが、今回はOBJECT_KEYSを紹介します。
入力オブジェクトのキーのリストを含む配列を返します。
早速、下記のクエリを実行してみます。
SELECT
filename,
"FLATTENしたやつ".INDEX AS "INDEX",
"FLATTENしたやつ".VALUE AS "オブジェクト",
OBJECT_KEYS("オブジェクト") AS "オブジェクトのKey",
"オブジェクトのKey"[0]::string AS "純粋な値"
FROM
raw_device_data,
LATERAL FLATTEN(INPUT => raw:Views) "FLATTENしたやつ"
;結果は下記の通り。「純粋な値」カラムに、最終的にバラしきった値が入っています。
OBJECT_KEYSは、その名の通り、指定したオブジェクトのキーを取得する関数です。FLATTENでバラした各オブジェクトを、OBJECT_KEYSに指定することで、バラした各オブジェクトのキーを取得することができます。ドキュメントに書いてますが、返り値は配列なので、[0]::stringというような指定をすれば、余計な記号（カギカッコなど）を除去した、純粋な値（今回はstringを指定）を取り出すことができます。
GET
各オブジェクトのキーが取得できたら、今度はオブジェクトの値もとりたいですよね。ということで、GET関数を使ってみます。
オブジェクトまたは配列から値を抽出します。
下記のクエリを実行してみます。
SELECT
filename,
"FLATTENしたやつ".INDEX AS "INDEX",
"FLATTENしたやつ".VALUE AS "オブジェクト",
OBJECT_KEYS("オブジェクト") AS "オブジェクトのKey",
"オブジェクトのKey"[0]::string AS "純粋な値",
GET("オブジェクト", "純粋な値") AS "GETしたオブジェクト",
"GETしたオブジェクト":EnterView::string AS enter_view,
"GETしたオブジェクト":ExitView::string AS exit_view
FROM
raw_device_data,
LATERAL FLATTEN(INPUT => raw:Views) "FLATTENしたやつ"
;
GET関数はオブジェクトから値を取り出すことができますが、引数としてフィールドを指定します。今回はオブジェクトのキー（OBJECT_KEYSで取り出した直後のものではなく、stringに変換したもの）を指定しているので、そのキー以下のオブジェクトを取り出すことができました。
ここまでバラすことができれば、後は"GETしたオブジェクト":EnterView::stringという指定の仕方で、それぞれの値を（SQLとして処理できる形で）取り出すことができます。
ARRAY_AGG
ARRAYにピボットされた入力値を返します。
これも、先に結果を見たほうがわかりやすいです。
WITH UI_VIEWS AS(
SELECT
filename,
"FLATTENしたやつ".INDEX AS "INDEX",
"FLATTENしたやつ".VALUE AS "オブジェクト",
OBJECT_KEYS("オブジェクト") AS "オブジェクトのKey",
"オブジェクトのKey"[0]::string AS "純粋な値",
GET("オブジェクト", "純粋な値") AS "GETしたオブジェクト",
"GETしたオブジェクト":EnterView::string AS enter_view,
"GETしたオブジェクト":ExitView::string AS exit_view
FROM
raw_device_data,
LATERAL FLATTEN(INPUT => raw:Views) "FLATTENしたやつ"
)
SELECT
filename,
ARRAY_AGG("純粋な値") AS "キーのリスト"
FROM
UI_VIEWS
GROUP BY
filename
;
今までは半構造な形のデータをバラすのが主目的でしたが、この関数は逆のイメージを想像するとわかりやすいかもしれません。OBJECT_KEYSを使って取り出したオブジェクトのキーを、ARRAY_AGGに引数として渡すと、そのキー（カラム）の値を全て取得して、配列にピボットして返してくれます。
シンプルな配列にできるので、下記のように、その配列から、さらに取り出したい値を狙い撃ちして取り出すこともできます。
-- withは省略
SELECT
filename,
ARRAY_AGG("純粋な値") AS "キーのリスト",
"キーのリスト"[4]::string
FROM
UI_VIEWS
GROUP BY
filename
;
OBJECT_AGG
グループごとに1つの OBJECT を返します。
例のごとく、まずは実例をば。
-- withは省略
SELECT
filename,
OBJECT_AGG(
"INDEX"::string,
"純粋な値"::variant
) AS "IDXとキー"
FROM
UI_VIEWS
GROUP BY
filename
;
これは関数名からイメージしやすいと思います。キーと値を引数に指定して、オブジェクト（ややこしいですが、カラムとしては、OBJECTという名のVARIANT型になります）として返してくれます。
IS_OBJECT
VARIANT 引数に OBJECT 値が含まれる場合、 TRUE を返します。
これはもうドキュメントの説明そのままで理解できると思います。
使いどころとしては、例えばFLATTENした時、データの構造によっては、バラせたところとバラし切れないところが混在する場合があります。
こういう時、「バラし切れなかった部分だけ別途処理かけたい」と思うことになりますが、この関数を使用することで（IFFとかCASEあたりと組み合わせたり）、実際に実現することができます。「バラし来れなかった部分=まだOBJECT」ということを利用するわけですね。
おわりに
「分析したいデータはJSONファイルなんです…」と言われても、「とりあえずSnowflakeに入れといて！」って感じで、SQLで分析できる（形に処理できる）のは、DWHとして非常に強力だと思います。
半構造化データ用の関数は他にもまだまだあるので、Snowflakeを導入している方は、是非いろいろと触ってみましょう。
15
