Робота з напівструктурованими даними

Вступ до Snowflake SQL

George Boorman

Senior Curriculum Manager, DataCamp

Структуровані vs напівструктуровані

Приклад структурованих даних

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

Приклад напівструктурованих даних

Напівструктуровані дані про клієнтів у форматі JSON; у клієнта 2 два email-и

Знайомство з JSON

JavaScript Object Notation
Поширені випадки: веб-API та конфіг-файли
Структура JSON:
- Пари «ключ–значення», напр., cust_id: 1

Приклад записів JSON з id, ім'ям, віком і email клієнта

JSON у Snowflake

Вбудована підтримка JSON
Гнучкий для змінних схем

Порівняння:

Postgres: використовує JSONB
Snowflake: використовує VARIANT

Як Snowflake зберігає JSON-дані

VARIANT підтримує типи даних OBJECT і ARRAY
- OBJECT: { "key": "value"}
- ARRAY: ["list", "of", "values"]

Створення таблиці Snowflake для JSON-даних

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

Функції для напівструктурованих даних

PARSE_JSON
- expr: JSON-дані у вигляді рядка
- Повертає: тип VARIANT, коректний JSON-обʼєкт

PARSE_JSON

Приклад:

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

Результат PARSE_JSON

OBJECT_CONSTRUCT

OBJECT_CONSTRUCT
- Синтаксис: OBJECT_CONSTRUCT( [<key1>, <value1> [, <keyN>, <valueN> ...]] )
- Повертає: JSON-обʼєкт

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

Таблиця зі стовпцем `customer_info` з даними у форматі JSON

Запити до JSON у Snowflake

Простий JSON

:

SELECT
  customer_info:cust_age, -- Використайте двокрапку, щоб звернутися до cust_age зі стовпця
  customer_info:cust_name,
  customer_info:cust_email,
FROM 
  cust_info_json_data;

Результат запиту з віком, імʼям і email клієнта в окремих стовпцях

Запити до вкладеного JSON у Snowflake

Приклад вкладеного JSON

Вкладені JSON-дані, де address — це обʼєкт із парами ключ–значення street, city і state

Двокрапка: :
Крапка: .

Запити до вкладеного JSON: двокрапки й крапки

Доступ за допомогою нотації з двокрапками

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

SELECT 
    customer_info:address:street AS street_name 
FROM 
    cust_info_json_data

Результат запиту зі street_name

Доступ за допомогою нотації з крапками

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

SELECT
    customer_info:address.street AS street_name
FROM
    cust_info_json_data

Результат запиту зі street_name

Давайте потренуємось!

Вступ до Snowflake SQL