Werken met semi-gestructureerde data

Introductie tot Snowflake SQL

George Boorman

Senior Curriculum Manager, DataCamp

Gestructureerd versus semi-gestructureerd

Voorbeeld van gestructureerde data

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

Voorbeeld van semi-gestructureerde data

Semi-gestructureerde klantdata als JSON, waarbij klant 2 twee e-mails heeft

Introductie tot JSON

JavaScript Object Notation
Veelvoorkomende use-cases: web-API's en configbestanden
JSON-datastructuur:
- Key-valueparen, bijv. cust_id: 1

Voorbeeld van klant-JSON met id, naam, leeftijd en e-mail

JSON in Snowflake

Native JSON-ondersteuning
Flexibel voor evoluerende schema's

Vergelijking:

Postgres: gebruikt JSONB
Snowflake: gebruikt VARIANT

Hoe Snowflake JSON opslaat

VARIANT ondersteunt OBJECT- en ARRAY-typen
- OBJECT: { "key": "value"}
- ARRAY: ["list", "of", "values"]

Een Snowflake-tabel maken voor JSON-data

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

Functies voor semi-gestructureerde data

PARSE_JSON
- expr: JSON-data als string
- Geeft terug: type VARIANT, geldig JSON-object

PARSE_JSON

Voorbeeld:

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

Resultaat van PARSE_JSON

OBJECT_CONSTRUCT

OBJECT_CONSTRUCT
- Syntax: OBJECT_CONSTRUCT( [<key1>, <value1> [, <keyN>, <valueN> ...]] )
- Geeft terug: JSON-object

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

Tabel met kolom `customer_info` met data in JSON-indeling

JSON-data bevragen in Snowflake

Eenvoudige JSON

:

SELECT
  customer_info:cust_age, -- Gebruik dubbelepunt om cust_age uit kolom te halen
  customer_info:cust_name,
  customer_info:cust_email,
FROM 
  cust_info_json_data;

Queryresultaat met leeftijd, naam en e-mail als aparte kolommen

Geneste JSON bevragen in Snowflake

Voorbeeld van geneste JSON

Een geneste JSON waarin address een object is met key-valueparen street, city en state

Dubbelepunt: :
Punt: .

Geneste JSON bevragen met dubbelepunt/puntnotatie

Waarden benaderen met dubbelepuntnotatie

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

SELECT 
    customer_info:address:street AS street_name 
FROM 
    cust_info_json_data

Queryresultaat met street_name

Waarden benaderen met puntnotatie

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

SELECT
    customer_info:address.street AS street_name
FROM
    cust_info_json_data

Queryresultaat met street_name

Laten we oefenen!

Introductie tot Snowflake SQL