Tratamiento de datos semiestructurados

Introducción a Snowflake SQL

George Boorman

Senior Curriculum Manager, DataCamp

Estructurado frente a semiestructurado

Ejemplo de datos estructurados

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

Ejemplo de datos semiestructurados

Datos semiestructurados de clientes en formato JSON, con el cliente 2 teniendo dos correos electrónicos.

Presentación de JSON

Notaciónde objetos**JavaScript**
Casos de uso habituales: API web y archivos de configuración.
Estructura de datos JSON:
- Pares clave-valor, por ejemplo: cust_id: 1

Ejemplo de registros JSON de clientes con ID de cliente, nombre, edad y correo electrónico.

JSON en Snowflake

Compatibilidad nativa con JSON
Flexible para esquemas en evolución

Comparaciones:

Postgres: Usos JSONB
Snowflake: Usos VARIANT

Cómo almacena Snowflake los datos JSON

VARIANT admite los tipos de datos OBJECT y ARRAY
- OBJETO: { "key": "value"}
- ARRAY: ["list", "of", "values"]

Creación de una tabla Snowflake para gestionar datos JSON

CREATE TABLE cust_info_json_data (
  customer_id INT,
  customer_info VARIANT -- Tipo de datos VARIANT
);

Funciones de datos semiestructurados

PARSE_JSON
- expr: Datos JSON en formato de cadena
- Devuelve: tipo VARIANT, objeto JSON válido.

PARSE_JSON

Ejemplo:

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

Resultado de PARSE_JSON

OBJECT_CONSTRUCT

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

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

$Tabla con la columna \`customer_info\` con datos almacenados en formato JSON$

Consultar datos JSON en Snowflake

JSON simple

:

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;

Resultado de la consulta que muestra la edad del cliente, el nombre del cliente y el correo electrónico en columnas separadas.

Consulta de datos JSON anidados en Snowflake

Ejemplo de JSON anidado

Datos JSON anidados en los que la dirección es un objeto que contiene pares clave-valor de calle, ciudad y estado

Colón: :
Punto: .

Consultar JSON anidado utilizando notaciones de dos puntos/puntos

Acceso a valores mediante la notación de dos puntos

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

SELECT 
    customer_info:address:street AS street_name 
FROM 
    cust_info_json_data

Resultado de la consulta mostrando street_name

Acceso a valores mediante notación de puntos

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

SELECT
    customer_info:address.street AS street_name
FROM
    cust_info_json_data

Resultado de la consulta mostrando street_name

¡Vamos a practicar!

Introducción a Snowflake SQL