Automatización de Jobs de ETL entre Amazon RDS for SQL Server y Azure Managed SQL con AWS Glue Studio

Por Daniel Maldonado, Arquitecto de Soluciones de AWS

Introducción

Hoy en día hay múltiples clientes que siguen una estrategia multi-nube, utilizando servicios administrados en la nube para tareas de análisis de datos, como Amazon RDS for SQL Server o Azure SQL Managed Instances para bases de datos y herramientas ETL tradicionales con interfaz visual para procesar los datos. Los clientes buscan una interfaz gráfica porque están familiarizados con SQL pero no con Python o Scala, lo que les dificulta desarrollar scripts personalizados ETL, también encuentran dificultades para automatizar sus pipelines.

Para estos casos de uso, en este blog muestro cómo automatizar Jobs de ETL entre Amazon RDS for SQL Server y Azure SQL Managed Instances con AWS Glue Studio, que forma parte de AWS Glue (Servicio de integración sin servidor administrado). AWS Glue Studio tiene una interfaz gráfica que facilita la creación, ejecución y supervisión de Jobs de extracción, transformación y carga (ETL) y puede crear un calendario para que sus Jobs se ejecuten en momentos específicos.

En las siguientes secciones describiré el proceso paso a paso para configurar las conexiones de Amazon RDS for SQL Server y Azure SQL Managed Instances, y para crear, programar y supervisar Jobs de ETL.

Descripción general de la solución

Para mover datos de una base de datos a otra, tenemos varios servicios que podemos utilizar. Algunos de ellos con diferentes características como, límites de ancho de banda, replicar cambios (CDC), modificaciones de esquema y tabla, etc. Pero cuando necesitamos aplicar transformaciones de datos avanzadas como las proporcionadas por un servicio ETL, podríamos enfrentar desafíos para ejecutar, monitorear y automatizar los Jobs de ETL. Aquí es donde AWS Glue Studio nos ayudará a facilitar estas actividades.

Como se muestra en el siguiente diagrama, usaremos AWS Glue Studio, como capa media para extraer datos de la base de datos de origen, en este caso específico es una instancia administrada de Azure SQL, luego para crear y automatizar los Jobs de ETL, utilizando una de las transformaciones preconstruidas existentes en AWS Glue Studio y finalmente para cargar los datos en la base de datos de destino, en este caso, una instancia de Amazon RDS for SQL Server.

Pasos de la solución

El flujo de trabajo de la solución consta de los siguientes pasos:

Poblar el catálogo de datos de AWS Glue con definiciones de tabla.
1. Crear conexiones.
2. Crear y programar Crawlers.
3. Ejecutar Crawlers.
Crear un Job de ETL que describa la transformación de los datos de origen a destino.
Ejecutar Jobs de ETL para transformar y cargar datos.
Programar Jobs de ETL.
Monitorear Jobs de ETL.

Prerrequisitos

SQL Server Management Studio (SSMS) o alguna herramienta equivalente instalada.
Configurar la conexión VPN entre Amazon Virtual Private Cloud (Amazon VPC) y Azure Private Subnet.
Crear un grupo de seguridad para AWS Glue ENI en su VPC.
Crear un rol de IAM para AWS Glue.
Abrir los puertos de firewall adecuados en Azure Private Subnet.
Tabla en la base de datos de origen (Azure SQL Managed Instance).
- Puede implementar la instancia de base de datos de Azure con este script de inicio rápido.
- Para fines de prueba, importaré la base de datos publica de ejemplo “AdventureWorks» y usaré la tabla «dbo». Employee«.

#Query table
SELECT * FROM [AdventureWorksLT2019].[dbo].[Employee]

Tabla de base de datos de destino (Amazon RDS for SQL Server).
- Puede implementar la instancia de Amazon RDS siguiendo esta guía.
- Puede crear una base de datos y una tabla vacía, siguiendo los siguientes comandos SQL. Esta es la tabla donde se almacenarán los datos procedentes de Azure.

#Create database
CREATE DATABASE AdventureWorksonRDS;
#Create table
CREATE TABLE Employee
(EmpID INT NOT NULL,
EmpName VARCHAR(50) NOT NULL,
Designation VARCHAR(50) NULL,
Department VARCHAR(50) NULL,
JoiningDate DATETIME NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED (EmpID)

#Query table
SELECT * FROM [AdventureWorksonRDS].[dbo].[Employee]

Procedimiento

Creación de conexiones

El primer paso es poblar nuestro catálogo de datos de AWS Glue, con la información del esquema procedente de nuestras fuentes de datos de origen/destino, en nuestro caso Azure SQL Managed Instances es el origen y Amazon RDS for SQL Server es el destino.

Para hacer eso, primero debemos crear «Conexiones«. Una conexión es un objeto del catálogo de datos que almacena información de conexión para un almacén de datos determinado. Las conexiones almacenan credenciales de inicio de sesión, cadenas de URI, información de nube privada virtual (VPC) y mucho más. La creación de conexiones en el catálogo de datos ahorra el esfuerzo de tener que especificar todos los detalles de la conexión cada vez que se crea un crawler o un Job.

Abra la consola de administración de AWS.
Vaya a la sección «Services«, en servicios de «Analytics» encontrará el servicio de «Glue«, haga clic allí.

Creación de conexión para «Azure SQL Managed Instance»

1. En el lado izquierdo de la consola de AWS Glue, haga clic en «AWS Glue Studio«.
2. En el lado izquierdo de la consola de AWS Glue Studio, haga clic en «Connectors«, luego desplácese hacia la derecha y haga clic en «Create connection«.
  1. 3. Especifique la siguiente información y luego haga clic en «Create connection«.
  - a. Name: AzureSQLManaged
  - b. Connection Type: JDBC
  - c. JDBC URL:
  (Siga la sintaxis de SQL Server «jdbc:protocol://host:port;database=db_name«).
  
  Puede encontrar la información del «Host» y “Database Name” en el lado izquierdo del servicio Azure SQL Managed Instance, en la sección «Overview«.
  
  Para este ejemplo específico, la información de nuestra instancia SQL de Azure es:

- Protocol – sqlserver
- Host – adi-qa-sql-managed-instance-test.public.xxxxxxxxxxxx.database.windows.net
- Port – 3342
- Database name – AdventureWorksLT2019

d. Username: *********

e. Password: *********

Creación de conexión para «Amazon RDS for SQL Server»

En el lado izquierdo de la consola de AWS Glue Studio, haga clic en «Connectors«, luego desplácese hacia la derecha y haga clic en «Create connection«.

2. Especifique la siguiente información y luego haga clic en «Create connection«.

Name: AWSRDSforSQL
Connection Type: Amazon RDS
Database engine: Microsoft SQL Server
Database instances: RDS_database_name
Database name: AdventureWorksonRDS
Username: ********
Password: ********

Verá las dos conexiones que acaba de crear en la sección «Connections«.

Creación de Crawlers

Puede utilizar un crawler para poblar el catálogo de datos de AWS Glue con tablas. Este es el método más común utilizado por la mayoría de los usuarios de AWS Glue. Un crawler puede rastrear varios almacenes de datos en una sola ejecución. Al finalizar actualiza el catálogo con las tablas encontradas. Los Jobs de extracción, transformación y carga (ETL) que defina en AWS Glue usan estas tablas del catálogo de datos como orígenes y destinos.

Crear crawler para «Azure SQL Managed Instance»

En el lado izquierdo de la consola de AWS Glue, puede encontrar «Crawlers«, haga clic allí y luego haga clic en «Create crawler».

2. Si los datos no se han asignado a una tabla de Glue, haga clic en «Not yet» y luego haga clic en «Add a data source”.

Especifique «JDBC» como origen de datos, elija la conexión «AzureSQLManaged» creada previamente y especifique la ruta de la base de datos, incluido el esquema «AdventureWorksLT2019/dbo/%».

Verá la fuente de datos que acaba de agregar, haga clic en «Next».

Elija el rol de IAM creado como parte de los requisitos previos y haga clic en «Next«.

Haga clic en «Add database» para crear la base de datos de destino que se creará en el catálogo de Glue.

Especifique el nombre de la base de datos «azuresqlmanaged_db» y haga clic en «Create database»

Elija la base de datos que acaba de crear «azuresqlmanaged_db» y haga clic en «Next»

5. Revise y si todo se ve correcto, haga clic en «Create crawler«.

Crear crawler para «Amazon RDS for SQL Server».

Repita los pasos anteriores del 1 al 5 para crear el crawler para el destino Amazon RDS for SQL Server, utilizando la siguiente información:

Crawler name:AmazonRDSSQL_Crawler
Data source: JDBC
Connection: AWSRDSforSQL
Include Path:AdventureWorksonRDS/dbo/%
IAM Role: AWSGlueServiceRoleDefault
Database name: amazonrdssql_db

Ahora es el momento de ejecutar los crawlers, los encontrará en la sección crawlers con el estado «Ready«, marque la casilla de verificación para ambos y haga clic en «Run«.

Una vez completado, vaya al lado izquierdo de la consola de AWS Glue y haga clic en «Databases«, donde encontrará las bases de datos descubiertas por el crawler.

Haga clic en «Tables» y explore las tablas descubiertas por el crawler que identificó correctamente el tipo de datos como SQL Server.

Choose Tables in the navigation pane and explore the tables discovered by the crawler that correctly identified the data type as SQL Server.

Elija la tabla «adventureworkslt2019_dbo_employee» y revise el esquema creado para el origen de datos.

Creación de Jobs de ETL

Ahora que hemos rastreado nuestras bases de datos de origen y destino, y tenemos los datos del catálogo de datos de AWS Glue, podemos crear Jobs de ETL para cargar y transformar estos datos.

Enel lado izquierdo de la consola de AWS Glue, en el grupo «Data Integration and ETL«, encontrará «Jobs«, haga clic allí.

2. La consola «AWS Glue Studio» está abierta, vaya al lado izquierdo y haga clic en «Jobs«, elija «Visual with a blank canvas«, que proporcionará una interfaz visual para crear nuestros Jobs de ETL, haga clic en «Create»

3. Primero especifique la base de datos «Source«, en este caso ya forma parte del «AWS Glue Data Catalog«.

4. Vaya al lado derecho de la consola en la pestaña «Data source properties» y elija la fuente «Database» y la «Table» que desea extraer, transformar o cargar, para este ejemplo usaremos el azuresqlmanaged_db y la tabla adventureworkslt2019_dbo_employee.

5. Ahora elijamos la tarea «Transform» que realizará nuestro job de ETL, en este caso quiero cargar datos desde la fuente hasta el destino, por lo tanto, selecciono «Apply Mapping«, que mapeará los campos de la base de datos de origen a la base de datos de destino. Como puede ver, tenemos múltiples opciones para remover, renombrar, duplicar, eliminar, etc.

6. En el lado derecho, en la pestaña «Transform«, puede ver los campos de datos que se cargarán, e incluso puede remover algunos de ellos si es necesario.

7. El siguiente paso es elegir la base de datos «Target«, que ya forma parte del «AWS Data Catalog»

8. Vaya al lado derecho de la consola en la pestaña «Data target properties» y elija el destino «Database» y «Table» donde desea cargar los datos transformados, para este ejemplo usaremos amazonrdssql_db y la tabla vacía adventureworksrds_dbo_employee.

9. Finalmente, elija la pestaña «Job details» y especifique el Nombre del Job de ETL y elija el rol de IAM, «Save» para guardar el Job de ETL.

10. Una vez que se crea el Job, recibirá un mensaje de éxito y podrá seleccionar «Run» para ejecutar el Job.

11. Si el Job de ETL se ejecutó correctamente, debe mapear los datos de la base de datos de origen (Azure SQL Manage) a la base de datos de destino (Amazon RDS for SQL), para confirmarlo, puede conectarse a la base de datos de destino mediante SSMS y consultar la base de datos/tabla vacía «AdventureWorksonRDS/dbo.Employee«, ahora debería tener los datos provenientes de la instancia administrada de SQL de Azure.

#Query table
SELECT * FROM [AdventureWorksonRDS].[dbo].[Employee]

Programar y supervisar Jobs de ETL

En AWS Glue Studio, puede crear una programación para que sus Jobs se ejecuten en momentos específicos, esto volverá a importar el conjunto de datos completo y hará referencia al uso de marcadores para realizar cargas incrementales.

Horario

Puede programar sus Jobs de ETL por hora, diariamente, semanalmente, mensualmente o de forma personalizada, según sus necesidades.

Para programar un Job, vaya a la pestaña «Schedules» y haga clic en «Create schedule«.

2. Especifique el «Name» de la tarea de programación, «Frequency», “Start hour”, “Minute of the hour” y opcionalmente una «Description», haga clic en «Create schedule».

3. Confirme que la tarea Schedule se creó y activó correctamente, de esta manera automatiza su Job de ETL para que se ejecute con la frecuencia deseada.

Monitoreo

1. El panel de monitoreo de Jobs proporciona un resumen general de las ejecuciones de Jobs, con totales para los Jobs que están en estado Running, Canceled, Success, o Failed.

The Runs tab shows the jobs for the specified date range and filters. You can filter the jobs on additional criteria, such as status, worker type, job type, and job name.

2. La lista de recursos Job runs muestra los Jobs durante el intervalo de fechas y los filtros especificados. Puede filtrar los Jobs según criterios adicionales, como status, worker type, job type, y job name.

Conclusión

En este blog, repasé los pasos para automatizar los Jobs de ETL utilizando AWS Glue Studio, que es una interfaz gráfica fácil de usar para realizar la mayoría de las tareas de integración de datos, como descubrir y extraer datos de varias fuentes; enriquecer, limpiar, normalizar y combinar datos; y cargar y organizar datos en bases de datos, almacenes de datos y lagos de datos. Además, los usuarios pueden encontrar y obtener acceso fácilmente a los datos mediante el catálogo de datos de AWS Glue. Los ingenieros de datos y los desarrolladores de ETL pueden crear, ejecutar y supervisar visualmente los flujos de trabajo de ETL con unos pocos clics en AWS Glue Studio.

Acerca del autor

Daniel Maldonado es un arquitecto de soluciones de AWS, especialista en cargas de trabajo de Microsoft y tecnologías de Big Data, dedicado a ayudar a los clientes a migrar sus aplicaciones y datos a AWS. Daniel tiene más de 13 años de experiencia trabajando con Tecnologías de la Información y disfruta ayudando a los clientes a cosechar los beneficios de ejecutar sus cargas de trabajo en la nube.

Blog de Amazon Web Services (AWS)