Oefening: een langzaam veranderende dimensie van type 1 ontwerpen en implementeren met toewijzingsgegevensstromen

  • 10 minuten

In deze oefening maakt u een gegevensstroom voor een SCD van het type 1 met behulp van een toegewezen SQL-pool van Azure Synapse als de bron en het doel. Deze gegevensstroom kan vervolgens worden toegevoegd aan een Synapse-pijplijn en worden uitgevoerd als onderdeel van het ETL-proces (extract, transform, load).

Bron- en dimensietabel instellen

Voor deze oefening wilt u een dimensietabel in Azure Synapse laden op basis van brongegevens die afkomstig kunnen zijn van veel verschillende systeemtypen, zoals Azure SQL, Azure Storage, enzovoort. In dit voorbeeld houdt u het eenvoudig door de brongegevens in uw Azure Synapse-database te maken.

  1. Navigeer vanuit Synapse Studio naar de datahub .

    Data hub.

  2. Selecteer het tabblad Werkruimte (1), vouw Databases uit en klik met de rechtermuisknop op SQLPool01 (2). Selecteer Nieuw SQL-script (3) en selecteer vervolgens Leeg script (4).

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

  3. Plak het volgende script in het lege scriptvenster en selecteer Vervolgens Uitvoeren of hit F5 om de query uit te voeren:

    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.

Een toewijzingsgegevensstroom maken

Toewijzingsgegevensstromen zijn pijplijnactiviteiten die een visuele manier bieden om gegevens te transformeren, via een codevrije ervaring. Vervolgens maakt u een toewijzingsgegevensstroom om een SCD van type 1 te maken.

  1. Navigeer naar de ontwikkelhub .

    Develop hub.

  2. Selecteer +en selecteer vervolgens Gegevensstroom.

    The plus button and data flow menu item are highlighted.

  3. Voer UpdateCustomerDimension in het eigenschappenvenster van de nieuwe gegevensstroom het veld Naam (1) in en selecteer vervolgens de knop Eigenschappen (2) om het eigenschappenvenster te verbergen.

    The data flow properties pane is displayed.

  4. Selecteer Bron toevoegen op het canvas.

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

  5. Configureer onder Source settingsde volgende eigenschappen:

    • Naam van uitvoerstroom: Enter SourceDB
    • Brontype: Selecteren Dataset
    • Opties: De Allow schema drift andere opties controleren en verlaten uitgeschakeld
    • Steekproeven: Selecteren Disable
    • Gegevensset: Selecteer + Nieuw om een nieuwe gegevensset te maken

    The New button is highlighted next to Dataset.

  6. Selecteer Azure Synapse Analytics in het dialoogvenster nieuwe integratiegegevensset en selecteer vervolgens Doorgaan.

    Azure SQL Database and the Continue button are highlighted.

  7. Configureer het volgende in de eigenschappen van de gegevensset:

    • Naam: Enter CustomerSource
    • Gekoppelde service: Selecteer de gekoppelde Synapse-werkruimteservice
    • Tabelnaam: Selecteer de knop Vernieuwen naast de vervolgkeuzelijst

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

  8. Voer in het veld Waarde de naam van uw SQL-pool in en selecteer VERVOLGENS OK.

    The SQLPool01 parameter is highlighted.

  9. Selecteer dbo.CustomerSource onder Tabelnaam, selecteer From connection/store onder Schema importeren en selecteer VERVOLGENS OK om de gegevensset te maken.

    The form is completed as described.

  10. Selecteer Openen naast de CustomerSource gegevensset die u hebt toegevoegd.

    The open button is highlighted next to the new dataset.

  11. Voer de naam van uw SQL-pool in het veld Waarde naast DBName.

  12. Selecteer in de gegevensstroomeditor het vak Bron toevoegen onder de BronDB-activiteit. Configureer deze bron als de tabel DimCustomer volgens dezelfde stappen die worden gebruikt voor CustomerSource.

    • Naam van uitvoerstroom: Enter DimCustomer
    • Brontype: Selecteren Dataset
    • Opties: De Allow schema drift andere opties controleren en verlaten uitgeschakeld
    • Steekproeven: Selecteren Disable
    • Gegevensset: Selecteer + Nieuw om een nieuwe gegevensset te maken. Gebruik de gekoppelde Azure Synapse-service en kies de tabel DimCustomer. Zorg ervoor dat u de DBName instelt op de naam van uw SQL-pool.

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

