Přírůstkové načítání dat z Azure SQL Database do úložiště objektů blob v Azure pomocí webu Azure Portal

  • Článek

PLATÍ PRO: Azure Data Factory Azure Synapse Analytics

Tip

Vyzkoušejte si službu Data Factory v Microsoft Fabric, řešení pro analýzy typu all-in-one pro podniky. Microsoft Fabric zahrnuje všechno od přesunu dat až po datové vědy, analýzy v reálném čase, business intelligence a vytváření sestav. Přečtěte si, jak začít používat novou zkušební verzi zdarma.

V tomto kurzu vytvoříte službu Azure Data Factory s kanálem, který načte rozdílová data z tabulky ve službě Azure SQL Database do služby Azure Blob Storage.

V tomto kurzu provedete následující kroky:

  • Příprava úložiště dat pro uložení hodnoty meze
  • Vytvoření datové továrny
  • Vytvoření propojených služeb
  • Vytvoření zdroje, jímky a datových sad mezí
  • Vytvořit kanál.
  • Spusťte kanál.
  • Monitorování spuštění kanálu
  • Kontrola výsledků
  • Přidání dalších dat do zdroje
  • Opětovné spuštění kanálu
  • Monitorování druhého spuštění kanálu
  • Kontrola výsledků druhého spuštění

Přehled

Tady je souhrnný diagram tohoto řešení:

Incrementally load data

Tady jsou důležité kroky pro vytvoření tohoto řešení:

  1. Vyberte sloupec meze. Ve zdrojovém úložišti dat vyberte jeden sloupec, který je možné použít k rozlišení nových nebo aktualizovaných záznamů pro každé spuštění. Data v tomto vybraném sloupci (například čas_poslední_změny nebo ID) se při vytváření nebo aktualizaci řádků obvykle zvyšují. Maximální hodnota v tomto sloupci se používá jako horní mez.

  2. Připravte úložiště dat pro uložení hodnoty meze. V tomto kurzu uložíte hodnotu meze do databáze SQL.

  3. Vytvořte kanál s následujícím pracovním postupem:

    Kanál v tomto řešení má následující aktivity:

    • Vytvořte dvě aktivity vyhledávání. První aktivitu vyhledávání použijte k načtení poslední hodnoty meze. Druhou aktivitu vyhledávání použijte k načtení nové hodnoty meze. Tyto hodnoty meze se předají aktivitě kopírování.
    • Vytvořte aktivitu kopírování, která ze zdrojového úložiště dat zkopíruje řádky, které ve sloupci horní meze mají hodnotu vyšší, než je stará hodnota meze, a nižší, než je nová hodnota meze. Potom tato rozdílová data zkopíruje ze zdrojového úložiště dat do úložiště objektů blob jako nový soubor.
    • Vytvořte aktivitu uložené procedury StoredProcedure, která aktualizuje hodnotu meze pro příští spuštění kanálu.

Pokud ještě nemáte předplatné Azure, vytvořte si napřed bezplatný účet.

Požadavky

  • Azure SQL Database Tuto databázi použijete jako zdrojové úložiště dat. Pokud ve službě Azure SQL Database nemáte databázi, přečtěte si téma Vytvoření databáze ve službě Azure SQL Database , kde najdete postup jeho vytvoření.
  • Azure Storage Úložiště objektů blob použijete jako úložiště dat jímky. Pokud nemáte účet úložiště, přečtěte si téma Vytvoření účtu úložiště, kde najdete kroky pro jeho vytvoření. Vytvořte kontejner s názvem adftutorial.

Vytvoření tabulky zdroje dat v databázi SQL

  1. Otevřete sadu SQL Server Management Studio. V Průzkumníku serveru klikněte pravým tlačítkem na databázi a zvolte Nový dotaz.

  2. Spuštěním následujícího příkazu SQL na vaší databázi SQL vytvořte tabulku s názvem data_source_table jako úložiště zdroje dat:

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

    V tomto kurzu jako sloupec meze použijete LastModifytime. Data v úložišti zdroje dat zobrazuje následující tabulka:

    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

