Types de données semi-structurées

Cette rubrique décrit les types de données semi-structurées pris en charge dans Snowflake.

Dans ce chapitre :

Types de données

Les types de données suivants sont utilisés pour représenter des structures de données arbitraires qui peuvent être utilisées pour importer et effectuer des opérations sur des données semi-structurées (JSON, Avro, ORC, Parquet ou XML). Snowflake stocke ces types en interne avec une représentation des documents sous forme binaire et avec des colonnes compressées efficacement, pour une meilleure performance et efficacité. L’optimisation de Snowflake pour le stockage de ces types de données est totalement transparente et ne produit aucun changement sémantique visible pour l’utilisateur.

VARIANT

Un type universel étiqueté, qui peut stocker des valeurs de tout autre type, y compris OBJECT et ARRAY, jusqu’à une taille maximale de 16 MB une fois compressé.

Une valeur de n’importe quel type de données peut être implicitement convertie en une valeur VARIANT, sous réserve de restrictions de taille. Lorsqu’une valeur VARIANT (sans conversion explicite) et une valeur NUMERIC sont contraintes dans le même type, la valeur NUMERIC est convertie en VARIANT. Par exemple :

var:json_path >= 56 est converti en var:json_path >= 56::VARIANT

Des conversions explicites peuvent être utilisées pour convertir les valeurs vers et depuis le type de données VARIANT. Par exemple, pour convertir une valeur en type de données VARIANT, utilisez TO_VARIANT ou expr::variant. Pour convertir une valeur de type variant, utilisez expr::<type_données>, où <type_données> correspond au type de données vers lequel vous souhaitez convertir la valeur, par exemple variant_col1::FLOAT. L’exemple de code ci-dessous montre les conversions de et vers variant :

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;

Notez qu’une valeur VARIANT peut être manquante (contenir SQL NULL), ce qui est différent d’une valeur VARIANT null, qui est une valeur réelle utilisée pour représenter une valeur nulle dans les données semi-structurées. VARIANT null est une valeur true qui se compare à elle-même.

Les colonnes VARIANT d’une table relationnelle sont stockées sous forme de colonnes physiques séparées. Les clés et les chemins répétitifs sont en outre stockés sous forme de colonnes physiques séparées, similaires aux attributs réguliers SQL. Actuellement, ce n’est pas le cas des données stockées dans les tableaux.

Pour les données qui sont le plus souvent régulières et n’utilisent que des types JSON natifs (chaînes et nombres, pas d’horodatage), les performances de stockage et de requête pour les opérations sur les données relationnelles et les données dans une colonne VARIANT sont très similaires. Les valeurs non natives, telles que les dates et les horodatages, sont stockées sous forme de chaînes lorsqu’elles sont chargées dans une colonne VARIANT. Les opérations sur ces valeurs peuvent donc être plus lentes et consommer plus d’espace que lorsqu’elles sont stockées dans une colonne relationnelle avec le type de données correspondant.

OBJECT

Utilisé pour représenter des ensembles de paires clé-valeur, où la clé est une chaîne non vide, et la valeur est une valeur de type VARIANT. Snowflake ne prend pas actuellement en charge les objets de types explicites.

ARRAY

Utilisé pour représenter des tableaux denses ou clairsemés de taille arbitraire, où l’index est un entier non négatif (jusqu’à 2^31-1), et les valeurs sont du type VARIANT. Snowflake ne prend pas actuellement en charge les tableaux de taille fixe ou les tableaux d’éléments d’un type spécifique autre que VARIANT.

Exemples

Ce premier exemple montre la sortie d’une commande DESC TABLE sur une table avec des données VARIANT, ARRAY et OBJECT.

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

Cet exemple montre comment charger des valeurs simples dans la table et à quoi ressemblent ces valeurs lorsque vous interrogez la table.

Créer une table et charger les données :

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 } '
              ||
               ' } ')
  ;

Maintenant, affichez les données dans la table.

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

Pour d’autres exemples d’utilisation de données semi-structurées, voir Interrogation de données semi-structurées.