Traiter les données semi-structurées

Introduction à Snowflake SQL

George Boorman

Senior Curriculum Manager, DataCamp

Structurées vs semi-structurées

Exemple de données structurées

| cust_id | cust_name | cust_age | cust_email            |
|---------|-----------|----------|-----------------------|
| 1       | cust1     | 40       | cust1***@gmail.com    |
| 2       | cust2     | 35       | cust2***@gmail.com    |
| 3       | cust3     | 42       | cust3***@gmail.com    |

Exemple de données semi-structurées

Données client semi-structurées en JSON, où le client 2 a deux courriels

Présentation de JSON

JavaScript Object Notation
Cas d'usage courants : API Web et fichiers de config
Structure des données JSON :
- Paires clé-valeur, p. ex. cust_id: 1

Exemple d'enregistrements JSON clients avec identifiant, nom, âge et courriel

JSON dans Snowflake

Prise en charge native de JSON
Souple pour des schémas évolutifs

Comparaisons :

Postgres : utilise JSONB
Snowflake : utilise VARIANT

Comment Snowflake stocke les données JSON

VARIANT prend en charge les types OBJECT et ARRAY
- OBJECT : { "key": "value"}
- ARRAY : ["list", "of", "values"]

Créer une table Snowflake pour gérer des données JSON

CREATE TABLE cust_info_json_data (
  customer_id INT, 
  customer_info VARIANT -- VARIANT data type
);

Fonctions pour données semi-structurées

PARSE_JSON
- expr : données JSON au format chaîne
- Renvoie : type VARIANT, objet JSON valide

PARSE_JSON

Exemple :

SELECT PARSE_JSON(
  -- Enclosed in strings      
  '{
  "cust_id": 1,
  "cust_name": "cust1",
  "cust_age": 40,
  "cust_email":"cust1***@gmail.com"
  }
  '-- Enclosed in strings
) AS customer_info_json

Résultat de PARSE_JSON

OBJECT_CONSTRUCT

OBJECT_CONSTRUCT
- Syntaxe : OBJECT_CONSTRUCT( [<key1>, <value1> [, <keyN>, <valueN> ...]] )
- Renvoie : objet JSON

    SELECT OBJECT_CONSTRUCT(
      -- Comma separated values rather than : notation
          'cust_id', 1,
          'cust_name', 'cust1',
          'cust_age', 40,
          'cust_email', 'cust1***@gmail.com'
    )

Table avec la colonne `customer_info` dont les données sont en format JSON

Interroger des données JSON dans Snowflake

JSON simple

:

SELECT
  customer_info:cust_age, -- Use colon to access cust_age from column
  customer_info:cust_name,
  customer_info:cust_email,
FROM 
  cust_info_json_data;

Résultat de la requête affichant l'âge, le nom et le courriel comme colonnes distinctes

Interroger un JSON imbriqué dans Snowflake

Exemple de JSON imbriqué

Un JSON imbriqué où address est un objet avec les paires clé-valeur street, city et state

Deux-points : :
Point : .

Interroger un JSON imbriqué : notations deux-points/point

Accéder aux valeurs avec la notation par deux-points

<column>:<level1_element>:<level2_element>:<level3_element>

SELECT 
    customer_info:address:street AS street_name 
FROM 
    cust_info_json_data

Résultat de la requête montrant street_name

Accéder aux valeurs avec la notation par point

<column>:<level1_element>.<level2_element>.<level3_element>

SELECT
    customer_info:address.street AS street_name
FROM
    cust_info_json_data