Semistrukturierte Datentypen

Unter diesem Thema werden die in Snowflake unterstützten semistrukturierten Datentypen beschrieben.

Unter diesem Thema:

Datentypen

Die folgenden Datentypen werden zur Darstellung beliebiger Datenstrukturen verwendet, die sich zum Importieren und Verarbeiten von semistrukturierten Daten (JSON, Avro, ORC, Parquet oder XML) nutzen lassen. Snowflake speichert diese Typen intern in einer effizienten komprimierten spaltenweise Binärdarstellung der Dokumente, um Leistung und Effizienz zu verbessern. Snowflakes Optimierung für die Speicherung dieser Datentypen ist vollständig transparent und führt zu keiner für Benutzer sichtbaren Änderung der Semantik.

VARIANT

Ein getaggter Universaltyp, der Werte eines beliebigen anderen Typs, einschließlich OBJECT und ARRAY, bis zu einer maximalen komprimierten Größe von 16 MB speichern kann.

Ein Wert eines beliebigen Datentyps kann, vorbehaltlich Größenbeschränkungen, implizit in einen VARIANT-Wert umgewandelt werden. Wenn ein VARIANT-Wert (ohne explizite Umwandlung) und ein NUMERIC-Wert in denselben Typ gezwungen werden, wird der NUMERIC-Wert in VARIANT umgewandelt. Beispiel:

var:json_path >= 56 wird umgewandelt in var:json_path >= 56::VARIANT

Explizite Umwandlungen können zum Konvertieren von Werten in und aus dem Datentyp VARIANT verwendet werden. Um beispielsweise einen Wert in den Datentyp VARIANT zu konvertieren, verwenden Sie TO_VARIANT oder expr::variant. Verwenden Sie zum Konvertieren eines Werts vom Variant-Typ expr::<Datentyp>, wobei <Datentyp> der Datentyp ist, in den Sie konvertieren möchten, z. B. variant_col1::FLOAT. Der folgende Beispielcode zeigt Konvertierungen von Variante zu Variante:

CREATE TABLE varia (float1 FLOAT, v VARIANT, float2 FLOAT);
INSERT INTO varia (float1, v, float2) VALUES (1.23, NULL, NULL);
SELECT * FROM varia;
UPDATE varia SET v = TO_VARIANT(float1);  -- converts FROM a float TO a variant.
UPDATE varia SET float2 = v::FLOAT;       -- converts FROM a variant TO a float.
SELECT * FROM varia;

Beachten Sie, dass ein VARIANT-Wert fehlen (SQL NULL enthalten) kann. Das unterscheidet sich von einem VARIANT null-Wert, bei dem es sich um einen tatsächlichen Wert handelt, der zur Darstellung eines Nullwerts in semistrukturierten Daten dient. VARIANT null ist ein wahrer Wert, der mit sich gleich ist.

VARIANT-Spalten in einer relationalen Tabelle werden als separate physische Spalten gespeichert. Sich wiederholende Schlüssel und Pfade werden außerdem als separate physische Spalten gespeichert, ähnlich wie reguläre SQL-Attribute. Derzeit gilt dies nicht für Daten, die in Arrays gespeichert sind.

Bei Daten, die größtenteils regulär sind und nur native JSON-Typen nutzen (Zeichenfolgen und Zahlen, keine Zeitstempel), ist die Speicher- und Abfrageleistung bei Operationen mit relationalen Daten und Daten in einer VARIANT-Spalte sehr ähnlich. Nicht-native Werte wie Datumsangaben und Zeitstempel werden beim Laden in eine VARIANT-Spalte als Zeichenfolgen gespeichert, sodass Operationen mit diesen Werten möglicherweise langsamer ausgeführt werden und mehr Platz beanspruchen als beim Speichern in einer relationalen Spalte mit dem entsprechenden Datentyp.

OBJECT

Wird verwendet, um Sammlungen von Schlüssel-Wert-Paaren darzustellen, wobei der Schlüssel eine nicht leere Zeichenfolge und der Wert ein Wert vom Typ VARIANT ist. Snowflake unterstützt derzeit keine explizit typisierten Objekte.

ARRAY

Wird verwendet, um dichte oder spärliche Arrays beliebiger Größe darzustellen, wobei index eine nicht negative ganze Zahl (bis zu 2^31-1) ist und Werte vom Typ VARIANT hat. Snowflake unterstützt derzeit keine Arrays mit fester Größe bzw. Arrays mit Elementen eines bestimmten Nicht-VARIANT-Typs.

Beispiele

Dieses erste Beispiel zeigt die Ausgabe eines DESC TABLE-Befehls in einer Tabelle mit VARIANT-, ARRAY- und OBJECT-Daten.

CREATE OR REPLACE TABLE test_semi_structured(var VARIANT,
                                    arr ARRAY,
                                    obj OBJECT
                                    );

DESC TABLE test_semi_structured;

+------+---------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type    | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+---------+--------+-------+---------+-------------+------------+-------+------------+---------|
| VAR  | VARIANT | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| ARR  | ARRAY   | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| OBJ  | OBJECT  | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+---------+--------+-------+---------+-------------+------------+-------+------------+---------+

In diesem Beispiel wird gezeigt, wie Sie einfache Werte in die Tabelle laden und wie diese Werte aussehen, wenn Sie die Tabelle abfragen.

Erstellen Sie eine Tabelle, und Laden Sie die Daten:

CREATE TABLE demonstration1 (
    ID INTEGER,
    array1 ARRAY,
    variant1 VARIANT,
    object1 OBJECT
    );

INSERT INTO demonstration1 (id, array1, variant1, object1) 
  SELECT 
    1, 
    ARRAY_CONSTRUCT(1, 2, 3), 
    PARSE_JSON(' { "key1": "value1", "key2": "value2" } '),
    PARSE_JSON(' { "outer_key1": { "inner_key1A": "1a", "inner_key1B": "1b" }, '
              ||
               '   "outer_key2": { "inner_key2": 2 } } ')
    ;

INSERT INTO demonstration1 (id, array1, variant1, object1) 
  SELECT 
    1, 
    ARRAY_CONSTRUCT(1, 2, 3, NULL), 
    PARSE_JSON(' { "key1": "value1", "key2": NULL } '),
    PARSE_JSON(' { "outer_key1": { "inner_key1A": "1a", "inner_key1B": NULL }, '
              ||
               '   "outer_key2": { "inner_key2": 2 } '
              ||
               ' } ')
  ;

Zeigen Sie nun die Daten in der Tabelle an.

SELECT * 
    FROM demonstration1;
+----+-------------+---------------------+--------------------------+
| ID | ARRAY1      | VARIANT1            | OBJECT1                  |
|----+-------------+---------------------+--------------------------|
|  1 | [           | {                   | {                        |
|    |   1,        |   "key1": "value1", |   "outer_key1": {        |
|    |   2,        |   "key2": "value2"  |     "inner_key1A": "1a", |
|    |   3         | }                   |     "inner_key1B": "1b"  |
|    | ]           |                     |   },                     |
|    |             |                     |   "outer_key2": {        |
|    |             |                     |     "inner_key2": 2      |
|    |             |                     |   }                      |
|    |             |                     | }                        |
|  1 | [           | {                   | {                        |
|    |   1,        |   "key1": "value1", |   "outer_key1": {        |
|    |   2,        |   "key2": null      |     "inner_key1A": "1a", |
|    |   3,        | }                   |     "inner_key1B": null  |
|    |   undefined |                     |   },                     |
|    | ]           |                     |   "outer_key2": {        |
|    |             |                     |     "inner_key2": 2      |
|    |             |                     |   }                      |
|    |             |                     | }                        |
+----+-------------+---------------------+--------------------------+

Weitere Beispiele für die Nutzung semistrukturierter Daten finden Sie unter Abfragen von semistrukturierten Daten.