Tutorial: JSON-Grundlagen für Snowflake¶
Einführung¶
In diesem Tutorial lernen Sie die Grundlagen der Verwendung von JSON mit Snowflake kennen.
Lerninhalte¶
In diesem Tutorial erfahren Sie, wie Sie Folgendes tun können:
Hochladen von JSON-Beispieldaten aus einem öffentlichen S3-Bucket in eine Spalte vom Typ
variantin einer Snowflake-Tabelle.Testen einfacher Abfragen auf den JSON-Daten in der Tabelle.
Verwenden der Funktion FLATTEN zum Umwandeln von JSON-Daten in eine vereinfachte, relationale Darstellung und deren Speicherung in einer anderen Tabelle.
Untersuchen verschiedener Optionen zum Sicherstellen der Eindeutigkeit der vereinfachten Daten nach dem Einfügen von Zeilen unter Verwendung von FLATTEN.
Voraussetzungen¶
Das Tutorial setzt Folgendes voraus:
Sie haben ein Snowflake-Konto, das für die Verwendung von Amazon Web Services (AWS) konfiguriert ist, und Sie haben einen Benutzer mit einer Rolle, die die erforderlichen Berechtigungen zum Erstellen von Datenbank-, Tabellen- und virtuellen Warehouse-Objekten erteilt.
Sie haben SnowSQL (CLI-Client) installiert.
Das Snowflake in 20 Minuten-Tutorial enthält die entsprechende Schritt-für-Schritt-Anleitung, um diese Anforderungen zu erfüllen.
Snowflake stellt Beispieldatendateien in einem öffentlichen Amazon S3-Bucket zur Verwendung in diesem Tutorial bereit. Bevor Sie beginnen, müssen Sie jedoch eine Datenbank, einige Tabellen, ein virtuelles Warehouse und einen externen Stagingbereich für dieses Tutorial erstellen. Dies sind die grundlegenden Snowflake-Objekte, die für die meisten Snowflake-Aktivitäten benötigt werden.
Allgemeine Informationen zu der Beispieldatendatei¶
In diesem Tutorial verwenden Sie die folgenden JSON-Beispieldaten für Anwendungsereignisse, die in einem öffentlichen S3 Bucket bereitgestellt werden.
{
"device_type": "server",
"events": [
{
"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
},
{
"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
}
],
"version": 2.6
}
Die Daten repräsentieren Beispielereignisse, die von Anwendungen auf S3 hochgeladen werden. Eine Vielzahl von Geräten und Anwendungen wie Server, Handys und Browser veröffentlichen Ereignisse. In einem allgemeinen Datenerfassungsszenario sammelt ein skalierbarer Web-Endpunkt POST-Daten aus verschiedenen Quellen und schreibt diese in ein Warteschlangensystem. Ein Ingest-Service/Dienstprogramm schreibt die Daten dann in einen S3-Bucket, von dem aus Sie die Daten in Snowflake laden können.
Die Beispieldaten veranschaulichen die folgenden Konzepte:
Anwendungen können entscheiden, Ereignisse in Batches zu gruppieren. Ein Batch ist ein Container, der Header-Informationen enthält, die für alle Ereignisse im Batch gelten. Das obige JSON-Beispiel ist ein Batch mit zwei Ereignissen mit gemeinsamen Header-Informationen:
device_typeundversion, die diese Ereignisse generiert haben.Amazon S3 unterstützt die Verwendung von Ordnern, um Buckets zu organisieren. Anwendungen können dieses Feature nutzen, um Ereignisdaten zu partitionieren. Partitionierungsschemas identifizieren typischerweise Details, wie die Anwendung oder den Speicherort, die das Ereignis generiert haben, zusammen mit dem Datum, an dem das Ereignis in S3 geschrieben wurde. Mit einem solchen Partitionierungsschema kann ein beliebiger Teil der partitionierten Daten mit einem einzigen COPY-Befehl in Snowflake kopiert werden. So können Sie beispielsweise bei der Erstbefüllung von Tabellen Ereignisdaten stunden-, daten-, monats- oder jahresweise kopieren.
Beispiel:
s3://bucket_name/application_a/2016/07/01/11/s3://bucket_name/application_b/location_c/2016/07/01/14/Beachten Sie, dass mit
application_a,application_b,location_cusw. die Angaben zur Quelle aller Daten im Pfad identifiziert werden können. Die Daten können nach dem Datum organisiert werden, an dem sie geschrieben wurden. Ein optionales 24-Stunden-Verzeichnis reduziert die Datenmenge in jedem Verzeichnis.Bemerkung
S3 sendet eine Verzeichnisliste mit allen von Snowflake verwendeten COPY-Anweisungen, sodass die Reduzierung der Anzahl der Dateien in jedem Verzeichnis die Leistung Ihrer COPY-Anweisungen verbessert. Sie können sogar erwägen, alle 10-15 Minuten pro Stunde einen Ordner zu erstellen.
Die im S3-Bucket bereitgestellten Beispieldaten verwenden ein ähnliches Partitionierungsschema. In einem COPY-Befehl geben Sie einen bestimmten Ordnerpfad zum Kopieren von Ereignisdaten an.
Erstellen von Datenbank, Tabelle, Warehouse und externem Stagingbereich¶
Führen Sie die folgenden Anweisungen aus, um eine Datenbank, eine Tabelle, ein virtuelles Warehouse und einen externen Stagingbereich zu erstellen, die für dieses Tutorial benötigt werden. Nachdem Sie das Tutorial abgeschlossen haben, können Sie diese Objekte wieder löschen.
CREATE OR REPLACE DATABASE mydatabase; USE SCHEMA mydatabase.public; CREATE OR REPLACE TABLE raw_source ( SRC VARIANT); CREATE OR REPLACE WAREHOUSE mywarehouse WITH WAREHOUSE_SIZE='X-SMALL' AUTO_SUSPEND = 120 AUTO_RESUME = TRUE INITIALLY_SUSPENDED=TRUE; USE WAREHOUSE mywarehouse; CREATE OR REPLACE STAGE my_stage URL = 's3://snowflake-docs/tutorials/json';
Beachten Sie Folgendes:
Die
CREATE DATABASE-Anweisung erstellt eine Datenbank. Die Datenbank enthält automatisch ein Schema namens „public“.Die Anweisung
USE SCHEMAgibt eine aktive Datenbank und ein aktives Schema für die aktuelle Benutzersitzung an. Die Angabe einer Datenbank ermöglicht es Ihnen nun, Ihre Arbeit in dieser Datenbank auszuführen, ohne den Namen jedes Mal angeben zu müssen, wenn er angefordert wird.Die Anweisung
CREATE TABLEerstellt eine Zieltabelle für JSON-Daten.Die
CREATE WAREHOUSE-Anweisung erstellt ein Warehouse, das zunächst angehalten ist. Die Anweisung legt auch AUTO_RESUME = true fest, wodurch das Warehouse automatisch gestartet wird, wenn Sie SQL-Anweisungen ausführen, die Computeressourcen benötigen. Die AnweisungUSE WAREHOUSElegt das von Ihnen erstellte Warehouse als aktives Warehouse für die aktuelle Benutzersitzung fest.Die Anweisung
CREATE STAGEerstellt einen externen Stagingbereich, der auf den S3-Bucket verweist, der die Beispieldatei für dieses Tutorial enthält.
Daten in die Zieltabelle kopieren¶
Führen Sie COPY INTO <Tabelle> aus, um Ihre bereitgestellten Daten in die Zieltabelle RAW_SOURCE zu laden.
COPY INTO raw_source
FROM @my_stage/server/2.6/2016/07/15/15
FILE_FORMAT = (TYPE = JSON);
Der Befehl kopiert alle neuen Daten aus dem angegebenen Pfad des externen Stagingbereichs in die Zieltabelle RAW_SOURCE. In diesem Beispiel führt der Pfad zu Daten, die in der 15. Stunde (15:00 Uhr) des 15. Juli 2016 geschrieben wurden. Beachten Sie, dass Snowflake den S3 ETag-Wert jeder Datei überprüft, um sicherzustellen, dass sie nur einmal kopiert wird.
Führen Sie eine SELECT-Abfrage aus, um zu überprüfen, ob die Daten erfolgreich kopiert wurden.
SELECT * FROM raw_source;
Die Abfrage gibt das folgende Ergebnis zurück:
+-----------------------------------------------------------------------------------+
| SRC |
|-----------------------------------------------------------------------------------|
| { |
| "device_type": "server", |
| "events": [ |
| { |
| "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 |
| }, |
| { |
| "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 |
| } |
| ], |
| "version": 2.6 |
| } |
+-----------------------------------------------------------------------------------+
In diesen JSON-Beispieldaten gibt es zwei Ereignisse. Die Schlüsselwerte device_type und version identifizieren die Datenquelle und die Version von Ereignissen eines bestimmten Geräts:
Daten abfragen¶
In diesem Schritt werden SELECT-Anweisungen zum Abfragen von JSON-Daten vorgestellt.
Rufen Sie den Wert von
device_typeab.SELECT src:device_type FROM raw_source;
Die Abfrage liefert das folgende Ergebnis:
+-----------------+ | SRC:DEVICE_TYPE | |-----------------| | "server" | +-----------------+
Die Abfrage verwendet die Notation
src:device_type, um den Spaltennamen und den Namen des abzurufenden JSON-Elements anzugeben. Diese Notation ist ähnlich wie die bekannte SQL-Notationtable.column. In Snowflake können Sie eine Unterspalte innerhalb einer übergeordneten Spalte angeben, die Snowflake dynamisch aus der in den JSON-Daten eingebetteten Schemadefinition ableitet. Weitere Informationen dazu finden Sie unter Abfragen von semistrukturierten Daten.Bemerkung
Der Spaltenname unterscheidet nicht zwischen Groß- und Kleinschreibung, aber bei den JSON-Elementnamen ist die Groß-/Kleinschreibung relevant.
Rufen Sie den
device_type-Wert ohne Anführungszeichen ab.Die vorherige Abfrage gibt den JSON-Datenwert in Anführungszeichen zurück. Sie können die Anführungszeichen entfernen, indem Sie die Daten in einen angegebenen Datentyp umwandeln, in diesem Beispiel in eine Zeichenfolge (String).
Diese Abfrage weist der Spalte optional auch einen Namen unter Verwendung eines Alias zu.
SELECT src:device_type::string AS device_type FROM raw_source;
Die Abfrage gibt das folgende Ergebnis zurück:
+-------------+ | DEVICE_TYPE | |-------------| | server | +-------------+
Rufen Sie die sich wiederholenden
f-Schlüssel ab, die in den Array-Ereignisobjekten verschachtelt sind.Die JSON-Beispieldaten enthalten das Array
events. Jedes Ereignisobjekt im Array hat das Feldf, wie unten gezeigt.{ "device_type": "server", "events": [ { "f": 83, .. } { "f": 1000083, .. } ]}
Um diese verschachtelten Schlüssel abzurufen, können Sie die Funktion FLATTEN verwenden. Die Funktion vereinfacht die Ereignisse in separaten Zeilen.
SELECT value:f::number FROM raw_source , LATERAL FLATTEN( INPUT => SRC:events );
Die Abfrage gibt das folgende Ergebnis zurück:
+-----------------+ | VALUE:F::NUMBER | |-----------------| | 83 | | 1000083 | +-----------------+
Beachten Sie, dass
valueeine der Spalten ist, die von der FLATTEN-Funktion zurückgegeben wird. Der nächste Schritt enthält weitere Einzelheiten zur Verwendung der FLATTEN-Funktion.
Daten vereinfachen¶
FLATTEN ist eine Tabellenfunktion, mit der die laterale Ansicht einer VARIANT-, OBJECT- oder ARRAY-Spalte erstellt werden kann. In diesem Schritt verwenden Sie diese Funktion, um verschiedene Ebenen der Vereinfachung zu untersuchen.
Array-Objekte in einer Variant-Spalte vereinfachen¶
Mit der Funktion FLATTEN können Sie die Ereignisobjekte im events-Array in separate Zeilen vereinfachen. Die Funktionsausgabe enthält eine VALUE-Spalte, in der die einzelnen Ereignisse gespeichert sind.
Sie können dann den LATERAL-Modifikator verwenden, um die Ausgabe der Funktion FLATTEN mit beliebigen Informationen außerhalb des Objekts zu verknüpfen – in diesem Beispiel mit device_type und version.
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 );
Die Abfrage gibt das folgende Ergebnis 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 | | | | } | +-------------------------+---------------------+-------------------------------------------------------------------------------+
Verwenden Sie eine CREATE TABLE ASSELECT-Anweisung, um die obigen Abfrageergebnisse 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 );
Führen Sie eine Abfrage auf der Ergebnistabelle aus.
SELECT * FROM flattened_source;
Die Abfrage gibt das folgende Ergebnis zurück:
+-------------+---------+-------------------------------------------------------------------------------+ | 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 | | | | } | +-------------+---------+-------------------------------------------------------------------------------+
Objektschlüssel in separaten Spalten vereinfachen¶
Im vorherigen Beispiel haben Sie die Ereignisobjekte im events-Array in separate Zeilen vereinfacht. Die resultierende Tabelle flattened_source behielt die Ereignisstruktur in der src-Spalte vom Typ VARIANT bei.
Ein Vorteil der Beibehaltung der Ereignisobjekte in der src-Spalte vom Typ VARIANT besteht darin, dass Sie bei einer Änderung des Ereignisformats solche Tabellen nicht neu erstellen und neu füllen müssen. Sie haben aber auch die Option, einzelne Schlüssel im Ereignisobjekt in Spalten mit unterschiedlichen Typen zu kopieren, wie in der folgenden Abfrage gezeigt.
Die folgende Anweisung CREATE TABLE AS SELECT erstellt eine neue Tabelle namens events, in der die Ereignisobjektschlüssel in separaten Spalten gespeichert sind. 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 |
+-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
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_typeundvalue:rvist ein Primärschlüssel. Diese beiden JSON-Schlüssel entsprechen den SpaltenDEVICE_TYPEundRVderEVENTS-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
EVENTShinzu:ALTER TABLE events ADD CONSTRAINT pk_DeviceType PRIMARY KEY (device_type, rv);
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 }');
Fügen Sie den neuen Datensatz, den Sie in die Tabelle
RAW_SOURCEeingefügt haben, auf Basis eines Vergleichs der Primärschlüsselwerte in die TabelleEVENTSein: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;
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 | +-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
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 }');
Fügen Sie den neuen Datensatz der Tabelle
RAW_SOURCEauf Basis eines Vergleichs aller sich wiederholenden Schlüsselwerte in die TabelleEVENTSein: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;
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 | +-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------+
Herzlichen Glückwunsch!¶
Herzlichen Glückwunsch, Sie haben das Tutorial erfolgreich abgeschlossen.
Kernpunkte des Tutorials¶
Die Partitionierung der Ereignisdaten in Ihrem S3-Bucket durch sehr präzise, logische Pfade ermöglicht es Ihnen, eine Teilmenge der partitionierten Daten mit einem einzigen Befehl nach Snowflake zu kopieren.
Über die Snowflake-Notation
column:key, die der bekannten SQL-Notationtable.columnähnlich ist, können Sie eine Spalte innerhalb der Spalte (d. h. eine Unterspalte) effektiv abfragen, die dynamisch aus der in den JSON-Daten eingebetteten Schemadefinition abgeleitet wird.Mit der Funktion FLATTEN können Sie JSON-Daten in separate Spalten parsen.
Es stehen mehrere Optionen zur Verfügung, um Tabellendaten auf Grundlage von Vergleichen mit Staging-Datendateien zu aktualisieren.
Tutorialdaten bereinigen (optional)¶
Führen Sie die folgenden DROP <Objekt>-Befehle aus, um Ihr System in den Zustand zu versetzen, bevor Sie das Tutorial begonnen haben:
DROP DATABASE IF EXISTS mydatabase; DROP WAREHOUSE IF EXISTS mywarehouse;
Wenn Sie die Datenbank löschen, werden automatisch alle untergeordneten Datenbankobjekte wie Tabellen entfernt.