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 wird die Funktion verwendet, um zwei Tabellen mit unterschiedlichen Graden der Vereinfachung zu erstellen.
Verwalten von Objektdaten in einer Variant-Spalte¶
Mit den Funktionen LATERAL JOIN und FLATTEN können Sie Ereignisse unter Beibehaltung der globalen Daten in einzelne JSON-Objekte unterteilen. FLATTEN gibt eine Zeile für jedes Objekt zurück, wobei der LATERAL-Modifikator die Daten mit allen Informationen außerhalb des Objekts verknüpft – in diesem Beispiel mit Gerätetyp und Version. Die Funktion gibt eine VALUE-Spalte aus, die den Wert des vereinfachten Objekts enthält.
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 );
Snowflake gibt die folgende Ausgabe 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 AS-Anweisung, um die obigen Ergebnisse 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 ); 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 | | | | } | +-------------+---------+-------------------------------------------------------------------------------+
Extrahieren von Schlüsselwerten zur Trennung von Spalten¶
Wie in diesem Tutorial gezeigt unterstützt Snowflake die direkte Abfrage von JSON. Ein Vorteil der Beibehaltung der JSON-Struktur ist, dass Sie bei Formatänderungen große Tabellen nicht neu erstellt und gefüllt werden müssen. Sie können jedoch optional JSON-Schlüsselwerte in typisierte Spalten kopieren.
Mit der folgenden Anweisung wird eine neue Tabelle EVENTS erstellt, und die Werte jedes Ereignisses werden in der RAW_SOURCE-Tabelle in separate Spalten kopiert. 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