Del 4: Rapportering på Log Analytics-data | Få de transformerte Databricks-dataene inn i Azure SQL Database

| 16 min lesing

I dette neste innlegget i serien tar vi dataene som er transformert til et maskinlesbart format i Azure Databricks, og lagrer dem i Azure SQL Database for senere bruk i rapportering.

Hvis du gikk glipp av det, finner du en lenke til del 3 her.

All koden som brukes i dette innlegget, er tilgjengelig på GitHub her.

Opprette en logisk Azure SQL-server og database

Du kan hoppe over dette steget hvis det ikke er relevant. Ellers må vi først opprette en logisk Azure SQL-server som skal være vert for databasen, og deretter opprette selve databasen.

  1. Åpne en PowerShell Core-terminal i Windows Terminal eller PowerShell Core som administrator, eller med utvidede tillatelser.
  2. Installer Az PowerShell-modulen. Hvis den allerede er installert, kontrollerer du om det finnes oppdateringer. Trykk A og Enter for å stole på repositoriet mens de nyeste modulene lastes ned og installeres. Dette kan ta noen minutter.
    ##Kjør hvis Az PowerShell-modulen ikke allerede er installert
Install-Module Az -AllowClobber

##Kjør hvis Az PowerShell-modulen allerede er installert, men oppdateringskontroll er nødvendig
Update-Module Az
  3. Importer Az PowerShell-modulen i den gjeldende økten.
    Import-Module Az
  4. Autentiser mot Azure som vist nedenfor. Hvis tenant eller subscription er en annen enn standarden som er knyttet til Azure AD-identiteten din, kan du angi dette på samme kodelinje med parameterne -Tenant eller -Subscription. For å finne Tenant GUID åpner du Azure Portal, kontrollerer at du er logget på riktig tenant og går til Azure Active Directory. Directory GUID skal vises på startsiden. For å finne Subscription GUID bytter du til riktig tenant i Azure Portal, skriver Subscriptions i søkefeltet og trykker Enter. GUID-ene for alle subscriptions du har tilgang til i den tenant-en, vises.
    Connect-AzAccount
  5. La oss angi noen variabler som skal gjenbrukes gjennom prosessen.
    $resourceGroup = '<samme resource group som vi har brukt så langt>'
$serverName = $resourceGroup + '-sql'
$databaseName = 'reportingDatabase'
$location = '<samme location som de andre ressursene er distribuert til, f.eks. uksouth>'
## currentUser trengs for at vi kan logge på med gjeldende bruker ved Azure Active Directory-autentisering
$currentUser = (Get-AzContext).Account
$publicIpAddress = (Invoke-WebRequest -uri 'https://api.ipify.org/').Content

##Vi må lagre connection string i Key Vault for å få tilgang til SQL-databasen fra Azure Data Factory
$keyVault = '<Key Vault-en vi har brukt>'
  6. Deretter må vi angi variabelen som skal brukes for administratorlegitimasjonen.
    ##Du blir bedt om å skrive inn et brukernavn og passord som lagres i denne variabelen som en secure string

$sqlAdminCredential = Get-Credential
  7. Nå som alle variablene er angitt, kan vi begynne å konfigurere den logiske SQL-serveren.
    New-AzSqlServer -ServerName $serverName -SqlAdministratorCredentials $sqlAdminCredential -Location $location -ServerVersion '12.0' -PublicNetworkAccess 'enabled' -MinimalTlsVersion '1.2' -ExternalAdminName $currentUser -ResourceGroupName $resourceGroup
  8. Serveren er nå opprettet. Deretter må vi legge til en firewall-regel på servernivå med public IP address-variabelen vi angav tidligere, slik at vi kan koble til fra SQL Server Management Studio (SSMS). Hvis du bruker public endpoint i stedet for private endpoint, kjører du den andre kodeblokken for å tillate at Azure Services kobler til SQL-serveren.
    New-AzSqlServerFirewallRule -FirewallRuleName 'AllowMyAccess' -StartIpAddress $publicIpAddress -EndIpAddress $publicIpAddress -ServerName $serverName -ResourceGroupName $resourceGroup

##Kjør bare hvis du bruker et public endpoint for SQL-serveren

New-AzSqlServerFirewallRule -AllowAllAzureServices 'AllowMyAccess' -ServerName $serverName -ResourceGroupName $resourceGroup
  9. Du skal nå kunne koble til serveren i SSMS med Azure Active Directory-legitimasjonen (AAD). sqladmin-legitimasjonen vil ikke fungere i de senere stegene, fordi du må autentisere mot AAD når service principal-tilgangen for ADF skal konfigureres.
  10. Gå tilbake til Windows Terminal, så fortsetter vi med å opprette databasen.
    ##Størrelsen som vises, er 2 GB. Da dette ble skrevet, var dette det minste Azure SQL Database-tilbudet i Basic-serien i DTU-prismodellen
New-AzSqlDatabase -DatabaseName $databaseName -ResourceGroupName $resourceGroup -ServerName $serverName -BackupStorageRedundancy 'Local' -Edition 'Basic' -LicenseType 'BasePrice' -MaxSizeBytes 2147483648
  11. Hvis du går tilbake til SSMS Object Explorer og oppdaterer, skal du se den nylig opprettede databasen. Den skal også vises i resource group-en din i Azure Portal.

Tilordne riktige tillatelser til Data Factory managed identity på SQL Server/databasen

  1. Åpne en ny spørring i konteksten til master-databasen, og kjør følgende:
    --Opprett SQL Server-login for service principal-en til Azure Data Factory-instansen i konteksten til master-databasen

CREATE LOGIN [<Name of our data factory>] FROM EXTERNAL PROVIDER
  2. Åpne en ny spørring i konteksten til reportingDatabase-databasen.
    --Opprett databasebruker for login-en som nettopp ble opprettet, i konteksten til reportingDatabase
CREATE USER [<Name of our data factory>] FROM LOGIN [<Name of our data factory>]

--Legg databasebrukeren til i database-rollene reader og writer
ALTER ROLE [db_datareader] ADD MEMBER [<Name of our data factory>]
ALTER ROLE [db_datawriter] ADD MEMBER [<Name of our data factory>]

--Opprett database-rolle for å kjøre stored procedures som vi trenger for å merge data fortløpende og unngå duplisering
CREATE ROLE [db_spexector]
GRANT EXECUTE TO [db_spexector]
ALTER ROLE [db_spexector] ADD MEMBER [<Name of our data factory>]

Lagre connection string i Azure Key Vault

  1. Gå tilbake til PowerShell-økten.
  2. Kjør kommandoen nedenfor for å lagre connection string i Key Vault-en.
    ##Lagre connection string til SQL-databasen i Azure Key Vault
$connectionString = ConvertTo-SecureString -String ('Data Source=' + $serverName + '.database.windows.net;Initial Catalog=' + $databaseName + ';') -AsPlainText

Set-AzKeyVaultSecret -VaultName $keyVault -Name 'sql-connection-string' -SecretValue $connectionString

Legge Azure SQL Database til som en linked service i Azure Data Factory

  1. Åpne Data Factory-en du opprettet tidligere.
  2. Klikk på ikonet Azure Data Factory Manage-ikon, verktøykasse med skiftenøkkel.
  3. Klikk på bladet Linked services.
  4. Klikk på New.
  5. Velg Azure SQL Database.
  6. Gi linked service et navn, for eksempel LS_Reporting_SQL.
  7. Endre connection type til Azure Key Vault.
  8. Velg key vault linked service-en du opprettet tidligere.
  9. Velg secret-en du nettopp opprettet i PowerShell.
  10. Kontroller at Authentication type er satt til Sql Authentication or Managed Identity.
  11. Klikk på Test connection for å kontrollere at Data Factory kan koble til databasen. Hvis ikke, må du kontrollere at riktige firewall-regler på SQL-serveren tillater tilgang fra Data Factory.
  12. Klikk på Create.

