Hinweise zu in VARIANT gespeicherten semistrukturierten Daten¶

Beschränkungen der Datengröße¶

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

Bei einigen semistrukturierten Datenformaten (z. B. JSON) sind die Datasets häufig 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.

Umwandeln von Schlüsselwerten¶

Wenn Sie Schlüsselwerte aus einer VARIANT-Spalte extrahieren, wandeln Sie die Werte in den gewünschten Datentyp (mit ::-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¶

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

Copy

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

Copy

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}
  

  Copy

  Das gleiche Element in einer anderen Zeile enthält eine Zeichenfolge:

  {"foo":"1"}
  

  Copy

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 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).

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 OBJECT nicht. Sie können die Funktion FLATTEN mit dem Argument RECURSIVE verwenden, um die Liste der verschiedenen Schlüsselnamen in allen geschachtelten Elementen eines OBJECT 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 einem OBJECT¶

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 OBJECT abzurufen.

Die folgende Abfrage gibt Schlüssel, Pfade und Werte (einschließlich VARIANT-„null“-Werte) für alle in einer VARIANT-Spalte gespeicherten Datentypen zurück: Der Code geht davon aus, dass die VARIANT-Spalte in jeder Zeile ein OBJECT enthält.

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 OBJECTs 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>

OBJECT, 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 ARRAY-Werte, andernfalls NULL.

Current Level Value

Wert auf der aktuellen Ebene in der Datenstruktur.

Above Level Value

Wert eine Ebene höher in der Datenstruktur.