Transformaties toevoegen aan gegevensstroom

  1. Selecteer + rechts van de SourceDB bron op het canvas en selecteer vervolgens Afgeleide kolom.

    The plus button and derived column menu item are highlighted.

  2. Configureer onder Derived column's settingsde volgende eigenschappen:

    • Naam van uitvoerstroom: Enter CreateCustomerHash
    • Binnenkomende stroom: selecteren SourceDB
    • Kolommen: Voer het volgende in:
    Kolom Expression Beschrijving
    Typen HashKey sha2(256, iifNull(Title,'') +FirstName +iifNull(MiddleName,'') +LastName +iifNull(Suffix,'') +iifNull(CompanyName,'') +iifNull(SalesPerson,'') +iifNull(EmailAddress,'') +iifNull(Phone,'')) Hiermee maakt u een SHA256-hash van de tabelwaarden. We gebruiken dit om rijwijzigingen te detecteren door de hash van de binnenkomende records te vergelijken met de hash-waarde van de doelrecords, die overeenkomen met de CustomerID waarde. De iifNull functie vervangt null-waarden door lege tekenreeksen. Anders worden de hashwaarden vaak gedupliceerd wanneer null-vermeldingen aanwezig zijn.

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

  3. Selecteer + rechts van de CreateCustomerHash afgeleide kolom op het canvas en selecteer Vervolgens Bestaat.

    The plus button and exists menu item are both highlighted.

  4. Configureer onder Exists settingsde volgende eigenschappen:

    • Naam van uitvoerstroom: Enter Exists
    • Linkerstream: Selecteren CreateCustomerHash
    • Rechterstream: Selecteren SynapseDimCustomer
    • Type bestaan: selecterenDoesn't exist
    • Bestaat voorwaarden: Stel het volgende in voor links en rechts:
    Links: De kolom CreateCustomerHash Rechts: Kolom SynapseDimCustomer
    HashKey HashKey

    The Exists settings form is configured as described.

  5. Selecteer + rechts van Exists het canvas en selecteer Opzoeken.

    The plus button and lookup menu item are both highlighted.

  6. Configureer onder Lookup settingsde volgende eigenschappen:

    • Naam van uitvoerstroom: Enter LookupCustomerID
    • Primaire stream: selecteren Exists
    • Opzoekstroom: Selecteren SynapseDimCustomer
    • Overeenkomen met meerdere rijen: Uitgeschakeld
    • Overeenkomst op: selecteren Any row
    • Opzoekvoorwaarden: Stel het volgende in voor links en rechts:
    Links: De kolom bestaat Rechts: Kolom SynapseDimCustomer
    CustomerID CustomerID

    The Lookup settings form is configured as described.

  7. Selecteer + rechts van LookupCustomerID het canvas en selecteer vervolgens Afgeleide kolom.

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

  8. Configureer onder Derived column's settingsde volgende eigenschappen:

    • Naam van uitvoerstroom: Enter SetDates
    • Binnenkomende stroom: selecteren LookupCustomerID
    • Kolommen: Voer het volgende in:
    Kolom Expression Beschrijving
    InsertedDate selecteren iif(isNull(InsertedDate), currentTimestamp(), {InsertedDate}) Als de InsertedDate waarde null is, voegt u de huidige tijdstempel in. Gebruik anders de InsertedDate waarde.
    ModifiedDate selecteren currentTimestamp() Werk de ModifiedDate waarde altijd bij met de huidige tijdstempel.

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

    Notitie

    Als u de tweede kolom wilt invoegen, selecteert u + Toevoegen boven de lijst Kolommen en selecteert u vervolgens Kolom toevoegen.

  9. Selecteer + rechts van de SetDates stap afgeleide kolom op het canvas en selecteer Vervolgens Rij wijzigen.

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

  10. Configureer onder Alter row settingsde volgende eigenschappen:

    • Naam van uitvoerstroom: Enter AllowUpserts
    • Binnenkomende stroom: selecteren SetDates
    • Rijvoorwaarden wijzigen: Voer het volgende in:
    Conditie Expression Beschrijving
    Upsert if selecteren true() Stel de voorwaarde true() in op de Upsert if voorwaarde om upserts toe te staan. Dit zorgt ervoor dat alle gegevens die de stappen in de toewijzingsgegevensstroom doorlopen, worden ingevoegd of bijgewerkt in de sink.

    The alter row settings form is configured as described.

  11. Selecteer + rechts van de AllowUpserts stap Rij wijzigen op het canvas en selecteer Vervolgens Sink.

    The plus button and sink menu item are both highlighted.

  12. Configureer onder Sinkde volgende eigenschappen:

    • Naam van uitvoerstroom: Enter Sink
    • Binnenkomende stroom: selecteren AllowUpserts
    • Sinktype: Selecteren Dataset
    • Gegevensset: selecteren DimCustomer
    • Opties: Controleren Allow schema drift en uitschakelen Validate schema

    The sink properties form is configured as described.

  13. Selecteer het tabblad Instellingen en configureer de volgende eigenschappen:

    • Methode Bijwerken: alle andere opties controleren Allow upsert en uitschakelen
    • Sleutelkolommen: Selecteren List of columnsen vervolgens selecteren CustomerID in de lijst
    • Tabelactie: Selecteren None
    • Fasering inschakelen: uitgeschakeld

    The sink settings are configured as described.

  14. Selecteer het tabblad Toewijzing en schakel automatisch toewijzen uit. Configureer de toewijzing van invoerkolommen zoals hieronder wordt beschreven:

    Invoerkolommen Uitvoerkolommen
    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. De voltooide toewijzingsstroom moet er als volgt uitzien. Selecteer Alles publiceren om uw wijzigingen op te slaan.

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

  16. Selecteer Publiceren.

    The publish button is highlighted.

De gegevensstroom testen

U hebt een SCD-gegevensstroom van type 1 voltooid. Als u ervoor kiest om deze te testen, kunt u deze gegevensstroom toevoegen aan een Synapse-integratiepijplijn. Vervolgens kunt u de pijplijn eenmaal uitvoeren om de initiële belasting van de gegevens van de klantbron naar de DimCustomer-bestemming uit te voeren.

Elke extra uitvoering van de pijplijn vergelijkt de gegevens in de brontabel met wat zich al in de dimensietabel bevindt (met behulp van de HashKey) en alleen records bijwerken die zijn gewijzigd. Als u dit wilt testen, kunt u een record in de brontabel bijwerken en vervolgens de pijplijn opnieuw uitvoeren en de recordupdates in de dimensietabel controleren.

Neem de klant Janet Gates als voorbeeld. De eerste belasting toont de LastName poort en de CustomerId is 4.

The script is displayed with the initial customer record.

Hier volgt een voorbeeldinstructie waarmee de achternaam van de klant in de brontabel wordt bijgewerkt.

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

Na het bijwerken van de record en het uitvoeren van de pijplijn zou DimCustomer deze bijgewerkte gegevens weergeven.

The script is displayed with the updated customer record.

De klantrecord heeft de waarde bijgewerkt zodat deze LastName overeenkomt met de bronrecord en de ModifiedDate, zonder de oude LastName waarde bij te houden. Dat is het verwachte gedrag voor een SCD van type 1. Als de geschiedenis vereist was voor het LastName veld, wijzigt u de tabel en gegevensstroom in een van de andere SCD-typen die u hebt geleerd.