Combine, limpie y transforme sus archivos CSV fácilmente con BigQuery

Muy buenas, les saluda Luis y aquí les traigo otro tutorial.

El formato CSV (valores separados por comas) es uno de los formato de archivo de texto más común para compartir datos estructurados y fácilmente legible por humanos, cualquier software como Hoja de Google y Microsoft Excel, o por motor de base de datos como MySQL, PostgreSql o BigQuery.

Sin embargo, la popularidad de este formato conduce a utilícelo para un gran volumen de datos siendo difícil de gestionar localmente y cargarlos en un software o una base de datos local. Por lo tanto, algunos las operaciones básicas se vuelven imposibles para realizar de forma manual y rápida manteniendo el formato CSV:

Actualizar el contenido del archivo CSV, como limpiar valores incorrectos o eliminar filas obsoletas.
Actualizar formato de campo, como el formato de fecha o las unidades.
Combinar o dividir columna, como direcciones.
Unir varios archivos juntos, especialmente cuando tienes un pivote. Por ejemplo, un archivo CSV del cliente (con un customerId) y un archivo CSV de pedidos (con customerId para cada pedido) al que desea unirse customerId para tener el detalle del cliente para cada pedido.

En Google Cloud, la mayor parte del tiempo, estos archivos CSV se almacenan en Cloud Storage .

Índice

Solución de Compute Engine (incorrecta)

Si tu máquina local no es lo suficientemente potente para administrar los archivos CSV, la primera idea es utilizar un potente servidor en la nube, en Compute Engine.

El proceso es similar a su entorno local:

Descargar los archivos CSV de Cloud Storage.
Realiza las transformaciones en la instancia de Compute Engine.
Subir los nuevos archivos CSV a Cloud Storage.

Con script linux bash , usted será capaz de realizar operaciones tan simples en archivos, como una sed para reemplazar caracteres, o un split para extraer solo la columna que desee.

Pero con rapidez, cuando tienes que construir más transformaciones avanzadas, como fusionar, dividir o unir, tienes problemas y dificultades con los scripts de bash.

POST RELACIONADO: Cree un registro de cambios continuo con AWS y Jira

Puedes usar lenguaje de secuencias de comandos más útil, me gusta la biblioteca Script de Python con Pandas, pero cargando Gb, o incluso Tb de datos en la memoria toma tiempo, espacio en disco, uso de memoria y, por lo tanto, es costoso.

Solución de BigQuery

BigQuery es una potente motor de análisis para procesar Tb y Pb de datos en segundos. Cuando tiene archivos CSV, hay 2 formas de consultar los datos con BigQuery:

Carga tus datos desde Cloud Storage a la tabla nativa de BigQuery con un trabajo de carga. La operación es gratuita. Pagará el almacenamiento en BigQuery (y el almacenamiento en Cloud Storage si no borra los archivos de origen).
Crea una tabla externa que haga referencia a la ubicación de los archivos CSV en Cloud Storage. Sólo pagará el almacenamiento en Cloud Storage; nada en BigQuery.

Ambas soluciones tienen sus casos de uso preferidos

Las mesas nativas ofrecen mejores actuaciones y capacidades de partición / agrupamiento. Sin embargo, en caso de archivos nuevos o actualización en los archivos de origen, debe realizar una nueva carga y un diff.
Las tablas externas son más lentas de consultar pero permiten definir un patrón de archivo comodín (my-file-*.csv) para incluir dinámicamente todos los archivos nuevos y tener en cuenta los cambios en los archivos existentes.

En el contexto actual, las tablas externas son más interesantes porque queremos transformar archivos CSV almacenados en Cloud Storage, pero no exclusivo.

UN La tabla de BigQuery (nativa o externa) es una capa de abstracción y no tiene que preocuparse por la ubicación de los datos cuando escribe su consulta. Entonces, para consultar los datos, realice una consulta SQL simple en las tablas!

En ambos casos, pagará por el volumen de datos que consulte.

POST RELACIONADO: Convenciones de nomenclatura de mejores prácticas en Angular

Eso es para cargar y consultar los datos. Para exportar los datos a Cloud Storage, se lanzó recientemente una nueva característica que es de gran ayuda en este caso de uso

EXPORTAR DATOS : la declaración EXPORT DATA exporta los resultados de una consulta a una ubicación de almacenamiento externo.

Usa BigQuery para transformar archivos CSV

Las 2 funciones “la lectura de datos desde una ubicación externa” y “exportación DATOS”, le permiten consultar los datos de almacenamiento en la nube y almacenar el resultado en la nube de almacenamiento mediante el uso del motor de BigQuery para realizar la transformación.

Para esto, comience a crear sus tablas externas.

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales
OPTIONS (
format = 'CSV',
uris = ['gs://mybucket/sales-2019*.csv',
'gs://mybucket/sales-2020*.csv'],
skip_leading_rows = 1
)

Luego, simplemente consúltelo y exporte los resultados a Cloud Storage.

EXPORT DATA OPTIONS(
uri='gs://mybucket/transformed/sales-*.csv',
format='CSV',
overwrite=true,
header=true,
field_delimiter=';') AS
SELECT field1, field2 FROM mydataset.sales ORDER BY field1

¡Eso es todo! ¡Un archivo complejo Extract-Transform-Load en CSV almacenado en Cloud Storage, en unos segundos y por unos pocos dólares! ¡Deja de perder el tiempo escribiendo guiones aburridos!

Ir más allá

Debido a que usa BigQuery, también puede usar todas las increíbles funciones de BigQuery.

Utilice funciones de análisis y SQL complejas durante su transformación.

Une los datos CSV con datos de BigQuery , o incluso de otras fuentes externas, como otros archivos, Google Drive / Sheet , base de datos Cloud SQL ,…

Enriquece el resultado con las capacidades de AA de BigQuery.

Por todos estos, solo paga el volumen de datos que procesa. No hay VM que aprovisionar, no hay script que escribir, probar y aplicar. Solo unos pocos pasos de configuración y sintaxis SQL.

Gracias por leer este post y a disfrutar.