Formats pris en charge pour les données semi-structurées¶

Qu’est-ce que JSON ?¶

JSON (notation d’objet JavaScript) est un format d’échange de données léger, en texte clair, basé sur un sous-ensemble du langage de programmation JavaScript.

N’importe quelle application peut produire des données JSON. En voici quelques exemples classiques :

• Applications JavaScript utilisant des méthodes natives pour générer des données JSON.

• Applications non-JavaScript utilisant des bibliothèques (généralement avec des extensions) pour générer des données JSON.

• Générateurs ad hoc JavaScript.

• Concaténation de documents JSON (qui peuvent ou non être séparés par des lignes).

Puisqu’il n’y a pas de spécification formelle, il y a des différences significatives entre les différentes implémentations. Ces différences rendent impossible l’importation d’ensembles de données de type JSON si l’analyseur JSON est strict dans sa définition de langage. Pour rendre l’importation des ensembles de données JSON aussi facile que possible, Snowflake prône la libéralité. Le but est d’accepter la gamme la plus large possible d’entrées JSON et semblables à JSON qui permettent une interprétation sans ambiguïté.

Ce chapitre décrit la syntaxe des documents JSON acceptés par Snowflake.

Pour plus d’informations sur JSON, voir json.org.

Syntaxe JSON de base¶

Les données JSON sont un ensemble hiérarchique de paires nom/valeur regroupées en objets et tableaux :

• Les deux points : séparent les noms et les valeurs dans les paires nom/valeur.

• Les accolades {} indiquent des objets.

• Les crochets [] indiquent des tableaux.

• Les virgules , séparent les entités dans les objets et tableaux.

Paires nom/valeur¶

Les paires nom/valeur JSON se composent d’un nom de champ (entre guillemets doubles) suivi de deux points, puis d’une valeur.

Par exemple :

{"firstName":"John", "empid":45611}

Types de données pris en charge¶

Une valeur dans une paire nom/valeur peut être :

• Un nombre (entier ou virgule flottante)

• Une chaîne (entre guillemets doubles)

• Un booléen (vrai ou faux)

• Un tableau (entre crochets)

• Un objet (entre accolades)

• Nul

Objets¶

Les objets JSON s’écrivent dans des accolades. Un objet peut contenir plusieurs paires nom/valeur séparées par des virgules. Par exemple :

{"firstName":"John", "lastName":"Doe"}

Tableaux¶

Les tableaux JSON s’écrivent entre crochets. Un tableau peut contenir plusieurs objets séparés par des virgules. Par exemple :

{"employees":[
    {"firstName":"John", "lastName":"Doe"},
    {"firstName":"Anna", "lastName":"Smith"},
    {"firstName":"Peter", "lastName":"Jones"}
  ]
}

Exemples de documents JSON¶

FILE NAME : json_sample_data1

Contient un tableau avec 3 enregistrements d’employé simples (objets) :

{"root":[{"employees":[
    {"firstName":"John", "lastName":"Doe"},
    {"firstName":"Anna", "lastName":"Smith"},
    {"firstName":"Peter", "lastName":"Jones"}
]}]}

Copy

FILE NAME : json_sample_data2

Contient un tableau contenant trois enregistrements d’employé (objets) et les données connexes sur les personnes à leur charge (enfants, noms et âges des enfants, villes où l’employé a vécu et années pendant lesquelles l’employé a vécu dans ces villes) :

{"root":
   [
    { "kind": "person",
      "fullName": "John Doe",
      "age": 22,
      "gender": "Male",
      "phoneNumber":
        {"areaCode": "206",
         "number": "1234567"},
      "children":
         [
           {
             "name": "Jane",
             "gender": "Female",
             "age": "6"
           },
           {
              "name": "John",
              "gender": "Male",
              "age": "15"
           }
         ],
      "citiesLived":
         [
            {
               "place": "Seattle",
               "yearsLived": ["1995"]
            },
            {
               "place": "Stockholm",
               "yearsLived": ["2005"]
            }
         ]
      },
      {"kind": "person", "fullName": "Mike Jones", "age": 35, "gender": "Male", "phoneNumber": { "areaCode": "622", "number": "1567845"}, "children": [{ "name": "Earl", "gender": "Male", "age": "10"}, {"name": "Sam", "gender": "Male", "age": "6"}, { "name": "Kit", "gender": "Male", "age": "8"}], "citiesLived": [{"place": "Los Angeles", "yearsLived": ["1989", "1993", "1998", "2002"]}, {"place": "Washington DC", "yearsLived": ["1990", "1993", "1998", "2008"]}, {"place": "Portland", "yearsLived": ["1993", "1998", "2003", "2005"]}, {"place": "Austin", "yearsLived": ["1973", "1998", "2001", "2005"]}]},
      {"kind": "person", "fullName": "Anna Karenina", "age": 45, "gender": "Female", "phoneNumber": { "areaCode": "425", "number": "1984783"}, "citiesLived": [{"place": "Stockholm", "yearsLived": ["1992", "1998", "2000", "2010"]}, {"place": "Russia", "yearsLived": ["1998", "2001", "2005"]}, {"place": "Austin", "yearsLived": ["1995", "1999"]}]}
    ]
}

Copy

Qu’est-ce qu’Avro ?¶

Avro est un framework open source de sérialisation de données et de RPC développé à l’origine pour une utilisation avec Apache Hadoop. Il utilise des schémas définis dans JSON pour produire des données sérialisées dans un format binaire compact. Les données sérialisées peuvent être envoyées vers une destination quelconque (application ou programme) et peuvent être facilement désérialisées à la destination car le schéma est inclus dans les données.

Un schéma Avro est constitué d’une chaîne JSON, d’un objet ou d’un tableau qui définit le type de schéma et les attributs de données (noms de champ, types de données, etc.) pour ce type de schéma. Les attributs diffèrent selon le type de schéma. Les types de données complexes tels que les tableaux et les cartes sont pris en charge.

Snowflake lit les données Avro dans une seule colonne VARIANT. Vous pouvez interroger les données dans une colonne VARIANT comme vous le feriez avec des données JSON, en utilisant des commandes et fonctions similaires.

Pour plus d’informations, voir avro.apache.org.

Exemple d’un schéma Avro¶

{
 "type": "record",
 "name": "person",
 "namespace": "example.avro",
 "fields": [
     {"name": "fullName", "type": "string"},
     {"name": "age",  "type": ["int", "null"]},
     {"name": "gender", "type": ["string", "null"]}
     ]
}

Qu’est-ce que ORC ?¶

ORC (Optimized Row Columnar) est un format binaire utilisé pour stocker les données de Hive. ORC a été conçu pour une compression efficace et des performances améliorées de la lecture, de l’écriture et du traitement de données par rapport aux formats de fichiers Hive précédents. Pour plus d’informations sur ORC, voir https://orc.apache.org/.

Snowflake lit les données ORC dans une seule colonne VARIANT . Vous pouvez interroger les données dans une colonne VARIANT comme vous le feriez avec des données JSON, en utilisant des commandes et fonctions similaires.

Vous pouvez également extraire des colonnes depuis un fichier ORC en zone de préparation vers des colonnes de table séparées en utilisant une instruction CREATE TABLE AS SELECT.

Exemple de ORC données chargées dans une VARIANT colonne¶

+--------------------------------------+
| SRC                                  |
|--------------------------------------|
| {                                    |
|   "boolean1": false,                 |
|   "byte1": 1,                        |
|   "bytes1": "0001020304",            |
|   "decimal1": 12345678.654745,       |
|   "double1": -1.500000000000000e+01, |
|   "float1": 1.000000000000000e+00,   |
|   "int1": 65536,                     |
|   "list": [                          |
|     {                                |
|       "int1": 3,                     |
|       "string1": "good"              |
|     },                               |
|     {                                |
|       "int1": 4,                     |
|       "string1": "bad"               |
|     }                                |
|   ]                                  |
| }                                    |
+--------------------------------------+

Qu’est-ce que Parquet ?¶

Parquet est une représentation de données en colonnes compressée et efficaces conçue pour les projets dans l’écosystème Hadoop. Le format de fichier prend en charge les structures de données imbriquées complexes et utilise les algorithmes de déchiquetage et d’assemblage des enregistrements Dremel. Les fichiers Parquet ne peuvent pas être ouverts dans un éditeur de texte. Pour plus d’informations, voir parquet.apache.org/docs/.

En fonction de votre cas d’utilisation du chargement, Snowflake lit les données Parquet dans une seule colonne VARIANT ou directement dans les colonnes de la table (comme lorsque vous chargez des données à partir de fichiers Parquet compatibles avec Iceberg).

Vous pouvez interroger les données dans une colonne VARIANT comme vous le feriez avec des données JSON, en utilisant des commandes et fonctions similaires. Vous pouvez également extraire des colonnes sélectionnées d’un fichier Parquet préparé vers des colonnes de table séparées en utilisant une instruction CREATE TABLE AS SELECT.

Exemple de données Parquet chargées dans une colonne VARIANT¶

+------------------------------------------+
| SRC                                      |
|------------------------------------------|
| {                                        |
|   "continent": "Europe",                 |
|   "country": {                           |
|     "city": {                            |
|       "bag": [                           |
|         {                                |
|           "array_element": "Paris"       |
|         },                               |
|         {                                |
|           "array_element": "Nice"        |
|         },                               |
|         {                                |
|           "array_element": "Marseilles"  |
|         },                               |
|         {                                |
|           "array_element": "Cannes"      |
|         }                                |
|       ]                                  |
|     },                                   |
|     "name": "France"                     |
|   }                                      |
| }                                        |
+------------------------------------------+

Qu’est-ce que XML ?¶

XML (eXtensible Markup Language) est un langage de balisage qui définit un ensemble de règles pour l’encodage de documents. Il était à l’origine basé sur SGML, un autre langage de balisage développé pour normaliser la structure et les éléments qui composent un document.

Depuis son introduction, XML s’est développé au-delà de la focalisation initiale sur les documents afin d’intégrer un large éventail d’utilisations, y compris la représentation de structures de données arbitraires, et de servir de langue de base aux protocoles de communication. En raison de son extensibilité, de sa polyvalence et de sa facilité d’utilisation, il est devenu l’un des standards les plus utilisés pour l’échange de données sur le Web.

Un document XML se compose principalement des constructions suivantes :

• Balises (identifiées par des chevrons, < et >)

• Éléments

Les éléments sont généralement constitués d’une balise « start » et d’une balise « end » correspondante, le texte entre les balises constituant le contenu de l’élément. Un élément peut également être constitué d’une balise « empty-element » sans balise « end ». Les balises « start » et « empty-element » peuvent contenir des attributs, qui aident à définir les caractéristiques ou les métadonnées de l’élément.

Lorsque vous effectuez une requête sur les données XML, l’opérateur du signe du dollar ($) renvoie le contenu, sous la forme d’une valeur VARIANT, de la valeur sur laquelle il opère. Pour un élément, le contenu de cet élément est renvoyé :

• Si l’élément contient du texte, celui-ci est renvoyé sous la forme d’une valeur VARIANT.

• Si l’élément contient un autre élément, celui-ci est renvoyé sous la forme d’une valeur VARIANT au format XML.

• Si l’élément contient une série d’éléments, un tableau des éléments est retourné sous la forme d’une valeur VARIANT au format JSON.

Utilisez les opérateurs suivants pour accéder à la valeur VARIANT dans une requête :

• $ pour le contenu de la valeur.

• @ pour le nom de la valeur. Cet opérateur est utile lorsque vous parcourez des éléments portant des noms différents.

  Utilisez @attribute_name pour le contenu d’un attribut nommé. Par exemple, pour @attr, le nom de l’attribut est attr. La requête renvoie le contenu de l’attribut dont le nom suit directement l’esperluette. Si aucun attribut n’est trouvé, NULL est renvoyé.

Pour les exemples qui interrogent des données XML, voir Exemples d’interrogation de données XML.

Vous pouvez utiliser les fonctions suivantes pour travailler avec les données XML :

• CHECK_XML

• PARSE_XML

• TO_XML

• XMLGET

Exemples d’utilisation de XML¶

Les exemples suivants vous montrent comment charger et interroger des données XML.

<?xml version="1.0"?>
<!DOCTYPE parts system "parts.dtd">
<?xml-stylesheet type="text/css" href="xmlpartsstyle.css"?>
<parts>
   <part count="4">
      <item>Spark Plugs</item>
      <partnum>A3-400</partnum>
      <manufacturer>ABC company</manufacturer>
      <price units="dollar"> 27.00</price>
   </part>
   <part count="1">
      <item>Motor Oil</item>
      <partnum>B5-200</partnum>
      <source>XYZ company</source>
      <price units="dollar"> 14.00</price>
   </part>
   <part count="1">
      <item>Motor Oil</item>
      <partnum>B5-300</partnum>
      <source>XYZ company</source>
      <price units="dollar"> 16.75</price>
   </part>
   <part count="1">
      <item>Engine Coolant</item>
      <partnum>B6-120</partnum>
       <source>XYZ company</source>
      <price units="dollar"> 19.00</price>
   </part>
   <part count="1">
      <item>Engine Coolant</item>
      <partnum>B6-220</partnum>
      <source>XYZ company</source>
      <price units="dollar"> 18.25</price>
   </part>
</parts>

SELECT src FROM sample_xml_parts;

+----------------------------------------------+
| SRC                                          |
|----------------------------------------------|
| <parts>                                      |
|   <part count="4">                           |
|     <item>Spark Plugs</item>                 |
|     <partnum>A3-400</partnum>                |
|     <manufacturer>ABC company</manufacturer> |
|     <price units="dollar">27.00</price>      |
|   </part>                                    |
|   <part count="1">                           |
|     <item>Motor Oil</item>                   |
|     <partnum>B5-200</partnum>                |
|     <source>XYZ company</source>             |
|     <price units="dollar">14.00</price>      |
|   </part>                                    |
|   <part count="1">                           |
|     <item>Motor Oil</item>                   |
|     <partnum>B5-300</partnum>                |
|     <source>XYZ company</source>             |
|     <price units="dollar">16.75</price>      |
|   </part>                                    |
|   <part count="1">                           |
|     <item>Engine Coolant</item>              |
|     <partnum>B6-120</partnum>                |
|     <source>XYZ company</source>             |
|     <price units="dollar">19.00</price>      |
|   </part>                                    |
|   <part count="1">                           |
|     <item>Engine Coolant</item>              |
|     <partnum>B6-220</partnum>                |
|     <source>XYZ company</source>             |
|     <price units="dollar">18.25</price>      |
|   </part>                                    |
| </parts>                                     |
+----------------------------------------------+

SELECT src:"$" FROM sample_xml_parts;

+--------------------------------+
| SRC:"$"                        |
|--------------------------------|
| [                              |
|   {                            |
|     "$": [                     |
|       {                        |
|         "$": "Spark Plugs",    |
|         "@": "item"            |
|       },                       |
|       {                        |
|         "$": "A3-400",         |
|         "@": "partnum"         |
|       },                       |
|       {                        |
|         "$": "ABC company",    |
|         "@": "manufacturer"    |
|       },                       |
|       {                        |
|         "$": 27,               |
|         "@": "price",          |
|         "@units": "dollar"     |
|       }                        |
|     ],                         |
|     "@": "part",               |
|     "@count": 4,               |
|     "item": 0,                 |
|     "manufacturer": 2,         |
|     "partnum": 1,              |
|     "price": 3                 |
|   },                           |
|   {                            |
|     "$": [                     |
|       {                        |
|         "$": "Motor Oil",      |
|         "@": "item"            |
|       },                       |
|       {                        |
|         "$": "B5-200",         |
|         "@": "partnum"         |
|       },                       |
|       {                        |
|         "$": "XYZ company",    |
|         "@": "source"          |
|       },                       |
|       {                        |
|         "$": 14,               |
|         "@": "price",          |
|         "@units": "dollar"     |
|       }                        |
|     ],                         |
|     "@": "part",               |
|     "@count": 1,               |
|     "item": 0,                 |
|     "partnum": 1,              |
|     "price": 3,                |
|     "source": 2                |
|   },                           |
|                                |
|              ...               |
|                                |
+--------------------------------+

SELECT src:"@" FROM sample_xml_parts;

+---------+
| SRC:"@" |
|---------|
| "parts" |
+---------+

SELECT src:"$"[0]."@count", src:"$"[1]."@count" FROM sample_xml_parts;

+---------------------+---------------------+
| SRC:"$"[0]."@COUNT" | SRC:"$"[1]."@COUNT" |
|---------------------+---------------------|
| 4                   | 1                   |
+---------------------+---------------------+

SELECT XMLGET(src, 'part') FROM sample_xml_parts;

SELECT XMLGET(src, 'part', 0) FROM sample_xml_parts;

+--------------------------------------------+
| XMLGET(SRC, 'PART')                        |
|--------------------------------------------|
| <part count="4">                           |
|   <item>Spark Plugs</item>                 |
|   <partnum>A3-400</partnum>                |
|   <manufacturer>ABC company</manufacturer> |
|   <price units="dollar">27.00</price>      |
| </part>                                    |
+--------------------------------------------+

SELECT XMLGET(src, 'part', 3) FROM sample_xml_parts;

+---------------------------------------+
| XMLGET(SRC, 'PART', 3)                |
|---------------------------------------|
| <part count="1">                      |
|   <item>Engine Coolant</item>         |
|   <partnum>B6-120</partnum>           |
|   <source>XYZ company</source>        |
|   <price units="dollar">19.00</price> |
| </part>                               |
+---------------------------------------+

SELECT XMLGET(VALUE, 'item'):"$"::VARCHAR AS item,
       XMLGET(VALUE, 'partnum'):"$"::VARCHAR AS partnum,
       COALESCE(XMLGET(VALUE, 'manufacturer'):"$"::VARCHAR,
                XMLGET(VALUE, 'source'):"$"::VARCHAR) AS manufacturer_or_source,
       XMLGET(VALUE, 'price'):"$"::VARCHAR AS price,
  FROM sample_xml_parts,
    LATERAL FLATTEN(INPUT => SRC:"$");

+----------------+---------+------------------------+-------+
| ITEM           | PARTNUM | MANUFACTURER_OR_SOURCE | PRICE |
|----------------+---------+------------------------+-------|
| Spark Plugs    | A3-400  | ABC company            | 27    |
| Motor Oil      | B5-200  | XYZ company            | 14    |
| Motor Oil      | B5-300  | XYZ company            | 16.75 |
| Engine Coolant | B6-120  | XYZ company            | 19    |
| Engine Coolant | B6-220  | XYZ company            | 18.25 |
+----------------+---------+------------------------+-------+