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

Limites relatives à la taille des données¶

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

Pour plus d’informations, voir VARIANT.

En général, les ensembles de données JSON sont 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    |           |              |
|          |           | ]         |           |              |
+----------+-----------+-----------+-----------+--------------+

Copy

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

Copy

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.

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