Types de données semi-structurées

Les types de données Snowflake suivants peuvent contenir d’autres types de données :

  • VARIANT (peut contenir tout autre type de données).

  • ARRAY (peut contenir directement VARIANT et donc indirectement tout autre type de données, y compris lui-même).

  • OBJECT (peut contenir directement VARIANT et donc indirectement tout autre type de données, y compris lui-même).

Nous faisons souvent référence à ces types de données comme étant des types de données semi-structurées. À proprement parler, OBJECT est le seul de ces types de données qui, à lui seul, possède toutes les caractéristiques d’un véritable type de données semi-structurées. Toutefois, la combinaison de ces types de données permet de représenter de façon explicite des structures de données hiérarchiques arbitraires, qui peuvent être utilisées pour charger et exploiter des données dans des formats semi-structurées (par exemple JSON, Avro, ORC, Parquet ou XML).

Chacun de ces types de données est décrit ci-dessous.

Dans ce chapitre :

VARIANT

Caractéristiques d’un VARIANT

Un VARIANT peut stocker une valeur de tout autre type, y compris OBJECT et ARRAY.

La longueur maximale d’un VARIANT est de 16 MB.

Utilisation des valeurs dans un VARIANT

Pour convertir une valeur à partir du type de données VARIANT ou vers celui-ci, vous pouvez explicitement utiliser la fonction CAST , la fonction TO_VARIANT ou l’opérateur :: (par exemple, expression::variant).

Dans certaines situations, les valeurs peuvent être implicitement converties en VARIANT. Pour plus de détails, voir Conversion de type de données.

L’exemple de code ci-dessous montre comment utiliser un VARIANT, notamment comment effectuer une conversion à partir d’un VARIANT et vers un VARIANT.

Créer une table et insérer une valeur :

CREATE TABLE varia (float1 FLOAT, v VARIANT, float2 FLOAT);
INSERT INTO varia (float1, v, float2) VALUES (1.23, NULL, NULL);

Le premier UPDATE convertit une valeur FLOAT en VARIANT. Le second UPDATE convertit une valeur VARIANT en FLOAT.

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 toutes les valeurs :

SELECT * FROM varia;
+--------+-----------------------+--------+
| FLOAT1 | V                     | FLOAT2 |
|--------+-----------------------+--------|
|   1.23 | 1.230000000000000e+00 |   1.23 |
+--------+-----------------------+--------+

Pour convertir une valeur du type de données VARIANT, spécifiez le type de données vers lequel vous souhaitez la convertir. Par exemple, l’instruction suivante utilise l’opérateur :: pour spécifier que le VARIANT doit être converti en FLOAT :

SELECT my_variant_column::FLOAT * 3.14 FROM ...;

VARIANT enregistre à la fois la valeur et le type de données de la valeur. Cela vous permet également d’utiliser des valeurs VARIANT dans des expressions où le type de données de la valeur est valide sans avoir à effectuer une conversion préalable de VARIANT. Par exemple, si la colonne VARIANT my_variant_column contient une valeur numérique, vous pouvez directement multiplier my_variant_column par une autre valeur numérique :

SELECT my_variant_column * 3.14 FROM ...;

Vous pouvez récupérer le type de données natif de la valeur en utilisant la fonction TYPEOF.

Par défaut, lorsque VARCHARs, DATEs, TIMEs et TIMESTAMPs sont extraits d’une colonne VARIANT, les valeurs sont entourées de guillemets doubles. Vous pouvez éliminer les guillemets en convertissant explicitement les valeurs en types de données sous-jacents (par exemple, de VARIANT à VARCHAR). Par exemple :

SELECT 'Sample', 'Sample'::VARIANT, 'Sample'::VARIANT::VARCHAR;
+----------+-------------------+----------------------------+
| 'SAMPLE' | 'SAMPLE'::VARIANT | 'SAMPLE'::VARIANT::VARCHAR |
|----------+-------------------+----------------------------|
| Sample   | "Sample"          | Sample                     |
+----------+-------------------+----------------------------+

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. Pour plus d’informations, voir VARIANT null.

Si les données ont été chargées à partir du format JSON et stockées dans VARIANT, les lignes directrices suivantes s’appliquent :

  • 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. Cependant, 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.

Pour plus d’informations sur l’utilisation de VARIANT, voir : Considérations pour les données semi-structurées stockées dans des VARIANT.

Pour plus d’informations sur l’interrogation de données semi-structurées stockées dans un VARIANT, voir : Interrogation de données semi-structurées.

Exemple d’insertion d’un VARIANT

Pour insérer directement des données VARIANT, utilisez IIS (INSERT INTO ... SELECT). L’exemple ci-dessous montre comment insérer des données au format JSON dans un VARIANT :

INSERT INTO varia (v) 
    SELECT TO_VARIANT(PARSE_JSON('{"key3": "value3", "key4": "value4"}'));

Utilisations courantes d’un VARIANT

Un VARIANT est généralement utilisé lorsque :

  • Vous voulez créer des données hiérarchiques en définissant explicitement une hiérarchie qui contient au moins deux ARRAYs ou OBJECTs.

  • Vous voulez charger directement des données JSON, Avro, ORC ou Parquet, sans décrire explicitement la structure hiérarchique des données.

    Snowflake peut convertir des données qui sont aux formats JSON, Avro, ORC ou Parquet en une hiérarchie interne de données ARRAY, OBJECT et VARIANT et stocker ces données hiérarchiques directement dans un VARIANT. Bien que vous puissiez construire manuellement la hiérarchie de données vous-même, il est généralement plus facile de laisser Snowflake le faire pour vous.

    Pour plus d’informations sur le chargement et la conversion de données semi-structurées, voir Chargement de données semi-structurées.

OBJECT

Un OBJECT Snowflake est analogue à un « objet JSON ». Dans d’autres langages de programmation, le type de données correspondant est souvent appelé « dictionnaire », « hachage » ou « map ».

Un OBJECT contient des paires clé-valeur.

Caractéristiques d’un OBJECT

Dans un OBJECT Snowflake, chaque clé est un VARCHAR et chaque valeur est un VARIANT.

Comme un VARIANT peut stocker tout autre type de données, différentes valeurs (dans différentes paires clé-valeur) peuvent avoir différents types de données sous-jacentes. Par exemple, un OBJECT peut contenir le nom d’une personne sous forme de VARCHAR et son âge sous forme d’INTEGER. Dans l’exemple ci-dessous, le nom et l’âge sont convertis en VARIANT.

SELECT OBJECT_CONSTRUCT(
    'name', 'Jones'::VARIANT,
    'age',  42::VARIANT
    );

Snowflake ne prend pas actuellement en charge les objets de types explicites.

Dans une paire clé-valeur, la clé ne doit pas être une chaîne vide. De plus, ni la clé ni la valeur ne doivent être NULL.

La longueur maximale d’un OBJECT est de 16 MB.

Un OBJECT peut contenir des données semi-structurées.

Un OBJECT peut être utilisé pour créer des structures de données hiérarchiques.

Accès aux éléments d’un OBJECT par clé

Pour récupérer la valeur d’un objet, indiquez la clé entre crochets, comme indiqué ci-dessous :

select my_variant_column['key1'] from my_table;

Vous pouvez également utiliser l’opérateur deux-points. La commande suivante montre que les résultats sont les mêmes, que vous utilisiez les deux-points ou les crochets :

SELECT object_column['thirteen'],
       object_column:thirteen
    FROM object_example;
+---------------------------+------------------------+
| OBJECT_COLUMN['THIRTEEN'] | OBJECT_COLUMN:THIRTEEN |
|---------------------------+------------------------|
| 13                        | 13                     |
+---------------------------+------------------------+

Pour plus d’informations sur l’opérateur deux-points, voir Notation par points, qui décrit l’utilisation des opérateurs : et . pour accéder à des données imbriquées.

Exemple d’insertion d’un OBJECT

Pour insérer directement des données OBJECT, utilisez IIS (INSERT INTO ... SELECT).

Le code suivant utilise la fonction OBJECT_CONSTRUCT pour construire le OBJECT qu’il insère.

INSERT INTO object_example (object_column)
    SELECT OBJECT_CONSTRUCT('thirteen', 13::VARIANT, 'zero', 0::VARIANT);

Le code suivant utilise un objet littéral pour construire l’OBJECT qu’il insère.

INSERT INTO object_example (object_column)
    SELECT { 'thirteen': 13::VARIANT, 'zero': 0::VARIANT };

Notez que dans chaque paire clé-valeur, la valeur a été explicitement convertie en VARIANT. La conversion explicite n’était pas nécessaire dans ces cas. Snowflake peut implicitement faire des conversions en VARIANT. (Pour des informations sur la conversion implicite, voir Conversion de type de données).

Utilisations courantes d’un OBJECT

Un OBJECT est généralement utilisé lorsqu’un ou plusieurs des éléments suivants sont vrais :

  • Vous avez plusieurs éléments de données qui sont identifiés par des chaînes. Par exemple, si vous souhaitez rechercher des informations par nom de province, vous pouvez utiliser un OBJECT.

  • Vous voulez stocker des informations sur les données avec les données ; les noms (clés) ne sont pas simplement des identificateurs distincts, mais sont significatifs.

  • Les informations n’ont pas d’ordre naturel ou l’ordre peut être déduit uniquement à partir des clés.

  • La structure des données varie ou les données peuvent être incomplètes. Par exemple, si vous souhaitez créer un catalogue de livres contenant généralement le titre, le nom de l’auteur et la date de publication, mais que dans certains cas la date de publication est inconnue, vous pouvez utiliser un OBJECT.

ARRAY

Un ARRAY Snowflake est similaire à un tableau dans de nombreux autres langages de programmation. Un ARRAY contient 0 ou plusieurs éléments de données. On accède à chaque élément en spécifiant sa position dans le tableau.

Caractéristiques d’un ARRAY

Chaque valeur dans un ARRAY est de type VARIANT. (Un VARIANT peut contenir une valeur de tout autre type de données).

Les valeurs d’autres types de données peuvent être converties en VARIANT, puis stockées dans un tableau. Certaines fonctions pour les ARRAYs, y compris ARRAY_CONSTRUCT, peuvent implicitement convertir des valeurs en VARIANT pour vous.

Comme les ARRAYs stockent des valeurs VARIANT et que les valeurs VARIANT peuvent contenir d’autres types de données, les types de données sous-jacents des valeurs d’un ARRAY ne doivent pas nécessairement être identiques. Toutefois, dans la plupart des cas, les éléments de données sont de types identiques ou compatibles, de sorte que tous peuvent être traités de la même manière.

Snowflake ne prend pas en charge les tableaux d’éléments d’un type spécifique non-VARIANT.

Un ARRAY Snowflake est déclaré sans spécifier le nombre d’éléments. Un ARRAY peut croître dynamiquement en fonction d’opérations telles que ARRAY_APPEND. Snowflake ne prend actuellement pas en charge les tableaux de taille fixe.

Un ARRAY peut contenir des valeurs NULL.

La taille maximale théorique combinée de toutes les valeurs d’un ARRAY est 16 MB. Cependant, les ARRAYs ont une surcharge interne. La taille maximale concrète des données est généralement plus petite, en fonction du nombre et des valeurs des éléments.

Accès aux éléments d’un ARRAY par indice ou par tranche

Les index des tableaux sont basés sur 0 ; le premier élément d’un tableau est l’élément 0.

On accède aux valeurs d’un tableau en spécifiant le numéro d’index d’un élément du tableau entre crochets. Par exemple, la requête suivante lit la valeur à la position d’index 2 dans le tableau stocké dans my_array_column.

select my_array_column[2] from my_table;

Les tableaux peuvent être imbriqués. La requête suivante lit l’élément zéro de l’élément zéro d’un tableau imbriqué :

select my_array_column[0][0] from my_table;

Tenter d’accéder à un élément au-delà de la fin d’un tableau renvoie NULL.

Une tranche d’un tableau est une séquence d’éléments adjacents (c’est-à-dire un sous-ensemble contigu du tableau).

Vous pouvez accéder à une tranche d’un tableau en appelant la fonction ARRAY_SLICE. Par exemple :

select array_slice(my_array_column, 5, 10) from my_table;

La fonction ARRAY_SLICE() renvoie les éléments à partir de l’élément de départ spécifié (5 dans l’exemple ci-dessus) jusqu’à mais sans inclure l’élément final spécifié (10 dans l’exemple ci-dessus).

Un tableau vide ou une tranche vide est souvent indiqué par une paire de crochets sans rien entre eux ([]).

ARRAYs clairsemés et denses

Un tableau peut être dense ou clairsemé.

Dans un tableau dense, les valeurs d’index des éléments commencent à zéro et sont séquentielles (0, 1, 2, etc.). Cependant, dans un tableau clairsemé, les valeurs d’index peuvent être non séquentielles (par exemple, 0, 2, 5). Les valeurs ne doivent pas nécessairement commencer à 0.

