Automatização de Jobs de ETL entre o Amazon RDS for SQL Server e o Azure Managed SQL com o AWS Glue Studio

Por Daniel Maldonado, Arquiteto de Soluções na AWS

Introdução

Atualmente, existem vários clientes que seguem uma estratégia multi-nuvem, usando serviços de nuvem gerenciados para tarefas de análise de dados, como Amazon RDS for SQL Server ou Azure SQL Managed Instances para bancos de dados e ferramentas tradicionais de ETL com uma interface visual para processar os dados. Os clientes procuram uma interface gráfica porque estão familiarizados com SQL, mas não com Python ou Scala, o que dificulta o desenvolvimento de scripts em ETL personalizados e também têm dificuldade em automatizar seus pipelines.

Para esses casos de uso, neste blog, mostro como automatizar Jobs de ETL entre o Amazon RDS for SQL Server e as Azure SQL Managed Instances com o AWS Glue Studio, que faz parte do AWS Glue (serviço de integração totalmente serverless e gerenciado). O AWS Glue Studio tem uma interface gráfica que facilita a criação, execução e monitoramento de Jobs de extração, transformação e carregamento (ETL) e você pode criar um cronograma para que seus Jobs sejam executados em horários específicos.

Nas seções a seguir, descreverei o processo passo a passo para configurar conexões entre Amazon RDS for SQL Server e Azure SQL Managed Instances e para criar, programar e monitorar Jobs de ETL.

Visão geral da solução

Para mover dados de um banco de dados para outro, temos vários serviços que podemos usar. Alguns deles têm características diferentes, como limites de largura de banda, alterações de replicação (CDC), modificações de esquema e tabela etc. Mas quando precisamos aplicar transformações avançadas de dados, como as fornecidas por um serviço de ETL, podemos enfrentar desafios na execução, monitoramento e automatização de Jobs de ETL. É aqui que o AWS Glue Studio nos ajudará a facilitar essas atividades.

Conforme mostrado no diagrama a seguir, usaremos o AWS Glue Studio, como a camada intermediária para extrair dados do banco de dados de origem. Nesse caso específico, é uma Azure SQL Managed Instance e, em seguida, para criar e automatizar os Jobs de ETL, usando uma das transformações pré-construídas existentes no AWS Glue Studio e, finalmente, para carregar os dados no banco de dados de destino, neste caso, uma instância do Amazon RDS for SQL Server.

Passos da solução

O fluxo de trabalho da solução consiste nas seguintes etapas:

Preencha o catálogo de dados do AWS Glue com definições de tabela.
- Crie conexões.
- Crie e programe Crawlers.
- Execute Crawlers.
Crie um Job de ETL que descreva a transformação dos dados da origem para o destino.
Execute Jobs de ETL para transformar e carregar dados.
Agende Jobs de ETL.
Monitore Jobs de ETL.

Pré-requisitos

SQL Server Management Studio (SSMS) ou uma ferramenta equivalente instalada.
Configure a conexão VPN entre o Amazon Virtual Private Cloud (Amazon VPC) e a sub-rede privada do Azure.
Crie um grupo de segurança para a AWS Glue ENI na sua VPC.
Crie uma função do IAM para o AWS Glue.
Abra as portas de firewall apropriadas na Sub-rede Privada do Azure.
Tabela no banco de dados de origem (Azure SQL Managed Instance).
- Você pode implantar a instância do banco de dados do Azure com esse script de início rápido.
- Para fins de teste, vou importar o banco de dados de amostra público “AdventureWorks” e usar a tabela “Employee”.

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

Tabela de banco de dados de destino (Amazon RDS for SQL Server).
- Você pode implantar a instância do Amazon RDS seguindo este guia.
- Você pode criar um banco de dados e uma tabela vazia seguindo os seguintes comandos SQL. Essa é a tabela em que os dados do Azure serão armazenados.

#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]

Procedimento

Criação de conexões

A primeira etapa é preencher nosso catálogo de dados do AWS Glue, com informações de esquema de nossas fontes de dados de origem/destino. No nosso caso, as Azure SQL Managed Instances são a origem e o Amazon RDS for SQL Server é o destino.

Para fazer isso, precisamos primeiro criar “Connections”. Uma conexão é um objeto de catálogo de dados que armazena informações de conexão para um determinado armazenamento de dados. As conexões armazenam credenciais de login, cadeias de URI, informações de nuvem privada virtual (VPC) e muito mais. A criação de conexões no catálogo de dados economiza o esforço de especificar todos os detalhes da conexão sempre que um rastreador ou uma tarefa é criada.

Console de Gerenciamento da AWS
Vá para a seção “Services“, nos serviços “Analytics” você encontrará o serviço “Glue”, clique aqui:

Criação de uma conexão para a “Azure SQL Managed Instance”

No lado esquerdo do console do AWS Glue, clique em “AWS Glue Studio”.

2. No lado esquerdo do console do AWS Glue Studio, clique em “Connectors “, vá para a direita e clique em “Create connection”.

3. Especifique as informações a seguir e clique em “Create connection”.

a. Name: AzureSQLManaged
b. Connection Type: JDBC
c. JDBC URL:

(Siga a sintaxe do SQL Server “jdbc:protocol://host:port;database=db_name”).

Você pode encontrar a informação do “Host” e Database Name”, no lado esquerdo do serviço Azure SQL Managed Instance, sob a seção “Overview”.

Para este ejemplo

Neste exemplo específico, as informações da nossa instância SQL do Azure são:

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

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

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

Criação de uma conexão para “Amazon RDS for SQL Server”

No lado esquerdo do console do AWS Glue Studio, clique em “Connectors “, vá para a direita e clique em “Create connection”.

2. Especifique as informações a seguir e clique em “Create connection”.

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

Você verá as duas conexões que acabou de criar na seção “Connections”.

Criando Crawlers

Você pode usar um crawler para preencher o catálogo de dados do AWS Glue com tabelas. Esse é o método mais comum usado pela maioria dos usuários do AWS Glue. Um crawler pode rastrear vários armazenamentos de dados em uma única execução. Ao terminar, atualize o catálogo com as tabelas encontradas. Os Jobs de extração, transformação e carregamento (ETL) que você define no AWS Glue usam essas tabelas do catálogo de dados como fontes e destinos.

Crie um crawler para a “Azure SQL Managed Instance”

No lado esquerdo do console do AWS Glue, clique em “Crawlers “ e depois clique em “Create crawler”.

2. Se os dados não tiverem sido atribuídos a uma tabela do Glue, clique em “Not yet” e depois em “Add a data source”.

Especifique “JDBC” como fonte de dados, escolha a conexão “AzureSQLManaged” criada anteriormente e especifique o caminho do banco de dados, incluindo o esquema “AdventureWorksLT2019/dbo/%”.

Você verá a fonte de dados que acabou de adicionar e clique em “Next”.

3. Escolha a função do IAM criada como parte dos pré-requisitos e clique em “Next”.

4. Clique em “ Add database” para criar o banco de dados de destino que será criado no catálogo do Glue.

Especifique o nome do banco de dados “azuresqlmanaged_db” e clique em “Create database”

Escolha o banco de dados que você acabou de criar “azuresqlmanaged_db” e clique em “Next”

5. Revise e, se tudo estiver correto, clique em “Create crawler”.

Crie um Crawler para “Amazon RDS for SQL Server”.

Repita as etapas de 1 a 5 acima para criar o crawler para o destino do Amazon RDS for SQL Server, usando as seguintes informações:

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

Agora é hora de executar os crawlers. Você os encontrará na seção de crawlers com o status “Ready “. Selecione ambos e clique em” Run”.

Depois de concluído, vá para o lado esquerdo do console do AWS Glue e clique em “Databases“, onde você encontrará os bancos de dados descobertos pelo crawler.

Clique em “Tables” e explore as tabelas descobertas pelo crawler que identificaram corretamente o tipo de dados como SQL Server.

Escolha a tabela “adventureworkslt2019_dbo_employee” e revise o esquema criado para a fonte de dados.

Criação de Jobs de ETL

Agora que rastreamos nossos bancos de dados de origem e destino e temos os dados do catálogo de dados do AWS Glue, podemos criar Jobs de ETL para carregar e transformar esses dados.

No lado esquerdo do console do AWS Glue, no grupo “Data Integration and ETL”, você encontrará “Jobs”, clique aqui:

2. O console do “AWS Glue Studio” está aberto, vá para o lado esquerdo e clique em “Jobs”, escolha “Visual with a blank canvas”, que fornecerá uma interface visual para a criação de nossos Jobs de ETL. Clique em “Create” .

3. Primeiro, especifique o banco de dados “Source“; nesse caso, ele já faz parte do “AWS Glue Data Catalog”.

4. Vá para o lado direito do console na guia “Data source properties” e escolha a fonte “Database” e a “Table” que você deseja extrair, transformar ou carregar. Neste exemplo, usaremos azuresqlmanaged_db e a tabela adventureworkslt2019_dbo_employee.

5. Agora vamos escolher a tarefa “Transform” que executará nosso Job de ETL. Nesse caso, quero carregar dados da origem para o destino, então seleciono “Apply Mapping“, que mapeará os campos do banco de dados de origem para o banco de dados de destino. Como você pode ver, temos várias opções para remover, renomear, duplicar, excluir etc.

6. No lado direito, na guia “Transform“, você pode ver os campos de dados que serão carregados e até mesmo remover alguns deles, se necessário.

7. A próxima etapa é escolher o banco de dados “Target“, que já faz parte do “AWS Data Catalog“.

8. Vá para o lado direito do console na guia “Data target properties” e escolha o destino “Database” e “Table” onde você deseja carregar os dados transformados. Neste exemplo, usaremos amazonrdssql_db e a tabela vazia adventureworksrds_dbo_employee.

9. Por fim, escolha a guia “Job details“, especifique o nome do Job de ETL, escolha a função do IAM e clique em “Save” para salvar o Job de ETL.

10. Depois que o Job for criado, você receberá uma mensagem de sucesso e poderá selecionar “Run” para executar o Job.

11. Se o Job de ETL for executado com sucesso, deve ter ocorrido o mapeamento dos dados do banco de origem (Azure SQL Manage) para o banco de dados de destino (Amazon RDS for SQL). Para confirmar isso, você pode se conectar ao banco de dados de destino usando SSMS e consultar o banco de dados/tabela vazio “AdventureWorksonRDS/dbo.Employee”. Os dados provenientes da instância gerenciada do Azure SQL devem estar disponíveis.

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

Agende e monitore Jobs de ETL

No AWS Glue Studio, você pode criar um cronograma para que seus Jobs sejam executados em horários específicos. Isso reimportará todo o conjunto de dados e fará referência ao uso de marcadores para realizar cargas incrementais.

Cronograma

Você pode agendar seus Jobs de ETL por hora, dia, semana, mês ou de forma personalizada, dependendo de suas necessidades.

Para agendar um Job, vá até a aba “Schedules” e clique em “Create schedule”.

2. Especifique o “Name” da tarefa de agendamento, “Frequency “, “Start hour”, “Minute of the hour” e, opcionalmente, uma “Description “. Clique em ”Create schedule”.

3. Confirme se a tarefa de agendamento foi criada e ativada com êxito, automatizando assim sua tarefa de ETL para ser executada com a frequência desejada.

Monitoramento

1. O painel de monitoramento de Jobs fornece uma visão geral das execuções de Jobs, com os totais de Jobs que estão nos estados Running, Canceled, Success ou Failed.

2. A aba Runs mostra os Jobs durante o intervalo de datas e filtros especificados. Você pode filtrar Jobs com base em critérios adicionais, como status, tipo de worker, tipo de Job e nome do Job.

Conclusão

Neste blog, analisei as etapas para automatizar Jobs de ETL usando o AWS Glue Studio, que é uma interface gráfica fácil de usar para realizar a maioria das tarefas de integração de dados, como descobrir e extrair dados de várias fontes; enriquecer, limpar, normalizar e combinar dados; e carregar e organizar dados em bancos de dados, data warehouses e data lakes. Além disso, os usuários podem encontrar e acessar dados com facilidade usando o catálogo de dados do AWS Glue. Engenheiros de dados e desenvolvedores de ETL podem criar, executar e monitorar visualmente fluxos de trabalho de ETL com alguns cliques no AWS Glue Studio.

Sobre o autor

Daniel Maldonado é arquiteto de soluções da AWS, especialista em cargas de trabalho da Microsoft e tecnologias de big data, dedicado a ajudar os clientes a migrar seus aplicativos e dados para a AWS. Daniel tem mais de 13 anos de experiência trabalhando com Tecnologia da Informação e gosta de ajudar os clientes a colher os benefícios de executar suas cargas de trabalho na nuvem.

O blog da AWS