Considérations pour les données semi-structurées stockées dans des VARIANT¶

Limites relatives à la taille des données¶

Le type de données VARIANT impose une limite de taille de 16 MB sur les lignes individuelles.

Pour certains formats de données semi-structurées (par exemple, JSON), les ensembles de données sont souvent une simple concaténation de plusieurs documents. La sortie JSON de certains logiciels est composée d’un seul grand tableau contenant plusieurs enregistrements. Il n’est pas nécessaire de séparer les documents par des sauts de ligne ou des virgules, bien que les deux soient pris en charge.

Si les données dépassent 16 MB, activez l’option de format de fichier STRIP_OUTER_ARRAY pour la commande COPY INTO <table> afin de supprimer la structure de tableau externe et de charger les enregistrements dans des lignes de table séparées :

COPY INTO <table>
  FROM @~/<file>.json
  FILE_FORMAT = (TYPE = 'JSON' STRIP_OUTER_ARRAY = true);

Stockage de données semi-structurées dans une colonne VARIANT vs. Aplatissement de la structure imbriquée¶

Si vous n’êtes pas encore sûr des types d’opérations que vous voulez effectuer sur vos données semi-structurées, Snowflake recommande de stocker les données dans une colonne VARIANT pour le moment.

Pour les données qui sont le plus souvent régulières et n’utilisent que des types de données natifs pour le format semi-structuré que vous utilisez (chaînes et entiers pour le format JSON), les exigences de stockage et les performances de requête pour les opérations sur les données relationnelles et les données dans une colonne VARIANT sont très similaires.

Pour un meilleur élagage et une consommation de stockage réduite, nous vous recommandons d’aplatir votre OBJECT et vos données clés en colonnes relationnelles séparées si vos données semi-structurées incluent :

• Des dates et horodatages, et plus particulièrement des dates et horodatages non ISO 8601 en tant que valeurs de chaîne.

• Des nombres dans les chaînes.

• Des tableaux.

Les valeurs non natives (telles que les dates et les horodatages en JSON), 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.

Si vous connaissez les cas d’utilisation des données, effectuez des tests sur un ensemble de données classique. Chargez l’ensemble de données dans une colonne VARIANT d’une table. Utilisez la fonction FLATTEN pour extraire les OBJECTs et les clés que vous souhaitez interroger vers une table séparée. Exécutez un ensemble classique de requêtes sur les deux tables pour voir quelle structure fournit les meilleures performances.

Conversion de paires clé-valeur¶

Lors de l’extraction des paires valeur-clé d’une colonne VARIANT, convertissez les valeurs vers le type de données souhaité (en utilisant la notation :: ) pour éviter des résultats inattendus. Par exemple, lorsque vous extrayez une paire clé-valeur de chaîne sans conversion, les résultats sont placés entre guillemets doubles (pour montrer que la valeur VARIANT contient une chaîne et non un type différent, c’est-à-dire que "1" est une chaîne et 1 est un nombre) :

SELECT col1:city;

+----------------------+
| CITY                 |
|----------------------|
| "Los Angeles"        |
+----------------------+

SELECT col1:city::string;

+----------------------+
| CITY                 |
|----------------------|
| Los Angeles          |
+----------------------+

Valeurs NULL¶

Snowflake prend en charge deux types de valeurs NULL dans les données semi-structurées :

• SQL NULL : SQL NULL signifie la même chose pour les types de données semi-structurées que pour les types de données structurées : la valeur est manquante ou inconnue.

• JSON null (parfois appelé « VARIANT NULL ») : dans une colonne VARIANT, les valeurs JSON null sont stockées sous la forme d’une chaîne contenant le mot « null » pour les distinguer des valeurs SQL NULL.

L’exemple suivant oppose SQL NULL et JSON null :

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

Pour convertir une valeur VARIANT "null" en SQL NULL, convertissez-la en tant que chaîne. Par exemple :

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

Fichiers de données semi-structurés et mise en colonnes¶

Lorsque des données semi-structurées sont insérées dans une colonne VARIANT, Snowflake extrait autant de données que possible sous forme de colonnes, selon certaines règles. Le reste est stocké sous la forme d’une colonne unique dans une structure semi-structurée analysée. Actuellement, les éléments présentant les caractéristiques suivantes ne sont pas extraits dans une colonne :

• Les éléments qui contiennent ne serait-ce qu’une seule valeur « null » ne sont pas extraits dans une colonne. Notez que cela s’applique aux éléments avec des valeurs « null » et non aux éléments avec des valeurs manquantes, qui sont représentés sous forme de colonne.

  Cette règle garantit que l’information n’est pas perdue, c’est-à-dire que la différence entre les valeurs « null » VARIANT et les valeurs SQL NULL n’est pas floue.

• Éléments contenant plusieurs types de données. Par exemple :

  L’élément foo dans une ligne contient un nombre :

  {"foo":1}
  

  Le même élément dans une autre ligne contient une chaîne :

  {"foo":"1"}
  

Lorsqu’un élément semi-structuré est interrogé :

• Si le champ a été extrait dans une colonne, le moteur d’exécution de Snowflake (qui est en colonnes) analyse uniquement la colonne extraite.

• Si l’élément n’a pas été extrait dans une colonne, le moteur d’exécution doit balayer toute la structure JSON, puis pour chaque ligne, parcourir la structure jusqu’aux valeurs de sortie, ce qui a un impact sur les performances.

Pour éviter cet impact sur les performances :

• Extrayez des éléments de données semi-structurés contenant des valeurs « null » dans des colonnes relationnelles avant le chargement.

  Alternativement, si les valeurs « null » dans vos fichiers indiquent des valeurs manquantes et n’ont pas d’autre signification particulière, nous vous recommandons de régler l’option de format de fichier STRIP_NULL_VALUES sur TRUE lors du chargement des fichiers de données semi-structurés. Cette option supprime les éléments OBJECT ou ARRAY contenant des valeurs « nulles ».

• Assurez-vous que chaque élément unique stocke des valeurs d’un seul type de données natif du format (par exemple, une chaîne ou un nombre pour 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                             |
+-----------------+-----------------------------------+

Utilisation de FLATTEN pour filtrer les résultats dans une clause WHERE¶

La fonction FLATTEN répartit les valeurs imbriquées en colonnes séparées. Vous pouvez utiliser cette fonction pour filtrer les résultats d’une requête dans une clause WHERE.

L’exemple suivant retourne les paires clé-valeur qui correspondent à une clause WHERE et les affiche dans des colonnes séparées :

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

Utilisation de FLATTEN pour répertorier les noms de clé distincts¶

Lorsque vous travaillez avec des données semi-structurées peu familières, il se peut que vous ne connaissiez pas les noms de clé d’un OBJECT. Vous pouvez utiliser la fonction FLATTEN avec l’argument RECURSIVE pour retourner la liste des noms de clé distincts dans tous les éléments imbriqués dans un OBJECT :

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;

La fonction REGEXP_REPLACE supprime les valeurs d’index de tableau (par exemple [0]) et les remplace par des parenthèses ([]) pour regrouper les éléments de tableau.

Par exemple :

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

Utilisation de FLATTEN pour répertorier les chemins de liste dans un OBJECT¶

En ce qui concerne l’utilisation de FLATTEN pour répertorier des noms de clé distincts, vous pouvez utiliser la fonction FLATTEN avec l’argument RECURSIVE pour récupérer les clés et chemins dans un OBJECT.

La requête suivante renvoie les clés, les chemins et les valeurs (y compris les valeurs VARIANT « nulles ») pour tous les types de données stockés dans une colonne VARIANT. Le code suppose que la colonne VARIANT contient un OBJECT dans chaque ligne.

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;

La requête suivante est similaire à la première, mais exclut les OBJECTs imbriqués et les ARRAYs :

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

Les requêtes renvoient les valeurs suivantes :

<colonne_variante>

OBJECT stocké sous forme de ligne dans la colonne VARIANT.

Séq.

Numéro de séquence unique associé aux données de la ligne.

Clé

Chaîne associée à une valeur dans la structure de données.

Chemin

Chemin d’accès à l’élément dans la structure de données.

Niveau

Niveau de la paire clé-valeur dans la structure de données.

Type

Type de données pour la valeur.

Index

Index de l’élément dans la structure de données. S’applique uniquement aux valeurs ARRAY ; sinon NULL.

Valeur de niveau actuel

Valeur au niveau actuel dans la structure de données.

Valeur de niveau supérieur

Valeur d’un niveau supérieur dans la structure de données.