Schritt 4: Daten aktualisieren

Bislang haben Sie in diesem Tutorial Folgendes getan:

  • Kopieren von JSON-Beispielereignisdaten aus einem S3-Bucket in die RAW_SOURCE-Tabelle und Untersuchen einfacher Abfragen.

  • Verwenden der Funktion FLATTEN, um die JSON-Daten zu vereinfachen und eine relationale Darstellung der Daten zu erhalten. Sie haben zum Beispiel Ereignisschlüssel extrahiert und die Schlüssel in separaten Spalten einer anderen EVENTS-Tabelle gespeichert.

Zu Beginn wird im Tutorial das Anwendungsszenario erläutert, bei dem mehrere Quellen Ereignisse generieren und ein Web-Endpunkt diese in Ihrem S3-Bucket speichert. Wenn dem S3-Bucket neue Ereignisse hinzugefügt werden, können Sie ein Skript verwenden, um kontinuierlich neue Daten in die Tabelle RAW_SOURCE zu kopieren. Wie kann erreicht werden, dass nur neue Ereignisdaten der EVENTS-Tabelle hinzugefügt werden?

Es gibt zahlreiche Möglichkeiten, die Datenkonsistenz zu wahren. In diesem Abschnitt werden zwei Optionen erläutert.

Primärschlüsselspalten zum Vergleichen verwenden

In diesem Abschnitt fügen Sie der EVENTS-Tabelle einen Primärschlüssel hinzu. Der Primärschlüssel garantiert dann die Eindeutigkeit.

  1. Untersuchen Sie Ihre JSON-Daten auf alle Werte, die naturgemäß eindeutig sind und sich gut für einen Primärschlüssel eignen. Angenommen, die Kombination aus src:device_type und value:rv ist ein Primärschlüssel. Diese beiden JSON-Schlüssel entsprechen den Spalten DEVICE_TYPE und RV der EVENTS-Tabelle.

    Bemerkung

    Snowflake erzwingt keine Einschränkung (Constraint) auf einen Primärschlüssel. Die Einschränkung fungiert eher wie eine Metainformation, mit der der natürliche Schlüssel in Information Schema ermittelt werden kann.

  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);
    
    Copy
  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
      }');
    
    Copy
  4. Fügen Sie den neuen Datensatz, den Sie in die Tabelle RAW_SOURCE eingefügt haben, 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);
    
    Copy

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

    select * from EVENTS;
    
    Copy

    Die Abfrage gibt das folgende Ergebnis zurück:

    +-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
    | 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 |
    +-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
    
    Copy

Alle Spalten zum Vergleichen verwenden

Wenn die JSON-Daten keine Felder haben, die als Primärschlüsselkandidaten in Frage kommen, können Sie alle sich wiederholenden JSON-Schlüssel in der Tabelle RAW_SOURCE mit den entsprechenden Spaltenwerten in der Tabelle EVENTS 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
      }');
    
    Copy
  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);
    
    Copy

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

    select * from EVENTS;
    
    Copy

    Die Abfrage gibt das folgende Ergebnis zurück:

    +-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------+
    | 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 |
    +-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------+
    
    Copy

Nächstes Thema: Schritt 5: Bereinigen