Como cargar datos a Google Bigquery desde MySQL

Published in

Datapath

14 min readFeb 16, 2024

La sincronización efectiva de datos entre MySQL y BigQuery es esencial para satisfacer tus necesidades tanto operativas como analíticas. Sin embargo, lograr esta sincronización de manera adecuada puede ser un desafío.

Introducción

En el mundo empresarial actual, las integraciones de datos son un componente vital de la infraestructura tecnológica, especialmente para aquellas empresas que manejan grandes volúmenes de datos. Si bien las transferencias de datos de MySQL a BigQuery son comunes, su configuración correcta puede resultar complicada.

Las bases de datos relacionales, como MySQL, sirven como la columna vertebral transaccional para una variedad de aplicaciones y sitios web de diferentes dimensiones. Entonces, ¿por qué una empresa consideraría conectar MySQL a un sistema de almacenamiento de datos adicional como BigQuery? La respuesta es simple: para análisis de datos.

La respuesta simple es: “para análisis de datos”.

Los almacenes de datos como BigQuery se vuelven esenciales cuando se trata de análisis a gran escala. Ya sea que tu equipo esté desarrollando modelos de datos tradicionales basados en SQL o Python, o esté inmerso en proyectos avanzados de aprendizaje automático, necesitarás un almacén de datos más allá de tu base de datos relacional.

Sin embargo, la conexión entre MySQL y BigQuery no es un proceso único. Conforme los datos se actualizan en la base de datos relacional, es crucial que se reflejen en el almacén de datos también.

Implementar una canalización de datos en tiempo real es la mejor manera de mantener esta sincronización para su uso en flujos de trabajo tanto operativos como analíticos. Alternativamente, también puedes optar por codificar manualmente las canalizaciones de ETL para lograr resultados similares, aunque con una respuesta menos instantánea.

En este artículo, proporcionaremos tutoriales detallados paso a paso para ambas estrategias. Al finalizar la lectura, contarás con dos métodos viables para conectar tu base de datos MySQL a BigQuery.

¡Comencemos!

Estableciendo la conexión: MySQL a BigQuery

¿Qué es BigQuery?

BigQuery, como parte integral de Google Cloud Platform (GCP), representa la incursión de Google en el ámbito del Data Warehouse empresarial. Aunque se destaca principalmente por su capacidad para realizar consultas analíticas complejas, BigQuery ofrece un valor adicional al brindar soporte a otras bases de datos relacionales, como MySQL, al manejar cargas de consultas exigentes.

La arquitectura sin servidor de BigQuery lo convierte en una opción atractiva para empresas que buscan trasladar sus operaciones a la nube sin la carga de administrar la infraestructura subyacente. Esta característica lo convierte en un destino popular para las canalizaciones de datos.

Además, BigQuery se integra sin problemas con otros servicios de Google, como Google Drive y hojas de cálculo, lo que facilita aún más la gestión y análisis de datos.

La plataforma ofrece a las empresas una flexibilidad excepcional al separar el motor de computación encargado del análisis de datos de los sistemas de almacenamiento. Los usuarios tienen la libertad de elegir entre almacenar y analizar datos dentro de BigQuery o acceder a los datos directamente desde su fuente original.

Razones para usar BigQuery

Funciona en un entorno sin servidor, lo que elimina la necesidad de gestionar infraestructura.
Ofrece soporte para transmisión de datos y funcionalidades de aprendizaje automático.
Su modelo de pago flexible se adapta a una variedad de necesidades y presupuestos empresariales.

¿Qué se puede migrar a BigQuery desde MySQL?

MySQL ha mantenido su posición como el sistema de gestión de bases de datos relacionales (RDBMS) de código abierto más popular desde la década de 1980 y actualmente es utilizado por empresas de todos los tamaños.

En esencia, MySQL es una base de datos relacional que organiza y consulta datos en forma de filas y columnas, destacándose por su estabilidad y rendimiento rápido.

Cuando te dispones a migrar a BigQuery, puedes esperar transferir lo siguiente:

Tablas

Fuente: How to query your data in BigQuery | Google Cloud Blog

Tanto MySQL como BigQuery almacenan datos en forma de tablas. Al transferir una tabla de MySQL a BigQuery, esta persiste en BigQuery como una tabla estándar o administrada.

Aunque MySQL y BigQuery utilizan SQL, admiten diferentes tipos de datos. Por lo tanto, será necesario mapear los tipos de datos de MySQL a sus equivalentes en BigQuery. Existen diversos métodos para llevar a cabo esta tarea, dependiendo de la canalización de datos que se esté utilizando.

Una vez que la tabla ha sido transferida a BigQuery, se almacena como copias de seguridad cifradas en el almacén de Google. Los usuarios tienen la capacidad de ejecutar consultas complejas en ella o realizar cualquier tarea habilitada por BigQuery.

Los beneficios de conectar MySQL a BigQuery

Fuente: Migrating your traditional data warehouse platform to BigQuery

En la actualidad, MySQL se sitúa en el segundo lugar como la base de datos más popular, según las clasificaciones de los motores de base de datos, mientras que BigQuery representa la incursión de Google en el ámbito del almacenamiento de datos empresariales multinube, sin servidor y rentable.

Por ende, replicar los datos de MySQL a BigQuery se convierte en una parte crucial de tu estrategia global de integración de datos. Al hacerlo, puedes obtener los siguientes beneficios:

BigQuery está diseñado para realizar análisis eficientes y rápidos, sin afectar las cargas de trabajo operativas que seguramente seguirás manejando en MySQL.
Optimiza las cargas de trabajo y crea una única fuente de información. Para los analistas, puede resultar complicado y consumir mucho tiempo migrar de una plataforma a otra. Mantener BigQuery sincronizado con MySQL asegura que ambas plataformas de almacenamiento de datos estén unidas en torno a una única fuente de verdad, garantizando que otras plataformas, ya sean operativas o analíticas, siempre extraigan datos precisos.
BigQuery mejora la seguridad de los datos. Al replicar los datos de MySQL a BigQuery, los usuarios eliminan la necesidad de otorgar permisos a otros ingenieros de datos en sistemas operativos.
BigQuery maneja el procesamiento analítico en línea (OLAP), a diferencia de MySQL, que está diseñado para el procesamiento de transacciones en línea (OLTP). Debido a esta diferencia, BigQuery puede ofrecer información más detallada sobre los datos y ayudar a transformar el big data en información valiosa. Además, al ser un almacén de datos rentable, sin servidores y multinube, BigQuery proporciona una solución escalable y eficiente para las necesidades analíticas de tu empresa.

Conexión de MySQL a BigQuery

Para transferir datos de MySQL a BigQuery, es necesario establecer una canalización de datos.

Existen diversas formas de crear una, pero en general se pueden clasificar en dos categorías principales:

Desarrollar manualmente una canalización ETL desde cero.
Utilizar una herramienta de canalización de datos sin necesidad de codificación.

La elección de la estrategia adecuada dependerá de su experiencia y requisitos específicos. A continuación, se presentan ejemplos y pasos para ambas estrategias.

Método 1: Proceso ETL manual para conectar MySQL a BigQuery

El enfoque manual para conectar MySQL a BigQuery implica la creación de scripts ETL personalizados para configurar este proceso de transferencia de datos. Este método puede implementarse de dos formas diferentes:

Full Dump and Load (Volcado y Carga Completa)
Incremental Dump and Load (Volcado e Incremento de Carga)

Full Dump and Load

Fuente: MySQL to BigQuery: 2 Easy Methods | Hevo

Este enfoque es relativamente simple: implica extraer todos los datos de la tabla MySQL de origen y migrarlos a BigQuery. Si la tabla de destino ya existe, se elimina y se crea una nueva tabla (o se eliminan los datos existentes y se insertan los datos recién extraídos).

Full Dump and Load (Volcado y carga completa) es la única opción para la primera carga, incluso si se utiliza un enfoque de carga incremental para cargas posteriores. Este método puede ser adecuado para tablas relativamente pequeñas, incluso en cargas recurrentes. También se puede considerar la integración de MySQL con Redshift.

Los pasos generales a seguir para replicar MySQL en BigQuery son:

Paso 1: Extraer datos de MySQL
Paso 2: Limpiar y transformar los datos
Paso 3: Subir a Google Cloud Storage(GCS)
Paso 4: Cargar en la tabla de BigQuery desde GCS

Ahora, examinemos detalladamente cada paso para migrar de SQLite a MariaDB.

Paso 1: Extraer datos de MySQL

Existen dos formas populares de extraer datos de MySQL: mediante el uso de mysqldump y mediante consultas SQL.

Extraer datos usando mysqldump

