Copiar várias tabelas em massa usando o Azure Data Factory usando o PowerShell
APLICA-SE A:
Azure Data Factory Azure Synapse Analytics
Dica
Experimente o Data Factory no Microsoft Fabric, uma solução de análise tudo-em-um para empresas. O Microsoft Fabric abrange desde movimentação de dados até ciência de dados, análise em tempo real, business intelligence e relatórios. Saiba como iniciar uma avaliação gratuita!
Este tutorial demonstra como copiar uma série de tabelas do Banco de Dados SQL do Azure para o Azure Synapse Analytics. Você também pode aplicar o mesmo padrão em outros cenários de cópia. Por exemplo, copiando tabelas do SQL Server/Oracle para o Banco de Dados SQL do Azure/Data Warehouse/Blob do Azure, copiando diferentes caminhos do Blob para tabelas do Banco de Dados SQL do Azure.
De forma mais abrangente, este tutorial envolve as seguintes etapas:
- Criar um data factory.
- Criar o Banco de Dados SQL do Azure, o Azure Synapse Analytics e os serviços vinculados do Armazenamento do Azure.
- Criar conjuntos de dados do Banco de Dados SQL do Azure e do Azure Synapse Analytics.
- Crie um pipeline para consultar as tabelas a serem copiadas e outro pipeline para executar a operação de cópia propriamente dita.
- Iniciar uma execução de pipeline.
- Monitore as execuções de pipeline e de atividade.
Este tutorial usa o Azure PowerShell. Para obter informações sobre como usar outras ferramentas/SDKs para criar um data factory, consulte Guias de início rápido.
Fluxos de trabalho completos
Nesse cenário, temos uma série de tabelas do Banco de Dados SQL do Azure que desejamos para copiar para o Azure Synapse Analytics. Aqui está a sequência lógica de etapas no fluxo de trabalho que ocorre em pipelines:
- O primeiro pipeline verifica a lista de tabelas que precisam ser copiadas nos armazenamentos de dados do coletor. Alternativamente, você pode manter uma tabela de metadados que lista todas as tabelas a serem copiadas para o armazenamento de dados de coletor. Em seguida, o pipeline dispara outro pipeline, que faz iteração por cada tabela no banco de dados e executa a operação de cópia de dados.
- O segundo pipeline realiza a cópia propriamente dita. Ele usa a lista de tabelas como um parâmetro. Para cada tabela da lista, copie a tabela específica no Banco de Dados SQL do Azure para a tabela correspondente no Azure Synapse Analytics usando a cópia preparada por meio do Armazenamento de Blobs e do PolyBase para obter o melhor desempenho. Neste exemplo, o primeiro pipeline envia a lista de tabelas como um valor para o parâmetro.
Se você não tiver uma assinatura do Azure, crie uma conta gratuita antes de começar.
Pré-requisitos
Observação
Recomendamos que você use o módulo Az PowerShell do Azure para interagir com o Azure. Para começar, consulte Instalar o Azure PowerShell. Para saber como migrar para o módulo Az PowerShell, confira Migrar o Azure PowerShell do AzureRM para o Az.
- Azure PowerShell. Siga as instruções em Como instalar e configurar o Azure PowerShell.
- Conta de Armazenamento do Azure. A conta de Armazenamento do Azure é usada como Armazenamento de Blobs de preparo na operação de cópia em massa.
- Banco de dados SQL do Azure. Este banco de dados contém os dados de origem.
- Azure Synapse Analytics. Esse data warehouse contém os dados copiados do Banco de Dados SQL.
Preparar o Banco de Dados SQL e o Azure Synapse Analytics
Prepare o Banco de Dados SQL do Azure de origem:
Crie um banco de dados contendo dados de exemplo do Adventure Works LT no Banco de Dados SQL, seguindo o artigo Criar um banco de dados no Banco de Dados SQL do Azure a seguir. Este tutorial copia todas as tabelas desse exemplo de banco de dados para o Azure Synapse Analytics.
Preparar o Azure Synapse Analytics coletor:
Se você não tiver um workspace do Azure Synapse Analytics, confira o artigo Introdução ao Azure Synapse Analytics para obter as etapas necessárias para criar um.
Crie esquemas de tabela correspondentes no Azure Synapse Analytics. Você usa o Azure Data Factory para migrar/copiar dados em uma etapa posterior.
Permitir que os serviços do Azure acessem o SQL Server
Para o Banco de Dados SQL e o Azure Synapse Analytics, permita que os serviços do Azure acessem o SQL Server. Verifique se a configuração Permitir acesso aos serviços do Azure está ATIVADA para o servidor. Essa configuração permite que o serviço Data Factory leia dados do Banco de Dados SQL do Azure e grave-os no Azure Synapse Analytics. Para verificar e ativar essa configuração, faça as seguintes etapas:
- Clique em Todos os serviços à esquerda e selecione Servidores SQL.
- Selecione seu servidor e clique em Firewall em CONFIGURAÇÕES.
- Na página Configurações de Firewall, clique em ATIVADO para Permitir acesso aos serviços do Azure.
Criar uma data factory
Inicie o PowerShell. Mantenha o Azure PowerShell aberto até o fim deste tutorial. Se você fechar e reabrir, precisará executar os comandos novamente.
Execute o comando a seguir e insira o nome de usuário e senha usados para entrar no portal do Azure:
Connect-AzAccountExecute o comando abaixo para exibir todas as assinaturas dessa conta:
Get-AzSubscriptionExecute o comando a seguir para selecionar a assinatura com a qual deseja trabalhar. Substitua SubscriptionId pela ID da assinatura do Azure:
Select-AzSubscription -SubscriptionId "<SubscriptionId>"Execute o cmdlet Set-AzDataFactoryV2 para criar um data factory. Substitua os espaços reservados por seus próprios valores antes de executar o comando.
$resourceGroupName = "<your resource group to create the factory>" $dataFactoryName = "<specify the name of data factory to create. It must be globally unique.>" Set-AzDataFactoryV2 -ResourceGroupName $resourceGroupName -Location "East US" -Name $dataFactoryNameObserve os seguintes pontos:
O nome da data factory do Azure deve ser globalmente exclusivo. Se você receber o erro a seguir, altere o nome e tente novamente.
The specified Data Factory name 'ADFv2QuickStartDataFactory' is already in use. Data Factory names must be globally unique.Para criar instâncias do Data Factory, você precisa ser um colaborador ou administrador da assinatura do Azure.
Para obter uma lista de regiões do Azure no qual o Data Factory está disponível no momento, selecione as regiões que relevantes para você na página a seguir e, em seguida, expanda Análise para localizar Data Factory: Produtos disponíveis por região. Os armazenamentos de dados (Armazenamento do Azure, Banco de Dados SQL do Azure, etc.) e serviços de computação (HDInsight, etc.) usados pelo data factory podem estar em outras regiões.
Criar serviços vinculados
Neste tutorial, você cria três serviços vinculados respectivamente para origem, coletor e blob de preparo, que inclui conexões os armazenamento de dados:
Criar o serviço vinculado do Banco de Dados SQL do Azure de origem
Crie um arquivo JSON chamado AzureSqlDatabaseLinkedService.json na pasta C:\ADFv2TutorialBulkCopy com o seguinte conteúdo: (Crie a pasta ADFv2TutorialBulkCopy caso ela ainda não exista.)
Importante
Substitua <servername>, <databasename>, <username>@<servername> e <password> pelos valores de seu Banco de Dados SQL do Azure antes de salvar o arquivo.
{ "name": "AzureSqlDatabaseLinkedService", "properties": { "type": "AzureSqlDatabase", "typeProperties": { "connectionString": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>@<servername>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30" } } }No Azure PowerShell, mude para a pasta ADFv2TutorialBulkCopy.
Execute o cmdlet Set-AzDataFactoryV2LinkedService para criar o serviço vinculado: AzureSqlDatabaseLinkedService.
Set-AzDataFactoryV2LinkedService -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "AzureSqlDatabaseLinkedService" -File ".\AzureSqlDatabaseLinkedService.json"Veja o exemplo de saída:
LinkedServiceName : AzureSqlDatabaseLinkedService ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> Properties : Microsoft.Azure.Management.DataFactory.Models.AzureSqlDatabaseLinkedService
Criar o serviço vinculado do Azure Synapse Analytics coletor
Crie um arquivo JSON denominado AzureSqlDWLinkedService.json na pasta C:\ADFv2TutorialBulkCopy, com o conteúdo a seguir:
Importante
Substitua <servername>, <databasename>, <username>@<servername> e <password> pelos valores de seu Banco de Dados SQL do Azure antes de salvar o arquivo.
{ "name": "AzureSqlDWLinkedService", "properties": { "type": "AzureSqlDW", "typeProperties": { "connectionString": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>@<servername>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30" } } }Para criar o serviço vinculado: AzureSqlDWLinkedService, execute o cmdlet Set-AzDataFactoryV2LinkedService.
Set-AzDataFactoryV2LinkedService -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "AzureSqlDWLinkedService" -File ".\AzureSqlDWLinkedService.json"Veja o exemplo de saída:
LinkedServiceName : AzureSqlDWLinkedService ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> Properties : Microsoft.Azure.Management.DataFactory.Models.AzureSqlDWLinkedService
Criar o serviço vinculado do Armazenamento do Azure de preparo
Neste tutorial, você usa o Armazenamento de Blobs do Azure como uma área de preparação intermediária para habilitar o PolyBase para um melhor desempenho de cópia.
Crie um arquivo JSON denominado AzureStorageLinkedService.json na pasta C:\ADFv2TutorialBulkCopy, com o conteúdo a seguir:
Importante
Antes de salvar o arquivo, substitua <accountName> e <accountKey> pelo nome e pela chave da sua conta de armazenamento do Azure, respectivamente.
{ "name": "AzureStorageLinkedService", "properties": { "type": "AzureStorage", "typeProperties": { "connectionString": "DefaultEndpointsProtocol=https;AccountName=<accountName>;AccountKey=<accountKey>" } } }Para criar o serviço vinculado: AzureStorageLinkedService, execute o cmdlet Set-AzDataFactoryV2LinkedService.
Set-AzDataFactoryV2LinkedService -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "AzureStorageLinkedService" -File ".\AzureStorageLinkedService.json"Veja o exemplo de saída:
LinkedServiceName : AzureStorageLinkedService ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> Properties : Microsoft.Azure.Management.DataFactory.Models.AzureStorageLinkedService
Criar conjuntos de dados
Neste tutorial você cria os conjuntos de dados de origem e de coletor, que especificam o local em que os dados são armazenados:
Criar um conjunto de dados para o Banco de Dados SQL de origem
Crie um arquivo JSON denominado AzureSqlDatabaseDataset.json na pasta C:\ADFv2TutorialBulkCopy, com o conteúdo a seguir. O "tableName" é um arquivo fictício, já que posteriormente você usa a consulta SQL na atividade de cópia para recuperar dados.
{ "name": "AzureSqlDatabaseDataset", "properties": { "type": "AzureSqlTable", "linkedServiceName": { "referenceName": "AzureSqlDatabaseLinkedService", "type": "LinkedServiceReference" }, "typeProperties": { "tableName": "dummy" } } }Para criar o conjunto de dados: AzureSqlDatabaseDataset, execute o cmdlet Set-AzDataFactoryV2Dataset.
Set-AzDataFactoryV2Dataset -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "AzureSqlDatabaseDataset" -File ".\AzureSqlDatabaseDataset.json"Veja o exemplo de saída:
DatasetName : AzureSqlDatabaseDataset ResourceGroupName : <resourceGroupname> DataFactoryName : <dataFactoryName> Structure : Properties : Microsoft.Azure.Management.DataFactory.Models.AzureSqlTableDataset
Crie um conjunto de dados para o Azure Synapse Analytics coletor
Crie um arquivo JSON chamado AzureSqlDWDataset.json na pasta C:\ADFv2TutorialBulkCopy com o seguinte conteúdo: O "tableName" é definido como um parâmetro; posteriormente, a atividade de cópia que referencia esse conjunto de dados passará o valor real para o conjunto de dados.
{ "name": "AzureSqlDWDataset", "properties": { "type": "AzureSqlDWTable", "linkedServiceName": { "referenceName": "AzureSqlDWLinkedService", "type": "LinkedServiceReference" }, "typeProperties": { "tableName": { "value": "@{dataset().DWTableName}", "type": "Expression" } }, "parameters":{ "DWTableName":{ "type":"String" } } } }Para criar o conjunto de dados: AzureSqlDWDataset, execute o cmdlet Set-AzDataFactoryV2Dataset.
Set-AzDataFactoryV2Dataset -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "AzureSqlDWDataset" -File ".\AzureSqlDWDataset.json"Veja o exemplo de saída:
DatasetName : AzureSqlDWDataset ResourceGroupName : <resourceGroupname> DataFactoryName : <dataFactoryName> Structure : Properties : Microsoft.Azure.Management.DataFactory.Models.AzureSqlDwTableDataset
Criar pipelines
Neste tutorial, você cria dois pipelines:
Criar o pipeline "IterateAndCopySQLTables"
Esse pipeline usa uma lista de tabelas como um parâmetro. Para cada tabela da lista, ele copia dados da tabela no Banco de Dados SQL do Azure para o Azure Synapse Analytics usando a cópia preparada e o PolyBase.
Crie um arquivo JSON denominado IterateAndCopySQLTables.json na pasta C:\ADFv2TutorialBulkCopy, com o conteúdo a seguir:
{ "name": "IterateAndCopySQLTables", "properties": { "activities": [ { "name": "IterateSQLTables", "type": "ForEach", "typeProperties": { "isSequential": "false", "items": { "value": "@pipeline().parameters.tableList", "type": "Expression" }, "activities": [ { "name": "CopyData", "description": "Copy data from Azure SQL Database to Azure Synapse Analytics", "type": "Copy", "inputs": [ { "referenceName": "AzureSqlDatabaseDataset", "type": "DatasetReference" } ], "outputs": [ { "referenceName": "AzureSqlDWDataset", "type": "DatasetReference", "parameters": { "DWTableName": "[@{item().TABLE_SCHEMA}].[@{item().TABLE_NAME}]" } } ], "typeProperties": { "source": { "type": "SqlSource", "sqlReaderQuery": "SELECT * FROM [@{item().TABLE_SCHEMA}].[@{item().TABLE_NAME}]" }, "sink": { "type": "SqlDWSink", "preCopyScript": "TRUNCATE TABLE [@{item().TABLE_SCHEMA}].[@{item().TABLE_NAME}]", "allowPolyBase": true }, "enableStaging": true, "stagingSettings": { "linkedServiceName": { "referenceName": "AzureStorageLinkedService", "type": "LinkedServiceReference" } } } } ] } } ], "parameters": { "tableList": { "type": "Object" } } } }Para criar o pipeline: IterateAndCopySQLTables, execute o cmdlet Set-AzDataFactoryV2Pipeline.
Set-AzDataFactoryV2Pipeline -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "IterateAndCopySQLTables" -File ".\IterateAndCopySQLTables.json"Veja o exemplo de saída:
PipelineName : IterateAndCopySQLTables ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> Activities : {IterateSQLTables} Parameters : {[tableList, Microsoft.Azure.Management.DataFactory.Models.ParameterSpecification]}
Criar o pipeline "GetTableListAndTriggerCopyData"
Esse pipeline realiza duas etapas:
- Pesquisa a tabela do sistema do Banco de Dados SQL do Azure para obter a lista de tabelas a serem copiadas.
- Dispara o pipeline "IterateAndCopySQLTables" para fazer a cópia de dados propriamente dita.
Crie um arquivo JSON denominado GetTableListAndTriggerCopyData.json na pasta C:\ADFv2TutorialBulkCopy, com o conteúdo a seguir:
{ "name":"GetTableListAndTriggerCopyData", "properties":{ "activities":[ { "name": "LookupTableList", "description": "Retrieve the table list from Azure SQL database", "type": "Lookup", "typeProperties": { "source": { "type": "SqlSource", "sqlReaderQuery": "SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE TABLE_TYPE = 'BASE TABLE' and TABLE_SCHEMA = 'SalesLT' and TABLE_NAME <> 'ProductModel'" }, "dataset": { "referenceName": "AzureSqlDatabaseDataset", "type": "DatasetReference" }, "firstRowOnly": false } }, { "name": "TriggerCopy", "type": "ExecutePipeline", "typeProperties": { "parameters": { "tableList": { "value": "@activity('LookupTableList').output.value", "type": "Expression" } }, "pipeline": { "referenceName": "IterateAndCopySQLTables", "type": "PipelineReference" }, "waitOnCompletion": true }, "dependsOn": [ { "activity": "LookupTableList", "dependencyConditions": [ "Succeeded" ] } ] } ] } }Para criar o pipeline: GetTableListAndTriggerCopyData, execute o cmdlet Set-AzDataFactoryV2Pipeline.
Set-AzDataFactoryV2Pipeline -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "GetTableListAndTriggerCopyData" -File ".\GetTableListAndTriggerCopyData.json"Veja o exemplo de saída:
PipelineName : GetTableListAndTriggerCopyData ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> Activities : {LookupTableList, TriggerCopy} Parameters :
Iniciar e monitorar uma execução de pipeline
Inicie uma execução de pipeline para o pipeline principal "GetTableListAndTriggerCopyData" e capture a ID da execução de pipeline para monitoramento futuro. Abaixo, ele dispara a execução do pipeline "IterateAndCopySQLTables", conforme especificado na atividade ExecutePipeline.
$runId = Invoke-AzDataFactoryV2Pipeline -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -PipelineName 'GetTableListAndTriggerCopyData'Execute o script a seguir para verificar continuamente o status de execução do pipeline GetTableListAndTriggerCopyData e imprima o resultado final da execução de atividade e da execução do pipeline.
while ($True) { $run = Get-AzDataFactoryV2PipelineRun -ResourceGroupName $resourceGroupName -DataFactoryName $DataFactoryName -PipelineRunId $runId if ($run) { if ($run.Status -ne 'InProgress') { Write-Host "Pipeline run finished. The status is: " $run.Status -ForegroundColor "Yellow" Write-Host "Pipeline run details:" -ForegroundColor "Yellow" $run break } Write-Host "Pipeline is running...status: InProgress" -ForegroundColor "Yellow" } Start-Sleep -Seconds 15 } $result = Get-AzDataFactoryV2ActivityRun -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -PipelineRunId $runId -RunStartedAfter (Get-Date).AddMinutes(-30) -RunStartedBefore (Get-Date).AddMinutes(30) Write-Host "Activity run details:" -ForegroundColor "Yellow" $resultAqui está a saída da execução de exemplo:
Pipeline run details: ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> RunId : 0000000000-00000-0000-0000-000000000000 PipelineName : GetTableListAndTriggerCopyData LastUpdated : 9/18/2017 4:08:15 PM Parameters : {} RunStart : 9/18/2017 4:06:44 PM RunEnd : 9/18/2017 4:08:15 PM DurationInMs : 90637 Status : Succeeded Message : Activity run details: ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> ActivityName : LookupTableList PipelineRunId : 0000000000-00000-0000-0000-000000000000 PipelineName : GetTableListAndTriggerCopyData Input : {source, dataset, firstRowOnly} Output : {count, value, effectiveIntegrationRuntime} LinkedServiceName : ActivityRunStart : 9/18/2017 4:06:46 PM ActivityRunEnd : 9/18/2017 4:07:09 PM DurationInMs : 22995 Status : Succeeded Error : {errorCode, message, failureType, target} ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> ActivityName : TriggerCopy PipelineRunId : 0000000000-00000-0000-0000-000000000000 PipelineName : GetTableListAndTriggerCopyData Input : {pipeline, parameters, waitOnCompletion} Output : {pipelineRunId} LinkedServiceName : ActivityRunStart : 9/18/2017 4:07:11 PM ActivityRunEnd : 9/18/2017 4:08:14 PM DurationInMs : 62581 Status : Succeeded Error : {errorCode, message, failureType, target}Você pode obter a ID da execução de pipeline "IterateAndCopySQLTables" e verificar o resultado detalhado da execução da atividade conforme demonstrado a seguir.
Write-Host "Pipeline 'IterateAndCopySQLTables' run result:" -ForegroundColor "Yellow" ($result | Where-Object {$_.ActivityName -eq "TriggerCopy"}).Output.ToString()Aqui está a saída da execução de exemplo:
{ "pipelineRunId": "7514d165-14bf-41fb-b5fb-789bea6c9e58" }$result2 = Get-AzDataFactoryV2ActivityRun -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -PipelineRunId <copy above run ID> -RunStartedAfter (Get-Date).AddMinutes(-30) -RunStartedBefore (Get-Date).AddMinutes(30) $result2Conecte-se ao Azure Synapse Analytics coletor e confirme se os dados foram corretamente copiados do Banco de Dados SQL do Azure.
Conteúdo relacionado
Neste tutorial, você realizará os seguintes procedimentos:
- Criar um data factory.
- Criar o Banco de Dados SQL do Azure, o Azure Synapse Analytics e os serviços vinculados do Armazenamento do Azure.
- Criar conjuntos de dados do Banco de Dados SQL do Azure e do Azure Synapse Analytics.
- Crie um pipeline para consultar as tabelas a serem copiadas e outro pipeline para executar a operação de cópia propriamente dita.
- Iniciar uma execução de pipeline.
- Monitore as execuções de pipeline e de atividade.
Avance para o tutorial a seguir para saber mais sobre como copiar dados incrementalmente de uma origem para um destino: