Gestionarea datelor semi-structurate

Introducere în Snowflake SQL

George Boorman

Senior Curriculum Manager, DataCamp

Date structurate vs. semi-structurate

Exemplu de date structurate

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

Exemplu de date semi-structurate

Date semi-structurate ale clientului în format JSON, clientul 2 având două adrese de e-mail

Introducere în JSON

JavaScript Object Notation
Cazuri de utilizare frecvente: API-uri web și fișiere de configurare
Structura datelor JSON:
- Perechi cheie-valoare, de ex., cust_id: 1

Exemplu de înregistrări JSON ale clientului cu id, nume, vârstă și e-mail

JSON în Snowflake

Suport nativ pentru JSON
Flexibil pentru scheme în evoluție

Comparații:

Postgres: utilizează JSONB
Snowflake: utilizează VARIANT

Cum stochează Snowflake datele JSON

VARIANT acceptă tipurile de date OBJECT și ARRAY
- OBJECT: { "key": "value"}
- ARRAY: ["list", "of", "values"]

Crearea unui tabel Snowflake pentru date JSON

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

Funcții pentru date semi-structurate

PARSE_JSON
- expr: date JSON în format șir
- Returnează: tipul VARIANT, obiect JSON valid

PARSE_JSON

Exemplu:

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

Rezultatul funcției PARSE_JSON

OBJECT_CONSTRUCT

OBJECT_CONSTRUCT
- Sintaxă: OBJECT_CONSTRUCT( [<key1>, <value1> [, <keyN>, <valueN> ...]] )
- Returnează: obiect JSON

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

Tabel cu coloana `customer_info` cu date stocate în format JSON

Interogarea datelor JSON în Snowflake

JSON simplu

:

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;

Rezultatul interogării cu vârsta, numele și e-mailul clientului ca coloane separate

Interogarea datelor JSON imbricate în Snowflake

Exemplu de JSON imbricat

Date JSON imbricate în care adresa este un obiect cu perechi cheie-valoare pentru stradă, oraș și județ

Două puncte: :
Punct: .

Interogarea JSON imbricat cu notațiile două puncte/punct

Accesarea valorilor cu notația două puncte

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

SELECT 
    customer_info:address:street AS street_name 
FROM 
    cust_info_json_data

Rezultatul interogării cu coloana street_name

Accesarea valorilor cu notația punct

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

SELECT
    customer_info:address.street AS street_name
FROM
    cust_info_json_data

Rezultatul interogării cu coloana street_name

Să exersăm!

Introducere în Snowflake SQL