Blog de Amazon Web Services (AWS)

Extraer, analizar y visualizar datos de bases de datos SQL on-premises con AWS Glue, Amazon Athena y Amazon QuickSight

Patricio Vazquez, Solutions Architect, Public Sector

 

Introducción

AWS Glue es un servicio gestionado de integración de datos, incluyendo ETL (por sus siglas en inglés extracción, transformación y carga). AWS Glue permite catalogar, limpiar, enriquecer y transportar información entre distintos almacenes de datos. Los trabajos de procesamiento (Jobs ETL) de Glue le permiten interactuar con múltiples fuentes de datos dentro y fuera de la nube de AWS.

Los Jobs de Glue pueden interactuar con almacenes de datos on-premises mediante una conexión JDBC, tanto para la lectura como para la escritura de datos en esos almacenes.

Para que Glue pueda interactuar con almacenes externos, tal como bases de datos alojadas on-premises, se deben tener en cuenta requerimientos adicionales con respecto a las configuraciones de red, firewall o DNS descritos en este artículo.

Otros servicios incluidos en este artículo son Amazon Athena, que es un servicio gestionado que permite ejecutar consultas SQL de forma interactiva, Amazon Simple Storage Service (Amazon S3), servicio gestionado de almacenamiento de objetos y Amazon QuickSight, servicio gestionado de Business Intelligence (Inteligencia Empresarial) que permite crear paneles y visualizaciones de datos.

 

Arquitectura

El siguiente diagrama muestra la arquitectura de una solución para catalogar, extraer y transformar datos provenientes de una base de datos relacional on-premises, a la que se puede acceder mediante una conexión AWS Site-to-Site VPN o AWS Direct Connect. Los datos procesados se almacenarán en el servicio de Amazon S3 en el formato columnar open-source Apache Parquet, que es recomendado para procesos analíticos debido a que ayuda a proporcionar una compresión de datos eficiente y permite recuperar columnas completas sin necesidad de leer cada fila, haciendo el proceso de consulta de datos más veloz y costo-efectivo.

Para la extracción de los datos se utiliza el servicio AWS Glue, que se conectará por medio de una Elastic Network Inteface (Interfaz de red elástica) en una Amazon Virtual Private Cloud con el origen de los datos en una red remota que, en este ejemplo, representa un centro de datos corporativo donde se aloja una base de datos MySQL. El proceso es similar para otros motores de base de datos relacionales.

Los datos transformados son llevados a un Bucket (contenedor) de S3, y luego catalogados por Glue para que puedan ser consultados usando Amazon Athena y visualizados en Amazon QuickSight.

En este ejemplo se utilizará la nueva interfaz gráfica de AWS Glue Studio, para construir el Job ETL que transportará los datos del origen al destino.

 

Requisitos previos

Para empezar, debemos asegurarnos de cubrir los requisitos previos.

  • Contar con una VPC con al menos una subred privada en la VPC. Para esto, consulte la documentación de Amazon VPC.
  • Crear una conexión VPN Site-to-Site con el centro de datos donde se encuentra la base de datos de origen. Para hacerlo, consulte la documentación.
  • Configurar las tablas de enrutamiento para agregar el camino apropiado desde la subred privada donde se alojará la interfaz de red de AWS Glue, hacia la red del centro de datos corporativo.

 

Implementación

Los 8 pasos a continuación describen cómo completar este tutorial:

Paso 1: Crear un Bucket de Amazon S3

Este Bucket servirá para depositar los datos una vez que han sido transformados.

Para crearlo, en la consola de Amazon S3:

  • Elegir “Create bucket”.
  • Proporcionar un nombre.
  • Elegir la región de AWS en la que está completando el tutorial.

Paso 2: Crear políticas y roles de AWS Identity and Access Managment (IAM)

A continuación, crearemos la política con los permisos que el rol asumido por Glue utilizará para ejecutar acciones sobre otros recursos de nuestra cuenta de AWS.

Para crear la política, dentro de la consola de AWS IAM:

  • Elegir “Policies” en el menú del lado izquierdo.
  • Clic en “Create Policy”.
  • Elegir la opción “JSON” para insertar la política directamente.
  • Copiar y pegar el siguiente JSON reemplazando <your_bucket_name> por el nombre de su bucket. Esta política otorga el permiso de leer y escribir sobre el bucket. Si desea restringir el acceso a una carpeta de su bucket, reemplace <ruta> por el nombre de la carpeta seguido de “/”. En caso contrario elimine <ruta>
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject"
            ],
            "Resource": "arn:aws:s3:::<your_bucket_name>/<ruta>*"
        }
    ]
}

 

  • Proporcionar un nombre, por ejemplo “AWSGlueServiceRole-access-to-S3-processed-data”.

 

Para crear el rol, dentro de la consola de AWS IAM:

  • Clic en “Create role”.
  • Bajo “trusted entity”, clic en “Glue”.

 

  • Añadimos dos políticas: una es la que creamos en el paso anterior (AWSGlueServiceRole-access-to-S3-processed-data) y la otra será una política administrada llamada “AWSGlueServiceRole”.

 

  • Finalizamos la creación del rol dándole un nombre como “GlueRoleForOnpremETL”.

Paso 3: Crear una conexión JDBC de AWS Glue

Crearemos una conexión que AWS Glue utilizará para conectarse a nuestra base de datos MySQL cuando realice el rastreo (crawler) y carga de datos (job ETL). Esta conexión crea una interfaz de red con un security group asociado.

Iniciamos por crear el security group, para ello en la consola de Amazon VPC:

  • Elegimos “Security groups” en el menú del lado izquierdo.
  • Clic en “Create Security group”.
  • Proporcionamos como nombre y descripción: “glue-sg”.
  • Elegimos la VPC con la conexión VPN hacia nuestro centro de datos remoto.
  • Clic en “Create Security Group”.

A continuación, editamos las reglas de entrada usando el botón “Edit Inbound rules”:

  • Clic en “Add rule”.
  • En “port range” elegimos “ALL traffic”.
  • En «Source” elegimos “Custom”.
  • En “Source” elegimos el mismo security group “glue-sg”.

 

  • Clic en “Save rules”.

Ahora creamos la conexión en Glue usando JDBC como tipo de conexión.

En la consola de Glue:

  • Ingresamos en la opción de “Connections” que se encuentra debajo de la opción “Databases” en el panel izquierdo y hacemos clic en “Add Connection”

 

  • Introducimos la JDBC URL que representa base de datos de origen, que utiliza este formato

jdbc:protocol://host:port/db_name

  • Para conectar con una base de datos MySQL, construimos la URL JDBC (como ejemplo, jdbc:mysql://192.0.2.1:3306/TestDB) y luego introducimos usuario y contraseña de base de datos, datos de la subred privada en nuestra VPC y finalmente el Security Group creado anteriormente.

 

Aprenda más sobre cómo trabajar con conexiones de AWS Glue.

La base de datos on-premises debe permitir estas conexiones en su configuración. También es posible que se deba gestionar el firewall on-premises para permitir el acceso desde direcciones IP en la subred elegida en AWS. En este ejemplo, el rango de direcciones IP que se usó para la VPC en AWS es 10.24.34.0/24, con una subred privada en el rango 10.24.34.0/25, de donde la interfaz de red que usa Glue tomará una IP, por lo que debemos asegurarnos que la base de datos y el firewall on-premises acepte conexiones desde este rango.

Ahora podemos probar la conexión haciendo clic en «Test Connection».

 

Si recibimos un error, revisar lo siguiente:

– La URL JDBC.

– Usuario y contraseña.

– La configuración de la VPC y sus tablas de enrutamiento.

– El Security Group para las interfaces de red que utiliza Glue.

Paso 4: Catalogar datos con AWS Glue Crawlers

Necesitamos crear un catálogo de los datos en AWS que apunte al origen on-premises, para lo que usaremos la funcionalidad de Crawlers de Glue.

En la consola de Glue, dentro de la sección Crawlers:

  • Clic en el botón de “Add crawler”.
  • Damos un nombre al nuevo Crawler.
  • Para el tipo de origen usamos las opciones por defecto.

 

  • Agregamos un almacén de datos JDBC e incluimos la ruta en el campo “Include path” que representa, en el caso de una base de datos MySQL, la base de datos y la(s) tabla(s) a utilizar. El símbolo “%” representa un comodín. En este ejemplo, la base de datos se llama “TestDB” y de ella leeremos todas las tablas disponibles.

 

Otros productos de bases de datos soportan diferentes propiedades.

  • Seleccionamos el rol de IAM creado previamente.

 

  • Elegimos que el Crawler se ejecutará on demand (bajo demanda), para que podamos ejecutarlo manualmente cuando necesitemos. Otras opciones incluyen usar una frecuencia programada mensual, semanal, diaria, por hora o con una frecuencia personalizada.

 

  • Agregamos una nueva base de datos en el catálogo para alojar las tablas descubiertas por el Crawler haciendo clic sobre “Add database”.

 

  • Proporcionar un nombre y dar clic en “Create”.

 

  • Dar clic en “Next”. Al finalizar, ejecutamos el Crawler marcando la caja de selección y dando clic en “Run crawler”.

Una vez completado el rastreo, Glue agregará nuevas tablas en la base de datos del catálogo creada durante la configuración del Crawler.

En este ejemplo, la base de datos de origen solo contiene una tabla que ha sido descubierta por el Crawler, cuyo nombre es “testdb_”.

 

Paso 5: Crear un proceso ETL con Glue studio

En este ejemplo configuramos un proceso ETL con tres nodos, aprovechando la plantilla proporcionada por defecto. Pero si se quisiera agregar más, encontramos tres tipos: Source (origen), Transform (transformación) y Target (destino). Nuevos nodos pueden agregarse desde los botones disponibles en el lienzo. Cada tipo de nodo nos permite configurar opciones adicionales en el panel de la derecha.

Para crear un nuevo Job desde la consola de Glue:

  • Accedemos a la opción llamada AWS Glue Studio debajo de la sección ETL en el menú del lado izquierdo.
  • Dentro de “Jobs”, crearemos un Job de ETL usando el editor visual. Elegimos «Relational DB» para el origen y «Amazon S3» para el destino. Hacemos clic en el botón «Create».

 

  • Dentro en el editor visual de Glue Studio, configuramos el Data Source JDBC Connection:
    • Elegimos el catálogo de la base de datos de origen que previamente nombramos «onprem-mysql»

 

  • En el nodo Transform: utilizamos una funcionalidad pre-construida, llamada “ApplyMapping”, que sirve para mapear el esquema de origen con el de destino. Podemos agregar pasos adicionales desde el botón “Transform” si fuese necesario, por ejemplo, para ejecutar operaciones como: unión, separación, filtrado, agregar código personalizado, etc.

 

  • Ahora procedemos al nodo data target, bucket de S3:
    • Elegimos Parquet como formato de datos.
    • Proporcionamos una ruta de destino en Amazon S3.

 

  • A continuación, configuramos otros detalles relacionados con este proceso en la pestaña de “Job details”:
    • Name: onprem-etl-s3
    • IAM Role: GlueRoleForOnpremETL
    • Type: Spark
    • Glue Version: Glue 3.0
    • Language: Python 3
    • Worker Type: G.1X
    • Requested Number of Workers: 2

 

Nota: Las opciones de “Worker Type” y “Requested number of Workers” son apropiadas para el ejemplo de este artículo, pero pueden variar para cada caso de uso y son fundamentales para el dimensionamiento de la solución.
Aprenda más sobre trabajos en AWS Glue, Workers y su capacidad de cómputo.

  • Guardamos el Job haciendo con el botón «Save» y lo ejecutamos con el botón “Run”. Podemos dar seguimiento a dicha ejecución desde la pestaña «Runs».

 

El resultado de este proceso genera nuevos objetos en el Bucket S3 creado anteriormente.

 

Paso 6: Crear un nuevo Crawler de Glue para catalogar los datos en S3

Para poder integrar el resultado del proceso anterior con herramientas de análisis y visualización, registraremos los datos en el catálogo creando un nuevo crawler de Glue que rastree los datos procesados.

En la consola de Glue:

  • Creamos un nuevo Crawler

 

  • Para el origen de los datos ahora elegimos “S3” y hacemos clic en «Next”.

 

  • Elegimos el mismo rol de IAM creado previamente y hacemos clic en «Next”.

 

  • En la siguiente pantalla elegimos la frecuencia, que para este caso también será On demand (bajo demanda) y hacemos clic en «Next”.

 

  • Al finalizar, ejecutamos el Crawler para generar nuevas tablas en la base de datos de catálogo.

 

Paso 7: Analizar los datos en S3 con Amazon Athena

En la consola del servicio Athena:

  • Seleccionamos la base de datos del catálogo recientemente generado.
  • Desplegamos el menú haciendo clic en el icono al costado de la tabla y luego en “Preview tables”.

 

El resultado de ejecutar la consulta se ve como en la siguiente imagen.

 

Paso 8: Crear un dashboard en Amazon QuickSight

En la consola de Amazon QuickSight:

  • Accedemos al panel de gestión para configurar el acceso a los recursos necesarios.

 

  • Dentro de «Security & permissions» podemos administrar los accesos.

 

  • Para que los usuarios puedan utilizar QuickSight para acceder a los datos procesados registrados en el catálogo, marcamos las cajas al lado de los nombres de los siguientes servicios:
    • Amazon Athena
    • Amazon S3
  • Para Amazon S3, seleccionar el Bucket que contiene los datos que queremos acceder haciendo clic en “Select S3 buckets”.

 

  • Una vez configurados los accesos, en la sección de Datasets creamos un nuevo conjunto de datos haciendo clic sobre el botón “New dataset”

 

  • Creamos un nuevo set de datos seleccionando Athena como fuente.

 

  • Proporcionamos un nombre.

 

  • Elegimos la base de datos y tabla del catálogo de datos procesados.

 

  • Hacemos clic en «Visualize».

 

  • Ahora podemos explorar los datos, por ejemplo, arrastrando alguno de los campos en el panel de la izquierda hacia el rectángulo central para popular el gráfico.

 

  • También podemos enriquecer la visualización haciendo clic en «Field wells» y arrastrando un segundo campo hacia el rectángulo verde para agregar una nueva dimensión. Es posible modificar el tipo de visualización en la parte inferior del panel izquierdo.

 

Otras consideraciones

En el ejemplo de este tutorial utilizamos un mismo rol de IAM para rastrear los datos en el origen on-premises, el trabajo ETL y para rastrear los datos procesados, sin embargo, es importante considerar el principio del mínimo privilegio donde cada componente tiene solamente los permisos mínimos requeridos. Además, cuando se configura Amazon QuickSight para acceder a los recursos en Amazon Athena, los usuarios pueden acceder a todos los recursos del catálogo de Glue; esto mismo sucede con el acceso a Amazon S3. Si desea limitar, considere gestionar el acceso al catálogo utilizando AWS Lake Formation o la configuración de permisos granulares en Amazon QuickSight.

 

Conclusión

Este artículo demuestra como podemos catalogar, extraer, analizar y visualizar datos desde un origen remoto en una arquitectura híbrida, utilizando servicios sin servidor como AWS Glue, Amazon Athena, Amazon S3 y Amazon QuickSight. Estos servicios nos ayudan a disminuir el esfuerzo relacionado con la administración de servidores, permitiéndonos aprovechar la capacidad de la nube de AWS para recolectar, transformar y compartir información ágilmente y a bajo costo para casos de uso diversos.

 

 

Sobre el autor

Patricio Vazquez es arquitecto de soluciones en Amazon Web Services y tiene 20 años de experiencia trabajando con tecnología informática en roles de ingeniería de infraestructura, arquitectura técnica y empresarial, en países como los Estados Unidos y Argentina.

Actualmente está enfocado en ayudar a clientes del sector público en el diseño e implementación de arquitecturas para soluciones de negocio utilizando Amazon Web Services.

 

 

Revisor técnico

Luis Gerardo Baeza es arquitecto de soluciones en Amazon Web Services y tiene 10 años de experiencia en transformación de procesos, arquitectura empresarial, adopción de metodologías ágiles e integración de tecnología cloud; actualmente enfocado en soluciones de analítica. Luis ha trabajado con empresas del sector salud, retail y seguros en México y Chile.