4. Preparación de la carga de datos

Empecemos preparándonos para cargar los datos estructurados de transacciones de ciclistas de Citi Bike en Snowflake.

Esta sección lo guía a través de los pasos para:

• Crea una base de datos y una tabla.
• Crea un escenario externo.
• Cree un formato de archivo para los datos.

Obtener datos en Snowflake Hay muchas maneras de obtener datos en Snowflake desde muchas ubicaciones, incluido el comando COPY, la ingesta automática de Snowpipe, conectores externos o soluciones ETL/ELT de terceros. Para obtener más información sobre cómo obtener datos en Snowflake, consulte la documentación de Snowflake. Para los fines de este laboratorio, usamos el comando COPY y el almacenamiento de AWS S3 para cargar datos manualmente. En un escenario del mundo real, lo más probable es que utilice un proceso automatizado o una solución ETL.

Los datos que utilizaremos son datos de bicicletas compartidas proporcionados por Citi Bike NYC. Los datos se han exportado y preconfigurado para usted en un depósito de Amazon AWS S3 en la región EE. UU.-ESTE. Los datos consisten en información sobre tiempos de viaje, ubicaciones, tipo de usuario, sexo, edad, etc. En AWS S3, los datos representan 61,5 millones de filas, 377 objetos y 1,9 GB comprimidos.

A continuación se muestra un fragmento de uno de los archivos de datos CSV de Citi Bike:

Está en formato delimitado por comas con una sola línea de encabezado y comillas dobles que encierran todos los valores de cadena, incluidos los encabezados de campo en la línea de encabezado. Esto entrará en juego más adelante en esta sección cuando configuremos la tabla Snowflake para almacenar estos datos.

Crear Una Base de Datos y Tabla

Primero, creemos una base de datos llamada CITIBIKE para cargar los datos estructurados.

Navegue a la pestaña Bases de datos. Haga clic en Crear, nombre la base de datos CITIBIKE, luego haga clic en CREAR.

Ahora navegue a la pestaña Hojas de trabajo. Debería ver la hoja de trabajo que creamos en el paso 3.

Necesitamos establecer el contexto apropiadamente dentro de la hoja de trabajo. En la esquina superior derecha de la hoja de trabajo, haga clic en el cuadro junto al + para mostrar el menú contextual. Aquí controlamos los elementos que puede ver y ejecutar desde cada hoja de cálculo. Estamos usando la interfaz de usuario aquí para establecer el contexto. Más adelante en el laboratorio, lograremos lo mismo a través de comandos SQL dentro de la hoja de trabajo.

Seleccione la siguiente configuración de contexto:

Rol: SYSADMIN DataWarehouse: COMPUTE_WH

A continuación, en el menú desplegable de la base de datos, seleccione la siguiente configuración de contexto:

Base de datos: Esquema CITIBIKE = PÚBLICO

¡Las operaciones del lenguaje de definición de datos (DDL) son gratuitas! Todas las operaciones DDL que hemos realizado hasta ahora no requieren recursos informáticos, por lo que podemos crear todos nuestros objetos de forma gratuita.

Para facilitar el trabajo en la hoja de trabajo, vamos a cambiarle el nombre. En la esquina superior izquierda, haga clic en el nombre de la hoja de trabajo, que es la marca de tiempo cuando se creó la hoja de trabajo, y cámbielo a CITIBIKE_ZERO_TO_SNOWFLAKE.

A continuación, creamos una tabla llamada TRIPS para usar para cargar los datos delimitados por comas. En lugar de usar la interfaz de usuario, usamos la hoja de trabajo para ejecutar el DDL que crea la tabla. Copie el siguiente texto SQL en su hoja de trabajo:

create or replace table trips

(tripduration integer,

starttime timestamp,

stoptime timestamp,

start_station_id integer,

start_station_name string,

start_station_latitude float,

start_station_longitude float,

end_station_id integer,

end_station_name string,

end_station_latitude float,

end_station_longitude float,

bikeid integer,

membership_type string,

usertype string,

birth_year integer,

gender integer);

