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.

  1. 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                                                          |
    |                         |                     | }                                                                             |
    +-------------------------+---------------------+-------------------------------------------------------------------------------+
    
  2. 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