Obsługa danych częściowo ustrukturyzowanych

Wprowadzenie do Snowflake SQL

George Boorman

Senior Curriculum Manager, DataCamp

Dane ustrukturyzowane a częściowo ustrukturyzowane

Przykład danych ustrukturyzowanych

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

Przykład danych częściowo ustrukturyzowanych

Częściowo ustrukturyzowane dane klientów w formacie JSON; klient 2 ma dwa adresy e-mail

Wprowadzenie do JSON

JavaScript Object Notation
Typowe zastosowania: Web API i pliki konfiguracyjne
Struktura danych JSON:
- Pary klucz-wartość, np. cust_id: 1

Przykładowe rekordy JSON klientów z identyfikatorem, imieniem, wiekiem i adresem e-mail

JSON w Snowflake

Natywna obsługa JSON
Elastyczność dla zmieniających się schematów

Porównanie:

Postgres: używa JSONB
Snowflake: używa VARIANT

Sposób przechowywania danych JSON w Snowflake

VARIANT obsługuje typy danych OBJECT i ARRAY
- OBJECT: { "key": "value"}
- ARRAY: ["list", "of", "values"]

Tworzenie tabeli Snowflake do przechowywania danych JSON

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

Funkcje dla danych częściowo ustrukturyzowanych

PARSE_JSON
- expr: dane JSON w formacie tekstowym
- Zwraca: typ VARIANT, prawidłowy obiekt JSON

PARSE_JSON

Przykład:

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

Wynik działania PARSE_JSON

OBJECT_CONSTRUCT

OBJECT_CONSTRUCT
- Składnia: OBJECT_CONSTRUCT( [<key1>, <value1> [, <keyN>, <valueN> ...]] )
- Zwraca: obiekt JSON

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

Tabela z kolumną `customer_info` zawierającą dane w formacie JSON

Zapytania o dane JSON w Snowflake

Prosty JSON

:

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;

Wynik zapytania z wiekiem, imieniem i adresem e-mail klienta jako osobne kolumny

Zapytania o zagnieżdżone dane JSON w Snowflake

Przykład zagnieżdżonego JSON

Zagnieżdżone dane JSON, gdzie adres jest obiektem z parami klucz-wartość: ulica, miasto i stan

Dwukropek: :
Kropka: .

Zapytania o zagnieżdżony JSON za pomocą notacji dwukropkowej/kropkowej

Dostęp do wartości za pomocą notacji dwukropkowej

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

SELECT 
    customer_info:address:street AS street_name 
FROM 
    cust_info_json_data

Wynik zapytania z kolumną street_name

Dostęp do wartości za pomocą notacji kropkowej

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

SELECT
    customer_info:address.street AS street_name
FROM
    cust_info_json_data

Wynik zapytania z kolumną street_name

Czas na ćwiczenia!

Wprowadzenie do Snowflake SQL