Share via

Läs in data stegvis från Azure SQL Database till Azure Blob Storage med hjälp av Azure-portalen

  • Artikel

GÄLLER FÖR: Azure Data Factory Azure Synapse Analytics

Dricks

Prova Data Factory i Microsoft Fabric, en allt-i-ett-analyslösning för företag. Microsoft Fabric omfattar allt från dataflytt till datavetenskap, realtidsanalys, business intelligence och rapportering. Lär dig hur du startar en ny utvärderingsversion kostnadsfritt!

I den här självstudien skapar du en Azure Data Factory med en pipeline som läser in deltadata från en tabell i Azure SQL Database till Azure Blob Storage.

I de här självstudierna går du igenom följande steg:

  • Förbered datalagringen för att lagra värdet för vattenstämpeln.
  • Skapa en datafabrik.
  • Skapa länkade tjänster.
  • Skapa datauppsättningar för källa, mottagare och vattenstämpel.
  • Skapa en pipeline.
  • Köra pipelinen.
  • Övervaka pipelinekörningen.
  • Granska resultat
  • Lägg till mer data i källan.
  • Kör pipelinen igen.
  • Övervaka den andra pipelinekörningen
  • Granska resultatet från den andra körningen

Översikt

Här är det avancerade diagrammet:

Incrementally load data

Här är några viktiga steg för att skapa den här lösningen:

  1. Markera vattenstämpelkolumnen. Markera en kolumn i källdatalagringen som går att använda för att dela upp de nya eller uppdaterade posterna för varje körning. Vanligtvis ökar data i den markerade kolumnen (till exempel last_modify_time elle ID) när rader skapas eller uppdateras. Det maximala värdet i den här kolumnen används som vattenstämpel.

  2. Förbered datalagringen för att lagra värdet för vattenstämpeln. I den här självstudien lagrar du storleksgränsen i en SQL-databas.

  3. Skapa en pipeline med följande arbetsflöde:

    Pipelinen i den här lösningen har följande aktiviteter:

    • Skapa två sökningsaktiviteter. Använd den första sökningsaktiviteten för att hämta det sista vattenstämpelvärdet. Använd den andra sökningsaktiviteten för att hämta det nya vattenstämpelvärdet. Vattenstämpelvärdena skickas till kopieringsaktiviteten.
    • Skapa en {0}kopieringsaktivitet{0} som kopierar raderna från källdatalagringen med värdet för vattenstämpelkolumnen som är större än det gamla värdet och mindre än det nya. Sedan kopieras deltadata från källdatalagringen till Blob-lagring som en ny fil.
    • Skapa en StoredProcedure-aktivitet som uppdaterar vattenstämpelvärdet för den pipeline som körs nästa gång.

Om du inte har någon Azure-prenumeration skapar du ett kostnadsfritt konto innan du börjar.

Förutsättningar

  • Azure SQL Database. Du använder databasen som källa för datalagringen. Om du inte har någon databas i Azure SQL Database kan du läsa Skapa en databas i Azure SQL Database för steg för att skapa en.
  • Azure Storage. Du kan använda blob-lagringen som mottagare för datalagringen. Om du inte har ett lagringskonto finns det anvisningar om hur du skapar ett i Skapa ett lagringskonto. Skapa en container med namnet adftutorial.

Skapa en datatabell i din SQL-databas

  1. Öppna SQL Server Management Studio. I Server Explorer högerklickar du på databasen och väljer Ny fråga.

  2. Kör följande SQL-kommando mot din SQL-databas för att skapa en tabell med namnet data_source_table som datakällagringen:

    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');

    I den här självstudien ska du använda LastModifytime som vattenstämpelkolumn. Data i datakällagringen visas i följande tabell:

    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

Skapa en annan tabell i SQL-databasen för att lagra värdet för högvattenmärket

  1. Kör följande SQL-kommando mot din SQL-databas för att skapa en tabell med namnet watermarktable för att lagra värdet för högvattenmärket:

    create table watermarktable
(

TableName varchar(255),
WatermarkValue datetime,
);

  2. Ställ in standardvärdet för högvattenmärket med tabellnamnet på källdatalagret. I den här självstudien är tabellnamnet: data_source_table.

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

  3. Granska informationen i tabellen watermarktable.

    Select * from watermarktable

    Utdata:

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

Skapa en lagrad procedur i din SQL-databas

Kör följande kommando för att skapa en lagrad procedur i din SQL-databas:

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

BEGIN

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

END

Skapa en datafabrik

  1. Starta webbläsaren Microsoft Edge eller Google Chrome. Just nu är det bara webbläsarna Microsoft Edge och Google Chrome som har stöd för Data Factory UI.

  2. Välj Skapa en resursintegreringsdatafabrik>> på den vänstra menyn:

    Data Factory selection in the "New" pane

  3. På sidan Ny datafabrik anger du ADFIncCopyTutorialDF som namn.

    Namnet på Azure Data Factory måste vara globalt unikt. Om du ser ett rött utropstecken med följande fel ändrar du namnet på datafabriken (till exempel dittnamnADFIncCopyTutorialDF) och provar att skapa fabriken igen. Se artikeln Data Factory – namnregler för namnregler för Data Factory-artefakter.

    Datafabriksnamnet "ADFIncCopyTutorialDF" är inte tillgängligt

  4. Välj den Azure-prenumeration som du vill skapa den nya datafabriken i.

  5. För resursgruppen utför du något av följande steg:

  6. Välj V2 för versionen.

  7. Välj plats för datafabriken. Endast platser som stöds visas i listrutan. Datalager (Azure Storage, Azure SQL Database, Azure SQL Managed Instance och så vidare) och beräkningar (HDInsight osv.) som används av datafabriken kan finnas i andra regioner.

  8. Klicka på Skapa.

  9. När datafabriken har skapats visas sidan Datafabrik som på bilden.

    Home page for the Azure Data Factory, with the Open Azure Data Factory Studio tile.

  10. Välj Öppna på panelen Öppna Azure Data Factory Studio för att starta Användargränssnittet för Azure Data Factory på en separat flik.

Skapa en pipeline

I den här självstudien skapar du en pipeline med två sökningsaktiviteter, en kopieringsaktivitet och en aktivitet för lagrad procedur i en länkad pipeline.

  1. På startsidan för Data Factory-användargränssnittet klickar du på orchestrate-panelen.

    Screenshot that shows the data factory home page with the Orchestrate button highlighted.

  2. I panelen Allmänt under Egenskaper anger du IncrementalCopyPipeline som Namn. Dölj sedan panelen genom att klicka på ikonen Egenskaper i det övre högra hörnet.

  3. Vi lägger till den första sökningsaktiviteten för att hämta det gamla vattenstämpelvärdet. I verktygslådan Aktiviteter expanderar du Allmänt och drar och släpper sökningen på pipelinedesignytan. Ändra aktivitetens namn till LookupOldWaterMarkActivity.

    First lookup activity - name

  4. Växla till fliken Inställningar och klicka på + Ny för källdatauppsättningen. I det här steget skapar du en datauppsättning för att representera data i watermarktable. Den här tabellen innehåller den gamla vattenstämpeln som användes i den tidigare kopieringen.

  5. I fönstret Ny datauppsättning väljer du Azure SQL Database och klickar på Fortsätt. Ett nytt fönster öppnas för datauppsättningen.

  6. I fönstret Ange egenskaper för datamängden anger du WatermarkDataset som Namn.

  7. För Länkad tjänst väljer du Ny och utför sedan följande steg:

    1. Ange AzureSqlDatabaseLinkedService som namn.

    2. Välj servern som Servernamn.

    3. Välj ditt databasnamn i listrutan.

    4. Ange användarnamnet och lösenordet.

    5. Om du vill testa anslutningen till SQL-databasen klickar du på Testa anslutning.

    6. Klicka på Finish.

    7. Bekräfta att AzureSqlDatabaseLinkedService har valts för länkad tjänst.

      New linked service window

    8. Välj Slutför.

  8. På fliken Anslut ion väljer du [dbo].[ watermarktable] för Table. Om du vill förhandsgranska data i tabellen klickar du på Förhandsgranska data.

    Watermark dataset - connection settings

  9. Växla till pipeline-redigeringsprogrammet genom att klicka på pipelinefliken högst upp eller på pipelinenamnet i trädvyn till vänster. I egenskapsfönstret för sökningsaktiviteten bekräftar du att WatermarkDataset är valt för fältet Source Dataset (Källdatauppsättning).

  10. I verktygslådan Aktiviteter expanderar du Allmänt och drar och släpper ytterligare en sökning på pipelinedesignytan och anger namnet LookupNewWaterMarkActivity på fliken Allmänt i egenskapsfönstret. Med den här sökningsaktiviteten kopieras det nya vattenstämpelvärdet från tabellen med källdata till målet.

  11. I egenskapsfönstret för den andra sökningsaktiviteten växlar du till fliken Inställningar och klickar på New (Nytt). Du skapar en datauppsättning för att peka på källtabellen som innehåller det nya vattenstämpelvärdet (det högsta värdet för LastModifyTime).

  12. I fönstret Ny datauppsättning väljer du Azure SQL Database och klickar på Fortsätt.

  13. I fönstret Ange egenskaper anger du SourceDataset som Namn. Välj AzureSqlDatabaseLinkedService som länkad tjänst.

  14. Välj [dbo].[data_source_table] som tabell. Du kan ange en fråga för den här datauppsättningen senare under kursen. Frågan åsidosätter den tabell som du anger i det här steget.

  15. Välj Slutför.

  16. Växla till pipeline-redigeringsprogrammet genom att klicka på pipelinefliken högst upp eller på pipelinenamnet i trädvyn till vänster. I egenskapsfönstret för sökningsaktiviteten bekräftar du att SourceDataset är valt för fältet Source Dataset (Källdatauppsättning).

  17. Välj Fråga i fältet Använd fråga och ange följande fråga: du väljer endast det högsta värdet för LastModifytime från data_source_table. Kontrollera att du också har markerat endast första raden.

    select MAX(LastModifytime) as NewWatermarkvalue from data_source_table

    Second lookup activity - query

  18. I verktygslådan Aktiviteter expanderar du Flytta och transformerar och drar och släpper aktiviteten Kopiera från verktygslådan Aktiviteter och anger namnet till IncrementalCopyActivity.

  19. Anslut båda sökningsaktiviteterna till kopieringsaktiviteten genom att dra den gröna knappen som är ansluten till sökningsaktiviteterna till kopieringsaktiviteten. Släpp musknappen när du ser att kantlinjefärgen för kopieringsaktiviteten ändras till blått.

    Connection Lookup activities to Copy activity

  20. Välj kopieringsaktiviteten och bekräfta att du ser egenskaperna för aktiviteten i fönstret Egenskaper.

  21. Växla till fliken Källa i fönstret Egenskaper och utför följande steg:

    1. Markera SourceDataset för fältet för källdatauppsättning.

    2. Välj Fråga i fältet Använd fråga.

    3. Ange följande SQL-fråga för fältet Fråga.

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

      Copy activity - source

  22. Växla till fliken Sink (Mottagare) och klicka på + Ny för fältet för datauppsättning för mottagare.

  23. I den här självstudien är datalager för mottagare av typen Azure Blob Storage. Välj därför Azure Blob Storage och klicka på Fortsätt i fönstret Ny datauppsättning .

  24. I fönstret Välj format väljer du formattypen för dina data och klickar på Fortsätt.

  25. I fönstret Ange egenskaper anger du SinkDataset som Namn. För Länkad tjänst väljer du + Ny. I det här steget skapar du en anslutning (länkad tjänst) till Azure Blob Storage.

  26. I fönstret Ny länkad tjänst (Azure Blob Storage) gör du följande:

    1. Ange AzureStorageLinkedService som namn.
    2. Välj ditt Azure Storage-konto i Lagringskontonamn.
    3. Testa Anslut ion och klicka sedan på Slutför.

  27. I fönstret Ange egenskaper bekräftar du att AzureStorageLinkedService har valts för länkad tjänst. Välj sedan Slutför.

  28. Gå till fliken Anslut ion i SinkDataset och gör följande:

    1. För fältet Filsökväg anger du adftutorial/incrementalcopy. adftutorial är blobcontainerns namn och incrementalcopy är mappens namn. Det här kodfragmentet förutsätter att du har en blobcontainer med namnet adftutorial i din blob-lagring. Skapa containern om den inte finns, eller ställ in den för namnet på en befintlig. Azure Data Factory skapar automatiskt utdatamappen incrementalcopy om den inte finns. Du kan också använda knappen Bläddra för Filsökväg för att navigera till en mapp i en blobcontainer.
    2. För fildelen av fältet Filsökväg väljer du Lägg till dynamiskt innehåll [Alt+P] och anger @CONCAT('Incremental-', pipeline().RunId, '.txt')sedan i det öppnade fönstret. Välj sedan Slutför. Filnamnet genereras dynamiskt med uttrycket. Varje pipelinekörning har ett unikt ID. Kopieringsaktiviteten använder körnings-ID för att generera filnamnet.

  29. Växla till pipeline-redigeringsprogrammet genom att klicka på pipelinefliken högst upp eller på pipelinenamnet i trädvyn till vänster.

  30. I verktygslådan Aktiviteter expanderar du Allmänt och drar och släpper aktiviteten Lagrad procedur från verktygslådan Aktiviteter till pipelinedesignerytan. Anslut gröna utdata (lyckades) från aktiviteten Kopiera till den lagrade proceduraktiviteten.

  31. Välj Lagrad proceduraktivitet i pipelinedesignern och ändra dess namn till StoredProceduretoWriteWatermarkActivity.

  32. Växla till fliken SQL-konto och välj AzureSqlDatabaseLinkedService för länkad tjänst.

  33. Växla till fliken Lagrad procedur och gör följande:

    1. Som Namn på lagrad procedur väljer du usp_write_watermark.

    2. När du ska ange värden för parametrarna för lagrad procedur klickar du på Importera parameter och anger följande värden för parametern:

      Namn Typ Värde
      LastModifiedtime Datum/tid @{activity('LookupNewWaterMarkActivity').output.firstRow.NewWatermarkvalue}
      TableName String @{activity('LookupOldWaterMarkActivity').output.firstRow.TableName}

      Stored Procedure Activity - stored procedure settings

  34. Verifiera pipelineinställningarna genom att klicka på Verifiera i verktygsfältet. Kontrollera att det inte finns några verifieringsfel. Om du vill stänga fönstret Pipelineverifieringsrapport klickar du på >>.

  35. Publicera entiteter (länkade tjänster, datauppsättningar och pipeliner) till Azure Data Factory-tjänsten genom att välja knappen Publicera alla. Vänta tills du ser ett meddelande om att publiceringen är klar.

Utlös en pipelinekörning

  1. Klicka på Lägg till utlösare i verktygsfältet och klicka på Utlös nu.

  2. I fönstret Pipeline Run (Pipelinekörning) väljer du Slutför.

Övervaka pipelinekörningen

  1. Växla till fliken Övervaka till vänster. Du ser status för pipelinekörningen som utlöses av en manuell utlösare. Du kan använda länkar under kolumnen PIPELINE NAME för att visa körningsinformation och köra pipelinen igen.

  2. Om du vill se aktivitetskörningar som är associerade med pipelinekörningen väljer du länken under kolumnen PIPELINE NAME (PIPELINE NAME ). Om du vill ha mer information om aktivitetskörningarna väljer du länken Information (glasögonikonen) under kolumnen AKTIVITETSNAMN . Välj Alla pipelinekörningar överst för att gå tillbaka till vyn Pipelinekörningar. Välj Uppdatera för att uppdatera vyn.

Granska resultaten

  1. Anslut till Azure Storage-kontot med verktyg som Azure Storage Explorer. Kontrollera att en utdatafil har skapats i mappen incrementalcopy i adftutorial-containern.

    First output file

  2. Öppna utdatafilen och notera att alla data har kopierats från data_source_table till blobfilen.

    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. Kontrollera det senaste värdet från watermarktable. Du kan se att vattenstämpeln har uppdaterats.

    Select * from watermarktable

    Här är utdata:

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

Lägg till mer data i källan

Infoga nya data i databasen (datakälllagret).

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')

Uppdaterade data i databasen är:

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

Utlös ytterligare en pipelinekörning

  1. Växla till fliken Redigera . Klicka på pipelinen i trädvyn om den inte öppnas i designern.

  2. Klicka på Lägg till utlösare i verktygsfältet och klicka på Utlös nu.

Övervaka den andra pipelinekörningen

  1. Växla till fliken Övervaka till vänster. Du ser status för pipelinekörningen som utlöses av en manuell utlösare. Du kan använda länkar under kolumnen PIPELINE NAME för att visa aktivitetsinformation och köra pipelinen igen.

  2. Om du vill se aktivitetskörningar som är associerade med pipelinekörningen väljer du länken under kolumnen PIPELINE NAME (PIPELINE NAME ). Om du vill ha mer information om aktivitetskörningarna väljer du länken Information (glasögonikonen) under kolumnen AKTIVITETSNAMN . Välj Alla pipelinekörningar överst för att gå tillbaka till vyn Pipelinekörningar. Välj Uppdatera för att uppdatera vyn.

Verifiera den andra utdata

  1. I blob-lagringen ser du att en annan fil har skapats. I den här självstudien är det nya filnamnet Incremental-<GUID>.txt. När du öppnar filen ser du två rader med poster i den.

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

  2. Kontrollera det senaste värdet från watermarktable. Du kan se att vattenstämpeln har uppdaterats igen.

    Select * from watermarktable

    Exempel på utdata:

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

Relaterat innehåll

I den här självstudiekursen fick du:

  • Förbered datalagringen för att lagra värdet för vattenstämpeln.
  • Skapa en datafabrik.
  • Skapa länkade tjänster.
  • Skapa datauppsättningar för källa, mottagare och vattenstämpel.
  • Skapa en pipeline.
  • Köra pipelinen.
  • Övervaka pipelinekörningen.
  • Granska resultat
  • Lägg till mer data i källan.
  • Kör pipelinen igen.
  • Övervaka den andra pipelinekörningen
  • Granska resultatet från den andra körningen

I den här självstudien kopierade pipelinen data från en enda tabell i SQL Database till Blob Storage. Gå vidare till följande självstudie för att lära dig hur du kopierar data från flera tabeller i en SQL Server-databas till SQL Database.

Läs in data stegvis från flera tabeller i SQL Server till Azure SQL Database