‘Mysqldump’ es una utilidad cliente que se incluye con la instalación de MySQL. Se utiliza principalmente para crear una copia de seguridad lógica de una base de datos o tabla. A continuación, se muestra cómo se puede utilizar para extraer una tabla:

mysqldump -u <nombre_usuario> -h <host_bd> -p nombre_bd nombre_tabla > nombre_tabla.sql

El archivo de salida ‘nombre_tabla.sql’ estará en forma de sentencias de inserción como

INSERT INTO nombre_tabla (columna1, columna2, columna3, ...)
VALUES (valor1, valor2, valor3, ...);

Esta salida debe convertirse en un archivo CSV. Se necesita escribir un pequeño script para realizar esta conversión. Aquí hay una biblioteca de Python bien aceptada que realiza esta tarea: ‘mysqldump_to_csv.py’

Alternativamente, se puede crear un archivo CSV utilizando el siguiente comando. Sin embargo, esta opción solo funciona cuando mysqldump se ejecuta en la misma máquina que el servidor ‘mysqld’ , lo cual no es lo habitual:

mysqldump -u [nombre_usuario] -p -t -T/ruta/al/directorio [base_de_datos] --fields-terminated-by=,

Extracción de datos mediante consulta SQL

La utilidad cliente de MySQL se puede utilizar para ejecutar comandos SQL y redirigir la salida a un archivo.

mysql -B -u usuario nombre_base_de_datos -h host_mysql -e "SELECT * FROM nombre_tabla;" > datos_tabla_raw.txt

Además, puede combinarse con utilidades de edición de texto como ‘sed’ o ‘awk’ para limpiar y formatear los datos.

Ejemplo:

mysql -B -u usuario nombre_base_de_datos -h host_mysql -e "SELECT * FROM nombre_tabla;" |   
sed "s/'/'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g"  > datos_tabla.csv

Paso 2: Limpiar y transformar los datos

Además de transformar los datos para ajustarse a la lógica empresarial, hay algunas consideraciones básicas a tener en cuenta:

BigQuery espera que los datos CSV estén codificados en UTF-8.
BigQuery no impone restricciones de clave primaria y clave única. El proceso ETL debe encargarse de esto.
Los tipos de columna son ligeramente diferentes. La mayoría de los tipos tienen equivalentes o pueden convertirse. A continuación, se muestra una lista de tipos de datos comunes:

Afortunadamente, el formato de fecha por defecto en MySQL es el mismo, AAAA-MM-DD. Por lo tanto, al tomar un mysqldump, no es necesario realizar ningún cambio específico para esto. Si está utilizando un campo de cadena para almacenar la fecha y desea convertirlo a un formato de fecha al pasar a BigQuery, puede utilizar la función STR_TO_DATE.

El valor DATE debe estar separado por guiones (-) y tener el formato AAAA-MM-DD (año-mes-día). Puedes visitar la página oficial de BigQuery para obtener más información sobre los tipos de datos de BigQuery.

Sintaxis:

STR_TO_DATE(cadena, formato)

Ejemplo:

SELECT STR_TO_DATE('31,12,1999','%d,%m,%Y');

Resultado:

1999-12-31

La parte hh:mm:ss (hora:minuto:segundo) de la marca de tiempo debe utilizar dos puntos (:) como separador.
Asegúrese de que las columnas de texto estén entrecomilladas si pueden contener caracteres delimitadores.

Paso 3: Subir a Google Cloud Storage(GCS)

Gsutil es una herramienta de línea de comandos para manipular objetos en GCS. Se puede utilizar para cargar archivos desde diferentes ubicaciones a su cubo GCS.

Para copiar un archivo en GCS:

gsutil cp table_name_data.csv  gs://mi-bucket/ruta/a/carpeta/

Para copiar una carpeta entera:

gsutil cp -r directorio gs://mi-bucket/ruta/a/parent/

Si los archivos están presentes en S3, se puede utilizar el mismo comando para transferirlos a GCS.

gsutil cp -R s3://nombre_del_bucket/ruta/de/origen gs://nombre_del_bucket/ruta/de/destino

Servicio de traslado de almacén

El servicio de transferencia de almacenamiento de la nube de Google es otra opción para subir archivos a GCS desde S3 u otras fuentes de datos en línea como la ubicación HTTP/HTTPS. El destino o sumidero es siempre un bucket de almacenamiento en la nube. También puede utilizarse para transferir datos de un bucket GCS a otro.

Este servicio es extremadamente práctico cuando se trata de movimiento de datos a GCS con soporte para:

Programar transferencias de datos puntuales o periódicas.
Eliminar los objetos existentes en el destino si no hay ningún objeto de origen correspondiente.
Eliminación del objeto de origen tras la transferencia.
Sincronización periódica entre origen y destino con filtros avanzados basados en fechas de creación de archivos, nombre de archivos, etc.

Cargar desde la consola web

Si realiza la carga desde su equipo local, también puede utilizar la interfaz de usuario de la consola web para cargar archivos en GCS. Estos son los pasos para subir un archivo a GCS con capturas de pantalla.

Inicie sesión en su cuenta de GCP. En la barra de la izquierda, haga clic en Almacenamiento y vaya a Navegador.

Seleccione el bucket GCS en el que desea cargar el archivo. Aquí el bucket que estamos usando es test-data-hevo. Haga clic en el bucket.

En la página de detalles del cubo, haga clic en el botón de carga de archivos y seleccione el archivo de su sistema.

Espere a que se complete la carga. Ahora, el archivo subido aparecerá en la lista del cubo:

Paso 4: Cargar en la tabla BigQuery desde GCS

Puede utilizar el comando ‘bq’ para interactuar con BigQuery. Es extremadamente conveniente cargar datos a la tabla desde GCS. Utilice el comando ‘bq load’ y especifique CSV como ‘source_format’.

La sintaxis general de bq load:

bq --location=[UBICACIÓN] load --source_format=[FORMATO] [CONJUNTO_DATOS].[TABLA] [RUTA_AL_ORIGEN] [ESQUEMA]

[UBICACIÓN] es su ubicación (opcional).
[FORMATO] es CSV.
[CONJUNTO_DATOS] es un conjunto de datos existente.
[TABLA] es el nombre de la tabla en la que está cargando los datos.
[RUTA_AL_ORIGEN] es un URI de almacenamiento en la nube completamente cualificado.
[ESQUEMA] es un esquema válido. El esquema puede ser un archivo JSON local o en línea. También puede utilizar la bandera autodetect en lugar de proporcionar una definición de esquema.

Existen numerosas opciones específicas para la carga de datos CSV. A continuación se muestra una tabla con algunas de estas opciones:

Fuente: https://hevodata.com/blog/mysql-to-bigquery/

Para ver la lista completa de opciones, visite la documentación de BigQuery sobre la carga de datos CSV desde almacenamiento en la nube aquí.

Aquí hay algunos comandos de ejemplo para cargar datos:

Especificar esquema utilizando un archivo JSON:

bq --location=US load --source_format=CSV mydataset.mytable gs://mybucket/mydata.csv ./myschema.json

Si desea que el esquema se detecte automáticamente a partir del archivo:

bq --location=US load --autodetect --source_format=CSV mydataset.mytable gs://mybucket/mydata.csv

Si está escribiendo en una tabla existente, BigQuery proporciona tres opciones: escribir si está vacía, añadir a la tabla o sobrescribir la tabla. Además, es posible añadir nuevos campos a la tabla mientras se cargan los datos. Veamos cada una con un ejemplo:

Para sobrescribir la tabla existente:

bq --location=US load --autodetect --replace --source_format=CSV mydataset.mytable
gs://mybucket/mydata.csv

Para añadir a una tabla existente:

bq --location=US load --autodetect --noreplace --source_format=CSV mydataset.mytable
gs://mybucket/mydata.csv ./myschema.json

Para añadir un nuevo campo a la tabla y permitir la adición de campos:

bq --location=asia-northeast1 load --noreplace --schema_update_option=ALLOW_FIELD_ADDITION
--source_format=CSV mydataset.mytable gs://mybucket/mydata.csv ./myschema.json

2. Incremental Dump and Load

En determinados casos de uso, realizar una carga de datos única desde MySQL a BigQuery puede no ser suficiente. Puede haber situaciones en las que, una vez extraídos los datos iniciales de la fuente, necesitemos mantener sincronizada la tabla de destino con la fuente en curso. Para una tabla pequeña, realizar un volcado completo de datos cada vez podría ser factible, pero si el volumen de datos es considerable, es prudente considerar un enfoque incremental.

Los siguientes pasos se utilizan en el enfoque incremental para conectar MySQL a BigQuery:

Paso 1: Extraer datos de MySQL
Paso 2: Actualizar la tabla de destino en BigQuery

Paso 1: Extraer datos de MySQL

Para la extracción incremental de datos de MySQL, utilice SQL con predicados apropiados y escriba la salida en un archivo. No se puede utilizar mysqldump aquí, ya que siempre extrae todos los datos.

Ejemplo: Extraer filas basadas en la columna updated_timestamp y convertirlas a formato CSV.

mysql -B -u user database_name -h mysql_host -e "select * from table_name where
updated_timestamp < now() and updated_timestamp > '#max_updated_ts_in_last_run#'" |
sed "s/'/'/;s/t/\",\"/g;s/^/\"/;s/$/\"/;s/n//g" > table_name_data.csv

Nota: En caso de que se produzca un borrado en la tabla de origen, este cambio no se reflejará en la tabla de destino.

Paso 2: Actualizar la tabla de destino en BigQuery

En primer lugar, cargue los datos en una tabla de preparación para luego insertar los datos recién extraídos en la tabla final de BigQuery. Esto implicará una carga completa, tal como se describe en la sección de carga completa de datos anterior. Llamaremos a esta tabla de preparación “tabla_delta”. Ahora, existen dos enfoques para cargar los datos en la tabla final:

Actualizar los valores de registros existentes en la tabla final e insertar nuevas filas de la tabla delta que no estén presentes en la tabla final.

UPDATE data_set.final_table t
SET t.value = s.value
FROM data_set.delta_table s
WHERE t.id = s.id;

INSERT INTO data_set.final_table (id, value)
SELECT id, value
FROM data_set.delta_table
WHERE id NOT IN (SELECT id FROM data_set.final_table);

2. Eliminar las filas de la tabla final que estén presentes en la tabla delta. Luego, insertar todas las filas de la tabla delta en la tabla final.

DELETE FROM data_set.final_table f
WHERE f.id IN (SELECT id FROM data_set.delta_table);

INSERT INTO data_set.final_table (id, value)
SELECT id, value
FROM data_set.delta_table;

Desventajas de la carga manual de datos

La carga manual de datos de MySQL a BigQuery presenta varios inconvenientes:

Proceso engorroso: Aunque el código personalizado puede adaptarse a movimientos de datos puntuales, las actualizaciones frecuentes se vuelven laboriosas con la carga manual, lo que conduce a ineficiencia y voluminosidad en el proceso.
Problemas de consistencia de datos: BigQuery no garantiza la coherencia de los datos de fuentes externas, lo que puede provocar comportamientos inesperados durante la ejecución de consultas en caso de cambios en los datos. Esto puede llevar a resultados inconsistentes o imprecisos.
Restricción de ubicación: La ubicación del conjunto de datos debe alinearse con la región o multirregión del Cloud Storage Bucket, lo que restringe la flexibilidad en el almacenamiento de datos y puede generar complicaciones adicionales al administrar múltiples ubicaciones geográficas.
Limitación con el formato CSV: Los archivos CSV tienen limitaciones en la capacidad de manejar datos anidados o repetidos, lo que reduce las posibilidades de representación de datos complejos y estructurados. Esto puede resultar en la pérdida de información o en la necesidad de realizar transformaciones adicionales antes de cargar los datos.
Limitación de la compresión de archivos: La mezcla de archivos comprimidos y sin comprimir en el mismo trabajo de carga utilizando el formato CSV no es factible, lo que añade complejidad a las tareas de carga de datos y puede requerir un procesamiento adicional para manejar diferentes tipos de archivos.
Restricción del tamaño de los archivos: El tamaño máximo de un archivo gzip en formato CSV está limitado a 4 GB, lo que puede ser insuficiente para manejar eficazmente grandes conjuntos de datos. Esto puede requerir la división de los datos en archivos más pequeños o la exploración de otras opciones de almacenamiento y carga para superar esta limitación.

Conclusiones

Conectar MySQL a BigQuery es fundamental para las empresas que desean potenciar los flujos de trabajo operativos y analíticos a partir de los mismos datos.

Hay muchas maneras de lograr esto. En este blog hemos cubierto uno de ellos, ETL codificado a mano y en el proximo blog haremos uso de un servicio de canalización administrado en tiempo real, son solo dos extremos de un gran espectro.

Mientras que los métodos codificados a mano ofrecen total libertad, los servicios gestionados le dan acceso a capacidades de canalización de datos más fiables, escalables y eficientes con menos esfuerzo de ingeniería.

Próximos Pasos

En un siguiente blog exploraremos otros metodos alternativos.
Método 2: Usar una herramienta de canalización sin código

Referencias Bibliográficas