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.
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 applique certaines règles pour extraire autant de données que possible sous forme de colonnes. Le reste des données est stocké sous la forme d’une seule colonne dans une structure semi-structurée analysée.
Par défaut, Snowflake extrait un maximum de 200 éléments par partition, par table. Pour augmenter cette limite, contactez le support Snowflake.
Éléments non extraits¶
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. 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 qu’aucune information n’est perdue (c’est-à-dire que la différence entre les valeurs VARIANT « null » et les valeurs SQL NULL n’est pas perdue).
Éléments contenant plusieurs types de données. Par exemple :
L’élément
foodans une ligne contient un nombre :{"foo":1}
Le même élément dans une autre ligne contient une chaîne :
{"foo":"1"}
Impact de l’extraction sur les requêtes¶
Lorsque vous interrogez un élément semi-structuré, le moteur d’exécution de Snowflake se comporte différemment selon qu’un élément a été extrait ou non.
Si l’élément a été extrait dans une colonne, le moteur analyse uniquement la colonne extraite.
Si l’élément n’a pas été extrait dans une colonne, le moteur doit analyser toute la structure JSON, puis pour chaque ligne, parcourir la structure pour émettre les valeurs. Cela a un impact sur les performances.
Pour éviter l’impact sur les performances des éléments non extraits, procédez comme suit :
Extrayez les éléments de données semi-structurées contenant des valeurs « null » dans des colonnes relationnelles avant leur chargement.
Sinon, si les valeurs « null » de vos fichiers indiquent des valeurs manquantes et n’ont pas d’autre signification particulière, nous vous recommandons de définir l”option de format de fichier STRIP_NULL_VALUES sur TRUE lors du chargement des fichiers de données semi-structurées. 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 |
+-----------------+-----------------------------------+