Работа с полуструктурированными данными

Введение в Snowflake SQL

George Boorman

Senior Curriculum Manager, DataCamp

Структурированные и полуструктурированные данные

Пример структурированных данных

| 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 указано два адреса электронной почты

Знакомство с JSON

JavaScript Object Notation
Типичные сценарии применения: Web API и конфигурационные файлы
Структура JSON:
- Пары ключ-значение, например cust_id: 1

Пример JSON-записей клиентов с идентификатором, именем, возрастом и email

JSON в Snowflake

Встроенная поддержка JSON
Гибкость при изменении схем

Сравнение:

Postgres: использует JSONB
Snowflake: использует VARIANT

Хранение JSON-данных в Snowflake

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, -- Use colon to access cust_age from column
  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