Muchas opciones para ejecutar comandos. Los comandos SQL se pueden ejecutar a través de la interfaz de usuario, a través de la pestaña Hojas de trabajo, usando nuestra herramienta de línea de comandos SnowSQL, con un editor SQL de su elección a través de ODBC/JDBC, o a través de nuestros otros conectores (Python, Spark, etc.). Como se mencionó anteriormente, para ahorrar tiempo, estamos realizando la mayoría de las operaciones en este laboratorio a través de SQL preescrito que se ejecuta en la hoja de trabajo en lugar de usar la interfaz de usuario.

Ejecute la consulta colocando el cursor en cualquier parte del texto SQL y haciendo clic en el botón azul Reproducir/Ejecutar en la parte superior derecha de la hoja de trabajo. O use el atajo de teclado [Ctrl]/[Cmd]+[Enter].

Verifique que su tabla TRIPS haya sido creada. En la parte inferior de la hoja de trabajo, debería ver una sección de Resultados que muestra el mensaje "Tabla TRIPS creada con éxito".

Navegue a la pestaña Bases de datos haciendo clic en el ícono de INICIO en la esquina superior izquierda de la hoja de trabajo. Luego haga clic en Datos > Bases de datos. En la lista de bases de datos, haga clic en CITIBIKE > PÚBLICO > TABLAS para ver su tabla VIAJES recién creada. Si no ve ninguna base de datos a la izquierda, expanda su navegador porque pueden estar ocultas.

Haga clic en VIAJES y en la pestaña Columnas para ver la estructura de la tabla que acaba de crear.

Crear un External Stage

Estamos trabajando con datos estructurados y delimitados por comas que ya se organizaron en un depósito de S3 externo y público. Antes de que podamos usar estos datos, primero debemos crear una etapa que especifique la ubicación de nuestro depósito externo.

Para este laboratorio, estamos utilizando un depósito de AWS-East. Para evitar la salida de datos/costos de transferencia en el futuro, debe seleccionar una ubicación provisional del mismo proveedor de nube y región que su cuenta de Snowflake.

En la pestaña Bases de datos, haga clic en la base de datos CITIBIKE y el esquema PÚBLICO. En la pestaña Etapas, haga clic en el botón Crear, luego Etapa > Amazon S3.

En el cuadro de diálogo "Crear objeto protegible" que se abre, reemplace los siguientes valores en la instrucción SQL:

stage_name: citibike_trips

url: s3://snowflake-workshop-lab/citibike-trips/

Nota: asegúrese de incluir la barra inclinada final (/) al final de la URL o encontrará errores más adelante al cargar los datos del depósito.

El depósito de S3 para este laboratorio es público, por lo que puede vaciar dejar las opciones de credenciales en la declaración. En un escenario del mundo real, el depósito utilizado para una etapa externa probablemente requiera información clave.

Ahora echemos un vistazo a los contenidos de la etapa citibike_trips. Navegue a la pestaña Hojas de trabajo y ejecute la siguiente instrucción SQL:

list @citibike_trips;

En los resultados del panel inferior, debería ver la lista de archivos en el escenario:

Crear File Format

Antes de que podamos cargar los datos en Snowflake, debemos crear un formato de archivo que coincida con la estructura de datos.

En la hoja de trabajo, ejecute el siguiente comando para crear el formato de archivo:

--create file format

create or replace file format csv type='csv'
 compression = 'auto' field_delimiter = ',' record_delimiter = '\n'
 skip_header = 0 field_optionally_enclosed_by = '\042' trim_space = false
 error_on_column_count_mismatch = false escape = 'none' escape_unenclosed_field = '\134'
 date_format = 'auto' timestamp_format = 'auto' null_if = ('') comment = 'file format for ingesting
 data for zero to snowflake';

Verifique que el formato de archivo se haya creado con la configuración correcta ejecutando el siguiente comando:

--verify file format is created
show file formats in database citibike;

El formato de archivo creado debe aparecer en el resultado: