Interrogation de données semi-structurées

Snowflake prend en charge les requêtes SQL qui accèdent à des données semi-structurées en utilisant des fonctions et des opérateurs spéciaux. Notez que ce chapitre s’applique aux données JSON, Avro, ORC et Parquet. Le chapitre ne s’applique pas aux données XML.

Dans ce chapitre :

Échantillon de données utilisé dans des exemples

Sauf indication contraire, les exemples de ce chapitre se réfèrent à un tableau nommé car_sales qui contient une seule colonne VARIANT nommée src :

CREATE OR REPLACE TABLE car_sales
(
  src variant
)
AS
SELECT PARSE_JSON(column1) AS src
FROM VALUES
('{
    "date" : "2017-04-28",
    "dealership" : "Valley View Auto Sales",
    "salesperson" : {
      "id": "55",
      "name": "Frank Beasley"
    },
    "customer" : [
      {"name": "Joyce Ridgely", "phone": "16504378889", "address": "San Francisco, CA"}
    ],
    "vehicle" : [
      {"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
    ]
}'),
('{
    "date" : "2017-04-28",
    "dealership" : "Tindel Toyota",
    "salesperson" : {
      "id": "274",
      "name": "Greg Northrup"
    },
    "customer" : [
      {"name": "Bradley Greenbloom", "phone": "12127593751", "address": "New York, NY"}
    ],
    "vehicle" : [
      {"make": "Toyota", "model": "Camry", "year": "2017", "price": "23500", "extras":["ext warranty", "rust proofing", "fabric protection"]}
    ]
}') v;

SELECT * FROM car_sales;

+-------------------------------------------+
| SRC                                       |
|-------------------------------------------|
| {                                         |
|   "customer": [                           |
|     {                                     |
|       "address": "San Francisco, CA",     |
|       "name": "Joyce Ridgely",            |
|       "phone": "16504378889"              |
|     }                                     |
|   ],                                      |
|   "date": "2017-04-28",                   |
|   "dealership": "Valley View Auto Sales", |
|   "salesperson": {                        |
|     "id": "55",                           |
|     "name": "Frank Beasley"               |
|   },                                      |
|   "vehicle": [                            |
|     {                                     |
|       "extras": [                         |
|         "ext warranty",                   |
|         "paint protection"                |
|       ],                                  |
|       "make": "Honda",                    |
|       "model": "Civic",                   |
|       "price": "20275",                   |
|       "year": "2017"                      |
|     }                                     |
|   ]                                       |
| }                                         |
| {                                         |
|   "customer": [                           |
|     {                                     |
|       "address": "New York, NY",          |
|       "name": "Bradley Greenbloom",       |
|       "phone": "12127593751"              |
|     }                                     |
|   ],                                      |
|   "date": "2017-04-28",                   |
|   "dealership": "Tindel Toyota",          |
|   "salesperson": {                        |
|     "id": "274",                          |
|     "name": "Greg Northrup"               |
|   },                                      |
|   "vehicle": [                            |
|     {                                     |
|       "extras": [                         |
|         "ext warranty",                   |
|         "rust proofing",                  |
|         "fabric protection"               |
|       ],                                  |
|       "make": "Toyota",                   |
|       "model": "Camry",                   |
|       "price": "23500",                   |
|       "year": "2017"                      |
|     }                                     |
|   ]                                       |
| }                                         |
+-------------------------------------------+

Parcours de données semi-structurées

Insérez deux-points : entre le nom de la colonne VARIANT et un élément de premier niveau quelconque : <colonne>:<élément_niveau1>.

Note

Dans les exemples suivants, la sortie de requête est entourée de guillemets doubles, car la sortie de la requête est VARIANT et non VARCHAR. (Les valeurs VARIANT ne sont pas des chaînes ; les valeurs VARIANT contiennent des chaînes.) Les opérateurs :, . et [] renvoient toujours des valeurs VARIANT contenant des chaînes.

Par exemple, obtenez une liste de tous les noms de concessionnaire :

SELECT src:dealership FROM car_sales;

+--------------------------+
| SRC:DEALERSHIP           |
|--------------------------|
| "Valley View Auto Sales" |
| "Tindel Toyota"          |
+--------------------------+

Note

Les chaînes Snowflake, et donc les valeurs VARIANT qui contiennent des chaînes en tant que clés ou valeurs, acceptent le caractère '\x00'. Toutefois, dans un chemin, le caractère de fin '\x00' est ignoré. Par exemple, le code suivant contient une clé se terminant par '\x00' mais pouvant être trouvée sans le '\x00' :

CREATE OR REPLACE TABLE double0 (variant1 VARIANT);
INSERT INTO double0 (variant1) SELECT PARSE_JSON(' { "key1\x00":"value1" } ');
SELECT GET_PATH(variant1, 'key1')  FROM double0;
+----------------------------+
| GET_PATH(VARIANT1, 'KEY1') |
|----------------------------|
| "value1"                   |
+----------------------------+

Snowflake recommande d’éviter les caractères '\x00' incorporés dans les clés de données semi-structurées.

Il existe deux façons d’accéder aux éléments d’un JSON objet :

Important

Quelle que soit la notation que vous utilisez, le nom de colonne n’est pas sensible à la casse, mais les noms d’élément le sont. Par exemple, dans la liste suivante, les deux premiers chemins sont équivalents, mais pas le troisième :

  • src:salesperson.name

  • SRC:salesperson.name

  • SRC:Salesperson.Name

Notation par points

Utilisez la notation par points pour parcourir un chemin dans un objet JSON : <colonne>:<élément_niveau1>.<élément_niveau2>.<élément_niveau3>. Vous pouvez aussi inclure les noms d’élément entre guillemets doubles : <colonne>:"<élément_niveau1>"."<élément_niveau2>"."<élément_niveau3>".

Note

Les règles pour les clés JSON (noms d’élément) sont différentes des règles pour les identificateurs SQL Snowflake.

Pour plus d’informations sur les règles concernant les identificateurs SQL de Snowflake, voir : Exigences relatives à l’identificateur.

Pour plus d’informations sur les clés JSON, voir http://json.org, en particulier la description d’une « chaîne ».

Si le nom d’un élément n’est pas conforme aux règles d’identification SQL de Snowflake, par exemple s’il contient des espaces, alors vous devez mettre le nom entre guillemets. Vous trouverez ci-dessous quelques exemples (qui ne proviennent pas tous de l’exemple car_sales ci-dessus) de noms d’élément JSON valides qui ne sont pas des noms d’identificateurs Snowflake valides, sauf s’ils sont entourés de guillemets :

-- This contains a blank.
SELECT src:"company name" FROM partners;

-- This does not start with a letter or underscore.
SELECT zipcode_info:"94987" FROM addresses;

-- This contains characters that are not letters, digits, or underscores, and
-- it does not start with a letter or underscore.
SELECT measurements:"#sPerSquareInch" FROM english_metrics;

Obtenez les noms de tous les vendeurs qui ont vendu des voitures :

SELECT src:salesperson.name FROM car_sales;

+----------------------+
| SRC:SALESPERSON.NAME |
|----------------------|
| "Frank Beasley"      |
| "Greg Northrup"      |
+----------------------+

Notation par crochets

Vous pouvez également utiliser la notation par crochets pour parcourir le chemin d’un objet : <colonne>['<élément_niveau1>']['<élément_niveau2>']. Mettez les noms d’élément entre guillemets simples. Les valeurs sont récupérées sous forme de chaînes.

Obtenez les noms de tous les vendeurs qui ont vendu des voitures :

SELECT src['salesperson']['name'] FROM car_sales;

+----------------------------+
| SRC['SALESPERSON']['NAME'] |
|----------------------------|
| "Frank Beasley"            |
| "Greg Northrup"            |
+----------------------------+

Récupération d’une instance unique d’un élément répétitif

Récupérez une instance numérotée spécifique d’un élément enfant dans un tableau répétitif en ajoutant un prédicat numéroté (à partir de 0) à la référence de tableau.

Notez que pour récupérer toutes instances d’un tableau enfant dans un tableau répétitif, il est nécessaire d’aplatir l’ensemble. Voir un exemple dans Utilisation de la fonction FLATTEN pour analyser des tableaux dans ce chapitre.

Obtenez les détails du véhicule pour chaque vente :

SELECT src:vehicle[0] FROM car_sales;

+-------------------------+
| SRC:VEHICLE[0]          |
|-------------------------|
| {                       |
|   "extras": [           |
|     "ext warranty",     |
|     "paint protection"  |
|   ],                    |
|   "make": "Honda",      |
|   "model": "Civic",     |
|   "price": "20275",     |
|   "year": "2017"        |
| }                       |
| {                       |
|   "extras": [           |
|     "ext warranty",     |
|     "rust proofing",    |
|     "fabric protection" |
|   ],                    |
|   "make": "Toyota",     |
|   "model": "Camry",     |
|   "price": "23500",     |
|   "year": "2017"        |
| }                       |
+-------------------------+

Obtenez le prix de chaque voiture vendue :

SELECT src:vehicle[0].price FROM car_sales;

+----------------------+
| SRC:VEHICLE[0].PRICE |
|----------------------|
| "20275"              |
| "23500"              |
+----------------------+

Valeurs de conversion explicites

Par défaut, les valeurs extraites d’une colonne VARIANT sont retournées sous forme de littéraux de chaîne. Pour éviter des résultats inattendus, il est recommandé de convertir explicitement les valeurs vers le type de données souhaité (à l’aide de la notation ::).

Obtenez la liste des ID des vendeurs sous forme de valeurs de chaîne :

SELECT src:salesperson.id::string FROM car_sales;

+----------------------------+
| SRC:SALESPERSON.ID::STRING |
|----------------------------|
| 55                         |
| 274                        |
+----------------------------+

Utilisation de la fonction FLATTEN pour analyser des tableaux

Analysez un tableau à l’aide de la fonction FLATTEN. FLATTEN est une fonction de table qui produit une vue latérale d’une colonne VARIANT, OBJECT ou ARRAY. La fonction retourne une ligne pour chaque objet, et le modificateur LATERAL joint les données avec toute information en dehors de l’objet.

Obtenez les noms et adresses de tous les clients. Convertissez la sortie VARIANT en valeurs de chaîne :

SELECT
  value:name::string as "Customer Name",
  value:address::string as "Address"
  FROM
    car_sales
  , LATERAL FLATTEN(INPUT => SRC:customer);

+--------------------+-------------------+
| Customer Name      | Address           |
|--------------------+-------------------|
| Joyce Ridgely      | San Francisco, CA |
| Bradley Greenbloom | New York, NY      |
+--------------------+-------------------+

Utilisation de la fonction FLATTEN pour analyser des tableaux imbriqués

Le tableau extras est imbriqué dans le tableau vehicle dans les données d’échantillon :

"vehicle" : [
     {"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
   ]

Ajoutez une deuxième clause FLATTEN pour aplatir le tableau extras à l’intérieur du tableau vehicle aplati et récupérer les « extras » achetés pour chaque voiture vendue :

SELECT
  vm.value:make::string as make,
  vm.value:model::string as model,
  ve.value::string as "Extras Purchased"
  FROM
    car_sales
  , LATERAL FLATTEN(INPUT => SRC:vehicle) vm
  , LATERAL FLATTEN(INPUT => vm.value:extras) ve;

  +--------+-------+-------------------+
  | MAKE   | MODEL | Extras Purchased  |
  |--------+-------+-------------------|
  | Honda  | Civic | ext warranty      |
  | Honda  | Civic | paint protection  |
  | Toyota | Camry | ext warranty      |
  | Toyota | Camry | rust proofing     |
  | Toyota | Camry | fabric protection |
  +--------+-------+-------------------+

Analyse de texte sous forme de valeurs VARIANT à l’aide de la fonction PARSE_JSON

Analysez un texte comme un document JSON en utilisant la fonction PARSE_JSON.

Si l’entrée est NULL, la sortie sera également NULL. Cependant, si la chaîne d’entrée est null, elle est interprétée comme une valeur VARIANT null. C’est-à-dire que le résultat n’est pas un SQL NULL, mais une valeur réelle utilisée pour représenter une valeur nulle dans des formats semi-structurés.

Pour un exemple, voir Échantillon de données utilisé dans des exemples dans ce chapitre.

Extraction de valeurs à l’aide de la fonction GET

GET accepte une valeur VARIANT, OBJECT ou ARRAY comme premier argument et extrait la valeur VARIANT de l’élément dans le chemin fourni comme second argument.

Calculez et extrayez le dernier élément de chaque tableau dans une colonne VARIANT en utilisant les fonctions GET et ARRAY_SIZE. ARRAY_SIZE renvoie la taille du tableau d’entrée :

Note

Cet exemple s’écarte de la table car_sales utilisée ailleurs dans ce chapitre.

CREATE OR replace TABLE colors (v variant);

INSERT INTO
   colors
   SELECT
      parse_json(column1) AS v
   FROM
   VALUES
     ('[{r:255,g:12,b:0},{r:0,g:255,b:0},{r:0,g:0,b:255}]'),
     ('[{c:0,m:1,y:1,k:0},{c:1,m:0,y:1,k:0},{c:1,m:1,y:0,k:0}]')
    v;

SELECT *, GET(v, ARRAY_SIZE(v)-1) FROM colors;

+---------------+-------------------------+
| V             | GET(V, ARRAY_SIZE(V)-1) |
|---------------+-------------------------|
| [             | {                       |
|   {           |   "b": 255,             |
|     "b": 0,   |   "g": 0,               |
|     "g": 12,  |   "r": 0                |
|     "r": 255  | }                       |
|   },          |                         |
|   {           |                         |
|     "b": 0,   |                         |
|     "g": 255, |                         |
|     "r": 0    |                         |
|   },          |                         |
|   {           |                         |
|     "b": 255, |                         |
|     "g": 0,   |                         |
|     "r": 0    |                         |
|   }           |                         |
| ]             |                         |
| [             | {                       |
|   {           |   "c": 1,               |
|     "c": 0,   |   "k": 0,               |
|     "k": 0,   |   "m": 1,               |
|     "m": 1,   |   "y": 0                |
|     "y": 1    | }                       |
|   },          |                         |
|   {           |                         |
|     "c": 1,   |                         |
|     "k": 0,   |                         |
|     "m": 0,   |                         |
|     "y": 1    |                         |
|   },          |                         |
|   {           |                         |
|     "c": 1,   |                         |
|     "k": 0,   |                         |
|     "m": 1,   |                         |
|     "y": 0    |                         |
|   }           |                         |
| ]             |                         |
+---------------+-------------------------+

Extraction de valeurs par chemin à l’aide de la fonction GET_PATH

Extrayez une valeur d’une colonne VARIANT à l’aide de la fonction GET_PATH , :. La fonction est une variation de GET utilisée pour extraire une valeur en utilisant un nom de chemin. GET_PATH est équivalent à une chaîne de fonctions GET.

Obtenez la marque de la voiture achetée par chaque client :

SELECT GET_PATH(src, 'vehicle[0]:make') FROM car_sales;

+----------------------------------+
| GET_PATH(SRC, 'VEHICLE[0]:MAKE') |
|----------------------------------|
| "Honda"                          |
| "Toyota"                         |
+----------------------------------+

Parcours de données semi-structurées décrit la syntaxe de chemin utilisée pour récupérer des éléments d’une colonne VARIANT. La syntaxe est abrégée pour la fonction GET ou GET_PATH , :. Contrairement à la syntaxe de chemin, ces fonctions peuvent gérer des chemins irréguliers ou des éléments de chemin.

Les requêtes suivantes produisent les mêmes résultats :

SELECT GET_PATH(src, 'vehicle[0].make') FROM car_sales;

SELECT src:vehicle[0].make FROM car_sales;

Analyse de tableaux directement depuis un fichier de données préparé

Supposons qu’un fichier préparé nommé contacts.json.gz contienne les données suivantes :

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

Supposons également qu’un format de fichier nommé my_json_format inclut TYPE=JSON dans sa définition.

Interrogez le nom du premier employé dans le fichier mis en zone de préparation. Dans cet exemple, le fichier est situé dans la zone de préparation de table customers, mais il peut être situé dans n’importe quelle zone de préparation interne (c-à-d. Snowflake) ou externe :

SELECT 'The First Employee Record is '||
    S.$1:root[0].employees[0].firstName||
    ' '||S.$1:root[0].employees[0].lastName
FROM @%customers/contacts.json.gz (file_format => 'my_json_format') as S;

+----------------------------------------------+
| 'THE FIRST EMPLOYEE RECORD IS '||            |
|      S.$1:ROOT[0].EMPLOYEES[0].FIRSTNAME||   |
|      ' '||S.$1:ROOT[0].EMPLOYEES[0].LASTNAME |
|----------------------------------------------|
| The First Employee Record is Anna Smith      |
+----------------------------------------------+