MySQL en la nube de Google para almacenar datos desde Jupyter Notebooks con Python

¿Por qué no generar tus propios data sets y almacenarlos en la nube para usarlos más tarde? Paso a paso para montar una infraestructura sencilla y accesible desde Jupyter Notebooks

Photo by Jan Antonin Kolar on Unsplash

Después de terminar el máster en Data Analytics y Big Data en agosto y de analizar varios data sets que encontraba por internet (UCI, Kaggle, etc), me planteé desplegar una pequeña máquina con una BBDD MySQL en la que pudiera almacenar data sets generados por mi (básicamente utilizando web scraping)

Entonces, ¿cuáles fueron los pasos que seguí?:

1. Instalar MySQL en un ordenador/servidor (físico o en la nube)
2. Crear los usuarios, base de datos y las tablas MySQL
3. Acceder e introducir información en esa BBDD remotamente desde Jupyter Notebook usando Python

⚠️ATENCIÓN⚠️ a lo largo de este artículo necesitaremos utilizar/conocer conceptos básicos(pero básicos de verdad) de:

• Google Cloud Platform
• Linux
• MySQL
• Python
• Jupyter Notebooks

Disclaimer: Aquí expongo el proceso que yo, personalmente, he realizado. No soy un experto en GCP ni en seguridad de BBDD, por lo que aconsejo que solo se utilice para proyectos personales que no contengan información sensible. Agradezco comentarios e ideas para mejorar el proceso seguido.

1. Google Cloud Platform (GCP)

Inicialmente me planteé realizar este ejercicio en una Raspberry Pi 4 que tengo, pero se cruzó Google Cloud Platform (GCP) con 300$ de crédito y quise probar con ellos.

Google Cloud Platform (GCP)

📝Nota: GCP ofrece un servicio ‘Cloud SQL’ directamente, pero me parece interesante tener la oportunidad de desplegar un sistema ‘similar’ desde 0.

1.1. ¿Qué es GCP?

Google Cloud consiste en un conjunto de recursos físicos, como computadoras y unidades de disco duro, y recursos virtuales, como máquinas virtuales (VM), que se encuentran en los centros de datos de Google en todo el mundo. Fuente

Básicamente, Google ofrece una serie de recursos físicos (ordenadores, máquinas virtuales, etc) y servicios ya “pre-configurados” desplegados por todo el mundo para que empresas y usuarios nos olvidemos del mantenimiento y seguridad de estos sistemas.

https://cloud.google.com/solutions/data-lifecycle-cloud-platform?hl=es

Tal y como vemos, GCP ofrece muuuchos productos y servicios. Nosotros usaremos Compute Engine, aunque como he comentado antes, podríamos utilizar Cloud SQL y nos ahorraríamos el 80% del trabajo, pero no tendría gracia ninguna 😬

1.2. Registro en GCP

Como todos los servicios de Google, es super sencillo registrarse. Actualmente Google ofrece 300$ de crédito para probar sus servicios durante 90 días.

Os podéis registrar a través de este enlace o directamente en la página web

📝Nota: Os va a pedir una tarjeta de crédito/débito para realizar el registro. No os van a cobrar nada, es para evitar que la gente se registre múltiples veces y haga uso incorrecto del periodo de prueba

Una vez que os registréis y accedáis al panel de control tendréis una vista similar a esta:

https://console.cloud.google.com/

2. Creación de una instancia en GCP

Vamos a configurar y alquilar un ordenador en la nube de Google (suena friki, lo sé) Estos ordenadores se llamas “instancias”.

Dramatización de los primeros minutos en Google Cloud Platform

Tal y como hemos comentado, para ello necesitaremos acceder a la solución “Compute Engine” de GCP. Se localiza en la barra lateral dentro del módulo “Compute”:

Módulo que vamos a utilizar para configurar nuestro ordenador en la nube

Posterior mente accederemos a “Instancias VM > Nueva instancia de VM”.

Aquí empezaremos a configurar la “máquina” que vamos a alquilar. Entre los aspectos más importantes a configurar encontramos:

• Nombre de la instancia
• Región: Europe en mi caso y, en concreto, europe-west2 por cercanía
• Configuración de la máquina: CPU, Tipo de máquina (memoria, GPU)

Tal y como vemos, una máquina sencilla, en la nube tiene un coste/hora MUY reducido

• Sistema Operativo: en mi caso utilizo Ubuntu
• Permitir tráfico HTTP y HTTPS: activo por si en algún caso quiero acceder desde navegador, pero es una configuración que puede cambiarse más tarde

Selección del SO y reglas del Firewall

Y una vez que le damos a crear…. ¡ya tenemos nuestra máquina en la nube de Google!

Detalle de la nueva máquina en la nube que acabamos de crear

3. Acceso y configuración de la máquina en la nube

Por ahora hemos hecho lo más fácil y sencillo. De primeras puede resultar un poco confuso (muchos productos que no conocemos, paneles, etc) pero una vez que has levantado una máquina volver a crear una es super sencillo.

📝Nota: Si en algún momento vemos que nos hemos cargado alguna configuración o que nos hemos complicado la vida instalando algo SIEMPRE podemos restaurar la máquina o, borrar la máquina y crear una nueva :)

3.1. Acceso SSH a la nueva máquina

Para acceder a la máquina pincharemos en “Conectar” SSH y se nos abrirá una nueva ventana:

Don’t worry, esto que ves es tu “ordenador” en la nube… ¡Un poco soso, pero bueno!

Si nunca has utilizado Linux, bienvenido, ¡ahora podrás hacer uso de Linux a través de la linea de comandos!

Vamos a ejecutar los siguientes comandos:

$ sudo apt-get update  #Actualiza lista de paquetes a actualizar
$ sudo apt-get upgrade  #Instala las actualizaciones identificadas

Veréis un montón de letras blancas corriendo por la pantalla, de nuevo, sin preocuparse que no es Matrix:

Vista después de actualizar el SO

3.2. Configuración puerto 3306 del firewall para acceder desde una IP externa

Es necesario configurar las reglas de acceso del firewall para nuestra máquina.

Primero deberemos comprobrar si la máquina tiene abierto o cerrado el puerto. Para eso utilizamos la IP Externa que nos marca en el panel de instancias y utilizaremos la Open Port Check Tool para verificar que el puerto 3306 está abierto:

Puerto 3306 abierto, es el que utiliza MySQL

##NOTA: Si está abierto pasar directamente al punto 4. del manual ##

Si el puerto 3306 está cerrado, desde el panel de “Instancias VM” pinchamos en “Configurar reglas de cortafuegos”:

Necesitamos abrir el puerto 3306 para acceder desde nuestro ordenador

Aplicamos la configuración según la imagen:

Lo más importante son los campos en azul

Una vez tenemos creada la regla del firewall, tenemos que aplicarla a nuestra instancia. Regresamos al panel de instancias y editamos la instancia que habíamos creado:

Accedemos a la instancia para editar la configuración

Una vez que editamos la máquina, añadimos la regla del firewall con el nombre de la “Etiqueta de destino” que habíamos creado:

Añadimos la Etiqueta de destino en el campo Etiquetas de red

Guardamos y listo, ya debería estar abierto el puerto de tu máquina.

¡Ahora sí que estará abierto!

4. Instalación y configuración de nuestro MySQL

4.1. Instalación de la aplicación de MySQL

Ahora toca el turno de instalar el servidor de MySQL en nuestra máquina, vamos con ello:

$ sudo apt-get install mysql-server

Os pedirá una contraseña para el usuario “root” y una vez que termine, ¡ya tendremos MySQL instalado en nuestra máquina!

Vamos a realizar unos pequeños “ajustes” de seguridad a nuestra instalación:

$ sudo mysql_secure_installation

Nos pedirá la contraseña “root” y a continuación podremos configurar los siguientes aspectos:

• Validar la política de seguridad de contraseñas
• Prohibir los usuarios anónimos
• Bloquear acceso “root” fuera de localhost
• Eliminar la BBDD test

¡Y ya tendremos la configuración básica de seguridad hecha!

$ mysql -u root -p  #Acceso a la instalación MySQL

MySQL dentro de nuestra máquina

mysql> exit; #Si queremos salir de la aplicación

4.2. Configuración MySQL para acceso externo

⚠️ATENCIÓN⚠️ este punto es, con diferencia, el más complejo de realizar. En mi caso fue el mayor problema que tuve a la hora de hacer funcionar mi instalación.

Vamos a necesitar cambiar una dirección IP en un fichero de configuración de MySQL.

En este caso vamos a utilizar un editor de texto de linux (vi), que hace uno de unos atajos de teclado “especiales”:

• :q #salir sin guardar, incluyendo los dos puntos
• :wq #guardar y salir

Desde la línea de comando de linux vamos a modificar el fichero “mysqld.cnf” y buscaremos la línea “bind-adress=127.0.0.1” y lo modificamos por 0.0.0.0 :

$ sudo su #Cambiamos a permisos de super usuario
$ vi /etc/mysql/mysql.conf.d/mysqld.cnf #Cambiamos a 0.0.0.0
$ service mysql restart #Reseteamos el servicio
$ exit #Para desconectarnos como super usuario

Una vez modificado, ESC y luego tecleamos :wq

4.3. Creación de usuarios en MySQL con acceso “externo”

Una vez hemos superado el problema del bind-address, lo siguiente ya es más sencillo.

Será necesario crear uno o varios usuarios que tengan capacidad de acceso “remoto” a las BBDD y las tablas que hemos creado.

$ mysql -u root -p
###Introducimos la contraseña del root user###
mysql> CREATE USER 'nombre_usuario'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'externo'@'%';
mysql> FLUSH PRIVILEGES;

📝Nota: Cualquier duda sobre creación de usuarios, permisos, etc se puede encontrar en Google

Resultado OK a la creación de nuevo usuario y a los privilegios de acceso del mismo

4.4. Creación de una BBDD y tablas para almacenar información

El siguiente paso será crear una BBDD en la que iremos almacenando la información.

Existe mucha literatura sobre MySQL, creación de BBDD y de tablas. Aquí vamos a ver un ejemplo sencillo pero cualquier otra duda/configuración se puede encontrar en Google.

mysql> CREATE DATABASE nombre_bbdd;
mysql> USE nombre_bbdd;
mysql> CREATE TABLE nombre_tabla (
              camp1 INT(5) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
              camp2 VARCHAR(30) NOT NULL,
              camp3 [....]
              );
mysql> DESCRIBE nombre_tabla;

Si habéis seguido los pasos, veremos algo tal que así:

Ejemplo de creación de BBDD y tabla

Por último, podemos ingresar un valor a la tabla que luego consultaremos desde Jupyte Notebooks.

mysql> INSERT INTO TABLE nombre_tabla (camp2, camp3...)
                    VALUES("STR2val", .....);
mysql> SELECT * FROM nombre_tabla;

Ejemplo de inserción de datos en la tabla

5. Acceso y carga de datos desde Jupyter Notebooks con Python

Si hemos llegado hasta aquí ya solo queda lo “más fácil”, que es acceder desde Jupyter Notebooks a tu máquina en Google!

Vamos a usar las librerías:

pandas
sqlalchemy
mysql-connector #esta librería NO tendremos que importarla

A continuación necesitaremos conectarnos a la BBDD utilizando sqlalchemy:

Generamos unos dummy data para insertarlos en la BBDD. Los datos los convertimos en un data frame de pandas y, con la función “.to_sql” de pandas, insertamos los datos:

Por último hacemos consulta a la tabla de la BBDD para ver si hemos hecho correctamente la inserción:

Si lo hemos hecho bien, tendremos una instancia lista para ir cargando los datos en nuevas BBDD.

⚠️ATENCIÓN⚠️ ¡No olvidemos PARAR la instancia de Google Cloud Platform para evitar que nos facturen los servicios cuando no lo estamos utilizando!

👉Jupyter Notebook en GitHub:

👉Código completo:

Otros artículos que os pueden interesar:

• Cómo monitorizar precios de productos con python y no morir en el intento [pre-blackfriday] — Parte I — enlace
• Crea tu “nube de palabras” Word Cloud con Python a partir de varias webs — enlace