Si un index n’a pas d’élément correspondant, alors la valeur correspondant à cet index est dite non définie. Par exemple, si un tableau clairsemé comporte trois éléments et que ces éléments sont aux index 0, 2 et 5, alors les éléments aux index 1, 3 et 4 sont undefined.

   0            2                  5
+-----+.....+-------+.....+.....+------+
| Ann |     | Carol |     |     | Fred |
+-----+.....+-------+.....+.....+------+

         ^             ^     ^
         |             |     |
        undefined--------------

Un élément indéfini est traité comme un élément. Par exemple, considérons l’exemple précédent d’un tableau clairsemé qui contient des éléments aux index 0, 2 et 5 (et n’a pas d’éléments après l’index 5). Si vous lisez la tranche contenant les éléments aux index 3 et 4, le résultat est similaire à ce qui suit :

[ undefined, undefined ]

La tentative d’accès à une tranche au-delà de la fin d’un tableau donne un tableau vide et non un tableau de valeurs undefined. L’instruction SELECT suivante tente de lire au-delà du dernier élément du tableau clairsemé de l’échantillon :

select array_slice(array_column, 6, 8) from table_1;

La sortie est un tableau vide :

+---------------------------------+
| array_slice(array_column, 6, 8) |
+---------------------------------+
| [ ]                             |
+---------------------------------+

Notez que undefined est différent de NULL. Une valeur NULL dans un tableau est un élément défini.

Dans un tableau dense, chaque élément consomme de l’espace de stockage, même si la valeur de l’élément est NULL.

Dans un tableau clairsemé, les éléments undefined ne consomment pas directement d’espace de stockage.

Dans un tableau dense, la plage théorique des valeurs d’index est comprise entre 0 et 16777215. (Le nombre maximum théorique d’éléments est 16777216, car la limite supérieure de la taille est de 16 MB (16777216 octets) et la plus petite valeur possible est un octet).

Dans un tableau clairsemé, la plage théorique des valeurs d’index est comprise entre 0 et 231 - 1. Cependant, en raison de la limitation de 16 MB, un tableau clairsemé ne peut pas contenir de valeurs 231. Le nombre maximal théorique de valeurs est toujours limité à 16777216.

(Rappelez-vous qu’en raison de la surcharge interne, la limite de taille pratique dans les tableaux denses et clairsemés est au moins légèrement inférieure au maximum théorique de 16 MB).

Vous pouvez créer un tableau clairsemé en utilisant la fonction ARRAY_INSERT pour insérer des valeurs à des points d’index spécifiques dans un tableau (en laissant les autres éléments du tableau undefined). Notez que parce que ARRAY_INSERT() pousse les éléments vers la droite, ce qui modifie les valeurs d’index requises pour y accéder, vous souhaitez presque toujours remplir un tableau clairsemé de gauche à droite (c’est-à-dire de 0 vers le haut, en augmentant la valeur de l’index pour chaque nouvelle valeur insérée).

Exemple d’insertion d’un ARRAY

Pour insérer directement des données ARRAY, utilisez IIS (INSERT INTO ... SELECT).

Le code suivant utilise la fonction ARRAY_CONSTRUCT pour construire le ARRAY qu’il insère.

INSERT INTO array_example (array_column)
    SELECT ARRAY_CONSTRUCT(12, 'twelve', NULL);

Le code suivant utilise un littéral de tableau pour construire le ARRAY qu’il insère.

INSERT INTO array_example (array_column)
    SELECT [ 12, 'twelve', NULL ];

Utilisations courantes d’un ARRAY

Un ARRAY est généralement utilisé lorsqu’un ou plusieurs des éléments suivants sont vrais :

  • Vous disposez de nombreux éléments de données, dont chacun est structuré de manière identique ou similaire.

  • Chaque élément de donnée doit être traité de la même manière. Par exemple, vous pouvez parcourir les données en boucle, en traitant chaque élément de la même manière.

  • Les données ont un ordre naturel, par exemple, chronologique.

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 
    2,
    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
    ORDER BY id;
+----+-------------+---------------------+--------------------------+
| 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      |
|    |             |                     |   }                      |
|    |             |                     | }                        |
|  2 | [           | {                   | {                        |
|    |   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.

Revenir au début