Cvičení – návrh a implementace pomalu se měnící dimenze typu 1 s mapováním toků dat
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.
Ze sady Synapse Studio přejděte do datového centra.
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).
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 )
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.
Přejděte do centra Vývoj .
Vyberte +a pak vyberte Tok dat.
V podokně vlastností nového toku dat zadejte
UpdateCustomerDimension
do pole Název (1) a pak výběrem tlačítka Vlastnosti (2) skryjte podokno vlastností.Na plátně vyberte Přidat zdroj .
V části
Source settings
Nakonfigurujte 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.
- Název výstupního streamu: Enter
V dialogovém okně nová datová sada integrace vyberte Azure Synapse Analytics a pak vyberte Pokračovat.
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.
- Název: Enter
Do pole Hodnota zadejte název fondu SQL a pak vyberte OK.
Vyberte
dbo.CustomerSource
v části Název tabulky, vyberteFrom connection/store
v části Importovat schéma a pak vyberte OK a vytvořte datovou sadu.Vyberte Otevřít vedle
CustomerSource
přidané datové sady.Do pole Hodnota vedle
DBName
pole Hodnota zadejte název fondu SQL.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.
- Název výstupního streamu: Enter
Přidání transformací do toku dat
Vyberte + napravo od
SourceDB
zdroje na plátně a pak vyberte Odvozený sloupec.V části
Derived column's settings
Nakonfigurujte 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
. FunkceiifNull
nahradí hodnoty null prázdnými řetězci. Jinak hodnoty hash mají tendenci duplikovat, pokud jsou k dispozici položky null.- Název výstupního streamu: Enter
Vpravo + od odvozeného
CreateCustomerHash
sloupce na plátně vyberte Možnost Existuje.V části
Exists settings
Nakonfigurujte následující vlastnosti:- Název výstupního streamu: Enter
Exists
- Levý stream: Vyberte
CreateCustomerHash
- Pravý datový proud: Vyberte
SynapseDimCustomer
- Typ existence: Vybrat
Doesn't exist
- Existuje podmínky: Nastavte pro levý a pravý následující:
Vlevo: Sloupec CreateCustomerHash Vpravo: Sloupec SynapseDimCustomer HashKey
HashKey
- Název výstupního streamu: Enter
Vyberte + napravo od
Exists
plátna a pak vyberte Vyhledat.V části
Lookup settings
Nakonfigurujte 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
- Název výstupního streamu: Enter
Vyberte + napravo od
LookupCustomerID
plátna a pak vyberte Odvozený sloupec.V části
Derived column's settings
Nakonfigurujte 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žijteInsertedDate
hodnotu.Vyberte ModifiedDate
currentTimestamp()
Vždy aktualizujte ModifiedDate
hodnotu aktuálním časovým razítkem.Poznámka:
Pokud chcete vložit druhý sloupec, vyberte + Přidat nad seznam sloupce a pak vyberte Přidat sloupec.
- Název výstupního streamu: Enter
Vyberte + napravo od kroku odvozeného
SetDates
sloupce na plátně a pak vyberte Změnit řádek.V části
Alter row settings
Nakonfigurujte 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í.- Název výstupního streamu: Enter
Vpravo + od
AllowUpserts
kroku alter row na plátně vyberte Jímku.V části
Sink
Nakonfigurujte 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
- Název výstupního streamu: Enter
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 columns
a pak vyberteCustomerID
v seznamu - Akce tabulky: Vybrat
None
- Povolit přípravu: Nezaškrtnuto
- Metoda aktualizace: Kontrola
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
Dokončený tok mapování by měl vypadat následovně. Výběrem možnosti Publikovat vše uložte provedené změny.
Vyberte Publikovat.
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.
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.
Záznam zákazníka úspěšně aktualizoval LastName
hodnotu tak, aby odpovídala zdrojovému záznamu a aktualizovala ModifiedDate
hodnotu 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.