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.

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.

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

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

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.

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

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