Types de données semi-structurées¶

Caractéristiques d’un VARIANT¶

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

Une VARIANT peut avoir une taille maximale de 16 MB de données non compressées. Toutefois, dans la pratique, la taille maximale est généralement inférieure en raison d’une surcharge interne. La taille maximale dépend également de l’objet stocké.

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

Copy

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.

Copy

SELECT toutes les valeurs :

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

Copy

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

Copy

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

Copy

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

Copy

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.

Caractéristiques d’un OBJECT¶

Dans un OBJECT semi-structuré Snowflake, chaque clé est un VARCHAR et chaque valeur est une 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
    );

Copy

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.

Constantes OBJECT¶

Une constante (également appelée littéral) fait référence à une valeur de données fixe. Snowflake prend en charge l’utilisation de constantes pour spécifier des valeurs OBJECT. Les constantes OBJECT sont délimitées par des accolades ({ et }).

Par exemple, le bloc de code suivant présente deux constantes OBJECT, dont la première est un OBJECT vide, et la seconde contient les noms et les capitales de deux provinces canadiennes :

{}

{ 'Alberta': 'Edmonton' , 'Manitoba': 'Winnipeg' }

Les instructions suivantes utilisent une constante OBJECT et la fonction OBJECT_CONSTRUCT pour effectuer la même tâche :

UPDATE my_table SET my_object = { 'Alberta': 'Edmonton' , 'Manitoba': 'Winnipeg' };

UPDATE my_table SET my_object = OBJECT_CONSTRUCT('Alberta', 'Edmonton', 'Manitoba', 'Winnipeg');

Notez que les instructions SQL spécifient les littéraux de chaîne à l’intérieur d’un OBJECT avec des guillemets simples (comme ailleurs dans SQL Snowflake), mais les littéraux de chaîne à l’intérieur d’un OBJECT sont affichés avec des guillemets doubles :

SELECT { 'Manitoba': 'Winnipeg' } AS province_capital;

+--------------------------+
| PROVINCE_CAPITAL         |
|--------------------------|
| {                        |
|   "Manitoba": "Winnipeg" |
| }                        |
+--------------------------+

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;

Copy

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

Copy

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

Copy

Le code suivant utilise une constante OBJECT pour spécifier l’OBJECT à insérer.

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

Copy

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.

Caractéristiques d’un ARRAY¶

Chaque valeur d’un ARRAY semi-structuré 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.

Constantes ARRAY¶

Une constante (également appelée littéral) fait référence à une valeur de données fixe. Snowflake prend en charge l’utilisation de constantes pour spécifier des valeurs ARRAY. Les constantes ARRAY sont délimitées par des crochets ([ et ]).

Par exemple, le bloc de code suivant présente deux constantes ARRAY, dont la première est un ARRAY vide, et la seconde contient les noms de deux provinces canadiennes :

[]

[ 'Alberta', 'Manitoba' ]

Les instructions suivantes utilisent une constante ARRAY et la fonction ARRAY_CONSTRUCT pour effectuer la même tâche :

UPDATE my_table SET my_array = [ 1, 2 ];

UPDATE my_table SET my_array = ARRAY_CONSTRUCT(1, 2);

Notez que les instructions SQL spécifient les littéraux de chaîne à l’intérieur d’un ARRAY avec des guillemets simples (comme ailleurs dans SQL Snowflake), mais les littéraux de chaîne à l’intérieur d’un ARRAY sont affichés avec des guillemets doubles :

SELECT [ 'Alberta', 'Manitoba' ] AS province;

+--------------+
| PROVINCE     |
|--------------|
| [            |
|   "Alberta", |
|   "Manitoba" |
| ]            |
+--------------+

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;

Copy

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;

Copy

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;

Copy

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

Copy

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 ]

Copy

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;

Copy

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 2³¹ - 1. Cependant, en raison de la limitation de 16 MB, un tableau clairsemé ne peut pas contenir de valeurs 2³¹. 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);

Copy

Le code suivant utilise une constante ARRAY pour spécifier l’ARRAY à insérer.

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

Copy

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.