Lidando com dados semiestruturados

Introdução ao Snowflake SQL

George Boorman

Senior Curriculum Manager, DataCamp

Estruturado vs. semiestruturado

Exemplo de dados estruturados

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

Exemplo de dados semiestruturados

Dados de clientes semiestruturados em JSON, com o cliente 2 tendo dois e-mails

Introdução ao JSON

JavaScript Object Notation
Usos comuns: Web APIs e arquivos de config
Estrutura do JSON:
- Pares chave-valor, ex.: cust_id: 1

Exemplo de registros JSON de cliente com id, nome, idade e email

JSON no Snowflake

Suporte nativo a JSON
Flexível para esquemas em evolução

Comparações:

Postgres: usa JSONB
Snowflake: usa VARIANT

Como o Snowflake armazena JSON

VARIANT suporta tipos OBJECT e ARRAY
- OBJECT: { "key": "value"}
- ARRAY: ["list", "of", "values"]

Criando uma tabela Snowflake para lidar com JSON

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

Funções para dados semiestruturados

PARSE_JSON
- expr: dados JSON em string
- Retorna: tipo VARIANT, objeto JSON válido

PARSE_JSON

Exemplo:

SELECT PARSE_JSON(
  -- Entre aspas      
  '{
  "cust_id": 1,
  "cust_name": "cust1",
  "cust_age": 40,
  "cust_email":"cust1***@gmail.com"
  }
  '-- Entre aspas
) AS customer_info_json

Resultado de PARSE_JSON

OBJECT_CONSTRUCT

OBJECT_CONSTRUCT
- Sintaxe: OBJECT_CONSTRUCT( [<key1>, <value1> [, <keyN>, <valueN> ...]] )
- Retorna: objeto JSON

    SELECT OBJECT_CONSTRUCT(
      -- Valores separados por vírgula, não por :
          'cust_id', 1,
          'cust_name', 'cust1',
          'cust_age', 40,
          'cust_email', 'cust1***@gmail.com'
    )

Tabela com a coluna `customer_info` com dados em formato JSON

Consultando dados JSON no Snowflake

JSON simples

:

SELECT
  customer_info:cust_age, -- Use dois-pontos para acessar cust_age da coluna
  customer_info:cust_name,
  customer_info:cust_email,
FROM 
  cust_info_json_data;

Resultado da consulta mostrando idade, nome e email como colunas separadas

Consultando JSON aninhado no Snowflake

Exemplo de JSON aninhado

Um JSON aninhado onde address é um objeto com os pares chave-valor street, city e state

Dois-pontos: :
Ponto: .

Consultando JSON aninhado com dois-pontos/ponto

Acessando valores com dois-pontos

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

SELECT 
    customer_info:address:street AS street_name 
FROM 
    cust_info_json_data

Resultado da consulta mostrando street_name

Acessando valores com ponto

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

SELECT
    customer_info:address.street AS street_name
FROM
    cust_info_json_data

Resultado da consulta mostrando street_name

Vamos praticar!

Introdução ao Snowflake SQL