Vytvoření další tabulky v databázi SQL pro ukládání hodnoty horní meze

  1. Spuštěním následujícího příkazu SQL na databázi SQL vytvořte tabulku s názvem watermarktable pro uložení hodnoty meze:

    create table watermarktable
(

TableName varchar(255),
WatermarkValue datetime,
);

  2. Jako výchozí hodnotu horní meze nastavte název tabulky zdrojového úložiště dat. V tomto kurzu má tato tabulka název data_source_table.

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

  3. Zkontrolujte data v tabulce watermarktable.

    Select * from watermarktable

    Výstup:

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

Vytvoření uložené procedury v databázi SQL

Spuštěním následujícího příkazu vytvořte v databázi SQL uloženou proceduru:

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

BEGIN

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

END

Vytvoření datové továrny

  1. Spusťte webový prohlížeč Microsoft Edge nebo Google Chrome. Uživatelské rozhraní služby Data Factory podporují v současnosti jenom webové prohlížeče Microsoft Edge a Google Chrome.

  2. V nabídce vlevo vyberte Vytvořit službu Data Factory pro integraci>prostředků>:

    Data Factory selection in the "New" pane

  3. Na stránce Nová datová továrna jako název zadejte ADFIncCopyTutorialDF.

    Název služby Azure Data Factory musí být globálně jedinečný. Pokud se zobrazí červený vykřičník s následující chybou, změňte název datové továrny (například na vaše_jméno_ADFIncCopyTutorialDF) a zkuste to znovu. Pravidla pojmenování artefaktů služby Data Factory najdete v článku Data Factory – pravidla pojmenování.

    Název datové továrny ADFIncCopyTutorialDF není k dispozici.

  4. Vyberte své předplatné Azure, ve kterém chcete vytvořit datovou továrnu.

  5. Pro Skupinu prostředků proveďte jeden z následujících kroků:

  6. Jako verzi vyberte V2.

  7. Vyberte umístění pro objekt pro vytváření dat. V rozevíracím seznamu se zobrazí pouze podporovaná umístění. Úložiště dat (Azure Storage, Azure SQL Database, Azure SQL Managed Instance atd.) a výpočetní prostředí (HDInsight atd.) používané datovou továrnou můžou být v jiných oblastech.

  8. Klikněte na Vytvořit.

  9. Po vytvoření se zobrazí stránka Datová továrna, jak je znázorněno na obrázku.

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

  10. Výběrem možnosti Otevřít na dlaždici Otevřít Azure Data Factory Studio spusťte uživatelské rozhraní (UI) služby Azure Data Factory na samostatné kartě.

Vytvořit kanál

V tomto kurzu vytvoříte kanál se dvěma aktivitami vyhledávání, jednou aktivitou kopírování a jednou aktivitou uložené procedury, a to všechno zřetězené v jednom kanálu.

  1. Na domovské stránce uživatelského rozhraní služby Data Factory klikněte na dlaždici Orchestrate .

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

  2. Na panelu Obecné v části Vlastnosti zadejte IncrementalCopyPipeline pro Název. Potom panel sbalte kliknutím na ikonu Vlastnosti v pravém horním rohu.

  3. Přidejme první aktivitu vyhledávání, která načte poslední hodnotu meze. V sadě nástrojů Aktivity rozbalte Obecné a přetáhněte aktivitu Vyhledávání na plochu návrháře kanálu. Změňte název aktivity na LookupOldWaterMarkActivity.

    First lookup activity - name

  4. Přepněte na kartu Nastavení a klikněte na + Nová u možnosti Zdrojová datová sada. V tomto kroku vytvoříte datovou sadu, která bude představovat data v tabulce watermarktable. Tato tabulka obsahuje starou mez, která se použila v předchozí operaci kopírování.

  5. V okně Nová datová sada vyberte Azure SQL Database a klikněte na Pokračovat. Zobrazí se nové okno otevřené pro datovou sadu.

  6. V okně Nastavit vlastnosti datové sady zadejte WatermarkDataset pro Název.

  7. V části Propojená služba vyberte Nový a pak proveďte následující kroky:

    1. Jako Název zadejte AzureSqlDatabaseLinkedService.

    2. Vyberte server pro název serveru.

    3. V rozevíracím seznamu vyberte název databáze.

    4. Zadejte svoje uživatelské jméno a heslo.

    5. Chcete-li otestovat připojení k databázi SQL, klikněte na tlačítko Test připojení.

    6. Klikněte na Finish (Dokončit).

    7. Ověřte, že je pro propojenou službu vybraná služba AzureSqlDatabaseLinkedService.

      New linked service window

    8. Vyberte Dokončit.

  8. Na kartě Připojení ion vyberte [dbo].[ watermarktable] for Table. Pokud chcete zobrazit náhled dat v tabulce, klikněte na Náhled dat.

    Watermark dataset - connection settings

  9. Přepněte na editor kanálu kliknutím na kartu kanálu v horní části nebo kliknutím na název kanálu ve stromovém zobrazení vlevo. V okně Vlastnosti aktivity Vyhledávání ověřte, že je v poli Zdrojová datová sada vybraná datová sada WatermarkDataset.

  10. V sadě nástrojů Aktivity rozbalte Obecné a přetáhněte další aktivitu Vyhledávání na plochu návrháře kanálu. Pak na kartě Obecné v okně vlastností nastavte název na LookupNewWaterMarkActivity. Tato aktivita vyhledávání získá hodnotu meze z tabulky se zdrojovými daty, která se mají zkopírovat do cíle.

  11. V okně Vlastnosti druhé aktivity Vyhledávání přepněte na kartu Nastavení a klikněte na Nový. Vytvoříte datovou sadu, která bude odkazovat na zdrojovou tabulku obsahující novou hodnotu meze (maximální hodnota LastModifyTime).

  12. V okně Nová datová sada vyberte Azure SQL Database a klikněte na Pokračovat.

  13. V okně Nastavit vlastnosti zadejte SourceDataset pro Název. Jako Propojená služba vyberte AzureSqlDatabaseLinkedService.

  14. Jako Tabulka vyberte [dbo].[tabulka_zdroje_dat]. Později v tomto kurzu pro tuto datovou sadu zadáte dotaz. Dotaz má přednost před tabulkou, kterou zadáte v tomto kroku.

  15. Vyberte Dokončit.

  16. Přepněte na editor kanálu kliknutím na kartu kanálu v horní části nebo kliknutím na název kanálu ve stromovém zobrazení vlevo. V okně Vlastnosti aktivity Vyhledávání ověřte, že je v poli Zdrojová datová sada vybraná datová sada SourceDataset.

  17. V poli Použít dotaz vyberte Dotaz a zadejte následující dotaz: vybíráte pouze maximální hodnotu LastModifytime z tabulky tabulka_zdroje_dat. Ujistěte se, že jste také zkontrolovali pouze první řádek.

    select MAX(LastModifytime) as NewWatermarkvalue from data_source_table

    Second lookup activity - query

  18. Na panelu nástrojů Aktivity rozbalte položku Přesunout a transformovat a přetáhněte aktivitu kopírování z panelu nástrojů Aktivity a nastavte název na IncrementalCopyActivity.

  19. Propojte obě aktivity vyhledávání s aktivitou kopírování přetažením zeleného tlačítka připojeného k aktivitám vyhledávání na aktivitu kopírování. Jakmile se barva ohraničení aktivity kopírování změní na modrou, uvolněte tlačítko myši.

    Connection Lookup activities to Copy activity

  20. Vyberte aktivitu kopírování a ověřte, že se v okně Vlastnosti zobrazí vlastnosti aktivity.

  21. V okně Vlastnosti přepněte na kartu Zdroj a proveďte následující kroky:

    1. V poli Zdrojová datová sada vyberte SourceDataset.

    2. V poli Použít dotaz vyberte Dotaz.

    3. Do pole Dotaz zadejte následující příkaz jazyka SQL.

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

      Copy activity - source

  22. Přepněte na kartu Jímka a klikněte na + Nová vedle pole Datová sada jímky.

  23. V tomto kurzu je úložiště dat jímky typu Azure Blob Storage. Proto vyberte Azure Blob Storage a v okně Nová datová sada klikněte na Pokračovat.

  24. V okně Vybrat formát vyberte typ formátu dat a klikněte na Pokračovat.

  25. V okně Nastavit vlastnosti zadejte SinkDataset pro název. Pro propojenou službu vyberte + Nový. V tomto kroku vytvoříte připojení (propojenou službu) ke svému úložišti objektů blob v Azure.

  26. V okně Nová propojená služba (Azure Blob Storage) proveďte následující kroky:

    1. Jako Název zadejte AzureStorageLinkedService.
    2. Jako Název účtu úložiště vyberte svůj účet služby Azure Storage.
    3. Otestujte Připojení ion a klepněte na tlačítko Dokončit.

  27. V okně Nastavit vlastnosti ověřte, že je pro propojenou službu vybraná služba AzureStorageLinkedService. Pak vyberte Dokončit.

  28. Přejděte na kartu Připojení ion sinkDataset a proveďte následující kroky:

    1. Do pole Cesta k souboru zadejte adftutorial/incrementalcopy. adftutorial je název kontejneru objektů blob a incrementalcopy je název složky. Tento fragment kódu předpokládá, že ve svém úložišti objektů blob máte kontejner objektů blob s názvem adftutorial. Pokud tento kontejner neexistuje, vytvořte ho nebo použijte název existujícího kontejneru. Azure Data Factory automaticky vytvoří výstupní složku incrementalcopy, pokud neexistuje. Můžete také použít tlačítko Procházet u možnosti Cesta k souboru a přejít ke složce v kontejneru objektů blob.
    2. V části Soubor v poli Cesta k souboru vyberte Přidat dynamický obsah [Alt+P] a pak zadejte @CONCAT('Incremental-', pipeline().RunId, '.txt')do otevřeného okna. Pak vyberte Dokončit. Název souboru se vygeneruje dynamicky pomocí tohoto výrazu. Každé spuštění kanálu má jedinečné ID. Aktivita kopírování používá ID spuštění k vygenerování názvu souboru.

  29. Přepněte na editor kanálu kliknutím na kartu kanálu v horní části nebo kliknutím na název kanálu ve stromovém zobrazení vlevo.

  30. V sadě nástrojů Aktivity rozbalte Obecné a přetáhněte aktivitu Uložená procedura ze sady nástrojů Aktivity na plochu návrháře kanálu. Připojte zelený výstup (Úspěch) aktivity Kopírování k aktivitě Uložená procedura.

  31. Vyberte v návrháři kanálu aktivitu Uložená procedura a změňte její název na StoredProceduretoWriteWatermarkActivity.

  32. Přepněte na kartu Účet SQL a vyberte AzureSqlDatabaseLinkedService pro propojenou službu.

  33. Přepněte na kartu Uložená procedura a proveďte následující kroky:

    1. Jako název uložené procedury vyberte usp_write_watermark.

    2. Pokud chcete zadat hodnoty parametrů uložené procedury, klikněte na Importovat parametr a zadejte následující hodnoty parametrů:

      Name Typ Hodnota
      LastModifiedtime DateTime @{activity('LookupNewWaterMarkActivity').output.firstRow.NewWatermarkvalue}
      TableName String @{activity('LookupOldWaterMarkActivity').output.firstRow.TableName}

      Stored Procedure Activity - stored procedure settings

  34. Pokud chcete ověřit nastavení kanálu, klikněte na Ověřit na panelu nástrojů. Ověřte, že se nezobrazí žádné chyby ověření. Chcete-li zavřít okno sestavy ověření kanálu, klepněte na tlačítko >>.

  35. Pomocí tlačítka Publikovat vše publikujte entity (propojené služby, datové sady a kanály) do služby Azure Data Factory. Počkejte, dokud se nezobrazí zpráva o úspěšném publikování.

Aktivace spuštění kanálu

  1. Klepněte na tlačítko Přidat aktivační událost na panelu nástrojů a klepněte na tlačítko Aktivovat nyní.

  2. V okně Spuštění kanálu vyberte Dokončit.

Monitorování spuštění kanálu

  1. Vlevo přepněte na kartu Monitorování. Zobrazí se stav spuštění kanálu aktivovaného ručním triggerem. Pomocí odkazů ve sloupci NÁZEV KANÁLU můžete zobrazit podrobnosti o spuštění a znovu spustit kanál.

  2. Pokud chcete zobrazit spuštění aktivit související se spuštěním kanálu, vyberte odkaz ve sloupci NÁZEV KANÁLU. Podrobnosti o spuštěních aktivit získáte tak, že ve sloupci NÁZEV AKTIVITY vyberete odkaz Podrobnosti (ikona brýle). Výběrem možnosti Všechny spuštění kanálu v horní části se vraťte do zobrazení Spuštění kanálu. Jestliže chcete zobrazení aktualizovat, vyberte Aktualizovat.

Kontrola výsledků

  1. Pomocí nástroje, jako je Průzkumník služby Azure Storage se připojte ke svému účtu služby Azure Storage. Ověřte, že se ve složce incrementalcopy kontejneru adftutorial vytvořil výstupní soubor.

    First output file

  2. Otevřete výstupní soubor a všimněte si, že se do souboru objektu blob zkopírovala veškerá data z tabulky tabulka_zdroje_dat.

    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. Zkontrolujte nejnovější hodnotu z watermarktable. Uvidíte, že hodnota meze byla aktualizována.

    Select * from watermarktable

    Tady je výstup:

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

Přidání dalších dat do zdroje

Vložení nových dat do databáze (úložiště zdrojů dat)

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

Aktualizovaná data v databázi jsou:

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

Aktivace dalšího spuštění kanálu

  1. Přepněte na kartu Upravit . Klikněte na kanál ve stromovém zobrazení, pokud není otevřený v návrháři.

  2. Klepněte na tlačítko Přidat aktivační událost na panelu nástrojů a klepněte na tlačítko Aktivovat nyní.

Monitorování druhého spuštění kanálu

  1. Vlevo přepněte na kartu Monitorování. Zobrazí se stav spuštění kanálu aktivovaného ručním triggerem. Pomocí odkazů ve sloupci NÁZEV KANÁLU můžete zobrazit podrobnosti o aktivitě a znovu spustit kanál.

  2. Pokud chcete zobrazit spuštění aktivit související se spuštěním kanálu, vyberte odkaz ve sloupci NÁZEV KANÁLU. Podrobnosti o spuštěních aktivit získáte tak, že ve sloupci NÁZEV AKTIVITY vyberete odkaz Podrobnosti (ikona brýle). Výběrem možnosti Všechny spuštění kanálu v horní části se vraťte do zobrazení Spuštění kanálu. Jestliže chcete zobrazení aktualizovat, vyberte Aktualizovat.

Ověření druhého výstupu

  1. V úložišti objektů blob uvidíte, že se vytvořil další soubor. V tomto kurzu se tento nový jmenuje Incremental-<GUID>.txt. Otevřete tento soubor a uvidíte, že obsahuje dva řádky záznamů.

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

  2. Zkontrolujte nejnovější hodnotu z watermarktable. Uvidíte, že hodnota meze byla znovu aktualizována.

    Select * from watermarktable

    Ukázkový výstup:

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

Související obsah

V tomto kurzu jste provedli následující kroky:

  • Příprava úložiště dat pro uložení hodnoty meze
  • Vytvoření datové továrny
  • Vytvoření propojených služeb
  • Vytvoření zdroje, jímky a datových sad mezí
  • Vytvořit kanál.
  • Spusťte kanál.
  • Monitorování spuštění kanálu
  • Kontrola výsledků
  • Přidání dalších dat do zdroje
  • Opětovné spuštění kanálu
  • Monitorování druhého spuštění kanálu
  • Kontrola výsledků druhého spuštění

V tomto kurzu kanál zkopíroval data z jedné tabulky ve službě SQL Database do úložiště objektů blob. V následujícím kurzu se dozvíte, jak zkopírovat data z více tabulek v databázi SQL Serveru do služby SQL Database.

Přírůstkové načtení dat z více tabulek v SQL Serveru do Azure SQL Database