Cvičení – návrh a implementace pomalu se měnící dimenze typu 1 s mapováním toků dat

  • 10 min

V tomto cvičení vytvoříte tok dat pro SCD typu 1 pomocí vyhrazeného fondu SQL Azure Synapse jako zdroje a cíle. Tento tok dat je pak možné přidat do kanálu Synapse a spustit ho jako součást procesu extrakce, transformace, načítání (ETL).

Nastavení zdrojové tabulky a tabulky dimenzí

V tomto cvičení chcete načíst tabulku dimenzí ve službě Azure Synapse ze zdrojových dat, která by mohla být z mnoha různých typů systémů, jako je Azure SQL, Azure Storage atd. V tomto příkladu můžete jednoduše vytvořit zdrojová data v databázi Azure Synapse.

  1. Ze sady Synapse Studio přejděte do datového centra.

    Data hub.

  2. Vyberte kartu Pracovní prostor (1), rozbalte položku Databáze a potom klikněte pravým tlačítkem na SQLPool01 (2). Vyberte Nový skript SQL (3) a pak vyberte Prázdný skript (4).

    The data hub is displayed with the context menus to create a new SQL script.

  3. Do prázdného okna skriptu vložte následující skript a pak vyberte Spustit nebo stiskněte a F5 spusťte dotaz:

    CREATE TABLE [dbo].[CustomerSource] (
    [CustomerID] [int] NOT NULL,
    [Title] [nvarchar](8),
    [FirstName] [nvarchar](50),
    [MiddleName] [nvarchar](50),
    [LastName] [nvarchar](50),
    [Suffix] [nvarchar](10),
    [CompanyName] [nvarchar](128),
    [SalesPerson] [nvarchar](256),
    [EmailAddress] [nvarchar](50),
    [Phone] [nvarchar](25)
) WITH ( HEAP )

COPY INTO [dbo].[CustomerSource]
FROM 'https://solliancepublicdata.blob.core.windows.net/dataengineering/dp-203/awdata/CustomerSource.csv'
WITH (
    FILE_TYPE='CSV',
    FIELDTERMINATOR='|',
    FIELDQUOTE='',
    ROWTERMINATOR='0x0a',
    ENCODING = 'UTF16'
)

CREATE TABLE dbo.[DimCustomer](
    [CustomerID] [int] NOT NULL,
    [Title] [nvarchar](8) NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [MiddleName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [Suffix] [nvarchar](10) NULL,
    [CompanyName] [nvarchar](128) NULL,
    [SalesPerson] [nvarchar](256) NULL,
    [EmailAddress] [nvarchar](50) NULL,
    [Phone] [nvarchar](25) NULL,
    [InsertedDate] [datetime] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
    [HashKey] [char](64)
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
)

    The script and Run button are both highlighted.

Vytvoření toku dat mapování

Mapování toků dat jsou aktivity kanálu, které poskytují vizuální způsob, jak transformovat data prostřednictvím prostředí bez kódu. Dále vytvoříte mapování toku dat, který vytvoří SCD typu 1.

  1. Přejděte do centra Vývoj .

    Develop hub.

  2. Vyberte +a pak vyberte Tok dat.

    The plus button and data flow menu item are highlighted.

  3. V podokně vlastností nového toku dat zadejte UpdateCustomerDimensiondo pole Název (1) a pak výběrem tlačítka Vlastnosti (2) skryjte podokno vlastností.

    The data flow properties pane is displayed.

  4. Na plátně vyberte Přidat zdroj .

    The Add Source button is highlighted on the data flow canvas.

  5. V části Source settingsNakonfigurujte následující vlastnosti:

    • Název výstupního streamu: Enter SourceDB
    • Typ zdroje: Vybrat Dataset
    • Možnosti: Zaškrtnutí Allow schema drift a ponechání ostatních možností nezaškrtnuté
    • Vzorkování: Výběr Disable
    • Datová sada: Výběrem + Nový vytvořte novou datovou sadu.

    The New button is highlighted next to Dataset.

  6. V dialogovém okně nová datová sada integrace vyberte Azure Synapse Analytics a pak vyberte Pokračovat.

    Azure SQL Database and the Continue button are highlighted.

  7. Ve vlastnostech datové sady nakonfigurujte následující:

    • Název: Enter CustomerSource
    • Propojená služba: Výběr propojené služby pracovního prostoru Synapse
    • Název tabulky: Vyberte tlačítko Aktualizovat vedle rozevíracího seznamu.

    The form is configured as described and the refresh button is highlighted.

  8. Do pole Hodnota zadejte název fondu SQL a pak vyberte OK.

    The SQLPool01 parameter is highlighted.

  9. Vyberte dbo.CustomerSource v části Název tabulky, vyberte From connection/store v části Importovat schéma a pak vyberte OK a vytvořte datovou sadu.

    The form is completed as described.

  10. Vyberte Otevřít vedle CustomerSource přidané datové sady.

    The open button is highlighted next to the new dataset.

  11. Do pole Hodnota vedle DBNamepole Hodnota zadejte názevfondu SQL.

  12. V editoru toku dat vyberte pole Přidat zdroj pod aktivitou SourceDB. Nakonfigurujte tento zdroj jako tabulku DimCustomer podle stejných kroků jako pro CustomerSource.

    • Název výstupního streamu: Enter DimCustomer
    • Typ zdroje: Vybrat Dataset
    • Možnosti: Zaškrtnutí Allow schema drift a ponechání ostatních možností nezaškrtnuté
    • Vzorkování: Výběr Disable
    • Datová sada: Vyberte + Nový a vytvořte novou datovou sadu. Použijte propojenou službu Azure Synapse a zvolte tabulku DimCustomer. Nezapomeňte nastavit dbName na název fondu SQL.

    The Add Source, Output stream name, and Dataset name are highlighted in the Source settings.

Přidání transformací do toku dat

  1. Vyberte + napravo od SourceDB zdroje na plátně a pak vyberte Odvozený sloupec.

    The plus button and derived column menu item are highlighted.

  2. V části Derived column's settingsNakonfigurujte následující vlastnosti:

    • Název výstupního streamu: Enter CreateCustomerHash
    • Příchozí datový proud: Vyberte SourceDB
    • Sloupce: Zadejte následující:
    Column Expression Popis
    Psaní HashKey sha2(256, iifNull(Title,'') +FirstName +iifNull(MiddleName,'') +LastName +iifNull(Suffix,'') +iifNull(CompanyName,'') +iifNull(SalesPerson,'') +iifNull(EmailAddress,'') +iifNull(Phone,'')) Vytvoří hodnotu hash SHA256 hodnot tabulky. Používáme to k detekci změn řádků porovnáním hodnoty hash příchozích záznamů s hodnotou hash cílových záznamů odpovídající hodnotě CustomerID . Funkce iifNull nahradí hodnoty null prázdnými řetězci. Jinak hodnoty hash mají tendenci duplikovat, pokud jsou k dispozici položky null.

    The Derived column's settings form is configured as described.

  3. Vpravo + od odvozeného CreateCustomerHash sloupce na plátně vyberte Možnost Existuje.

    The plus button and exists menu item are both highlighted.

  4. V části Exists settingsNakonfigurujte následující vlastnosti:

    • Název výstupního streamu: Enter Exists
    • Levý stream: Vyberte CreateCustomerHash
    • Pravý datový proud: Vyberte SynapseDimCustomer
    • Typ existence: VybratDoesn't exist
    • Existuje podmínky: Nastavte pro levý a pravý následující:
    Vlevo: Sloupec CreateCustomerHash Vpravo: Sloupec SynapseDimCustomer
    HashKey HashKey

    The Exists settings form is configured as described.

  5. Vyberte + napravo od Exists plátna a pak vyberte Vyhledat.

    The plus button and lookup menu item are both highlighted.

  6. V části Lookup settingsNakonfigurujte následující vlastnosti:

    • Název výstupního streamu: Enter LookupCustomerID
    • Primární datový proud: Vyberte Exists
    • Vyhledávací stream: Vybrat SynapseDimCustomer
    • Shoda s více řádky: Nezaškrtnuto
    • Shoda dne: Vybrat Any row
    • Podmínky vyhledávání: Nastavte pro levý a pravý následující kód:
    Vlevo: Existuje sloupec Vpravo: Sloupec SynapseDimCustomer
    CustomerID CustomerID

    The Lookup settings form is configured as described.

  7. Vyberte + napravo od LookupCustomerID plátna a pak vyberte Odvozený sloupec.

    The plus button and derived column menu item are both highlighted.

  8. V části Derived column's settingsNakonfigurujte následující vlastnosti:

    • Název výstupního streamu: Enter SetDates
    • Příchozí datový proud: Vyberte LookupCustomerID
    • Sloupce: Zadejte následující:
    Column Expression Popis
    Vyberte InsertedDate iif(isNull(InsertedDate), currentTimestamp(), {InsertedDate}) InsertedDate Pokud je hodnota null, vložte aktuální časové razítko. V opačném případě použijte InsertedDate hodnotu.
    Vyberte ModifiedDate currentTimestamp() Vždy aktualizujte ModifiedDate hodnotu aktuálním časovým razítkem.

    Another Derived column's settings form is configured as described.

    Poznámka:

    Pokud chcete vložit druhý sloupec, vyberte + Přidat nad seznam sloupce a pak vyberte Přidat sloupec.

  9. Vyberte + napravo od kroku odvozeného SetDates sloupce na plátně a pak vyberte Změnit řádek.

    The plus button and alter row menu item are both highlighted.

  10. V části Alter row settingsNakonfigurujte následující vlastnosti:

    • Název výstupního streamu: Enter AllowUpserts
    • Příchozí datový proud: Vyberte SetDates
    • Změnit podmínky řádku: Zadejte následující:
    Podmínka Expression Popis
    Vyberte Upsert if true() Nastavte podmínku na podmínku true()Upsert if tak, aby umožňovala upserty. Tím zajistíte, že se do jímky vloží nebo aktualizuje všechna data, která procházejí kroky v toku dat mapování.

    The alter row settings form is configured as described.

  11. Vpravo + od AllowUpserts kroku alter row na plátně vyberte Jímku.

    The plus button and sink menu item are both highlighted.

  12. V části SinkNakonfigurujte následující vlastnosti:

    • Název výstupního streamu: Enter Sink
    • Příchozí datový proud: Vyberte AllowUpserts
    • Typ jímky: Vybrat Dataset
    • Datová sada: Vyberte DimCustomer
    • Možnosti: Zaškrtnutí Allow schema drift a zrušení zaškrtnutí Validate schema

    The sink properties form is configured as described.

  13. Vyberte kartu Nastavení a nakonfigurujte následující vlastnosti:

    • Metoda aktualizace: Kontrola Allow upsert a zrušení zaškrtnutí všech ostatních možností
    • Klíčové sloupce: Vyberte List of columnsa pak vyberte CustomerID v seznamu
    • Akce tabulky: Vybrat None
    • Povolit přípravu: Nezaškrtnuto

    The sink settings are configured as described.

  14. Vyberte kartu Mapování a zrušte zaškrtnutí políčka Automatické mapování. Nakonfigurujte mapování vstupních sloupců, jak je uvedeno níže:

    Vstupní sloupce Výstupní sloupce
    SourceDB@CustomerID CustomerID
    SourceDB@Title Title
    SourceDB@FirstName FirstName
    SourceDB@MiddleName MiddleName
    SourceDB@LastName LastName
    SourceDB@Suffix Suffix
    SourceDB@CompanyName CompanyName
    SourceDB@SalesPerson SalesPerson
    SourceDB@EmailAddress EmailAddress
    SourceDB@Phone Phone
    InsertedDate InsertedDate
    ModifiedDate ModifiedDate
    CreateCustomerHash@HashKey HashKey

    Mapping settings are configured as described.

  15. Dokončený tok mapování by měl vypadat následovně. Výběrem možnosti Publikovat vše uložte provedené změny.

    The completed data flow is displayed and Publish all is highlighted.

  16. Vyberte Publikovat.

    The publish button is highlighted.

Jak otestovat tok dat

Dokončili jste tok dat SCD typu 1. Pokud se rozhodnete tento tok dat otestovat, můžete ho přidat do kanálu integrace Synapse. Potom můžete kanál spustit jednou a provést počáteční načtení zdrojových dat zákazníka do cíle DimCustomer.

Každé další spuštění kanálu porovná data ve zdrojové tabulce s tím, co už je v tabulce dimenzí (pomocí hashKey) a aktualizuje pouze záznamy, které se změnily. Abyste to mohli otestovat, mohli byste aktualizovat záznam ve zdrojové tabulce a pak kanál spustit znovu a ověřit aktualizace záznamů v tabulce dimenzí.

Vezměte zákazníka Janet Gatesa jako příklad. Počáteční zatížení ukazuje, že LastName je Gates a CustomerId je 4.

The script is displayed with the initial customer record.

Tady je příklad příkazu, který by aktualizoval příjmení zákazníka ve zdrojové tabulce.

UPDATE [dbo].[CustomerSource]
SET LastName = 'Lopez'
WHERE [CustomerId] = 4

Po aktualizaci záznamu a opětovném spuštění kanálu by dimCustomer zobrazil tato aktualizovaná data.

The script is displayed with the updated customer record.

Záznam zákazníka úspěšně aktualizoval LastName hodnotu tak, aby odpovídala zdrojovému záznamu a aktualizovala ModifiedDatehodnotu bez sledování staré LastName hodnoty. Toto je očekávané chování pro SCD typu 1. Pokud se pro LastName pole vyžadovala historie, upravte tabulku a tok dat tak, aby byly jedním z dalších typů SCD, které jste se naučili.