Schritt 3: Daten vereinfachen¶
FLATTEN ist eine Tabellenfunktion, mit der die laterale Ansicht einer VARIANT-, OBJECT- oder ARRAY-Spalte erstellt werden kann. In diesem Schritt verwenden Sie diese Funktion, um verschiedene Ebenen der Vereinfachung zu untersuchen.
Array-Objekte in einer Variant-Spalte vereinfachen¶
Mit der Funktion FLATTEN können Sie die Ereignisobjekte im events-Array in separate Zeilen vereinfachen. Die Funktionsausgabe enthält eine VALUE-Spalte, in der die einzelnen Ereignisse gespeichert sind.
Sie können dann den LATERAL-Modifikator verwenden, um die Ausgabe der Funktion FLATTEN mit beliebigen Informationen außerhalb des Objekts zu verknüpfen – in diesem Beispiel mit device_type und version.
Fragen Sie die Daten für jedes Ereignis ab:
SELECT src:device_type::string, src:version::String, VALUE FROM raw_source, LATERAL FLATTEN( INPUT => SRC:events );
Die Abfrage gibt das folgende Ergebnis zurück:
+-------------------------+---------------------+-------------------------------------------------------------------------------+ | 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 | | | | } | +-------------------------+---------------------+-------------------------------------------------------------------------------+
Verwenden Sie eine CREATE TABLE ASSELECT-Anweisung, um die obigen Abfrageergebnisse in einer Tabelle zu speichern:
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 );
Führen Sie eine Abfrage auf der Ergebnistabelle aus.
SELECT * FROM flattened_source;
Die Abfrage gibt das folgende Ergebnis zurück:
+-------------+---------+-------------------------------------------------------------------------------+ | 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 | | | | } | +-------------+---------+-------------------------------------------------------------------------------+
Objektschlüssel in separaten Spalten vereinfachen¶
Im vorherigen Beispiel haben Sie die Ereignisobjekte im events-Array in separate Zeilen vereinfacht. Die resultierende Tabelle flattened_source behielt die Ereignisstruktur in der src-Spalte vom Typ VARIANT bei.
Ein Vorteil der Beibehaltung der Ereignisobjekte in der src-Spalte vom Typ VARIANT besteht darin, dass Sie bei einer Änderung des Ereignisformats solche Tabellen nicht neu erstellen und neu füllen müssen. Sie haben aber auch die Option, einzelne Schlüssel im Ereignisobjekt in Spalten mit unterschiedlichen Typen zu kopieren, wie in der folgenden Abfrage gezeigt.
Die folgende Anweisung CREATE TABLE AS SELECT erstellt eine neue Tabelle namens events, in der die Ereignisobjektschlüssel in separaten Spalten gespeichert sind. Jeder Wert wird unter Verwendung eines doppelten Doppelpunktes (::) gefolgt vom Typ in einen für den Wert geeigneten Datentyp umgewandelt. Wenn Sie die Umwandlung weglassen, nimmt die Spalte den Datentyp VARIANT an, der jeden beliebigen Wert enthalten kann:
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 );
Die Anweisung vereinfacht die verschachtelten Daten im Schlüssel EVENTS.SRC:V und fügt für jeden Wert eine separate Spalte hinzu. Die Anweisung gibt für jedes Schlüssel/Wert-Paar eine Zeile aus. Die folgende Ausgabe zeigt die ersten beiden Datensätze in der neuen events-Tabelle:
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 |
+-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
Nächstes Thema: Schritt 4: Daten aktualisieren