Hinweise zu in VARIANT gespeicherten semistrukturierten Daten¶
Unter diesem Thema finden Sie Best Practices, allgemeine Richtlinien und wichtige Hinweise zum Laden und Verwenden von VARIANT-Werten, die semistrukturierte Daten enthalten. Dies können explizit konstruierte hierarchische Daten oder Daten sein, die Sie aus semistrukturierten Datenformaten wie JSON, Avro, ORC und Parquet geladen haben. Die Informationen unter diesem Thema gelten nicht unbedingt für XML-Daten.
Unter diesem Thema:
Beschränkungen der Datengröße¶
Ein VARIANT-Wert kann eine maximale Größe von bis zu 16 MB an unkomprimierten Daten haben. In der Praxis ist die maximale Größe jedoch aufgrund des internen Overheads meist geringer. Die maximale Größe hängt auch von dem zu speichernden Objekt ab.
Weitere Informationen dazu finden Sie unter VARIANT.
Im Allgemeinen sind JSON-Datasets eine einfache Verkettung mehrerer Dokumente. Die JSON-Ausgabe einer Software besteht aus einem einzigen großen Array, das mehrere Datensätze enthält. Es ist nicht erforderlich, die Dokumente durch Zeilenumbrüche oder Kommas zu trennen, obwohl beide unterstützt werden.
Wenn der Datenumfang größer 16 MB ist, aktivieren Sie die Dateiformatoption STRIP_OUTER_ARRAY für den Befehl COPY INTO <Tabelle>, um die äußere Array-Struktur zu entfernen und die Datensätze in separate Tabellenzeilen zu laden:
COPY INTO <table>
FROM @~/<file>.json
FILE_FORMAT = (TYPE = 'JSON' STRIP_OUTER_ARRAY = true);
Speichern von semistrukturierten Daten in einer VARIANT-Spalte vs. Vereinfachen der geschachtelten Struktur¶
Wenn Sie sich noch nicht sicher sind, welche Arten von Operationen Sie mit Ihren semistrukturierten Daten durchführen werden, empfiehlt Snowflake, diese zunächst in einer VARIANT-Spalte zu speichern.
Für Daten, die meist regulär sind und nur Datentypen verwenden, die für das von Ihnen verwendete semistrukturierte Format typisch sind (z. B. Zeichenfolgen und ganze Zahlen für JSON-Format), sind die Speicheranforderungen und die Abfrageleistung bei Operationen auf relationalen Daten und Daten in einer VARIANT-Spalte sehr ähnlich.
Für ein besseres Verkürzen und einen geringeren Speicherverbrauch empfehlen wir, Ihre OBJECT- und Schlüsseldaten in separate relationale Spalten zu vereinfachen, wenn Ihre semistrukturierten Daten Folgendes enthalten:
Datumsangaben und Zeitstempel, die nicht im ISO 8601-Format sind, als Zeichenfolgenwerte
Zahlen innerhalb von Zeichenfolgen
Arrays
Werte, die nicht systemeigen sind (wie Datum und Zeitstempel in JSON), werden beim Laden in eine VARIANT-Spalte als Zeichenfolgen gespeichert, sodass Operationen mit diesen Werten langsamer sein können und auch mehr Platz beanspruchen als bei der Speicherung in einer relationalen Spalte mit dem entsprechenden Datentyp.
Wenn Sie Ihre Anwendungsfälle für die Daten kennen, führen Sie Tests an einem typischen Dataset durch. Laden Sie das Dataset in die VARIANT-Spalte einer Tabelle. Verwenden Sie die Funktion FLATTEN, um die OBJECTs und Schlüssel, die Sie abfragen möchten, in einer separaten Tabelle zu extrahieren. Führen Sie einige typische Abfragen auf beiden Tabellen aus, um festzustellen, welche Struktur die beste Performance bietet.
NULL-Werte¶
Snowflake unterstützt zwei Arten von NULL-Werten in semistrukturierten Daten:
SQL NULL: SQL NULL bedeutet für semistrukturierte Datentypen das Gleiche wie für strukturierte Datentypen: Der Wert fehlt oder ist unbekannt.
JSON null (manchmal auch „VARIANT NULL“ genannt): In einer VARIANT-Spalte werden JSON null-Werte als Zeichenfolge gespeichert, der das Wort „null“ enthält, um sie von SQL NULL-Werten zu unterscheiden.
Im folgenden Beispiel werden SQL NULL und JSON null gegenübergestellt:
select parse_json(NULL) AS "SQL NULL", parse_json('null') AS "JSON NULL", parse_json('[ null ]') AS "JSON NULL", parse_json('{ "a": null }'):a AS "JSON NULL", parse_json('{ "a": null }'):b AS "ABSENT VALUE"; +----------+-----------+-----------+-----------+--------------+ | SQL NULL | JSON NULL | JSON NULL | JSON NULL | ABSENT VALUE | |----------+-----------+-----------+-----------+--------------| | NULL | null | [ | null | NULL | | | | null | | | | | | ] | | | +----------+-----------+-----------+-----------+--------------+
Um einen VARIANT-"null"-Wert in SQL NULL zu konvertieren, wandeln Sie ihn in eine Zeichenfolge um. Beispiel:
select parse_json('{ "a": null }'):a, to_char(parse_json('{ "a": null }'):a); +-------------------------------+----------------------------------------+ | PARSE_JSON('{ "A": NULL }'):A | TO_CHAR(PARSE_JSON('{ "A": NULL }'):A) | |-------------------------------+----------------------------------------| | null | NULL | +-------------------------------+----------------------------------------+
Semistrukturierte Datendateien und Spaltenbildung¶
Wenn semistrukturierte Daten in eine VARIANT-Spalte eingefügt werden, verwendet Snowflake bestimmte Regeln, um so viele der Daten wie möglich in eine spaltenweise Form zu extrahieren. Der Rest der Daten wird als eine einzige Spalte in einer geparsten, semistrukturierten Struktur gespeichert.
Standardmäßig extrahiert Snowflake maximal 200 Elemente pro Partition und Tabelle. Wenn Sie dieses Limit erhöhen möchten, wenden Sie sich an den Snowflake-Support.
Elemente, die nicht extrahiert werden¶
Elemente mit den folgenden Eigenschaften werden nicht in eine Spalte extrahiert:
Elemente, die auch nur einen einzigen „null“-Wert enthalten, werden nicht in eine Spalte extrahiert. Beachten Sie, dass dies für Elemente mit „Null“-Werten gilt und nicht für Elemente mit fehlenden Werten, die in Spaltenform dargestellt werden.
Diese Regel stellt sicher, dass keine Informationen verloren gehen (d. h., dass der Unterschied zwischen VARIANT-„null“-Werten und SQL-NULL-Werten erhalten bleibt).
Elemente, die verschiedene Datentypen enthalten. Beispiel:
Das
foo-Element in einer Zeile enthält eine Nummer:{"foo":1}
Das gleiche Element in einer anderen Zeile enthält eine Zeichenfolge:
{"foo":"1"}
Auswirkung der Extraktion auf Abfragen¶
Wenn Sie ein semistrukturiertes Element abfragen, verhält sich die Ausführungs-Engine von Snowflake unterschiedlich, je nachdem, ob ein Element extrahiert wurde.
Wenn das Element in eine Spalte extrahiert wurde, wird nur die extrahierte Spalte durchsucht.
Wenn das Element nicht in eine Spalte extrahiert wurde, muss die Engine die gesamte JSON-Struktur scannen und dann für jede Zeile die Struktur durchlaufen, um Werte auszugeben. Dies hat Auswirkungen auf die Verarbeitungsleistung.
Um die Auswirkungen auf die Leistung für nicht extrahierte Elemente zu vermeiden, gehen Sie wie folgt vor:
Extrahieren Sie semistrukturierte Datenelemente, die „null“-Werte enthalten, in relationale Spalten, bevor Sie sie laden.
Wenn die „null“-Werte in Ihren Dateien fehlende Werte anzeigen und keine andere besondere Bedeutung haben, empfehlen wir Ihnen, beim Laden von Dateien mit semistrukturierten Daten die Dateiformatoption STRIP_NULL_VALUES auf TRUE zu setzen. Mit dieser Option werden OBJECT-Elemente oder ARRAY-Elemente, die „null“-Werte enthalten, entfernt.
Stellen Sie sicher, dass jedes einzelne Element Werte eines einzigen Datentyps speichert, der dem Format eigen ist (z. B. Zeichenfolge oder Zahl für JSON).
Analysieren von NULL-Werten¶
Um einen SQL-NULL-Wert aus einem VARIANT-"null"-Schlüsselwert auszugeben, können Sie mit der Funktion TO_CHAR , TO_VARCHAR den Wert in eine Zeichenfolge umwandeln, z. B.:
SELECT column1
, TO_VARCHAR(PARSE_JSON(column1):a)
FROM
VALUES('{"a" : null}')
, ('{"b" : "hello"}')
, ('{"a" : "world"}');
+-----------------+-----------------------------------+
| COLUMN1 | TO_VARCHAR(PARSE_JSON(COLUMN1):A) |
|-----------------+-----------------------------------|
| {"a" : null} | NULL |
| {"b" : "hello"} | NULL |
| {"a" : "world"} | world |
+-----------------+-----------------------------------+