Copie várias tabelas em massa usando o Azure Data Factory usando PowerShell

APLICA-SE A: Azure Data Factory Azure Synapse Analytics

Este tutorial demonstra copiar várias tabelas de Base de Dados SQL do Azure para 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 Base de Dados SQL do Azure/Data Warehouse/Azure Blob, copiar diferentes caminhos do Blob para as tabelas do Base de Dados SQL do Azure.

A um nível elevado, este tutorial envolve os seguintes passos:

Este tutorial utiliza 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

Neste cenário, temos várias tabelas no Base 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 devem ser copiadas para os repositórios de dados de destino. Em alternativa, pode manter uma tabela de metadados que liste todas as tabelas a copiar para o repositório de dados de destino. Em seguida, o pipeline aciona outro pipeline, que itera sobre cada tabela na base de dados e executa a operação de cópia de dados.
• O segundo fluxo de trabalho executa a cópia real. Aceita a lista de tabelas como um parâmetro. Para cada tabela da lista, copie a tabela específica do Base de Dados SQL do Azure para a tabela correspondente no Azure Synapse Analytics utilizando uma cópia escalonada através do armazenamento 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 Azure, crie uma conta free antes de começar.

Pré-requisitos

• Azure PowerShell. Siga as instruções em Como instalar e configurar Azure PowerShell.
• conta de Armazenamento do Azure. A conta Armazenamento do Azure é utilizada como armazenamento de blob de preparação durante a 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 SQL Database e Azure Synapse Analytics

Prepare a Base de Dados SQL do Azure de origem

Crie uma base de dados com os dados de exemplo do Adventure Works LT na base de dados SQL seguindo Crie uma base de dados no artigo Base de Dados SQL do Azure. Este tutorial copia todas as tabelas desta base de dados de exemplo para o Azure Synapse Analytics.

Prepare o destino Azure Synapse Analytics:

1. Se não tiver um espaço de trabalho Azure Synapse Analytics, consulte o artigo Comece com Azure Synapse Analytics para os passos para criar um.

2. Crie esquemas de tabela correspondentes no Azure Synapse Analytics. Usa o Azure Data Factory para migrar/copiar dados numa etapa posterior.

Serviços Azure para aceder ao SQL Server

Para SQL Database e Azure Synapse Analytics, permita que os serviços do Azure acedam ao SQL Server. Certifique-se de que a definição Permitir o acesso a Azure serviços está ativada ON para o seu servidor. Esta configuração permite que o serviço Data Factory leia dados do seu Base de Dados SQL do Azure e escreva dados no Azure Synapse Analytics. Para verificar e ativar esta definição, execute os passos seguintes:

1. Clique em Todos os serviços à esquerda e clique em Servidores SQL.
2. Selecione o seu servidor e clique em Firewall em DEFINIÇÕES.
3. Na página Definições de firewall, clique em ON para Permitir acesso a Azure serviços.

Criar uma fábrica de dados

1. Inicie o PowerShell. Mantenha o Azure PowerShell aberto até ao final deste tutorial. Se fechar e reabrir, você irá precisar executar os comandos novamente.

   Execute o seguinte comando e introduza o nome de utilizador e a palavra-passe que utiliza para iniciar sessão no portal Azure:

   Connect-AzAccount
   

   Execute o comando seguinte para ver todas as subscrições desta conta:

   Get-AzSubscription
   

   Execute o comando seguinte para selecionar a subscrição com a qual pretende trabalhar. Substitua SubscriptionId pelo ID da sua subscrição Azure:

   Select-AzSubscription -SubscriptionId "<SubscriptionId>"
   

2. 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 $dataFactoryName
   

   Tenha em conta os seguintes pontos:

   • O nome da fábrica de dados do Azure deve ser globalmente único. 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, deve ser Contribuidor ou Administrador da subscrição do Azure.

   • Para uma lista das regiões do Azure em que o Data Factory está disponível atualmente, selecione as regiões que lhe interessam na página seguinte e depois expanda Analytics para localizar Data Factory: Produtos disponíveis por região. Os armazenamentos de dados (Armazenamento do Azure, Base de Dados SQL do Azure, etc.) e os computadores (HDInsight, etc.) usados pela data factory podem estar noutras regiões.

Criar serviços ligados

Neste tutorial, vai criar três serviços vinculados para blobs de origem, destino e intermédio, respetivamente, que incluem ligações para os repositórios de dados.

Criar o serviço vinculado Base de Dados SQL do Azure de origem

1. 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 <nome>, <nome da base de dados>, <nome de utilizador>@<nome> e <password> pelos valores do seu Base de Dados SQL do Azure antes de guardar o ficheiro.

   {
       "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"
           }
       }
   }
   

2. Em Azure PowerShell, mude para a pasta ADFv2TutorialBulkCopy.

3. 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
   

Crie o serviço vinculado do Azure Synapse Analytics de destino

1. Crie um ficheiro JSON com o nome AzureSqlDWLinkedService.json na pasta C:\ADFv2TutorialBulkCopy, com o seguinte conteúdo:

   Importante

   Substitua <nome>, <nome da base de dados>, <nome de utilizador>@<nome> e <password> pelos valores do seu Base de Dados SQL do Azure antes de guardar o ficheiro.

   {
       "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"
           }
       }
   }
   

2. 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 staging Armazenamento do Azure

Neste tutorial, usas o armazenamento Azure Blob como uma área de preparação provisória para ativar o PolyBase e melhorar o desempenho das cópias.

1. Crie um ficheiro JSON com o nome AzureStorageLinkedService.json na pasta C:\ADFv2TutorialBulkCopy, com o seguinte conteúdo:

   Importante

   Substitua <NomeConta> e <ChaveConta> pelo nome e chave da sua conta de armazenamento Azure antes de guardar o ficheiro.

   {
       "name": "AzureStorageLinkedService",
       "properties": {
           "type": "AzureStorage",
           "typeProperties": {
               "connectionString": "DefaultEndpointsProtocol=https;AccountName=<accountName>;AccountKey=<accountKey>"
           }
       }
   }
   

2. 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

1. Crie um ficheiro JSON com o nome AzureSqlDatabaseDataset.json na pasta C:\ADFv2TutorialBulkCopy, com o seguinte conteúdo. O "tableName" é um nome fictício, pois posteriormente irá 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"
           }
       }
   }
   

2. 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 sink Azure Synapse Analytics

1. 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"
               }
           }
       }
   }
   

2. 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 da lista, copia-se os dados da tabela no Base de Dados SQL do Azure para o Azure Synapse Analytics usando o Staged Copy e o PolyBase.

1. 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"
               }
           }
       }
   }
   

2. 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 um pipeline "GetTableListAndTriggerCopyData"

Esta canalização executa duas etapas:

• Procuro a tabela do sistema 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.

1. 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"
                           ]
                       }
                   ]
               }
           ]
       }
   }
   

2. 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

1. Inicie uma execução do pipeline principal "GetTableListAndTriggerCopyData" e capture o ID de execução do mesmo para monitorização futura. Por baixo, aciona a execução do pipeline "IterateAndCopySQLTables", conforme especificado na atividade ExecutePipeline.

   $runId = Invoke-AzDataFactoryV2Pipeline -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -PipelineName 'GetTableListAndTriggerCopyData'
   

2. Execute o seguinte script para verificar continuamente o estado de execução do pipeline GetTableListAndTriggerCopyData e imprimir a execução final do pipeline e o resultado da execução da 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"
   $result
   

   Eis 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}
   

3. Pode obter o ID de execução do pipeline "IterateAndCopySQLTables" e verificar o resultado detalhado da execução da atividade 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)
   $result2
   

4. Ligue-se ao seu sink Azure Synapse Analytics e confirme que os dados foram copiados corretamente do Base de Dados SQL do Azure.

Conteúdos relacionados

Neste tutorial, executou os passos seguintes:

Avance para o tutorial seguinte para saber como copiar dados de forma incremental de uma origem para um destino: