Incrementeel gegevens laden van Azure SQL Database naar Azure Blob Storage met behulp van de Azure-portal

Van toepassing op: Azure Data Factory Azure Synapse Analytics

In deze zelfstudie maakt u een Azure Data Factory met een pijplijn waarmee deltagegevens uit een tabel in Azure SQL Database worden geladen naar Azure Blob Storage.

In deze zelfstudie voert u de volgende stappen uit:

Overzicht

Hier volgt de diagramoplossing op hoog niveau:

Dit zijn de belangrijke stappen voor het maken van deze oplossing:

1. Selecteer de watermerkkolom. Selecteer één kolom in de brongegevensopslag die kan worden gebruikt om de nieuwe of bijgewerkte records voor elke uitvoering te segmenteren. Normaal gesproken nemen de gegevens in deze geselecteerde kolom (bijvoorbeeld, last_modify_time of id) toe wanneer de rijen worden gemaakt of bijgewerkt. De maximale waarde in deze kolom wordt gebruikt als grenswaarde.

2. Bereid een gegevensopslag voor om de watermerkwaarde in op te slaan. In deze zelfstudie slaat u de watermerkwaarde op in een SQL-database.

3. Maak een pijplijn met de volgende werkstroom:

   De pijplijn in deze oplossing heeft de volgende activiteiten:

   • Maak twee opzoekactiviteiten. Gebruik de eerste opzoekactiviteit om de laatste watermerkwaarde op te halen. Gebruik de tweede opzoekactiviteit om de nieuwe grenswaarde op te halen. Deze watermerkwaarden worden doorgegeven aan de Copy activity.
   • Maak een Copy activity waarmee rijen uit het brongegevensarchief worden gekopieerd met de waarde van de watermerkkolom die groter is dan de oude watermerkwaarde en kleiner dan de nieuwe grenswaarde. Vervolgens worden de deltagegevens uit de brongegevensopslag als een nieuw bestand gekopieerd naar een Blob-opslag.
   • Maak een StoredProcedure-activiteit waarmee de watermerkwaarde wordt bijgewerkt voor de pijplijn die de volgende keer wordt uitgevoerd.

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

Vereisten

• Azure SQL Database. U gebruikt de database als de brongegevensopslag. Als u geen database in Azure SQL Database hebt, raadpleegt u Database maken in Azure SQL Database voor stappen om er een te maken.
• Azure Storage. U gebruikt de Blob-opslag als de sinkgegevensopslag. Als u geen opslagaccount hebt, zie Een opslagaccount maken voor de stappen om er een aan te maken. Maak een container met de naam adftutorial.

Een gegevensbrontabel maken in uw SQL-database

1. Open SQL Server Management Studio. Klik in Server Explorer met de rechtermuisknop op de database en kies Nieuwe query.

2. Voer de volgende SQL-opdracht uit voor de SQL-database om een tabel met de naam data_source_table te maken als de gegevensbronopslag:

   create table data_source_table
   (
       PersonID int,
       Name varchar(255),
       LastModifytime datetime
   );
   
   INSERT INTO data_source_table
       (PersonID, Name, LastModifytime)
   VALUES
       (1, 'aaaa','9/1/2017 12:56:00 AM'),
       (2, 'bbbb','9/2/2017 5:23:00 AM'),
       (3, 'cccc','9/3/2017 2:36:00 AM'),
       (4, 'dddd','9/4/2017 3:21:00 AM'),
       (5, 'eeee','9/5/2017 8:06:00 AM');
   

   In deze handleiding gebruikt u LastModifytime als de watermerk kolom. De gegevens in de brongegevensopslag worden weergegeven in de volgende tabel:

   PersonID | Name | LastModifytime
   -------- | ---- | --------------
   1        | aaaa | 2017-09-01 00:56:00.000
   2        | bbbb | 2017-09-02 05:23:00.000
   3        | cccc | 2017-09-03 02:36:00.000
   4        | dddd | 2017-09-04 03:21:00.000
   5        | eeee | 2017-09-05 08:06:00.000
   

Nog een tabel in uw SQL-database maken om de bovengrenswaarde op te slaan

1. Voer de volgende SQL-opdracht uit op de SQL-database om een tabel met de naam watermarktable te maken om de grenswaarde op te slaan:

   create table watermarktable
   (
   
   TableName varchar(255),
   WatermarkValue datetime,
   );
   

2. Stel de standaardwaarde van de bovengrens in met de tabelnaam van de brongegevensopslag. In deze handleiding is de tabelnaam data_source_table.

   INSERT INTO watermarktable
   VALUES ('data_source_table','1/1/2010 12:00:00 AM')    
   

3. Controleer de gegevens in de tabel watermarktable.

   Select * from watermarktable
   

   Uitvoer:

   TableName  | WatermarkValue
   ----------  | --------------
   data_source_table | 2010-01-01 00:00:00.000
   

Een opgeslagen procedure maken in uw SQL-database

Voer de volgende opdracht uit om een opgeslagen procedure te maken in uw SQL-database:

CREATE PROCEDURE usp_write_watermark @LastModifiedtime datetime, @TableName varchar(50)
AS

BEGIN

UPDATE watermarktable
SET [WatermarkValue] = @LastModifiedtime
WHERE [TableName] = @TableName

END

Een data factory maken

1. Start Microsoft Edge of Google Chrome webbrowser. Momenteel wordt de Data Factory-gebruikersinterface alleen ondersteund in Microsoft Edge- en Google Chrome-webbrowsers.

2. Kies in het bovenste menu de optie Een resource maken>Analytics>Data Factory.

   

3. Voer op de pagina Nieuwe gegevensfactoryADFIncCopyTutorialDF in als de naam.

   De naam van de Azure Data Factory moet globally uniek zijn. Als u een rood uitroepteken ziet met het volgende foutbericht, wijzigt u de naam van de gegevensfactory (bijvoorbeeld uwnaamADFIncCopyTutorialDF) en probeert u het opnieuw. Zie het artikel Data factory - Naamgevingsregels voor meer informatie over naamgevingsregels voor Data Factory-artefacten.

   Data factory-naam 'ADFIncCopyTutorialDF' is niet beschikbaar

4. Selecteer uw Azure subscription waarin u de data factory wilt maken.

5. Voer een van de volgende stappen uit voor de Resourcegroep:

   • Selecteer Bestaande gebruiken en selecteer een bestaande resourcegroep in de vervolgkeuzelijst.

   • Selecteer Nieuwe maken en voer de naam van een resourcegroep in.

     Zie Resourcegroepen gebruiken om uw Azure resources te beheren voor meer informatie over resourcegroepen.

6. Selecteer V2 als de versie.

7. Selecteer de locatie voor de data factory. In de vervolgkeuzelijst worden alleen ondersteunde locaties weergegeven. De gegevensarchieven (Azure Storage, Azure SQL Database, Azure SQL Managed Instance enzovoort) en berekeningen (HDInsight, enzovoort) die door data factory worden gebruikt, kunnen zich in andere regio's bevinden.

8. Klik op Create.

9. Wanneer het maken is voltooid, ziet u de pagina Data Factory zoals in de afbeelding wordt weergegeven.

   

10. Selecteer Open op de tegel Open Azure Data Factory Studio om de Azure Data Factory gebruikersinterface (UI) op een afzonderlijk tabblad te starten.

Een pipeline maken

In deze zelfstudie maakt u een pijplijn met twee Lookup-activiteiten, één Kopieer-activiteit en één StoredProcedure-activiteit die in één pijplijn is gekoppeld.

1. Klik op de startpagina van de Data Factory-gebruikersinterface op de tegel Orchestrate .

   

2. Geef op het tabblad Algemeen bij EigenschappenIncrementalCopyPipeline op als Naam. Vouw het paneel vervolgens samen door in de rechterbovenhoek op het pictogram Eigenschappen te klikken.

3. Laten we de eerste opzoekactie toevoegen om de oude watermerkwaarde op te halen. Vouw in de Activiteiten-werkset de optie Algemeen uit, en sleep de Lookup-activiteit naar het ontwerpoppervlak van de pijplijn. Wijzig de naam van de activiteit in LookupOldWaterMarkActivity.

   

4. Ga naar het tabblad Instellingen en klik op + Nieuw voor Brongegevensset. In deze stap maakt u een gegevensset die de gegevens in de watermarktabel vertegenwoordigt. Deze tabel bevat het oude watermerk dat is gebruikt in de vorige kopieerbewerking.

5. Selecteer in het venster Nieuwe gegevenssetAzure SQL Database en klik op Continue. Er wordt nu een nieuw venster geopend voor de gegevensset.

6. Voer in het venster Eigenschappen instellen voor de gegevensset WatermarkDataset in als Naam.

7. Selecteer Nieuw voor Gekoppelde service en voer de volgende stappen uit:

   1. Voer AzureSqlDatabaseLinkedService in als Naam.

   2. Selecteer uw server als Servernaam.

   3. Selecteer de naam van uw database in de vervolgkeuzelijst bij Databasenaam.

   4. Voer uw gebruikersnaam en wachtwoord in.

   5. Als u de verbinding met uw SQL-database wilt testen, klikt u op Verbinding testen.

   6. Klik op Voltooien.

   7. Controleer of AzureSqlDatabaseLinkedService is geselecteerd voor Gekoppelde service.

      

   8. Selecteer Voltooien.

8. Selecteer op het tabblad Verbinding[dbo].[watermarktable] voor Tabel. Klik op Gegevens vooraf bekijken om een voorbeeld van de gegevens in de tabel te bekijken.

   

9. Ga naar de pijplijneditor door op het pijplijntabblad bovenaan te klikken of door in de structuurweergave aan de linkerkant op de naam van de pijplijn te klikken. Bevestig in het venster Eigenschappen voor de opzoekactiviteit dat WatermarkDataset is geselecteerd in het veld Brongegevensset.

10. Vouw in de Activiteiten-werkset de optie Algemeen uit, en sleep een nieuwe Opzoekactiviteit naar het ontwerpoppervlak voor pijplijnen. Stel op het tabblad Algemeen van het venster Eigenschappen de naam in op LookupNewWaterMarkActivity. Met deze opzoekactiviteit wordt de nieuwe grenswaarde opgehaald uit de tabel met de brongegevens die moeten worden gekopieerd naar de bestemming.

11. Ga in het venster Eigenschappen voor de tweede opzoekactiviteit naar het tabblad Instellingen en klik op Nieuw. U maakt een gegevensset om te verwijzen naar de brontabel met de nieuwe grenswaarde (maximumwaarde van LastModifyTime).

12. Selecteer in het venster Nieuwe gegevenssetAzure SQL Database en klik op Continue.

13. Voer in het venster Eigenschappen instellenSourceDataset in als Naam. Selecteer AzureSqlDatabaseLinkedService als Gekoppelde service.

14. Selecteer [dbo].[data_source_table] als Tabel. Verderop in de zelfstudie geeft u een query op voor deze gegevensset. De query heeft voorrang op de tabel die u in deze stap opgeeft.

15. Selecteer Voltooien.

16. Ga naar de pijplijneditor door op het pijplijntabblad bovenaan te klikken of door in de structuurweergave aan de linkerkant op de naam van de pijplijn te klikken. Bevestig in het venster Eigenschappen voor de opzoekactiviteit dat SourceDataset is geselecteerd in het veld Brongegevensset.

17. Selecteer Query in het veld Query gebruiken en voer de volgende query in: u selecteert alleen de maximumwaarde van LastModifytime uit data_source_table. Zorg ervoor dat u ook Alleen eerste rij hebt geselecteerd.

    select MAX(LastModifytime) as NewWatermarkvalue from data_source_table
    

    

18. Vouw in de Activiteiten-gereedschapskist de sectie Verplaatsen & Transformeren uit, en gebruik slepen-en-neerzetten om de activiteit Copy uit de Activiteiten-gereedschapskist te slepen, en stel de naam in op IncrementalCopyActivity.

19. Verbind beide opzoekactiviteiten met de Copy activity door de green button die aan de opzoekactiviteiten is gekoppeld naar de Copy activity te slepen. Laat de muisknop los wanneer de rand van de kopieeractiviteit blauw wordt.

    

20. Selecteer de Copy activity en controleer of u de eigenschappen voor de activiteit in het venster Properties ziet.

21. Ga naar Source in het venster Properties en voer de volgende stappen uit:

    1. Selecteer SourceDataset in het veld Brongegevensset.

    2. Selecteer Query in het veld Query gebruiken.

    3. Voer de volgende SQL-query in het veld Query in.

       select * from data_source_table where LastModifytime > '@{activity('LookupOldWaterMarkActivity').output.firstRow.WatermarkValue}' and LastModifytime <= '@{activity('LookupNewWaterMarkActivity').output.firstRow.NewWatermarkvalue}'
       

       

22. Ga naar het tabblad Sink en klik op + Nieuw voor het veld Sink-gegevensset.

23. In deze zelfstudie is sinkgegevensarchief van het type Azure Blob Storage. Selecteer daarom Azure Blob Storage en klik op Continue in het venster Nieuwe gegevensset.

24. Selecteer in het venster Indeling selecteren de indeling van uw gegevens en klik op Doorgaan.

25. Voer in het venster Eigenschappen instellenSinkDataset in als Naam. Selecteer + Nieuw bij Gekoppelde service. In deze stap maakt u een verbinding (gekoppelde service) met uw Azure Blob Storage.

26. Voer in het venster New Linked Service (Azure Blob Storage) de volgende stappen uit:

    1. Voer AzureStorageLinkedService in als Naam.
    2. Selecteer uw Azure Storage-account voor Storage-accountnaam.
    3. Test de verbinding en klik vervolgens op Voltooien.

27. Controleer of in het venster Eigenschappen instellenAzureStorageLinkedService is geselecteerd bij Gekoppelde service. Kies dan Voltooien.

28. Ga naar het tabblad Verbinding van SinkDataset en voer de volgende stappen uit:

    1. Voor het veld Bestandspad, voer adftutorial/incrementalcopy in. adftutorial is de naam van de blob-container en incrementalcopy is de naam van de map. In dit fragment wordt ervan uitgegaan dat u een blobcontainer hebt met de naam adftutorial in uw Blob-opslag. Maak de container als deze bestaat niet of stel deze in op de naam van een bestaande container. Azure Data Factory maakt automatisch de uitvoermap incrementalcopy als deze niet bestaat. U kunt ook de knop Bladeren voor het bestandspad gebruiken om naar een map in een blob-container te navigeren.
    2. Voor het gedeelte Bestand van het veld Bestandspad selecteert u Dynamische inhoud toevoegen [Alt+P] en typt u vervolgens @CONCAT('Incremental-', pipeline().RunId, '.txt') in het venster dat wordt geopend. Kies dan Voltooien. De bestandsnaam wordt dynamisch gegenereerd met behulp van de expressie. Elke pijplijnuitvoering heeft een unieke id. De Copy activity gebruikt de run-id om de bestandsnaam te genereren.

29. Ga naar de pijplijneditor door op het pijplijntabblad bovenaan te klikken of door in de structuurweergave aan de linkerkant op de naam van de pijplijn te klikken.

30. Vouw in de Activiteiten-werkset de optie Algemeen uit. Gebruik vervolgens slepen-en-neerzetten om de opgeslagen-procedureactiviteit uit de Activiteiten-werkset te verplaatsen naar het ontwerpoppervlak voor pijplijnen. Verbind de groene uitvoer (Succes) van de Kopieeractiviteit met de Stored Procedure activiteit.

31. Selecteer Opgeslagen procedureactiviteit in de pijplijnontwerper en verander de naam ervan in StoredProceduretoWriteWatermarkActivity.

32. Ga naar het tabblad SQL-account en selecteer AzureSqlDatabaseLinkedService als Gekoppelde service.

33. Open het tabblad Stored Procedure en voer de volgende stappen uit:

    1. Selecteer usp_write_watermark als naam van de opgeslagen procedure.

    2. Als u waarden wilt opgeven voor de opgeslagen-procedureparameters, klikt u op Importparameter en voert u de volgende waarden voor de parameters in:

       Naam	Typologie	Waarde
       LaatstGewijzigdeTijd	Datum en tijd	@{activity('LookupNewWaterMarkActivity').output.firstRow.NewWatermarkvalue}
       TabelNaam	Snaar / Touwtje	@{activity('LookupOldWaterMarkActivity').output.firstRow.TableName}

       

34. Klik in de werkbalk op Valideren om de instellingen voor de pijplijn te valideren. Controleer of er geen validatiefouten zijn. Als u het venster Pijplijnvalidatierapport wilt sluiten, klikt u op >>.

35. Publiceer entiteiten (gekoppelde services, gegevenssets en pijplijnen) naar de Azure Data Factory-service door de knop Alles publiceren te selecteren. Wacht tot u een bericht ziet waarin staat dat het publiceren is voltooid.

Een pijplijnuitvoering activeren

1. Klik in de werkbalk op Trigger toevoegen en klik op Nu activeren.

2. Selecteer in het venster Pijplijnuitvoering de optie Voltooien.

De pijplijnrun controleren.

1. Ga naar het tabblad Controleren aan de linkerkant. U ziet de status van de pijplijnrun die is geïnitieerd door een handmatige trigger. U kunt via koppelingen in de kolom NAAM PIJPLIJN uitvoeringsdetails bekijken en de pijplijn opnieuw uitvoeren.

2. Selecteer de koppeling in de kolom NAAM PIJPLIJN om de uitvoering van activiteiten te zien die zijn gekoppeld aan de pijplijnuitvoering. Selecteer de koppeling Details (pictogram van een bril) in de kolom NAAM ACTIVITEIT om details van de uitvoeringen van een activiteit te zien. Selecteer Alle pijplijnuitvoeringen bovenaan om terug te gaan naar de weergave Pijplijnuitvoeringen. Selecteer Vernieuwen om de weergave te vernieuwen.

De resultaten bekijken

1. Maak verbinding met uw Azure Storage-account met behulp van hulpprogramma's zoals Azure Storage Explorer. Controleer of er een uitvoerbestand is gemaakt in de map incrementalcopy in de container adftutorial.

   

2. Als u het uitvoerbestand opent, ziet u dat alle gegevens uit data_source_table zijn gekopieerd naar het blob-bestand.

   1,aaaa,2017-09-01 00:56:00.0000000
   2,bbbb,2017-09-02 05:23:00.0000000
   3,cccc,2017-09-03 02:36:00.0000000
   4,dddd,2017-09-04 03:21:00.0000000
   5,eeee,2017-09-05 08:06:00.0000000
   

3. Controleer de laatste waarde van watermarktable. U ziet dat de watermerkwaarde is bijgewerkt.

   Select * from watermarktable
   

   Dit is de uitvoer:

   | TableName | WatermarkValue |
   | --------- | -------------- |
   | data_source_table | 2017-09-05	8:06:00.000 |
   

Meer gegevens toevoegen aan de bron

Voeg nieuwe gegevens in uw database (brongegevensopslag) in.

INSERT INTO data_source_table
VALUES (6, 'newdata','9/6/2017 2:23:00 AM')

INSERT INTO data_source_table
VALUES (7, 'newdata','9/7/2017 9:01:00 AM')

De bijgewerkte gegevens in uw database zijn:

PersonID | Name | LastModifytime
-------- | ---- | --------------
1 | aaaa | 2017-09-01 00:56:00.000
2 | bbbb | 2017-09-02 05:23:00.000
3 | cccc | 2017-09-03 02:36:00.000
4 | dddd | 2017-09-04 03:21:00.000
5 | eeee | 2017-09-05 08:06:00.000
6 | newdata | 2017-09-06 02:23:00.000
7 | newdata | 2017-09-07 09:01:00.000

Een andere pijplijnuitvoering activeren

1. Ga naar het tabblad Bewerken . Klik op de pijplijn in de structuurweergave als deze niet is geopend in de ontwerpfunctie.

2. Klik in de werkbalk op Trigger toevoegen en klik op Nu activeren.

Controleer de tweede pijplijnuitvoering.

1. Ga naar het tabblad Controleren aan de linkerkant. U ziet de status van de pijplijnrun die is geïnitieerd door een handmatige trigger. U kunt via koppelingen in de kolom NAAM PIJPLIJN details van activiteiten bekijken en de pijplijn opnieuw uitvoeren.

2. Selecteer de koppeling in de kolom NAAM PIJPLIJN om de uitvoering van activiteiten te zien die zijn gekoppeld aan de pijplijnuitvoering. Selecteer de koppeling Details (pictogram van een bril) in de kolom NAAM ACTIVITEIT om details van de uitvoeringen van een activiteit te zien. Selecteer Alle pijplijnuitvoeringen bovenaan om terug te gaan naar de weergave Pijplijnuitvoeringen. Selecteer Vernieuwen om de weergave te vernieuwen.

De tweede uitvoer controleren

1. In de Blob-opslag ziet u dat een ander bestand is gemaakt. In deze zelfstudie is de nieuwe bestandsnaam Incremental-<GUID>.txt. Als u dit bestand opent, ziet u twee rijen met records.

   6,newdata,2017-09-06 02:23:00.0000000
   7,newdata,2017-09-07 09:01:00.0000000    
   

2. Controleer de laatste waarde van watermarktable. U ziet dat de watermerkwaarde opnieuw is bijgewerkt.

   Select * from watermarktable
   

   Voorbeelduitvoer:

   | TableName | WatermarkValue |
   | --------- | -------------- |
   | data_source_table | 2017-09-07 09:01:00.000 |
   

Gerelateerde inhoud

In deze zelfstudie hebt u de volgende stappen uitgevoerd:

In deze zelfstudie heeft de pijplijn gegevens uit één tabel in een SQL-database naar een Blob-opslag gekopieerd. Ga naar de volgende zelfstudie voor meer informatie over het kopiëren van gegevens uit meerdere tabellen in een SQL Server-database naar SQL Database.