Copiare più tabelle in blocco usando Azure Data Factory tramite PowerShell

APPLICABILE A: Azure Data Factory Azure Synapse Analytics

Questa esercitazione illustra come copiare una serie di tabelle da database SQL di Azure a Azure Synapse Analytics. È possibile applicare lo stesso modello anche in altri scenari di copia, Ad esempio, la copia di tabelle da SQL Server/Oracle a database SQL di Azure/Data Warehouse/Azure BLOB, copiando percorsi diversi dal BLOB alle tabelle database SQL di Azure.

A livello generale, questa esercitazione prevede la procedura seguente:

Questa esercitazione usa Azure PowerShell. Per informazioni sull'uso di altri strumenti/SDK per creare una data factory, vedere le Guide introduttive.

Flusso di lavoro completo

In questo scenario sono presenti numerose tabelle in database SQL di Azure che si vuole copiare in Azure Synapse Analytics. Ecco la sequenza logica di passaggi nel flusso di lavoro che si verifica nelle pipeline:

• La prima pipeline cerca l'elenco delle tabelle che devono essere copiate negli store dati di destinazione. In alternativa è possibile mantenere una tabella di metadati che elenca tutte le tabelle da copiare nell'archivio dati sink. Successivamente, la pipeline ne attiva un'altra, che itera su ogni tabella nel database e performa l'operazione di copia dei dati.
• La seconda pipeline esegue la copia effettiva. Accetta l'elenco di tabelle come parametro. Per ogni tabella nell'elenco, copiare la tabella specifica in database SQL di Azure nella tabella corrispondente in Azure Synapse Analytics usando copia staged tramite archiviazione BLOB e PolyBase per ottenere migliori prestazioni. In questo esempio la prima pipeline passa l'elenco di tabelle come valore per il parametro.

Se non si ha una sottoscrizione Azure, creare un account free prima di iniziare.

Prerequisiti

• Azure PowerShell. Seguire le istruzioni in Come installare e configurare Azure PowerShell.
• Archiviazione di Azure account. L'account Archiviazione di Azure viene usato come memorizzazione BLOB di staging nell'operazione di copia di massa.
• database SQL di Azure. Questo database contiene i dati di origine.
• Azure Synapse Analytics. Questo data warehouse contiene i dati trasferiti dal database SQL.

Preparare il database SQL e Azure Synapse Analytics

Prepara il database di origine Azure SQL:

Creare un database con i dati di esempio Adventure Works LT nel database SQL seguendo Creare un database in database SQL di Azure articolo. Questa esercitazione copia tutte le tabelle da questo database di esempio in Azure Synapse Analytics.

Prepare il sink Azure Synapse Analytics:

1. Se non si ha un'area di lavoro Azure Synapse Analytics, vedere l'articolo Introduzione a Azure Synapse Analytics per la procedura per crearne una.

2. Creare schemi di tabella corrispondenti in Azure Synapse Analytics. Usare Azure Data Factory per eseguire la migrazione o la copia dei dati in un passaggio successivo.

Servizi Azure per accedere a SQL Server

Sia per il database SQL che per Azure Synapse Analytics, consentire ai servizi di Azure di accedere a SQL Server. Assicurarsi che l'impostazione Consenti accesso ai servizi Azure sia impostata su ATTIVO per il proprio server. Questa impostazione consente al servizio Data Factory di leggere i dati dal database SQL di Azure e scrivere dati in Azure Synapse Analytics. Per verificare e attivare l'impostazione, seguire questa procedura:

1. Fare clic su Tutti i servizi a sinistra e quindi su Server SQL.
2. Selezionare il server e fare clic su Firewall in IMPOSTAZIONI.
3. Nella pagina Impostazioni diFirewall fare clic su ON per Allow access to Azure services.

Creare una data factory

1. Avviare PowerShell. Mantenere Azure PowerShell aperto fino alla fine di questa esercitazione. Se si chiude e si riapre, sarà necessario eseguire di nuovo questi comandi.

   Eseguire il comando seguente e immettere il nome utente e la password usati per accedere al portale di Azure:

   Connect-AzAccount
   

   Eseguire questo comando per visualizzare tutte le sottoscrizioni per l'account:

   Get-AzSubscription
   

   Eseguire il comando seguente per selezionare la sottoscrizione da usare. Sostituire SubscriptionId con l'ID della sottoscrizione Azure:

   Select-AzSubscription -SubscriptionId "<SubscriptionId>"
   

2. Eseguire il cmdlet Set-AzDataFactoryV2 per creare una data factory. Sostituire i segnaposto con i propri valori prima di eseguire il 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
   

   Notare i punti seguenti:

   • Il nome della data factory Azure deve essere univoco a livello globale. Se viene visualizzato l'errore seguente, modificare il nome e riprovare.

     The specified Data Factory name 'ADFv2QuickStartDataFactory' is already in use. Data Factory names must be globally unique.
     

   • Per creare istanze di Data Factory, è necessario essere un collaboratore o un amministratore della sottoscrizione Azure.

   • Per un elenco di aree Azure in cui Data Factory è attualmente disponibile, selezionare le aree a cui si è interessati nella pagina seguente e quindi espandere Analytics per individuare Data Factory: Products disponibile in base all'area. Gli archivi dati (Archiviazione di Azure, database SQL di Azure e così via) e i calcoli (HDInsight e così via) usati dalla data factory possono trovarsi in altre aree.

Creare servizi collegati

In questa esercitazione, crei tre servizi collegati per origine, destinazione e blob di staging, rispettivamente, incluse le connessioni ai tuoi archivi dati.

Creare il servizio collegato di origine database SQL di Azure

1. Creare un file JSON denominato AzureSqlDatabaseLinkedService.json nella cartella C:\ADFv2TutorialBulkCopy con il contenuto seguente: (Creare la cartella ADFv2TutorialBulkCopy, se non esiste già).

   Importante

   Sostituire <servername>, <databasename>, <username>@<servername> e <password> con i valori del database SQL di Azure prima di salvare il file.

   {
       "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. In Azure PowerShell passare alla cartella ADFv2TutorialBulkCopy.

3. Eseguire il cmdlet Set-AzDataFactoryV2LinkedService per creare il servizio collegato AzureSqlDatabaseLinkedService.

   Set-AzDataFactoryV2LinkedService -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "AzureSqlDatabaseLinkedService" -File ".\AzureSqlDatabaseLinkedService.json"
   

   Di seguito è riportato l'output di esempio:

   LinkedServiceName : AzureSqlDatabaseLinkedService
   ResourceGroupName : <resourceGroupName>
   DataFactoryName   : <dataFactoryName>
   Properties        : Microsoft.Azure.Management.DataFactory.Models.AzureSqlDatabaseLinkedService
   

Creare il servizio collegato di destinazione Azure Synapse Analytics

1. Creare un file JSON denominato AzureSqlDWLinkedService.json nella cartella C:\ADFv2TutorialBulkCopy con il contenuto seguente:

   Importante

   Sostituire <servername>, <databasename>, <username>@<servername> e <password> con i valori del database SQL di Azure prima di salvare il file.

   {
       "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. Per creare il servizio collegato: AzureSqlDWLinkedService, eseguire il cmdlet Set-AzDataFactoryV2LinkedService .

   Set-AzDataFactoryV2LinkedService -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "AzureSqlDWLinkedService" -File ".\AzureSqlDWLinkedService.json"
   

   Di seguito è riportato l'output di esempio:

   LinkedServiceName : AzureSqlDWLinkedService
   ResourceGroupName : <resourceGroupName>
   DataFactoryName   : <dataFactoryName>
   Properties        : Microsoft.Azure.Management.DataFactory.Models.AzureSqlDWLinkedService
   

Creare il servizio collegato di staging di Archiviazione di Azure

In questa esercitazione si usa Azure Blob Storage come area di staging per abilitare PolyBase e ottenere prestazioni di copia migliori.

1. Creare un file JSON denominato AzureStorageLinkedService.json nella cartella C:\ADFv2TutorialBulkCopy con il contenuto seguente:

   Importante

   Sostituire <accountName> e <accountKey> con nome e chiave dell'account di archiviazione Azure prima di salvare il file.

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

2. Per creare il servizio collegato AzureStorageLinkedService, eseguire il cmdlet Set-AzDataFactoryV2LinkedService.

   Set-AzDataFactoryV2LinkedService -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "AzureStorageLinkedService" -File ".\AzureStorageLinkedService.json"
   

   Di seguito è riportato l'output di esempio:

   LinkedServiceName : AzureStorageLinkedService
   ResourceGroupName : <resourceGroupName>
   DataFactoryName   : <dataFactoryName>
   Properties        : Microsoft.Azure.Management.DataFactory.Models.AzureStorageLinkedService
   

Creare i set di dati

In questa esercitazione vengono creati i set di dati di origine e sink, che specificano la posizione in cui vengono archiviati i dati:

Creare un set di dati per il database SQL di origine

1. Creare un file JSON denominato AzureSqlDatabaseDataset.json nella cartella C:\ADFv2TutorialBulkCopy con il contenuto seguente: Il valore "tableName" è un valore fittizio, perché successivamente viene usata la query SQL nell'attività di copia per recuperare i dati.

   {
       "name": "AzureSqlDatabaseDataset",
       "properties": {
           "type": "AzureSqlTable",
           "linkedServiceName": {
               "referenceName": "AzureSqlDatabaseLinkedService",
               "type": "LinkedServiceReference"
           },
           "typeProperties": {
               "tableName": "dummy"
           }
       }
   }
   

2. Per creare il set di dati AzureSqlDatabaseDataset, eseguire il cmdlet Set-AzDataFactoryV2Dataset.

   Set-AzDataFactoryV2Dataset -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "AzureSqlDatabaseDataset" -File ".\AzureSqlDatabaseDataset.json"
   

   Di seguito è riportato l'output di esempio:

   DatasetName       : AzureSqlDatabaseDataset
   ResourceGroupName : <resourceGroupname>
   DataFactoryName   : <dataFactoryName>
   Structure         :
   Properties        : Microsoft.Azure.Management.DataFactory.Models.AzureSqlTableDataset
   

Creare un set di dati per Azure Synapse Analytics sink

1. Creare un file JSON denominato AzureSqlDWDataset.json nella cartella C:\ADFv2TutorialBulkCopy con il contenuto seguente: Il valore "tableName" viene configurato come parametro e successivamente l'attività di copia che fa riferimento a questo set di dati passa il valore effettivo al set di dati.

   {
       "name": "AzureSqlDWDataset",
       "properties": {
           "type": "AzureSqlDWTable",
           "linkedServiceName": {
               "referenceName": "AzureSqlDWLinkedService",
               "type": "LinkedServiceReference"
           },
           "typeProperties": {
               "tableName": {
                   "value": "@{dataset().DWTableName}",
                   "type": "Expression"
               }
           },
           "parameters":{
               "DWTableName":{
                   "type":"String"
               }
           }
       }
   }
   

2. Per creare il set di dati AzureSqlDWDataset, eseguire il cmdlet Set-AzDataFactoryV2Dataset.

   Set-AzDataFactoryV2Dataset -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "AzureSqlDWDataset" -File ".\AzureSqlDWDataset.json"
   

   Di seguito è riportato l'output di esempio:

   DatasetName       : AzureSqlDWDataset
   ResourceGroupName : <resourceGroupname>
   DataFactoryName   : <dataFactoryName>
   Structure         :
   Properties        : Microsoft.Azure.Management.DataFactory.Models.AzureSqlDwTableDataset
   

Creare le pipeline

In questa esercitazione vengono create due pipeline:

Creare la pipeline "IterateAndCopySQLTables"

Questa pipeline accetta l'elenco di tabelle come parametro. Per ogni tabella nell'elenco, copia i dati dalla tabella in database SQL di Azure in Azure Synapse Analytics usando copia temporanea e PolyBase.

1. Creare un file JSON denominato IterateAndCopySQLTables.json nella cartella C:\ADFv2TutorialBulkCopy con il contenuto seguente:

   {
       "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. Per creare la pipeline IterateAndCopySQLTables, eseguire il cmdlet Set-AzDataFactoryV2Pipeline.

   Set-AzDataFactoryV2Pipeline -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "IterateAndCopySQLTables" -File ".\IterateAndCopySQLTables.json"
   

   Di seguito è riportato l'output di esempio:

   PipelineName      : IterateAndCopySQLTables
   ResourceGroupName : <resourceGroupName>
   DataFactoryName   : <dataFactoryName>
   Activities        : {IterateSQLTables}
   Parameters        : {[tableList, Microsoft.Azure.Management.DataFactory.Models.ParameterSpecification]}
   

Creare la pipeline "GetTableListAndTriggerCopyData"

Questa pipeline esegue due passaggi:

• Cerca la tabella di sistema database SQL di Azure per ottenere l'elenco delle tabelle da copiare.
• Attivazione della pipeline "IterateAndCopySQLTables" per l'esecuzione della copia effettiva dei dati.

1. Creare un file JSON denominato GetTableListAndTriggerCopyData.json nella cartella C:\ADFv2TutorialBulkCopy con il contenuto seguente:

   {
       "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. Per creare la pipeline GetTableListAndTriggerCopyData, eseguire il cmdlet Set-AzDataFactoryV2Pipeline.

   Set-AzDataFactoryV2Pipeline -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "GetTableListAndTriggerCopyData" -File ".\GetTableListAndTriggerCopyData.json"
   

   Di seguito è riportato l'output di esempio:

   PipelineName      : GetTableListAndTriggerCopyData
   ResourceGroupName : <resourceGroupName>
   DataFactoryName   : <dataFactoryName>
   Activities        : {LookupTableList, TriggerCopy}
   Parameters        :
   

Avviare e monitorare un'esecuzione della pipeline

1. Avvia un'esecuzione per la pipeline principale "GetTableListAndTriggerCopyData" e raccogli l'ID di esecuzione della pipeline per monitorarla in futuro. Di conseguenza, viene avviata l'esecuzione della pipeline "IterateAndCopySQLTables", come specificato nell'attività ExecutePipeline.

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

2. Eseguire lo script seguente per verificare continuamente lo stato di esecuzione della pipeline GetTableListAndTriggerCopyData e stampare il risultato finale dell'esecuzione della pipeline e dell'attività.

   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
   

   Ecco l'output dell'esecuzione di esempio:

   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. È possibile ottenere l'ID dell'esecuzione della pipeline "IterateAndCopySQLTables" e verificare il risultato dettagliato dell'esecuzione attività come illustrato di seguito.

   Write-Host "Pipeline 'IterateAndCopySQLTables' run result:" -ForegroundColor "Yellow"
   ($result | Where-Object {$_.ActivityName -eq "TriggerCopy"}).Output.ToString()
   

   Ecco l'output dell'esecuzione di esempio:

   {
       "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. Connettiti al sistema di destinazione Azure Synapse Analytics e verifica che i dati siano stati copiati correttamente da database SQL di Azure.

Contenuto correlato

In questa esercitazione sono stati eseguiti i passaggi seguenti:

Passare all'esercitazione successiva per ottenere informazioni sulla copia incrementale di dati da un'origine a una destinazione: