Meerdere tabellen bulksgewijs kopiëren met behulp van Azure Data Factory met Behulp van PowerShell

Van toepassing op: Azure Data Factory Azure Synapse Analytics

In deze handleiding wordt getoond hoe je een aantal tabellen kopieert van Azure SQL Database naar Azure Synapse Analytics. U kunt hetzelfde patroon toepassen in andere kopieerscenario's. Als u bijvoorbeeld tabellen kopieert van SQL Server/Oracle naar Azure SQL Database/Data Warehouse/Azure Blob, kopieert u verschillende paden van Blob naar Azure SQL Database tabellen.

Op hoog niveau bevat deze zelfstudie de volgende stappen:

In deze zelfstudie wordt gebruikgemaakt van Azure PowerShell. Zie Quickstarts (Snelstartgidsen) voor meer informatie over het gebruik van andere hulpprogramma's/SDK's voor het maken van een gegevensfactory.

Eind-tot-eind werkstroom

In dit scenario hebben we een aantal tabellen in Azure SQL Database die we willen kopiëren naar Azure Synapse Analytics. Dit is de logische volgorde van de stappen in de werkstroom die in pijplijnen plaatsvindt:

• De eerste pijplijn zoekt de lijst op met tabellen die moeten worden gekopieerd naar de sinkgegevensopslag. U kunt ook een metagegevenstabel bijhouden waarin alle tabellen worden vermeld die moeten worden gekopieerd naar de sinkgegevensopslag. De pijplijn activeert vervolgens een andere pijplijn, die elke tabel in de database langsloopt en de bewerking uitvoert waarmee de gegevens worden gekopieerd.
• De tweede pijplijn voert de daadwerkelijke kopieerbewerking uit. De lijst met tabellen wordt gebruikt als parameter. Kopieer voor elke tabel in de lijst de specifieke tabel in Azure SQL Database naar de bijbehorende tabel in Azure Synapse Analytics met behulp van staged kopie via Blob Storage en PolyBase voor de beste prestaties. In dit voorbeeld wordt de lijst met tabellen in de eerste pijplijn doorgegeven als een waarde voor de parameter.

Als u geen Azure-abonnement hebt, maakt u een free-account voordat u begint.

Vereisten

• Azure PowerShell. Volg de instructies in Hoe u Azure PowerShell installeert en configureert.
• Azure Storage account. Het Azure Storage-account wordt gebruikt als tijdelijke blobopslag tijdens de bulkkopiebewerking.
• Azure SQL Database. Deze database bevat de brongegevens.
• Azure Synapse Analytics. Dit datawarehouse bevat de uit de SQL Database gekopieerde gegevens.

SQL Database en Azure Synapse Analytics voorbereiden

Bereid de bron Azure SQL Database voor:

Maak een database met de voorbeeldgegevens van Adventure Works LT in een SQL-database door het artikel Maak een database in Azure SQL Database te volgen. In deze zelfstudie worden alle tabellen uit deze voorbeelddatabase gekopieerd naar Azure Synapse Analytics.

Bereid de Azure Synapse Analytics-sink voor:

1. Als u geen Azure Synapse Analytics werkruimte hebt, raadpleegt u het artikel Get gestart met Azure Synapse Analytics artikel voor stappen om er een te maken.

2. Maak bijbehorende tabelschema's in Azure Synapse Analytics. U gebruikt Azure Data Factory om gegevens in een latere stap te migreren/kopiëren.

Azure-services voor toegang tot SQL Server

Geef zowel voor SQL Database als Azure Synapse Analytics toestemming aan Azure-services om toegang te krijgen tot SQL Server. Zorg ervoor dat de instelling Toegang tot Azure-servicesAAN staat voor uw server. Met deze instelling kan de Data Factory-service gegevens lezen uit uw Azure SQL Database en gegevens naar Azure Synapse Analytics schrijven. Voer de volgende stappen uit om dit te controleren en de instelling in te schakelen:

1. Klik op Alle services aan de linkerkant en klik op SQL-servers.
2. Selecteer uw server en klik op Firewall onder INSTELLINGEN.
3. Klik op de pagina Firewall-instellingen op ON voor Toegang tot Azure services.

Een data factory maken

1. Start PowerShell. Houd Azure PowerShell open tot het einde van deze zelfstudie. Als u het programma sluit en opnieuw opent, moet u de opdrachten opnieuw uitvoeren.

   Voer de volgende opdracht uit en voer de gebruikersnaam en het wachtwoord in waarmee u zich aanmeldt bij de Azure-portal:

   Connect-AzAccount
   

   Voer de volgende opdracht uit om alle abonnementen voor dit account weer te geven:

   Get-AzSubscription
   

   Voer de volgende opdracht uit om het abonnement te selecteren waarmee u wilt werken. Vervang SubscriptionId door de id van uw Azure-abonnement:

   Select-AzSubscription -SubscriptionId "<SubscriptionId>"
   

2. Voer de cmdlet Set-AzDataFactoryV2 uit om een data factory te maken. Vervang voordat u de opdracht uitvoert de tijdelijke aanduidingen door uw eigen waarden.

   $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
   

   Let op de volgende punten:

   • De naam van de Azure data factory moet wereldwijd uniek zijn. Als de volgende fout zich voordoet, wijzigt u de naam en probeert u het opnieuw.

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

   • Als u Data Factory-exemplaren wilt maken, moet u een inzender of beheerder van het Azure-abonnement zijn.

   • Voor een lijst met Azure regio's waarin Data Factory momenteel beschikbaar is, selecteert u de regio's die u interesseren op de volgende pagina en vouwt u vervolgens Analytics uit om Data Factory uit te vouwen: Products beschikbaar per regio. De gegevensarchieven (Azure Storage, Azure SQL Database, enzovoort) en berekeningen (HDInsight, enzovoort) die door data factory worden gebruikt, kunnen zich in andere regio's bevinden.

Gekoppelde services maken

In deze zelfstudie maakt u drie gekoppelde services voor respectievelijk de bron-, sink- en staging-blob, met verbindingen naar uw gegevensopslag.

De gekoppelde bronservice Azure SQL Database maken

1. Maak een JSON-bestand met de naam AzureSqlDatabaseLinkedService.json in de map C:\ADFv2TutorialBulkCopy met de volgende inhoud (maak de map ADFv2TutorialBulkCopy als deze nog niet bestaat):

   Belangrijk

   Vervang <servername>, <databasenaam>, <username>@<servername> en <password> met waarden van uw Azure SQL Database voordat u het bestand opslaat.

   {
       "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. Schakel in Azure PowerShell over naar de map ADFv2TutorialBulkCopy.

3. Voer de cmdlet Set-AzDataFactoryV2LinkedService uit om de gekoppelde service te maken: AzureSqlDatabaseLinkedService.

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

   Hier is een voorbeeld van de uitvoer:

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

De gekoppelde service sink Azure Synapse Analytics maken

1. Maak een JSON-bestand met de naam AzureSqlDWLinkedService.json in de map C:\ADFv2TutorialBulkCopy. Geef dit bestand de volgende inhoud:

   Belangrijk

   Vervang <servername>, <databasenaam>, <username>@<servername> en <password> met waarden van uw Azure SQL Database voordat u het bestand opslaat.

   {
       "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. Voer de cmdlet Set-AzDataFactoryV2LinkedService uit om de gekoppelde service te maken: AzureSqlDWLinkedService.

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

   Hier is een voorbeeld van de uitvoer:

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

De gekoppelde stageringsservice van Azure Storage maken

In deze zelfstudie gebruikt u Azure Blob Storage als tijdelijke faseringsgebied om PolyBase in te schakelen voor betere kopieerprestaties.

1. Maak een JSON-bestand met de naam AzureStorageLinkedService.json in de map C:\ADFv2TutorialBulkCopy met de volgende inhoud:

   Belangrijk

   Vervang <accountName> en <accountKey> door de naam en sleutel van uw Azure-opslagaccount voordat u het bestand opslaat.

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

2. Voer de cmdlet Set-AzDataFactoryV2LinkedService uit om de gekoppelde service te maken: AzureStorageLinkedService.

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

   Hier is een voorbeeld van de uitvoer:

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

Gegevenssets maken

In deze handleiding maakt u bron- en doelgegevenssets aan, waarmee de locatie wordt gespecificeerd waar de gegevens worden opgeslagen.

Een gegevensset maken voor bron SQL-Database

1. Maak een JSON-bestand met de naam AzureSqlDatabaseDataset.json in de map C:\ADFv2TutorialBulkCopy. Geef dit bestand de volgende inhoud. De 'tableName' is een tijdelijke tabelnaam; later gebruikt u de SQL-query bij de kopieeractiviteit om de data op te halen.

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

2. Om de gegevensset AzureSqlDatabaseDataset te maken, voert u de cmdlet Set-AzDataFactoryV2Dataset uit.

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

   Hier is een voorbeeld van de uitvoer:

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

Een gegevensset maken voor sink-Azure Synapse Analytics

1. Maak een JSON-bestand met de naam AzureSqlDWDataset.json in de map C:\ADFv2TutorialBulkCopy, met de volgende inhoud: De tableName' wordt ingesteld als een parameter; de kopieeractiviteit waarin naar deze gegevensset wordt verwezen, geeft later de werkelijke waarde door naar de gegevensset.

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

2. Gebruik de cmdlet Set-AzDataFactoryV2Dataset om de gegevensset AzureSqlDWDataset te maken.

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

   Hier is een voorbeeld van de uitvoer:

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

Pijplijnen maken

In deze zelfstudie maakt u twee pijplijnen:

De pijplijn 'IterateAndCopySQLTables' maken

In deze pijplijn wordt een lijst met tabellen gebruikt als parameter. Voor elke tabel in de lijst worden gegevens uit de tabel in Azure SQL Database gekopieerd naar Azure Synapse Analytics met behulp van gefaseerde kopie en PolyBase.

1. Maak een JSON-bestand met de naam IterateAndCopySQLTables.json in de map C:\ADFv2TutorialBulkCopy. Geef dit bestand de volgende inhoud:

   {
       "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. Voer de cmdlet Set-AzDataFactoryV2Pipeline uit om de pijplijn te maken: IterateAndCopySQLTables.

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

   Hier is een voorbeeld van de uitvoer:

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

De pijplijn 'GetTableListAndTriggerCopyData' maken

Deze pijplijn voert twee stappen uit:

• Hiermee zoekt u de Azure SQL Database systeemtabel op om de lijst met te kopiëren tabellen op te halen.
• Activeert de pijplijn 'IterateAndCopySQLTables' om het kopiëren van de gegevens daadwerkelijk uit te voeren.

1. Maak een JSON-bestand met de naam GetTableListAndTriggerCopyData.json in de map C:\ADFv2TutorialBulkCopy. Geef dit bestand de volgende inhoud:

   {
       "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. Voer de cmdlet Set-AzDataFactoryV2Pipeline uit om de pijplijn te maken: GetTableListAndTriggerCopyData.

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

   Hier is een voorbeeld van de uitvoer:

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

Pijplijnuitvoering starten en controleren

1. Start een uitvoer van de pijplijn "GetTableListAndTriggerCopyData" en leg de ID voor de pijplijnuitvoering vast voor toekomstige monitoring. Hieronder wordt de uitvoering van de pijplijn 'IterateAndCopySQLTables' gestart zoals opgegeven in de ExecutePipeline-activiteit.

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

2. Voer het volgende script uit om continu de uitvoeringsstatus van de pijplijn GetTableListAndTriggerCopyData te controleren, en druk het eindresultaat van de pijplijnuitvoering en de uitvoering van de activiteit af.

   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
   

   Hier volgt een voorbeeld van de voorbeelduitvoering:

   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. U kunt de run-id van de pijplijn 'IterateAndCopySQLTables' ophalen en vervolgens het gedetailleerde uitvoeringsresultaat van de activiteit controleren.

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

   Hier volgt een voorbeeld van de voorbeelduitvoering:

   {
       "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. Maak verbinding met uw sink-Azure Synapse Analytics en controleer of de gegevens correct zijn gekopieerd uit Azure SQL Database.

Gerelateerde inhoud

In deze zelfstudie hebt u de volgende stappen uitgevoerd:

Ga door naar de volgende zelfstudie voor informatie over het incrementeel kopiëren van gegevens uit een bron naar een bestemming: