7. Trabajo con Datos Semi estructurados, vistas y Joins

Esta sección requiere la carga de datos adicionales y, por lo tanto, proporciona una revisión de la carga de datos al mismo tiempo que presenta la carga de datos semiestructurados.

Volviendo al ejemplo del laboratorio, el equipo de análisis de Citi Bike quiere determinar cómo el clima afecta el conteo de viajes. Para ello, en este apartado vamos a:

Cargue datos meteorológicos en formato JSON semiestructurado en un depósito público de S3.

Cree una vista y consulte los datos JSON mediante la notación de puntos SQL.

Ejecute una consulta que una los datos JSON con los datos de TRIPS cargados previamente.

Analice el clima y los datos de conteo de viajes para determinar su relación.

Los datos JSON consisten en información meteorológica proporcionada por OpenWeatherMap que detalla las condiciones históricas de la ciudad de Nueva York desde 2016-07-05 hasta 2019-06-25. También se organiza en AWS S3, donde los datos constan de 57,9k filas, 61 objetos y 2,5 MB comprimidos. Si se ve en un editor de texto, el JSON sin procesar en los archivos GZ se ve así:

DATOS SEMIESTRUCTURADOS Snowflake puede cargar y consultar fácilmente datos semiestructurados como JSON, Parquet o Avro sin transformación. Esta es una característica clave de Snowflake porque una cantidad cada vez mayor de datos relevantes para el negocio que se generan hoy en día están semiestructurados, y muchos DataWarehouses de datos tradicionales no pueden cargar y consultar dichos datos fácilmente. ¡Snowflake lo hace fácil!

Crear una nueva base de datos y tabla para los datos

Primero, en la hoja de trabajo, vamos a crear una base de datos llamada WEATHER para usarla para almacenar los datos JSON semiestructurados.

create database weather;

Ejecute los siguientes comandos USE para establecer el contexto de la hoja de trabajo de manera adecuada:

use role sysadmin;
use warehouse compute_wh;
use database weather;
use schema public;

Ejecución de múltiples comandos Recuerde que necesita ejecutar cada comando individualmente. Sin embargo, puede ejecutarlos juntos en secuencia seleccionando todos los comandos y luego haciendo clic en el botón Reproducir/Ejecutar (o usando el atajo de teclado).

A continuación, creemos una tabla llamada JSON_WEATHER_DATA para usarla para cargar los datos JSON. En la hoja de trabajo, ejecute el siguiente comando CREAR TABLA:

create table json_weather_data (v variant);

Tenga en cuenta que Snowflake tiene un tipo de datos de columna especial llamado VARIANT que permite almacenar todo el objeto JSON como una sola fila y, finalmente, consultar el objeto directamente.

Magia de datos semiestructurados El tipo de datos VARIANT permite a Snowflake ingerir datos semiestructurados sin tener que predefinir el esquema.

En el panel de resultados en la parte inferior de la hoja de trabajo, verifique que se haya creado su tabla, JSON_WEATHER_DATA:

 Crear otra etapa externa

En la hoja de trabajo CITIBIKE_ZERO_TO_SNOWFLAKE, use el siguiente comando para crear una etapa que apunte al depósito donde se almacenan los datos JSON semiestructurados en AWS S3:

create stage nyc_weather
url = 's3://snowflake-workshop-lab/weather-nyc;

Ahora echemos un vistazo a los contenidos de la etapa nyc_weather. Ejecute el siguiente comando LIST para mostrar la lista de archivos:

list @nyc_weather;

En el panel de resultados, debería ver una lista de archivos .gz de S3:

Cargue y verifique los datos semiestructurados

En esta sección, usaremos un DataWarehouse para cargar los datos del depósito S3 en la tabla JSON_WEATHER_DATA que creamos anteriormente.

En la hoja de cálculo CITIBIKE_ZERO_TO_SNOWFLAKE, ejecute el siguiente comando COPY para cargar los datos.

Tenga en cuenta que puede especificar un objeto FORMATO DE ARCHIVO en línea en el comando. En la sección anterior, donde cargamos datos estructurados en formato CSV, tuvimos que definir un formato de archivo para admitir la estructura CSV. Debido a que los datos JSON aquí están bien formados, podemos simplemente especificar el tipo JSON y usar todas las configuraciones predeterminadas:

copy into json_weather_data
from @nyc_weather
file_format = (type=json);

Verifique que cada archivo tenga un estado de CARGADO:

Ahora, echemos un vistazo a los datos que se cargaron:

select * from json_weather_data limit 10;

Haga clic en cualquiera de las filas para mostrar el JSON formateado en el panel derecho:

Para cerrar la pantalla en el panel y volver a mostrar los detalles de la consulta, haga clic en el botón X (Cerrar) que aparece cuando pasa el mouse por la esquina derecha del panel.

Crear una vista y consultar datos semiestructurados

A continuación, veamos cómo Snowflake nos permite crear una vista y también consultar los datos JSON directamente mediante SQL.

Vistas y Vistas Materializadas Una vista permite acceder al resultado de una consulta como si fuera una tabla. Las vistas pueden ayudar a presentar datos a los usuarios finales de una manera más clara, limitar lo que los usuarios finales pueden ver en una tabla de origen y escribir SQL más modular. Snowflake también admite vistas materializadas en las que los resultados de la consulta se almacenan como si fueran una tabla. Esto permite un acceso más rápido, pero requiere espacio de almacenamiento. Las vistas materializadas se pueden crear y consultar si está utilizando Snowflake Enterprise Edition (o superior).

Ejecute el siguiente comando para crear una vista en columnas de los datos meteorológicos JSON semiestructurados para que los analistas los comprendan y consulten más fácilmente. El valor 5128638 para city_id corresponde a la ciudad de Nueva York.

create view json_weather_data_view as
select
v:time::timestamp as observation_time,
v:city.id::int as city_id,
v:city.name::string as city_name,
v:city.country::string as country,
v:city.coord.lat::float as city_lat,
v:city.coord.lon::float as city_lon,
v:clouds.all::int as clouds,
(v:main.temp::float)-273.15 as temp_avg,
(v:main.temp_min::float)-273.15 as temp_min,
(v:main.temp_max::float)-273.15 as temp_max,
v:weather[0].main::string as weather,
v:weather[0].description::string as weather_desc,
v:weather[0].icon::string as weather_icon,
v:wind.deg::float as wind_dir,
v:wind.speed::float as wind_speed
from json_weather_data
where city_id = 5128638;

La notación de punto SQL v:city.coord.lat se usa en este comando para extraer valores en niveles inferiores dentro de la jerarquía de objetos JSON. Esto nos permite tratar cada campo como si fuera una columna en una tabla relacional.

La nueva vista debería aparecer como JSON_WEATHER_DATA en WEATHER > PUBLIC > Views en el navegador de objetos de la izquierda. Es posible que deba expandir o actualizar el navegador de objetos para verlo.

Verifique la vista con la siguiente consulta:

select * from json_weather_data_view
where date_trunc('month',observation_time) = '2018-01-01'
limit 20;

Observe que los resultados se parecen a una fuente de datos estructurada regular. Su conjunto de resultados puede tener diferentes valores de tiempo de observación:

Use una operación de combinación para correlacionar con conjuntos de datos

Ahora uniremos los datos meteorológicos JSON a nuestros datos CITIBIKE.PUBLIC.TRIPS para responder a nuestra pregunta original de cómo el clima afecta la cantidad de viajes.

Ejecute la consulta a continuación para unir WEATHER a TRIPS y contar la cantidad de viajes asociados con ciertas condiciones climáticas:

Debido a que todavía estamos en la hoja de trabajo, la base de datos WEATHER todavía está en uso. Por lo tanto, debe calificar completamente la referencia a la tabla TRIPS proporcionando su base de datos y nombre de esquema.

select weather as conditions,
count(*) as num_trips
from citibike.public.trips
left outer join json_weather_data_view
on date_trunc('hour', observation_time) = date_trunc('hour', starttime)
where conditions is not null
group by 1 order by 2 desc;

El objetivo inicial era determinar si había alguna correlación entre el número de paseos en bicicleta y el clima analizando tanto la cantidad de pasajeros como los datos meteorológicos. Según los resultados anteriores, tenemos una respuesta clara. Como es de imaginar, el número de viajes es significativamente mayor cuando hace buen tiempo.