Remarques relatives aux données semi-structurées

Ce chapitre fournit des bonnes pratiques, des lignes directrices générales et des remarques importantes pour le chargement et l’utilisation de données JSON, Avro, ORC et Parquet. Les informations contenues dans ce chapitre ne s’appliquent pas nécessairement aux données XML.

Dans ce chapitre :

Limites relatives à la taille des données

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

En général, les ensembles de données JSON et Avro sont une simple concaténation de plusieurs documents. La sortie JSON ou Avro 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.

Nous recommandons plutôt d’activer 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 allez effectuer sur vos données semi-structurées, nous vous recommandons de les stocker 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 natifs (chaînes et entiers), 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 objet 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, 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 objets 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

Dans une colonne VARIANT , les valeurs NULL sont stockées sous la forme d’une chaîne contenant le mot « null ». Ce comportement permet de distinguer les valeurs « nulles » des valeurs absentes qui produisent un SQL NULL.

Par exemple :

select parse_json('{ "a": null}'):a, parse_json('{ "a": null}'):b;

+------------------------------+------------------------------+
| PARSE_JSON('{ "A": NULL}'):A | PARSE_JSON('{ "A": NULL}'):B |
|------------------------------+------------------------------|
| null                         | NULL                         |
+------------------------------+------------------------------+

Pour convertir une valeur VARIANT « nulle » en SQL NULL, convertissez-la en tant que chaîne.

Par exemple :

select to_char(parse_json('{ "a": null}'):a);

+---------------------------------------+
| TO_CHAR(PARSE_JSON('{ "A": NULL}'):A) |
|---------------------------------------|
| 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 d’objet ou les éléments de tableau contenant des valeurs « nulles ».

  • Assurez-vous que chaque élément unique stocke les valeurs d’un seul type de données natif (chaîne ou nombre).

Analyse de valeurs NULL

Pour sortir une valeur SQL NULL à partir d’une paire valeur-clé VARIANT « nulle », utilisez la fonction TO_CHAR , TO_VARCHAR pour convertir la valeur en tant que chaîne, par exemple :

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 objet. 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 objet :

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 dans des objets

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

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 des objets :

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 objets imbriqués et les tableaux :

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>

Objet 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 tableaux. 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.