Copiar várias tabelas em massa usando o Azure Data Factory usando o PowerShell
APLICA-SE A: 
Azure Data Factory Azure Synapse Analytics
Gorjeta
Experimente o Data Factory no Microsoft Fabric, uma solução de análise tudo-em-um para empresas. O Microsoft Fabric abrange tudo, desde a movimentação de dados até ciência de dados, análises em tempo real, business intelligence e relatórios. Saiba como iniciar uma nova avaliação gratuitamente!
Este tutorial demonstra a cópia de várias tabelas do Banco de Dados SQL do Azure para o Azure Synapse Analytics. Também pode aplicar o mesmo padrão noutros cenários de cópia. Por exemplo, copiar tabelas do SQL Server/Oracle para a Base de Dados SQL do Azure/Data Warehouse/Blob do Azure, copiar caminhos diferentes do Blob para tabelas de Base de Dados SQL do Azure.
A um nível elevado, este tutorial envolve os seguintes passos:
- Criar uma fábrica de dados.
- Crie o Banco de Dados SQL do Azure, o Azure Synapse Analytics e os serviços vinculados do Armazenamento do Azure.
- Crie conjuntos de dados do Banco de Dados SQL do Azure e do Azure Synapse Analytics.
- Criar um pipeline para procurar as tabelas a copiar e outro pipeline para executar a operação de cópia real.
- Iniciar uma execução de pipeline.
- Monitorizar o pipeline e execuções de atividades.
Este tutorial utiliza o Azure PowerShell. Para saber mais sobre como utilizar outras ferramentas/SDKs para criar uma fábrica de dados, veja Inícios rápidos.
Fluxo de trabalho ponto a ponto
Nesse cenário, temos várias tabelas no Banco de Dados SQL do Azure que queremos copiar para o Azure Synapse Analytics. Segue-se a sequência lógica de passos no fluxo de trabalho que ocorre nos pipelines:
- O primeiro pipeline procura a lista de tabelas que têm de ser copiadas para os arquivos de dados de sink. Em alternativa, pode manter uma tabela de metadados que apresenta uma lista de todas as tabelas a copiar para o arquivo de dados de sink. Em seguida, o pipeline aciona outro pipeline, que itera cada tabela na base de dados e executa a operação de cópia de dados.
- O segundo pipeline executa a cópia real. Aceita a lista de tabelas como um parâmetro. Para cada tabela na lista, copie a tabela específica no Banco de Dados SQL do Azure para a tabela correspondente no Azure Synapse Analytics usando cópia em estágios via armazenamento de Blob e PolyBase para obter o melhor desempenho. Neste exemplo, o primeiro pipeline passa a lista de tabelas como um valor para o parâmetro.
Se não tiver uma subscrição do Azure, crie uma conta gratuita antes de começar.
Pré-requisitos
Nota
Recomendamos que utilize o módulo Azure Az do PowerShell para interagir com o Azure. Para começar, consulte Instalar o Azure PowerShell. Para saber como migrar para o módulo do Az PowerShell, veja Migrar o Azure PowerShell do AzureRM para o Az.
- Azure PowerShell. Siga as instruções em How to install and configure Azure PowerShell (Como instalar e configurar o Azure PowerShell).
- Conta do Armazenamento do Azure. A conta de Armazenamento do Azure é utilizada como armazenamento de blobs de teste na operação de cópia em massa.
- Base de Dados SQL do Azure. Esta base de dados contém os dados de origem.
- Azure Synapse Analytics. Este armazém de dados contém os dados copiados da Base de Dados SQL.
Preparar o Banco de Dados SQL e o Azure Synapse Analytics
Preparar a Base de Dados SQL do Azure de origem:
Crie um banco de dados com os 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. Este tutorial copia todas as tabelas deste banco de dados de exemplo para o Azure Synapse Analytics.
Prepare o coletor Azure Synapse Analytics:
Se você não tiver um espaço de trabalho do Azure Synapse Analytics, consulte o artigo Introdução ao Azure Synapse Analytics para conhecer as etapas para criar um.
Crie esquemas de tabela correspondentes no Azure Synapse Analytics. Irá utilizar o Azure Data Factory para migrar/copiar dados num passo mais à frente.
Serviços do Azure para aceder ao 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 seu servidor. Essa configuração permite que o serviço Data Factory leia dados do Banco de Dados SQL do Azure e grave dados no Azure Synapse Analytics. Para verificar e ativar desta definição, execute os passos seguintes:
- Clique em Todos os serviços à esquerda e clique em Servidores SQL.
- Selecione o seu servidor e clique em Firewall em DEFINIÇÕES.
- Na página Definições de firewall, clique em ATIVAR para Permitir acesso aos serviços do Azure.
Criar uma fábrica de dados
Inicie o Azure PowerShell. Mantenha o Azure PowerShell aberto até ao fim deste tutorial. Se o fechar e reabrir, terá de executar os comandos novamente.
Execute o comando seguinte e introduza o nome de utilizador e a palavra-passe que utiliza para iniciar sessão no Portal do Azure:
Connect-AzAccountExecute o comando seguinte para ver todas as subscrições desta conta:
Get-AzSubscriptionExecute o comando seguinte para selecionar a subscrição com a qual pretende trabalhar. Substitua SubscriptionId pelo ID da sua subscrição do Azure:
Select-AzSubscription -SubscriptionId "<SubscriptionId>"Execute o cmdlet Set-AzDataFactoryV2 para criar um data factory. Substitua os marcadores de posição pelos 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 $dataFactoryNameTenha em conta os seguintes pontos:
O nome do Azure Data Factory deve ser globalmente exclusivo. Se receber o erro seguinte, 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, tem de ser Contribuidor ou Administrador da subscrição do Azure.
Para obter uma lista de regiões do Azure em que o Data Factory está atualmente disponível, selecione as regiões que lhe interessam na página seguinte e, em seguida, expanda Analytics para localizar Data Factory: Produtos disponíveis por região. Os arquivos de dados (Armazenamento do Azure, Base de Dados SQL do Azure, etc.) e as computações (HDInsight, etc.) utilizados pela fábrica de dados podem estar noutras regiões.
Criar serviços ligados
Neste tutorial, vai criar três serviços ligados para blob de origem, sink e teste, respetivamente, que incluem ligações para os arquivos de dados:
Criar o serviço ligado da Base de Dados SQL do Azure de origem
Crie um ficheiro JSON com o nome AzureSqlDatabaseLinkedService.json na pasta C:\ADFv2TutorialBulkCopy com o seguinte conteúdo: (crie a pasta ADFv2TutorialBulkCopy, caso ainda não exista.)
Importante
Substitua <servername>, <databasename>, <username>@<servername> e <password> por valores do 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"Segue-se o resultado do exemplo:
LinkedServiceName : AzureSqlDatabaseLinkedService ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> Properties : Microsoft.Azure.Management.DataFactory.Models.AzureSqlDatabaseLinkedService
Criar o coletor do serviço vinculado do Azure Synapse Analytics
Crie um ficheiro JSON com o nome AzureSqlDWLinkedService.json na pasta C:\ADFv2TutorialBulkCopy, com o seguinte conteúdo:
Importante
Substitua <servername>, <databasename>, <username>@<servername> e <password> por valores do 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"Segue-se o resultado do exemplo:
LinkedServiceName : AzureSqlDWLinkedService ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> Properties : Microsoft.Azure.Management.DataFactory.Models.AzureSqlDWLinkedService
Criar o serviço ligado de Armazenamento do Azure de teste
Neste tutorial, vai utilizar o armazenamento de Blobs do Azure como área de teste provisória para ativar o PolyBase para um melhor desempenho de cópia.
Crie um ficheiro JSON com o nome AzureStorageLinkedService.json na pasta C:\ADFv2TutorialBulkCopy, com o seguinte conteúdo:
Importante
Substitua <accountName> e <accountKey> pelo nome e chave da sua conta de armazenamento do Azure antes de guardar o ficheiro.
{ "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"Segue-se o resultado do exemplo:
LinkedServiceName : AzureStorageLinkedService ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> Properties : Microsoft.Azure.Management.DataFactory.Models.AzureStorageLinkedService
Criar conjuntos de dados
Neste tutorial, vai criar conjuntos de dados de origem e sink, que especificam a localização onde os dados são armazenados:
Criar um conjunto de dados para a Base de Dados SQL de origem
Crie um ficheiro JSON com o nome AzureSqlDatabaseDataset.json na pasta C:\ADFv2TutorialBulkCopy, com o seguinte conteúdo. "tableName" é um nome fictício, uma vez que mais à frente vai utilizar a consulta SQL na atividade de cópia para obter 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"Segue-se o resultado do exemplo:
DatasetName : AzureSqlDatabaseDataset ResourceGroupName : <resourceGroupname> DataFactoryName : <dataFactoryName> Structure : Properties : Microsoft.Azure.Management.DataFactory.Models.AzureSqlTableDataset
Criar um conjunto de dados para o coletor Azure Synapse Analytics
Crie um ficheiro JSON com o nome AzureSqlDWDataset.json na pasta C:\ADFv2TutorialBulkCopy, com o seguinte conteúdo: "tableName" está definido como um parâmetro; mais à frente, a atividade de cópia que faz referência a este conjunto de dados transmite 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"Segue-se o resultado do exemplo:
DatasetName : AzureSqlDWDataset ResourceGroupName : <resourceGroupname> DataFactoryName : <dataFactoryName> Structure : Properties : Microsoft.Azure.Management.DataFactory.Models.AzureSqlDwTableDataset
Criar pipelines
Neste tutorial, vai criar dois pipelines:
Criar o pipeline "IterateAndCopySQLTables"
Este pipeline aceita uma lista de tabelas como parâmetro. Para cada tabela na lista, ele copia dados da tabela no Banco de Dados SQL do Azure para o Azure Synapse Analytics usando cópia em estágios e PolyBase.
Crie um ficheiro JSON com o nome IterateAndCopySQLTables.json na pasta C:\ADFv2TutorialBulkCopy, com o seguinte conteúdo:
{ "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"Segue-se o resultado do exemplo:
PipelineName : IterateAndCopySQLTables ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> Activities : {IterateSQLTables} Parameters : {[tableList, Microsoft.Azure.Management.DataFactory.Models.ParameterSpecification]}
Criar o pipeline "GetTableListAndTriggerCopyData"
Este pipeline executa dois passos:
- Procura a tabela do sistema da Base de Dados SQL do Azure para obter a lista de tabelas a copiar.
- Aciona o pipeline "IterateAndCopySQLTables" para executar a cópia de dados real.
Crie um ficheiro JSON com o nome GetTableListAndTriggerCopyData.json na pasta C:\ADFv2TutorialBulkCopy, com o seguinte conteúdo:
{ "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"Segue-se o resultado do exemplo:
PipelineName : GetTableListAndTriggerCopyData ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> Activities : {LookupTableList, TriggerCopy} Parameters :
Iniciar e monitorizar uma execução de pipeline
Inicie uma execução de pipeline para o pipeline "GetTableListAndTriggerCopyData" principal e capture o ID de execução de pipeline para futura monitorização. Por baixo, aciona a execução do pipeline "IterateAndCopySQLTables", conforme especificado na atividade ExecutePipeline.
$runId = Invoke-AzDataFactoryV2Pipeline -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -PipelineName 'GetTableListAndTriggerCopyData'Execute o script seguinte para verificar continuamente o estado de execução do pipeline GetTableListAndTriggerCopyData e imprima a execução de pipeline final e o resultado da execução de atividade.
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" $resultEis o resultado 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}Pode obter o ID de execução do pipeline "IterateAndCopySQLTables" e verificar o resultado da execução de atividade detalhada da seguinte forma.
Write-Host "Pipeline 'IterateAndCopySQLTables' run result:" -ForegroundColor "Yellow" ($result | Where-Object {$_.ActivityName -eq "TriggerCopy"}).Output.ToString()Eis o resultado 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 coletor do Azure Synapse Analytics e confirme se os dados foram copiados do Banco de Dados SQL do Azure corretamente.
Conteúdos relacionados
Neste tutorial, executou os passos seguintes:
- Criar uma fábrica de dados.
- Crie o Banco de Dados SQL do Azure, o Azure Synapse Analytics e os serviços vinculados do Armazenamento do Azure.
- Crie conjuntos de dados do Banco de Dados SQL do Azure e do Azure Synapse Analytics.
- Criar um pipeline para procurar as tabelas a copiar e outro pipeline para executar a operação de cópia real.
- Iniciar uma execução de pipeline.
- Monitorizar o pipeline e execuções de atividades.
Avance para o tutorial seguinte para saber como copiar dados de forma incremental de uma origem para um destino: