Hinweise zu semistrukturierten Daten

Unter diesem Thema finden Sie Best Practices, allgemeine Richtlinien und wichtige Hinweise zum Laden und Verwenden von Daten aus JSON, Avro, ORC und Parquet. Die Informationen unter diesem Thema gelten nicht unbedingt für XML-Daten.

Unter diesem Thema:

Beschränkungen der Datengröße

Der Datentyp VARIANT erlegt den einzelnen Zeilen eine Größenbeschränkung von 16 MB (komprimiert) auf.

Im Allgemeinen sind JSON- und Avro-Datasets eine einfache Verkettung mehrerer Dokumente. Die JSON- oder Avro-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.

Stattdessen empfehlen wir, die Dateiformatoption STRIP_OUTER_ARRAY für den Befehl COPY INTO <Tabelle> zu aktivieren, 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, empfehlen wir Ihnen, diese zunächst in einer VARIANT-Spalte zu speichern. Für Daten, die meist regulär sind und nur native Typen (Zeichenfolgen und ganze Zahlen) verwenden, 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 Objekt- 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

Nicht-native Werte wie Datum und Zeitstempel 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 Objekte und Schlüssel, die Sie abfragen möchten, in eine separate Tabelle zu extrahieren. Führen Sie einige typische Abfragen auf beiden Tabellen aus, um festzustellen, welche Struktur die beste Performance bietet.

Umwandeln von Schlüsselwerten

Wenn Sie Schlüsselwerte aus einer VARIANT-Spalte extrahieren, wandeln Sie die Werte in den gewünschten Datentyp (mit der ::-Notation) um, damit unerwartete Ergebnisse vermieden werden. Wenn Sie beispielsweise einen Zeichenfolgen-Schlüsselwert ohne Umwandlung extrahieren, werden die Ergebnisse in doppelte Anführungszeichen gesetzt (um zu zeigen, dass der VARIANT -Wert eine Zeichenfolge und keinen anderen Typ enthält; d. h. "1" ist eine Zeichenfolge, während 1 eine Zahl ist):

SELECT col1:city;

+----------------------+
| CITY                 |
|----------------------|
| "Los Angeles"        |
+----------------------+

SELECT col1:city::string;

+----------------------+
| CITY                 |
|----------------------|
| Los Angeles          |
+----------------------+

NULL-Werte

In einer VARIANT-Spalte werden NULL-Werte als Zeichenfolge mit dem Wort „null“ gespeichert. Dieses Verhalten ermöglicht es, „Null“-Werte von fehlenden Werten zu unterscheiden, die ein SQL NULL ergeben.

Beispiel:

select parse_json('{ "a": null}'):a, parse_json('{ "a": null}'):b;

+------------------------------+------------------------------+
| PARSE_JSON('{ "A": NULL}'):A | PARSE_JSON('{ "A": NULL}'):B |
|------------------------------+------------------------------|
| null                         | NULL                         |
+------------------------------+------------------------------+

Um einen VARIANT-„null“-Wert in SQL NULL zu konvertieren, wandeln Sie ihn in eine Zeichenfolge um.

Beispiel:

select to_char(parse_json('{ "a": null}'):a);

+---------------------------------------+
| TO_CHAR(PARSE_JSON('{ "A": NULL}'):A) |
|---------------------------------------|
| NULL                                  |
+---------------------------------------+

Semistrukturierte Datendateien und Spaltenbildung

Wenn semistrukturierte Daten in eine VARIANT-Spalte eingefügt werden, extrahiert Snowflake so viele der Daten wie möglich in eine spaltenweise Form, basierend auf bestimmten Regeln. Der Rest wird als eine einzige Spalte in einer geparsten, semistrukturierten Struktur gespeichert. Derzeit werden Elemente mit den folgenden Eigenschaften 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. die Differenz zwischen VARIANT-„null“-Werten und SQL NULL-Werten wird nicht verschleiert.

  • 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"}
    

Wenn ein semistrukturiertes Element abgefragt wird:

  • Wenn das Element in eine Spalte extrahiert wurde, scannt das Ausführungsmodul von Snowflake (das spaltenweise vorgeht) nur die extrahierte Spalte.

  • Wenn das Element nicht in eine Spalte extrahiert wurde, muss das Ausführungsmodul die gesamte JSON-Struktur scannen und dann für jede Zeile die Struktur durchlaufen, um Werte auszugeben, was sich auf die Leistung auswirkt.

So vermeiden Sie diese Leistungseinbuße:

  • 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 semistrukturierter Datendateien die Dateiformatoption STRIP_NULL_VALUES auf TRUE zu setzen. Mit dieser Option werden Objektelemente oder Array-Elemente, die „null“-Werte enthalten, entfernt.

  • Stellen Sie sicher, dass jedes eindeutige Element Werte eines einzelnen nativen Datentyps speichert (Zeichenfolge oder Zahl).

Analysieren von NULL-Werten

Um einen VARIANT-„null“-Schlüsselwert als SQL NULL-Wert auszugeben, verwenden Sie die Funktion TO_CHAR , TO_VARCHAR für die Umwandlung des Wertes in eine Zeichenfolge, 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                             |
+-----------------+-----------------------------------+

Verwenden von FLATTEN zum Filtern der Ergebnisse in einer WHERE-Klausel

Die Funktion FLATTEN löst geschachtelte Werte in separate Spalten auf. Sie können die Funktion verwenden, um Abfrageergebnisse in einer WHERE-Klausel zu filtern.

Das folgende Beispiel gibt Schlüssel-Wert-Paare zurück, die eine WHERE-Klausel erfüllen, und zeigt das Paar in separaten Spalten an:

CREATE TABLE pets (v variant);

INSERT INTO pets SELECT PARSE_JSON ('{"species":"dog", "name":"Fido", "is_dog":"true"} ');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"Bubby", "is_dog":"false"}');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"dog terror", "is_dog":"false"}');

SELECT a.v, b.key, b.value FROM pets a,LATERAL FLATTEN(input => a.v) b
WHERE b.value LIKE '%dog%';

+-------------------------+---------+--------------+
| V                       | KEY     | VALUE        |
|-------------------------+---------+--------------|
| {                       | species | "dog"        |
|   "is_dog": "true",     |         |              |
|   "name": "Fido",       |         |              |
|   "species": "dog"      |         |              |
| }                       |         |              |
| {                       | name    | "dog terror" |
|   "is_dog": "false",    |         |              |
|   "name": "dog terror", |         |              |
|   "species": "cat"      |         |              |
| }                       |         |              |
+-------------------------+---------+--------------+

Verwenden von FLATTEN zur Auflistung eindeutiger Schlüsselnamen

Wenn Sie mit unbekannten semistrukturierten Daten arbeiten, kennen Sie möglicherweise die Schlüsselnamen in einem Objekt nicht. Sie können die Funktion FLATTEN mit dem Argument RECURSIVE verwenden, um die Liste der verschiedenen Schlüsselnamen in allen geschachtelten Elementen eines Objekts zurückzugeben:

SELECT REGEXP_REPLACE(f.path, '\\[[0-9]+\\]', '[]') AS "Path",
  TYPEOF(f.value) AS "Type",
  COUNT(*) AS "Count"
FROM <table>,
LATERAL FLATTEN(<variant_column>, RECURSIVE=>true) f
GROUP BY 1, 2 ORDER BY 1, 2;

Die Funktion REGEXP_REPLACE entfernt die Array-Indexwerte (z. B. [0]) und ersetzt diese durch Klammern ([]), um Array-Elemente zu gruppieren.

Beispiel:

{"a": 1, "b": 2, "special" : "data"}   <--- row 1 of VARIANT column
{"c": 3, "d": 4, "normal" : "data"}    <----row 2 of VARIANT column

Output from query:

+---------+---------+-------+
| Path    | Type    | Count |
|---------+---------+-------|
| a       | INTEGER |     1 |
| b       | INTEGER |     1 |
| c       | INTEGER |     1 |
| d       | INTEGER |     1 |
| normal  | VARCHAR |     1 |
| special | VARCHAR |     1 |
+---------+---------+-------+

Verwenden von FLATTEN zur Auflistung von Pfaden in Objekten

In Bezug auf Verwenden von FLATTEN zur Auflistung eindeutiger Schlüsselnamen können Sie die FLATTEN-Funktion mit dem RECURSIVE-Argument verwenden, um alle Schlüssel und Pfade eines Objekts abzurufen.

Die folgende Abfrage gibt Schlüssel, Pfade und Werte (einschließlich VARIANT-„null“-Werte) für alle in Objekten gespeicherten Datentypen zurück:

SELECT
  t.<variant_column>,
  f.seq,
  f.key,
  f.path,
  REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
  TYPEOF(f.value) AS "Type",
  f.index,
  f.value AS "Current Level Value",
  f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f;

Die folgende Abfrage ist ähnlich wie die erste Abfrage, schließt aber geschachtelte Objekte und Arrays aus:

SELECT
  t.<variant_column>,
  f.seq,
  f.key,
  f.path,
  REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
  TYPEOF(f.value) AS "Type",
  f.value AS "Current Level Value",
  f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f
WHERE "Type" NOT IN ('OBJECT','ARRAY');

Die Abfragen geben die folgenden Werte zurück:

<Variant-Spalte>

Objekt, das als Zeile in der VARIANT-Spalte gespeichert ist.

Seq

Eindeutige Sequenznummer, die den Daten in der Zeile zugeordnet ist.

Key

Zeichenfolge, die einem Wert in der Datenstruktur zugeordnet ist.

Path

Pfad zum Element innerhalb der Datenstruktur.

Level

Ebene des Schlüssel-Wert-Paares innerhalb der Datenstruktur.

Type

Datentyp für den Wert.

Index

Index des Elements in der Datenstruktur. Gilt nur für Arrays, ansonsten NULL.

Current Level Value

Wert auf der aktuellen Ebene in der Datenstruktur.

Above Level Value

Wert eine Ebene höher in der Datenstruktur.