ステップ3。データをフラット化する¶
FLATTEN は、 VARIANT、 OBJECT、または ARRAY 列の側面ビューを生成するテーブル関数です。このステップでは、この関数を使用して、フラット化のさまざまなレベルを調べます。
バリアント列の配列オブジェクトをフラット化する¶
FLATTEN 関数を使用して、 events 配列内のイベントオブジェクトを個別の行にフラット化できます。関数の出力には、これらの個々のイベントを格納する VALUE 列が含まれます。
その後、 LATERAL 修飾子を使用して、 FLATTEN 関数の出力をオブジェクト外の情報（この例では device_type と version）と結合できます。
各イベントのデータをクエリします。
SELECT src:device_type::string, src:version::String, VALUE FROM raw_source, LATERAL FLATTEN( INPUT => SRC:events );
クエリは次の結果を返します。
+-------------------------+---------------------+-------------------------------------------------------------------------------+ | 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 SELECT ステートメントを使用して、前述のクエリ結果をテーブルに保存します。
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 | | | | } | +-------------+---------+-------------------------------------------------------------------------------+
オブジェクトキーを個別の列にフラット化する¶
前述の例では、 events 配列内のイベントオブジェクトを個別の行にフラット化しました。結果の flattened_source テーブルでは、 VARIANT 型の src 列にイベント構造が保持されていました。
VARIANT 型の src 列にイベントオブジェクトを保持する利点の1つは、イベントの形式が変更されたときに、そのようなテーブルを再作成して再入力する必要がないことです。ただし、次のクエリに示すように、イベントオブジェクト内の個別のキーを異なる型に指定された列にコピーするオプションもあります。
次の CREATE TABLE AS SELECT ステートメントは、イベントオブジェクトのキーが異なる列に格納された events という名前の新しいテーブルを作成します。各値は、ダブルコロン（::）とそれに続く型を使用して、値に適したデータ型にキャストされます。キャストを省略すると、列は 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 |
+-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+