Schritt 4: Daten aktualisieren

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!