Práce s polostrukturovanými daty

Introduction to Snowflake SQL

George Boorman

Senior Curriculum Manager, DataCamp

Strukturovaná versus polostrukturovaná data

Příklad strukturovaných dat

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

Příklad polostrukturovaných dat

Polostrukturovaná data zákazníka ve formátu JSON, zákazník 2 má dva e-maily

Úvod do JSON

JavaScript Object Notation
Časté případy použití: webová API a konfigurační soubory
Struktura JSON dat:
- Páry klíč–hodnota, např. cust_id: 1

Příklad JSON záznamů zákazníků s id, jménem, věkem a e-mailem

JSON ve Snowflake

Nativní podpora JSON
Flexibilní pro měnící se schémata

Srovnání:

Postgres: používá JSONB
Snowflake: používá VARIANT

Jak Snowflake ukládá JSON data

VARIANT podporuje datové typy OBJECT a ARRAY
- OBJECT: { "key": "value"}
- ARRAY: ["list", "of", "values"]

Vytvoření tabulky ve Snowflake pro práci s JSON

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

Funkce pro polostrukturovaná data

PARSE_JSON
- expr: JSON data ve formátu řetězce
- Vrací: typ VARIANT, platný objekt JSON

PARSE_JSON

Příklad:

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

Výsledek funkce PARSE_JSON

OBJECT_CONSTRUCT

OBJECT_CONSTRUCT
- Syntaxe: OBJECT_CONSTRUCT( [<key1>, <value1> [, <keyN>, <valueN> ...]] )
- Vrací: objekt JSON

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

Tabulka se sloupcem `customer_info` obsahujícím data ve formátu JSON

Dotazování na data JSON ve Snowflake

Jednoduchý 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;

Výsledek dotazu zobrazující věk, jméno a e-mail zákazníka jako samostatné sloupce

Dotazování na vnořená JSON data ve Snowflake

Příklad vnořeného JSON

Vnořená JSON data, kde adresa je objekt s páry klíč–hodnota: ulice, město a stát

Dvojtečka: :
Tečka: .

Dotazování na vnořený JSON pomocí zápisu dvojtečkou/tečkou

Přístup k hodnotám pomocí zápisu dvojtečkou

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

SELECT 
    customer_info:address:street AS street_name 
FROM 
    cust_info_json_data

Výsledek dotazu zobrazující street_name

Přístup k hodnotám pomocí tečkového zápisu

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

SELECT
    customer_info:address.street AS street_name
FROM
    cust_info_json_data

Výsledek dotazu zobrazující street_name

Pojďme procvičovat!

Introduction to Snowflake SQL