Fylle databasen med dimensjonstabeller og opprette staging-/fact-tabeller

  1. For fact-datasettet vårt finnes det noen dimension-tabeller som hjelper oss å analysere dataene på ulike måter: DimDate og DimHTTPCode. Her er koden jeg bruker for å opprette og fylle disse tabellene.
    --Kjør i konteksten til reportingDatabase
--Opprett DimDate-tabellen

CREATE TABLE [dbo].[DimDate]
(
    [Date_Value] DATE NOT NULL,
    [Day_Number_Month] TINYINT NOT NULL,
    [Day_Number_Week] TINYINT NOT NULL,
    [Week_Number_CY] TINYINT NOT NULL,
    [Month_Number_CY] TINYINT NOT NULL,
    [Quarter_Number_CY] TINYINT NOT NULL,
    [Day_Name_Long] NVARCHAR(9) NOT NULL,
    [Day_Name_Short] NCHAR(3) NOT NULL,
    [Month_Name_Long] NVARCHAR(9) NOT NULL,
    [Month_Name_Short] NCHAR(3) NOT NULL,
    [Quarter_Name_CY] NCHAR(2) NOT NULL,
    [Year_Quarter_Name_CY] NVARCHAR(7) NOT NULL,
    [IsWeekday] BIT NOT NULL,
    [IsWeekend] BIT NOT NULL,

CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED
(
    [Date_Value] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

--Fyll DimDate

DECLARE @StartDate AS DATE
SET @StartDate = '2020-01-01'

DECLARE @EndDate AS DATE
SET @EndDate = '2025-12-31'

DECLARE @LoopDate AS DATE
SET @Loopdate = @StartDate

--Angir mandag som første dag i uken
SET DATEFIRST 1

WHILE @Loopdate < @EndDate
BEGIN
    INSERT INTO [dbo].[DimDate]
    VALUES (
        -- [Date_Value]
        @LoopDate,
        --[Day_Number_Month]
        DATEPART(DAY, @LoopDate),
        -- [Day_Number_Week]
        DATEPART(WEEKDAY, @LoopDate),
        -- [Week_Number_CY]
        DATENAME(WEEK, @LoopDate),
        -- [Month_Number_CY]
        MONTH(@LoopDate),
        --[Quarter_Number_CY]
        DATEPART(QUARTER, @LoopDate),
        -- [Day_Name_Long]
        DATENAME(WEEKDAY, @LoopDate),
        --[Day_Name_Short]
        FORMAT(@LoopDate, 'ddd'),
        -- [Month_Name_Long]
        DATENAME(MONTH, @LoopDate),
        -- [Month_Name_Short]
        FORMAT(@LoopDate, 'MMM'),
        -- [Quarter_Name_CY]
        CONCAT ('Q',DATEPART(QUARTER, @LoopDate)),
        -- [Year_Quarter_Name_CY]
        CONCAT (YEAR(@LoopDate),'-Q',DATEPART(QUARTER, @LoopDate)),
        --[IsWeekday]
        CASE WHEN DATEPART(WEEKDAY, @LoopDate) IN (1,2,3,4,5) THEN 1 ELSE 0 END,
        -- [IsWeekend]
        CASE WHEN DATEPART(WEEKDAY, @LoopDate) IN (6,7) THEN 1 ELSE 0 END
        )
    SET @Loopdate = DATEADD(DAY, 1, @LoopDate)
END

--Kjør i konteksten til reportingDatabase
--Opprett DimHttpCode-tabellen
CREATE TABLE [dbo].[DimHTTPCode] (

    [Http_Code_Key] INT IDENTITY(1,1) NOT NULL,
    [Http_Error_Code] INT NOT NULL,
    [Http_Error_Code_Description] NVARCHAR(150) NOT NULL,

CONSTRAINT [PK_DimHTTPCode] PRIMARY KEY CLUSTERED
(
    [Http_Code_Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

--Fyll DimHttpCode-tabellen
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('100','Continue')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('101','Switching Protocols')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('102','Processing')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('103','Early Hints')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('200','OK')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('201','Created')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('202','Accepted')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('203','Non-Authoritative Information')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('204','No Content')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('205','Reset Content')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('206','Partial Content')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('207','Multi-Status')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('208','Already Reported')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('226','IM Used')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('300','Multiple Choices')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('301','Moved Permanently')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('302','Found')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('303','See Other')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('304','Not Modified')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('305','Use Proxy')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('306','(Unused)')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('307','Temporary Redirect')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('308','Permanent Redirect')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('400','Bad Request')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('401','Unauthorized')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('402','Payment Required')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('403','Forbidden')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('404','Not Found')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('405','Method Not Allowed')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('406','Not Acceptable')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('407','Proxy Authentication Required')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('408','Request Timeout')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('409','Conflict')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('410','Gone')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('411','Length Required')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('412','Precondition Failed')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('413','Content Too Large')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('414','URI Too Long')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('415','Unsupported Media Type')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('416','Range Not Satisfiable')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('417','Expectation Failed')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('418','(Unused)')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('421','Misdirected Request')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('422','Unprocessable Content')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('423','Locked')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('424','Failed Dependency')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('425','Too Early')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('426','Upgrade Required')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('427','Unassigned')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('428','Precondition Required')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('429','Too Many Requests')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('430','Unassigned')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('431','Request Header Fields Too Large')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('451','Unavailable For Legal Reasons')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('500','Internal Server Error')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('501','Not Implemented')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('502','Bad Gateway')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('503','Service Unavailable')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('504','Gateway Timeout')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('505','HTTP Version Not Supported')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('506','Variant Also Negotiates')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('507','Insufficient Storage')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('508','Loop Detected')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('509','Unassigned')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('510','Not Extended (OBSOLETED)')
INSERT INTO [dbo].[DimHTTPCode] ([Http_Error_Code], [Http_Error_Code_Description]) VALUES ('511','Network Authentication Required')
  2. Let’s create the staging and fact tables
    --Run in the context of reportingDatabase

CREATE TABLE [dbo].[staging_factHubnetCloudWebsiteStats](
    [timeGenerated] DATETIME2 NULL,
    [userAction] NVARCHAR(500) NULL,
    [appUrl] NVARCHAR(500) NULL,
    [successFlag] BIT NULL,
    [httpResultCode] INT NULL,
    [durationOfRequestMs] FLOAT NULL,
    [clientType] NVARCHAR(50) NULL,
    [clientOS] NVARCHAR(50) NULL,
    [clientCity] NVARCHAR(50) NULL,
    [clientStateOrProvince] NVARCHAR(50) NULL,
    [clientCountryOrRegion] NVARCHAR(50) NULL,
    [clientBrowser] NVARCHAR(50) NULL,
    [appRoleName] NVARCHAR(50) NULL,
    [requestDate] DATE NULL,
    [requestHour] TINYINT NULL,
    [adfPipelineRunId] NVARCHAR(50) NOT NULL,
    [adfCopyTimestamp] DATETIME2 NOT NULL
)

ALTER TABLE [dbo].[staging_factHubnetCloudWebsiteStats] ADD DEFAULT GETUTCDATE() FOR [adfCopyTimestamp]

--Run in the context of reportingDatabase

CREATE TABLE [dbo].[FactHubnetCloudWebsiteStats](
    [Request_Timestamp_UTC] DATETIME2 NULL,
    [Request_Date_UTC] DATE NULL,
    [Request_Hour_UTC] TINYINT NULL,
    [Request_User_Action] NVARCHAR(500) NULL,
    [Request_App_URL] NVARCHAR(500) NULL,
    [Request_Success_Flag] BIT NULL,
    [Request_HTTP_Code] INT NULL,
    [Request_Duration_Milliseconds] FLOAT NULL,
    [Request_Client_Type] NVARCHAR(50) NULL,
    [Request_Client_OS] NVARCHAR(50) NULL,
    [Request_Client_Browser] NVARCHAR(50) NULL,
    [Request_Client_City] NVARCHAR(50) NULL,
    [Request_Client_State_Or_Province] NVARCHAR(50) NULL,
    [Request_Client_Country_Or_Region] NVARCHAR(50) NULL,
    [Request_App_Role_Name] NVARCHAR(50)
)
GO

ALTER TABLE [dbo].[FactHubnetCloudWebsiteStats] ADD CONSTRAINT [FK_FactHubnetCloudWebsiteStats_DimDate] FOREIGN KEY([Request_Date_UTC])
REFERENCES [dbo].[DimDate] ([Date_Value])
GO

ALTER TABLE [dbo].[FactHubnetCloudWebsiteStats] ADD CONSTRAINT [FK_FactHubnetCloudWebsiteStats_DimHTTPCode] FOREIGN KEY([Request_HTTP_Code])
REFERENCES [dbo].[DimHTTPCode] ([Http_Code_Key])
GO

Hvis vi lagret dataene i et Kimball data warehouse, ville vi sikre at skjemaet følger et star schema. Vi har delvis gjort dette ved å legge til date- og HTTP code-dimensjoner med foreign key-relasjoner. For å følge Kimball-metodikken fullt ut, ville vi også opprettet dimensjoner for andre datapunkter, som User Action Type, Client OS, Client Type og Client City/State/Country. Dette optimaliserer datalagringen: Det tar for eksempel langt mindre plass å lagre heltall som foreign key i fact-tabellen enn en string-verdi. SQL Server utfører også joins på heltallsverdier bedre enn joins på strings. I endelige fact-tabeller ville vi heller ikke tillatt nullable-verdier.

Opprette stored procedure for å merge staging-data inn i fact-tabellen

I ADF kunne vi ganske enkelt brukt en Copy data-oppgave som overfører de rensede dataene fra Databricks direkte til fact-tabellen. Det er imidlertid stor risiko for duplisering, avhengig av hvor ofte ADF-pipelinene kjøres og hvilken tidsperiode som er angitt i Log Analytics-function-en. I dette tilfellet oppretter vi en stored procedure som velger poster som nylig er kopiert til staging-tabellen i gjeldende pipeline-kjøring. Den joiner deretter datasettet med relevante tabeller og kolonner – her bare DimHTTPCode – for å hente foreign/surrogate key-verdien som skal brukes i fact-tabellen. Vi gir også kolonnene nye navn slik at de samsvarer med det endelige tabellskjemaet og koden blir ryddigere.

```sql
 --Kjør i konteksten til reportingDatabase

CREATE PROCEDURE [dbo].[sp_MergeWebsiteStatsToLive]
    @adfPipelineRunId NVARCHAR(36)
AS

 --Velger alle nylig tilføyde poster inn i en midlertidig tabell og joiner mot riktige poster i dimension-tabellene ved behov.
 --I dette tilfellet er det bare DimHTTPCode vi henter en verdi fra, fordi vi bruker primary/surrogate key-kolonnen fra tabellen i stedet
 --for å skrive HTTP Code-verdien eksplisitt hver gang. Vi joiner ikke mot DimDate fordi dette er en 1-til-1-join med samme verdi på hver
 --side av relasjonen. Vi gir også kolonnene i den midlertidige tabellen nye navn slik at de samsvarer med skjemaet til den endelige fact-tabellen.

SELECT
STG.[timeGenerated] AS [Request_Timestamp_UTC],
STG.[requestDate] AS [Request_Date_UTC],
STG.[requestHour] AS [Request_Hour_UTC],
STG.[userAction] AS [Request_User_Action],
STG.[appUrl] AS [Request_App_URL],
STG.[successFlag] AS [Request_Success_Flag],
DHC.[Http_Code_Key] AS [Request_HTTP_Code],
STG.[durationOfRequestMs] AS [Request_Duration_Milliseconds],
STG.[clientType] AS [Request_Client_Type],
STG.[clientOS] AS [Request_Client_OS],
STG.[clientBrowser] AS [Request_Client_Browser],
STG.[clientCity] AS [Request_Client_City],
STG.[clientStateOrProvince] AS [Request_Client_State_Or_Province],
STG.[clientCountryOrRegion] AS [Request_Client_Country_Or_Region],
STG.[appRoleName] AS [Request_App_Role_Name]
INTO #WebsiteStatsNewRecords
FROM [dbo].[staging_factHubnetCloudWebsiteStats] STG
LEFT JOIN [dbo].[DimHTTPCode] DHC ON STG.[httpResultCode] = DHC.[Http_Error_Code]
WHERE STG.[adfPipelineRunId] = @adfPipelineRunId

 --Nå merger vi poster som ikke allerede finnes i fact-tabellen, slik at vi unngår duplisering.

MERGE INTO [dbo].[FactHubnetCloudWebsiteStats] DST
USING #WebsiteStatsNewRecords SRC
ON DST.[Request_Timestamp_UTC] = SRC.[Request_Timestamp_UTC]
AND DST.[Request_Date_UTC] = SRC.[Request_Date_UTC]
AND DST.[Request_Hour_UTC] = SRC.[Request_Hour_UTC]
AND DST.[Request_User_Action] = SRC.[Request_User_Action]
AND DST.[Request_App_URL] = SRC.[Request_App_URL]
AND DST.[Request_Success_Flag] = SRC.[Request_Success_Flag]
AND DST.[Request_HTTP_Code] = SRC.[Request_HTTP_Code]
AND DST.[Request_Duration_Milliseconds] = SRC.[Request_Duration_Milliseconds]
AND DST.[Request_Client_Type] = SRC.[Request_Client_Type]
AND DST.[Request_Client_OS] = SRC.[Request_Client_OS]
AND DST.[Request_Client_Browser] = SRC.[Request_Client_Browser]
AND DST.[Request_Client_City] = SRC.[Request_Client_City]
AND DST.[Request_Client_State_Or_Province] = SRC.[Request_Client_State_Or_Province]
AND DST.[Request_Client_Country_Or_Region] = SRC.[Request_Client_Country_Or_Region]
AND DST.[Request_App_Role_Name] = SRC.[Request_App_Role_Name]
WHEN NOT MATCHED BY TARGET THEN INSERT
(
[Request_Timestamp_UTC],
[Request_Date_UTC],
[Request_Hour_UTC],
[Request_User_Action],
[Request_App_URL],
[Request_Success_Flag],
[Request_HTTP_Code],
[Request_Duration_Milliseconds],
[Request_Client_Type],
[Request_Client_OS],
[Request_Client_Browser],
[Request_Client_City],
[Request_Client_State_Or_Province],
[Request_Client_Country_Or_Region],
[Request_App_Role_Name]
)
VALUES
(
SRC.[Request_Timestamp_UTC],
SRC.[Request_Date_UTC],
SRC.[Request_Hour_UTC],
SRC.[Request_User_Action],
SRC.[Request_App_URL],
SRC.[Request_Success_Flag],
SRC.[Request_HTTP_Code],
SRC.[Request_Duration_Milliseconds],
SRC.[Request_Client_Type],
SRC.[Request_Client_OS],
SRC.[Request_Client_Browser],
SRC.[Request_Client_City],
SRC.[Request_Client_State_Or_Province],
SRC.[Request_Client_Country_Or_Region],
SRC.[Request_App_Role_Name]
);

 --Logikk for å kontrollere størrelsen på staging-tabellen ved å beholde begrenset historikk, i dette tilfellet én uke.

DELETE FROM [dbo].[staging_factHubnetCloudWebsiteStats]
WHERE [adfCopyTimestamp] < DATEADD(WEEK,-1,GETUTCDATE())
```

Implementere den siste copy- og merge-logikken i ADF-pipelinjen

Vi er nesten klare til å implementere logikken i ADF-pipelinen. Dette er siste etappe i denne delen av prosessen. Først må vi opprette noen nye datasets.

  1. Åpne authoring mode i ADF Studio.
  2. Gå til Datasets.
  3. Klikk på plussikonet for å legge til et nytt datasett.
  4. Velg Azure Data Lake Storage Gen 2 som data store, og klikk på Continue.
  5. Angi formatet til Parquet. Vi bruker Parquet fordi det komprimerer datasets langt mer effektivt enn JSON/CSV.
  6. Gi datasettet navnet DS_Datalake_Parquet.
  7. Klikk på OK.
  8. Nå parameteriserer vi file path slik at den kan brukes av flere pipelines.
  9. Gå til fanen Parameters.
  10. Opprett en ny string-parameter kalt container.
  11. Opprett en ny string-parameter kalt folderPath.
  12. Opprett en ny string-parameter kalt fileName.
  13. Gå tilbake til fanen Connection, og tildel parameteren container til feltet File system med følgende dynamic content-kode:
    @dataset().container
  14. Tildel parameteren folderPath til feltet Directory med følgende dynamic content-kode:
    @dataset().folderPath
  15. Tildel parameteren fileName til feltet File med følgende dynamic content-kode:
    @dataset().fileName
  16. Opprett et nytt parquet-datasett etter samme fremgangsmåte som over, kalt DS_Datalake_Parquet_Wildcard.
  17. Gå til fanen Parameters.
  18. Opprett en ny string-parameter kalt container.
  19. Opprett en ny string-parameter kalt folderPath.
  20. Gå tilbake til fanen Connection, og tildel parameteren container til feltet File system med følgende dynamic content-kode:
    @dataset().container
  21. Tildel parameteren folderPath til feltet Directory med følgende dynamic content-kode:
    @dataset().folderPath
  22. Opprett enda et datasett.
  23. Velg Azure SQL Database som data store, og klikk på Continue.
  24. Gi det navnet DS_Azure_SQL_Staging_Website_Stats.
  25. Angi tabellnavnet til staging-tabellen du opprettet tidligere.
  26. Åpne pipelinen i ADF.
  27. Vi må angi en ny pipeline-variabel kalt outputFolderPath.
    • Du angir den på fanen Variables nederst i authoring-vinduet, som tidligere.
    • Angi typen til String.
  28. Legg til en ny aktivitet av typen Set variable, og la den kjøre etter at Databricks notebook-aktiviteten er fullført. Gi den navnet Set output folder path på fanen General.
  29. Verdien vi skal angi er et uttrykk. Klikk i verditekstfeltet og bruk koblingen Add Dynamic Content.
    @activity('Transform Source Data').output.runOutput
  30. Deretter må vi hente listen over filer i mappen som er angitt av variabelen. Dra en aktivitet av typen Get Metadata inn i pipelinen, og legg den etter Set output folder path.
  31. Gi aktiviteten navnet Get files in output folder på fanen General.
  32. Angi Dataset til DS_Datalke_Parquet_Wildcard på fanen Settings.
  33. For parameteren container angir du tekstverdien loganalytics, som er navnet på containeren i data lake-en.
  34. For parameteren folderPath angir du følgende dynamic content-verdi:
    @variables('outputFolderPath')
  35. Klikk på New under Field list.
  36. Velg Child items.
  37. Deretter legger vi til en Filter-aktivitet etter Get files in output folder, slik at bare parquet-filer vises. Den ligger under overskriften Iteration & conditionals.
  38. Gi aktiviteten navnet Filter for parquet files.
  39. Angi følgende dynamic content-verdi i feltet Items:
    @activity('Get files in output folder').Output.childItems
  40. Angi følgende dynamic content-verdi i feltet Condition:
    @endswith(item().name,'snappy.parquet')
  41. Noen ganger er datasets fra Databricks så store at det opprettes flere parquet-filer. For å håndtere dette legger vi til en ForEach-aktivitet under Iteration & conditionals, i stedet for én Copy data-aktivitet. Den skal komme etter Filter for parquet files.
  42. Gi ForEach-aktiviteten navnet Copy to Staging Table på fanen General.
  43. Gå til fanen Settings, og merk av for Sequential.
  44. Angi følgende dynamic content i feltet Items:
    @activity('Filter for parquet files').output.Value
  45. Gå til fanen Activities, og klikk på blyantikonet.
  46. Dra en aktivitet av typen Copy data fra overskriften Move & transform inn på canvaset.
  47. Gi aktiviteten navnet Copy to Azure SQL på fanen General.
  48. Angi Source dataset til DS_Datalake_Parquet på fanen Source.
  49. Angi den statiske strengen loganalytics for parameteren container.
  50. Angi følgende dynamic content for parameteren folderPath:
    @variables('outputFolderPath')
  51. Angi følgende dynamic content for parameteren fileName:
    @item().name
  52. Gå til fanen Sink, og angi DS_Azure_SQL_Staging_Website_Stats.
  53. Gå til fanen Mapping.
  54. Klikk på Import schemas. Dette forutsetter at du har kjørt Debug for pipelinen etter at Databricks ble implementert. Hvis ikke, setter du et breakpoint på Databricks-aktiviteten, kjører Debug for pipelinen og fjerner breakpointet etterpå.
  55. Åpne Azure Storage Explorer i nettleseren eller skrivebordsprogrammet.
  56. Finn parquet-filen fra output-en av Databricks-kjøringen, og kopier banen.
  57. Lim inn mappebanen i feltet @variables(‘outputFolderPath’), og kontroller at den avsluttes med /.
  58. Lim inn filnavnet i feltet @item().name, og klikk på OK.
  59. Alle kolonner skal være tilordnet bortsett fra adfPipelineRunId og adfCopyTimestamp.
  60. Kontroller at tilordningen for adfPipelineRunId er satt til kolonnen pipelineRunId.
  61. Fjern tilordningen for adfCopyTimestamp, siden kolonnen allerede har en standardverdi.
  62. Gå tilbake til hovedpipelinen.
  63. Legg til en aktivitet av typen Stored procedure fra overskriften General, som en etterfølger til ForEach-løkken Copy to Staging Table.
  64. Gi aktiviteten navnet Merge staging to fact table på fanen General.
  65. Angi Linked Service til DS_Azure_SQL på fanen Settings.
  66. Angi stored procedure-en vi opprettet tidligere, i dette tilfellet sp_MergeWebsiteStatsToLive.
  67. Legg til en ny stored procedure parameter kalt adfPipelineRunId, eller navnet du angav da stored procedure-en ble opprettet, av typen String.
  68. Angi følgende dynamic content som verdi:
    @pipeline().RunId
  69. Klikk på Publish øverst i authoring-vinduet for å lagre endringene.
  70. Klikk på Debug.

Oppsummering

Skjermbilde av komplett ADF-pipeline der alle steg er vellykkede

Da er vi i mål: Vi har nå kopiert dataene til Azure SQL-databasen med ADF, klare for rapportering.

Med det er denne serien om å hente ut data fra Log Analytics avsluttet.

Relatert innhold

Del 3: Rapportering på Log Analytics-data | Behandle data med Azure Databricks

Del 3: Rapportering på Log Analytics-data | Behandle data med Azure Databricks

Del 3 av serien om rapportering på Log Analytics-data.

Azure Azure Data Factory Azure Databricks
Del 2: Rapportering på Log Analytics-data | Bygge spørringen og hente ut data med Azure Data Factory

Del 2: Rapportering på Log Analytics-data | Bygge spørringen og hente ut data med Azure Data Factory

Del 2 av serien om rapportering på Log Analytics-data.

Azure Data Factory Azure Data Lake Data Engineering
Del 1: Rapportering på Log Analytics-data | Sette opp det første miljøet

Del 1: Rapportering på Log Analytics-data | Sette opp det første miljøet

Del 1 av serien om rapportering på Log Analytics-data.

Azure Azure Data Factory Azure Data Lake