Considérations pour les données semi-structurées stockées dans des VARIANT¶
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 avec des valeurs VARIANT qui contiennent des données semi-structurées. Il peut s’agir de données hiérarchiques construites explicitement ou de données que vous avez chargées à partir de formats de données semi-structurées tels que 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 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).
Analyse de valeurs NULL¶
Pour sortir une valeur SQL NULL à partir d’une paire valeur-clé VARIANT "null"
, 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 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.