ステップ3。データをフラット化する¶
FLATTEN は、 VARIANT、 OBJECT、または ARRAY 列の側面ビューを生成するテーブル関数です。この手順では、この関数を使用して、異なるフラット化レベルを持つ2つのテーブルを作成します。
バリアント列のオブジェクトデータの管理¶
LATERAL JOIN および FLATTEN 関数を使用して、グローバルデータを保持しながら、イベントを個々の JSON オブジェクトに分離できます。FLATTEN 各オブジェクトの行を返し、 LATERAL 修飾子はデータをオブジェクトの外部の情報（この例ではデバイスタイプとバージョン）と結合します。この関数は、フラット化されたオブジェクトの値を含む VALUE 列を出力します。
各イベントのデータをクエリします。
SELECT src:device_type::string , src:version::String , VALUE FROM raw_source , LATERAL FLATTEN( INPUT => SRC:events );
Snowflakeは次の出力を返します。
+-------------------------+---------------------+-------------------------------------------------------------------------------+ | SRC:DEVICE_TYPE::STRING | SRC:VERSION::STRING | VALUE | |-------------------------+---------------------+-------------------------------------------------------------------------------| | server | 2.6 | { | | | | "f": 83, | | | | "rv": "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19", | | | | "t": 1437560931139, | | | | "v": { | | | | "ACHZ": 42869, | | | | "ACV": 709489, | | | | "DCA": 232, | | | | "DCV": 62287, | | | | "ENJR": 2599, | | | | "ERRS": 205, | | | | "MXEC": 487, | | | | "TMPI": 9 | | | | }, | | | | "vd": 54, | | | | "z": 1437644222811 | | | | } | | server | 2.6 | { | | | | "f": 1000083, | | | | "rv": "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22", | | | | "t": 1437036965027, | | | | "v": { | | | | "ACHZ": 6953, | | | | "ACV": 346795, | | | | "DCA": 250, | | | | "DCV": 46066, | | | | "ENJR": 9033, | | | | "ERRS": 615, | | | | "MXEC": 0, | | | | "TMPI": 112 | | | | }, | | | | "vd": 626, | | | | "z": 1437660796958 | | | | } | +-------------------------+---------------------+-------------------------------------------------------------------------------+
CREATE TABLE AS ステートメントを使用して、上記の結果をテーブルに保存します。
CREATE OR REPLACE TABLE flattened_source AS SELECT src:device_type::string AS device_type , src:version::string AS version , VALUE AS src FROM raw_source , LATERAL FLATTEN( INPUT => SRC:events ); SELECT * FROM flattened_source; +-------------+---------+-------------------------------------------------------------------------------+ | DEVICE_TYPE | VERSION | SRC | |-------------+---------+-------------------------------------------------------------------------------| | server | 2.6 | { | | | | "f": 83, | | | | "rv": "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19", | | | | "t": 1437560931139, | | | | "v": { | | | | "ACHZ": 42869, | | | | "ACV": 709489, | | | | "DCA": 232, | | | | "DCV": 62287, | | | | "ENJR": 2599, | | | | "ERRS": 205, | | | | "MXEC": 487, | | | | "TMPI": 9 | | | | }, | | | | "vd": 54, | | | | "z": 1437644222811 | | | | } | | server | 2.6 | { | | | | "f": 1000083, | | | | "rv": "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22", | | | | "t": 1437036965027, | | | | "v": { | | | | "ACHZ": 6953, | | | | "ACV": 346795, | | | | "DCA": 250, | | | | "DCV": 46066, | | | | "ENJR": 9033, | | | | "ERRS": 615, | | | | "MXEC": 0, | | | | "TMPI": 112 | | | | }, | | | | "vd": 626, | | | | "z": 1437660796958 | | | | } | +-------------+---------+-------------------------------------------------------------------------------+
Key-Valueの抽出による列の分離¶
このチュートリアルに示すように、Snowflakeは JSON の直接クエリをサポートしています。JSON 構造を保持する利点の1つは、フォーマットが変更されたときに、大きなテーブルを再作成および再作成する必要がないことです。ただし、オプションで JSON キー値を型付き列にコピーできます。
次のステートメントは、新しいテーブル EVENTS を作成し、 RAW_SOURCE テーブルの各イベントの値を個別の列にコピーします。各値は、ダブルコロン（::）とそれに続くタイプを使用して、値に適したデータ型にキャストされます。キャストを省略すると、列は VARIANT データ型を想定し、任意の値を保持できます。
create or replace table events as select src:device_type::string as device_type , src:version::string as version , value:f::number as f , value:rv::variant as rv , value:t::number as t , value:v.ACHZ::number as achz , value:v.ACV::number as acv , value:v.DCA::number as dca , value:v.DCV::number as dcv , value:v.ENJR::number as enjr , value:v.ERRS::number as errs , value:v.MXEC::number as mxec , value:v.TMPI::number as tmpi , value:vd::number as vd , value:z::number as z from raw_source , lateral flatten ( input => SRC:events );
このステートメントは、EVENTS.SRC：Vキーのネストされたデータをフラット化し、各値に個別の列を追加します。このステートメントは、キー/値のペアごとに行を出力します。次の出力は、新しい EVENTS テーブルの最初の2つのレコードを示しています。
SELECT * FROM EVENTS; +-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+ | DEVICE_TYPE | VERSION | F | RV | T | ACHZ | ACV | DCA | DCV | ENJR | ERRS | MXEC | TMPI | VD | Z | |-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------| | server | 2.6 | 83 | "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19" | 1437560931139 | 42869 | 709489 | 232 | 62287 | 2599 | 205 | 487 | 9 | 54 | 1437644222811 | | server | 2.6 | 1000083 | "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22" | 1437036965027 | 6953 | 346795 | 250 | 46066 | 9033 | 615 | 0 | 112 | 626 | 1437660796958 | +-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+