Schritt 4: Daten aktualisieren

.

Schrittliste

Bisher haben Sie in diesem Tutorial einen Batch von JSON-Ereignisdaten in eine RAW_SOURCE-Tabelle geladen, abgefragt und eine EVENTS-Tabelle erstellt, die aus Ereignisschlüsselwerten besteht. In dem Szenario, das wir zuvor in diesem Tutorial beschrieben haben, fließen JSON-Daten aus mehreren Quellen über einen Webendpunkt in Ihren S3-Bucket. Sie können ein Skript schreiben, um neue Datendateien in die RAW_SOURCE-Tabelle zu schreiben. Wie lassen sich dann nur neue Ereignisdaten in die EVENTS-Tabelle einfügen?

Es gibt zahlreiche Möglichkeiten, um Ihre JSON-Daten in der RAW_SOURCE-Tabelle von den vereinfachten Daten in der EVENTS-Tabelle zu unterscheiden. In diesem Abschnitt werden zwei Optionen vorgeschlagen:

Vergleich von Primärschlüsseldaten

  1. Untersuchen Sie Ihre JSON-Daten auf alle Werte, die naturgemäß eindeutig sind und sich gut für einen Primärschlüssel eignen. Im aktuellen Beispiel wird angenommen, dass die Kombination von src:device_type und value:rv einen geeigneten zusammengesetzten Primärschlüssel ergeben würde. Diese beiden JSON-Schlüssel entsprechen den Spalten DEVICE_TYPE und RV der Tabelle EVENTS.

    Bemerkung

    Snowflake erzwingt keine Einschränkung (Constraint) auf einen Primärschlüssel. Die Einschränkung fungiert eher wie eine Metainformation zur Identifizierung des natürlichen Schlüssels in Information Schema.

  2. Fügen Sie die Primärschlüsseleinschränkung zur Tabelle EVENTS hinzu:

    ALTER TABLE events ADD CONSTRAINT pk_DeviceType PRIMARY KEY (device_type, rv);

  3. Fügen Sie in die RAW_SOURCE-Tabelle einen neuen JSON-Ereignisdatensatz ein:

    insert into raw_source
  select
  PARSE_JSON ('{
    "device_type": "cell_phone",
    "events": [
      {
        "f": 79,
        "rv": "786954.67,492.68,3577.48,40.11,343.00,345.8,0.22,8765.22",
        "t": 5769784730576,
        "v": {
          "ACHZ": 75846,
          "ACV": 098355,
          "DCA": 789,
          "DCV": 62287,
          "ENJR": 2234,
          "ERRS": 578,
          "MXEC": 999,
          "TMPI": 9
        },
        "vd": 54,
        "z": 1437644222811
      }
    ],
    "version": 3.2
  }');

  4. Fügen Sie den neuen Datensatz der Tabelle RAW_SOURCE auf Basis eines Vergleichs der Primärschlüsselwerte in die Tabelle EVENTS ein:

    insert into events
select
      src:device_type::string
    , src:version::string
    , value:f::number
    , value:rv::variant
    , value:t::number
    , value:v.ACHZ::number
    , value:v.ACV::number
    , value:v.DCA::number
    , value:v.DCV::number
    , value:v.ENJR::number
    , value:v.ERRS::number
    , value:v.MXEC::number
    , value:v.TMPI::number
    , value:vd::number
    , value:z::number
    from
      raw_source
    , lateral flatten( input => src:events )
    where not exists
    (select 'x'
     from events
     where events.device_type = src:device_type
     and events.rv = value:rv);

    Durch Abfragen der EVENTS-Tabelle wird die hinzugefügte Zeile angezeigt:

    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 |
| cell_phone  | 3.2     |      79 | "786954.67,492.68,3577.48,40.11,343.00,345.8,0.22,8765.22"           | 5769784730576 | 75846 |  98355 | 789 | 62287 | 2234 |  578 |  999 |    9 |  54 | 1437644222811 |
+-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+

Vergleich aller Schlüssel/Spalten

Wenn Ihre JSON-Daten keine Schlüsselwerte enthalten, die natürliche Primärschlüsselkandidaten sind, können Sie auch alle sich wiederholenden JSON-Schlüssel in der RAW_SOURCE-Tabelle mit den entsprechenden Spaltenwerten in der EVENTS-Tabelle vergleichen.

Es sind keine Änderungen an Ihrer bestehenden EVENTS-Tabelle erforderlich.

  1. Fügen Sie in die RAW_SOURCE-Tabelle einen neuen JSON-Ereignisdatensatz ein:

    insert into raw_source
  select
  parse_json ('{
    "device_type": "web_browser",
    "events": [
      {
        "f": 79,
        "rv": "122375.99,744.89,386.99,12.45,78.08,43.7,9.22,8765.43",
        "t": 5769784730576,
        "v": {
          "ACHZ": 768436,
          "ACV": 9475,
          "DCA": 94835,
          "DCV": 88845,
          "ENJR": 8754,
          "ERRS": 567,
          "MXEC": 823,
          "TMPI": 0
        },
        "vd": 55,
        "z": 8745598047355
      }
    ],
    "version": 8.7
  }');

  2. Fügen Sie den neuen Datensatz der Tabelle RAW_SOURCE auf Basis eines Vergleichs aller sich wiederholenden Schlüsselwerte in die Tabelle EVENTS ein:

    insert into events
select
      src:device_type::string
    , src:version::string
    , value:f::number
    , value:rv::variant
    , value:t::number
    , value:v.ACHZ::number
    , value:v.ACV::number
    , value:v.DCA::number
    , value:v.DCV::number
    , value:v.ENJR::number
    , value:v.ERRS::number
    , value:v.MXEC::number
    , value:v.TMPI::number
    , value:vd::number
    , value:z::number
    from
      raw_source
    , lateral flatten( input => src:events )
    where not exists
    (select 'x'
     from events
     where events.device_type = src:device_type
     and events.version = src:version
     and events.f = value:f
     and events.rv = value:rv
     and events.t = value:t
     and events.achz = value:v.ACHZ
     and events.acv = value:v.ACV
     and events.dca = value:v.DCA
     and events.dcv = value:v.DCV
     and events.enjr = value:v.ENJR
     and events.errs = value:v.ERRS
     and events.mxec = value:v.MXEC
     and events.tmpi = value:v.TMPI
     and events.vd = value:vd
     and events.z = value:z);

    Durch Abfragen der EVENTS-Tabelle wird die hinzugefügte Zeile angezeigt:

    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 |
| cell_phone  | 3.2     |      79 | "786954.67,492.68,3577.48,40.11,343.00,345.8,0.22,8765.22"           | 5769784730576 |  75846 |  98355 |   789 | 62287 | 2234 |  578 |  999 |    9 |  54 | 1437644222811 |
| web_browser | 8.7     |      79 | "122375.99,744.89,386.99,12.45,78.08,43.7,9.22,8765.43"              | 5769784730576 | 768436 |   9475 | 94835 | 88845 | 8754 |  567 |  823 |    0 |  55 | 8745598047355 |
+-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------+

Nächstes Thema: Schritt 5: Herzlichen Glückwunsch!