Azure Data Factory: Activities General: Script

Olá galera o/

Vamos a mais uma postagem sobre o Azure Data Factory, se você não conhece o Azure Data Factory (ADF) abaixo segue a minha série de posts sobre o mesmo, os posts estão bem legais e detalhados.

• Posts sobre Azure Data Factory >> LINK <<

Lembrando da nossa nova série de postagens, aonde vamos aprender sobre as Activities do ADF funcionam e como podemos utilizar em nossos projetos da realidade. Vou selecionar as activities que mais utilizo no meu dia a dia.

Hoje vamos falar sobre a mais nova Activity adicionada no Azure Data Factory/Integrate do Synapse Analytics, a activity SCRIPT.

A activity SCRIPT fornece a capacidade de executar uma ou várias instruções SQL no seu pipeline. [Microsoft].

• Esta atividade de script pode ser usada para vários propósitos:
  • Truncar uma tabela ou view para preparação de inserção de dados.
  • Criar, alterar e eliminar objetos de banco de dados, como tabelas e views. Comandos DDL.
  • Recriar tabelas fatos e dimensões antes de carregar dados nelas.
  • Executar Stored Procedures.
  • Usar os dados de output retornado de uma consulta em uma atividade downstream.

• Suporta os seguintes data stores:
  • Azure SQL Database
  • Azure Synapse Analytics
  • SQL Server Database
  • Oracle
  • Snowflake

• Activity Output

Property name	Description	Condition
resultSetCount	Contagem de result sets retornados pelo script.	Sempre
resultSets	Array que contém todos os result sets.	Sempre
resultSets.rowCount	Total de linhas do result sets.	Sempre
resultSets.rows	Array de linhas no result sets	Sempre
recordsAffected	Contagem de linhas afetadas pelo script.	Se scriptType for NonQuery.
outputParameters	Os parâmetros de saída do script.	Se o tipo de parâmetro for Output ou InputOutput.
outputLogs	Os logs escritos pelo script, por exemplo, print statement.	Se o conector der suporte à instrução de log e enableScriptLogs for true e logLocationSettings não for fornecido.
outputLogsPath	O caminho completo do arquivo de log.	Se enableScriptLogs for true e logLocationSettings for fornecido.
outputTruncated	Indicador se a saída excede os limites e fica truncada.	Se a saída exceder os limites.

Mais informações no site da Microsoft >> LINK <<

• Para exemplificar, vamos realizar os seguintes testes:
  1. Criar um pipeline chamado pipeline_script para executar 3 comandos SQL
     1. Um comando para Dropar uma tabela caso exista na database.
     2. Um comando para fazer um SELECT INTO e criar uma tabela na database a partir de um SELECT.
     3. Executar um select na nova tabela criada na database e analisar seu retorno a partir do output do componente.
  2. Criar um pipeline chamado pipeline_script2 para executar um comando de select na nova tabela e passar o output para um outro componente.
     1. Configurar o componente Filter para ler o output do componente Script
     2. Configurar o componente Filter para filtrar um registro.
     3. analisar o resultado do filtro realizado
  3. Analisar a tabela criada via SSMS.

Criando o pipeline com a activity de Script

Com o Azure Data Factory aberto vamos realizar os seguintes passos:

1 – Clicar no ícone do Editor (Ícone do Lápis). Selecionar Pipelines, clicar nos 3 pontinhos (…) ao lado do nome Pipeline.
2 – Selecionar a opção listada NEW PIPELINE.

Com a tela do pipeline designer aberta, vamos fazer os seguintes passos:

1 – Na tela de Properties (lado direito), iremos mudar o nome do pipeline para pipeline_script.
2 – Clicamos no ícone do properties para ocultar a tela lateral.
3 – Clicamos nas Activities e procuramos a seção General das Activities.
4 – Selecionamos a activity SCRIPT e arrastamos para a tela do pipeline designer. Devemos arrastar 3 componentes Script para a tela do pipeline designer. Todos os componentes devem estar conectados.
5 – Clicamos em cima da primeira activity adicionada.
6 – Na aba General, mudamos o valor da propriedade Name para drop_table_if_exists.
7 – Clicamos na aba Settings.

Na aba settings, vamos realizar a configuração do componente script.

1 – Selecione o Linked Service que este comando via utilizar. Utilizei o linked service com o Azure SQL.
2 – Selecionamos a categoria de script. Atualmente temos 2 categorias. Selecionamos NonQuery.

2.1 – Query: Comandos que retornam um ou mais conjuntos de resultados. Ex.: Um comando de select sob uma tabela.

2.2 – NonQuery: Comando que executam operações de catálogo (por exemplo, consultando a estrutura de um banco de dados ou criando objetos de banco de dados, como tabelas) ou alteram os dados em um banco de dados executando instruções UPDATE, INSERT ou DELETE.

3 – Na label de script que está vazia, vamos adicionar um script que vai verificar se uma tabela existe em nossa database, caso sim, a tabela será apagada da database.

--Script utilizado

IF OBJECT_ID(N'SalesLT.TB_ADF_SCRIPT_ACTIVITY', N'U') IS NOT NULL  

DROP TABLE [SalesLT].[TB_ADF_SCRIPT_ACTIVITY];

1 – Também podemos utilizar a funcionalidade Script Parameters, porém este parâmetro é para conectores Oracle e Snowflake, apenas o parâmetro posicional é suportado.
2 – Caso queira criar um parâmetro, devemos clicar em +New.
3 – Após clicar em +New, um novo parâmetro é adicionado, devemos informar um nome, qual o tipo, qual valor e a direção que podemos obter os dados, atualmente para direção possuímos os valores: Input, output e input/output.

Obs: Não utilizamos a feature Script Parameters nesta postagem

1 – Outra funcionalidade que podemos utilizar é a parte do Advanced. Nesta funcionalidade podemos habilitar o a flag Enable Logging, ao marcar resta flag, podemos armazenar os dados de processamento em um blob storage e ou data lake
2 – Desmarcar a flag Enable Logging.

Obs: Não utilizamos a feature Advanced nesta postagem.

De volta ao pipeline designer .

1 – Clicamos na segunda activity do nosso pipeline.
2 – Na aba General, alteramos o valor do atributo Name para create_table_select_into.
3 – Clicamos na aba Settings.

Com a aba settings da nossa segunda activity de Script aberta:

1 – Selecione o Linked Service que este comando via utilizar. Utilizei o linked service com o Azure SQL.
2 – Selecionamos a categoria de script. Atualmente temos 2 categorias. Selecionamos NonQuery.

2.1 – Query: Comandos que retornam um ou mais conjuntos de resultados. Ex.: Um comando de select sob uma tabela.

2.2 – NonQuery: Comando que executam operações de catálogo (por exemplo, consultando a estrutura de um banco de dados ou criando objetos de banco de dados, como tabelas) ou alteram os dados em um banco de dados executando instruções UPDATE, INSERT ou DELETE.

3 – Na label de script que está vazia, vamos adicionar um script que vai criar uma tabela no schema SALESLT chamada TB_ADF_SCRIPT_ACTIVITY na database do azure, está tabela é criada utilizando um procedimento se chama SELECT INTO, ou seja, a tabela é criada com base em um select feito em outra tabela da database, neste caso, utilizamos a tabela Customer no schema SalesLT.

--Script utilizado

Select * 
into SalesLT.TB_ADF_SCRIPT_ACTIVITY
from SalesLT.Customer

De volta ao pipeline designer .

1 – Clicamos na segunda activity do nosso pipeline.
2 – Na aba General, alteramos o valor do atributo Name para select_new_table.
3 – Clicamos na aba Settings.

Com a aba settings da nossa terceira activity de Script aberta:

1 – Selecione o Linked Service que este comando via utilizar. Utilizei o linked service com o Azure SQL.
2 – Selecionamos a categoria de script. Atualmente temos 2 categorias. . Selecionamos Query.

2.1 – Query: Comandos que retornam um ou mais conjuntos de resultados. Ex.: Um comando de select sob uma tabela.

2.2 – NonQuery: Comando que executam operações de catálogo (por exemplo, consultando a estrutura de um banco de dados ou criando objetos de banco de dados, como tabelas) ou alteram os dados em um banco de dados executando instruções UPDATE, INSERT ou DELETE.

3 – Na label de script que está vazia, vamos adicionar um script que vai realizar uma consulta (select) na nova tabela criada nos passos anteriores.

4 – Clicamos no botão Save para guardar as alterações realizadas. Este botão fica disponível quando há integração com o GitHub ou Azure Devops. Post sobre integração com o GitHub. >> LINK <<

5 – Clicar no botão Debug para executar o pipeline.

--Script utilizado

select *
from [SalesLT].[TB_ADF_SCRIPT_ACTIVITY]

Analisando Outputs da activity Script

Para realizar a análise da execução dos componentes vamos fazer os seguintes procedimentos:

1 – Na área branca do pipeline designer.
2 – Clicar na Aba Output para analisarmos a execução.

Ao analisar o output da primeira activity, notamos que as propriedades resultSetCount (Contagem de result sets retornados pelo script), recordsAffected (Contagem de linhas afetadas pelo script) e resultSets (Array que contém todos os result sets) não nos retornaram nada.

Porém, o script foi executado com sucesso pelo fato de termos selecionado a opção NonQuery e o comando ser do tipo DDL.

o analisar o output da segunda activity, notamos que a propriedade recordsAffected (Contagem de linhas afetadas pelo script)retornou que 847 linhas foram afetadas, ou seja, o script executou, criou a nova tabela com a mesma estrutura da SalesLT.Customer e inseriu os 847 registros existentes nesta tabela na tabela TB_ADF_SCRIPT_ACTIVITY.

Lembrando que, selecionamos a opção NonQuery e o comando ser do tipo DML.

Ao analisar o output da terceira activity, notamos que as propriedades resultSetCount (Contagem de result sets retornados pelo script) retornou o valor 1 e a propriedade resultSets (Array que contém todos os result sets) retornou duas novas propriedades interna: resultSets.rowCount (Total de linhas do result sets) que possui o valor 847 de linhas lidas e resultSets.rows (Array de linhas no result sets), ou seja, colunas e valores contidos nelas.

Utilizamos a opção Query e o comando tipo DQL (Data Query Language).

Passando os valores do ResultSets para outro componente

Neste exemplo, vamos aprender como passar os valores utilizados no resultset da activity para um outro componente como um Filter ou ForEach.

1 – Criamos um novo pipeline chamado pipeline_script2.
2 – Clicamos no ícone do properties para ocultar a tela lateral.
3 – Na categoria General das activities > Selecionamos a activity de script, puxamos e adicionamos a mesma no pipeline designer. Na aba General da activity devemos colocar o nome de select_new_table na propriedade Name. Vamos utilizar a tabela criada nos passos anteriores. Clicamos em cima da activity adicionada.
4 – Selecionamos a aba Settings
5 – Selecione o Linked Service que este comando via utilizar. Utilizei o linked service com o Azure SQL.
6 – Selecionamos a categoria de script. Atualmente temos 2 categorias. . Selecionamos Query.

6.1 – Query: Comandos que retornam um ou mais conjuntos de resultados. Ex.: Um comando de select sob uma tabela.

6.2 – NonQuery: Comando que executam operações de catálogo (por exemplo, consultando a estrutura de um banco de dados ou criando objetos de banco de dados, como tabelas) ou alteram os dados em um banco de dados executando instruções UPDATE, INSERT ou DELETE.

7 – Na label de script que está vazia, vamos adicionar um script que vai realizar uma consulta (select) na nova tabela criada nos passos anteriores.
8 – Na categoria Iteration & conditionals das activities > Selecionamos a activity Filter e adicionamos ao pipeline designer.
9 – Realizamos uma ligação entre o componente script e o componente filter.

--Script utilizado 

select *
from [SalesLT].[TB_ADF_SCRIPT_ACTIVITY]

Para realizar a configuração do componente filter vamos realizar os seguintes passos:

1 – Clicar em cima do componente.
2 – Clicar na Aba Settings.
3 – Clicar na propriedade items (Array de entrada onde o filtro deve ser aplicado) e clicar na opção Add Dynamic content.
4 – Dado que a propriedade items foi configurada vamos realizar o mesmo procedimento para a propriedade Condition (Condição a ser usada para filtrar a entrada).

No Add dynamic content da propriedade Items vamos adicionar o seguinte código a seguir, porém, precisamos entender o porquê do mesmo.

Lembrem que o resultSets é um array que contém todos os dados retornados pela query e este array possui dois elementos, o resultSets.rowCount que é o Total de linhas do result sets e o r esultSets.rows que é outro Array de linhas no result sets.

1 – Explicação sobre o código do Add Dynamic Content

No trecho marcado em Azul estamos informando que vamos buscar os elementos da Activity com nome select_new_table.

No trecho em vermelho informamos que queremos a saída desde componente, ou seja, seu output. Este output possui um elemento chamado resultSets, que, possui 2 sub-elementos rowCount e rows. Para ler sub-elemento de um array, é necessário definir qual o índice a ser referenciado e qual o elemento que você deseja retornar.

No techo em verde, eu defino índice eu necessito que neste caso é 0, ou seja, os primeiros elementos listados neste array e depois eu defino qual o elemento desejo, que neste caso é o elemento rows.

2 – Clicar em OK para aplicar a fórmula na propriedade do filtro.

-- Código utilizado

@activity('select_new_table').output.resultSets[0]['rows']

Para o Add dynamic content da propriedade Condition vamos adicionar o seguinte código a seguir, porém, precisamos entender o porquê do mesmo.

Visto que, conseguimos ter acesso aos dados contidos no elemento ROWS do nosso ResultSets, agora podemos utilizar os sub-elementos contidos no ROWS para realizar alguns categorias de filtro.

1 – Explicação sobre o código do Add Dynamic Content

Utilizamos uma função chamada EQUALS que recebe 2 parâmetros. Esta função verifica se o valor de um item é igual a um determinado valor que eu informe. O primeiro parâmetro desta função será um elemento do meu array de ROWS e o segundo parâmetro será o valor 10.

Para acessar o valor de um elemento de um array, utilizamos a cláusula ITEM().NOME_DO_ELEMENTO, para este exemplo, vamos retornar o CustomerID, ou seja, o primeiro parâmetro da função será item().CustomerID e o segundo parâmetro o valor 10.

Estou informando para a função que, só quero clientes quando o valor do CustomerID for igual a 10.

2 – Clicar em OK para aplicar a fórmula na propriedade do filtro.

-- Código utilizado

@equals( item().CustomerID, 10)

Agora que o componente de filter foi configurado, vamos executar o processo e analisar as saídas.

1 – Clicar no botão SAVE, para salvar o processo no GitHub ou Azure Devops.
2 – Clicar no botão DEBUG para executar o pipeline.

Primeiro vamos realizar a análise do output do componente Script.

1 – Clicar no ícone do output.
2 – Notamos que as propriedades resultSetCount (Contagem de result sets retornados pelo script) retornou o valor 1 e a propriedade resultSets (Array que contém todos os result sets) retornou os seus dois sub-elementos rowCount (Total de linhas do result sets) e .rows Array de linhas no result sets), ou seja, colunas e valores contidos nelas.
3 – Clicamos no botão X para fechar o pop-up do output.

Primeiro vamos realizar a análise do output do componente Filter.

1 – Clicar no ícone do output.
2 – Notamos que o filtro recebeu uma massa de 847 items como exibido no elemento ItemsCount, ele conseguiu filtrar 1 como exibido no elemento FilteredItemsCount. Por último, temos o elemento Value, ele mostra os valores da massa de dados que atenderam a condição do filtro.

Para este exemplo, foi somente 1 cliente que atendeu a condição do filtro, quando o CustomerID for igual a 10.

Por desencargo de consciência eu fiz um SELECT na tabela SalesLT.TB_ADF_SCRIPT_ACTIVITY utilizando a ferrament SQL Server Management Studio. Notem que, a tabela existe na minha database e o seu processo de criação foi utilizando a activity script do azure data factory.

-- Código Utilizado
SELECT *
FROM SalesLT.TB_ADF_SCRIPT_ACTIVITY

Considerações Finais

Hoje aprendemos como utilizar a activity SCRIPT no Azure Data Factory / Integrate, vimos como funcionam as saídas e como podemos passa-las para outros componentes.

A activity SCRIPT é nova e ainda pode passar por diversas alterações durante testes e utilizações em projetos reais, então, por conta disto o conteúdo deste post pode ser atualizado daqui a algum tempo. Esta activity vai ser muito útil para evitar a utilização do componente procedure e lookup. Por enquanto vamos testar e trazer mais insights sobre este componente.

Atenção: Se você esta com uma conta gratuita, pay-as-you-go ou de estudante, não esquecer de apagar os recursos criados nesta postagem. Para contas de estudante e gratuita a exclusão dos recursos evita o consumo dos créditos disponíveis, além disso, evita que aconteça cobrança indevida em sua subscription (assinatura). Para a conta pay-as-you-go é cobrado do cartão de crédito conforme o uso dos serviços, então, recomendo apagar para não gerar cobrança no seu cartão de crédito.

Quem quiser mandar comentários, sugestões, criticas e complementos, fiquem a vontade, pois feedbacks positivos ou negativos engradecem meu conhecimento para poder melhorar as postagens para vocês.

Até a próxima o/

Acessem nossas Redes Sociais:

• Instagram
• LinkedIn
• YouTube
• Link

Publicidade