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

| 16 min lesing

I dette neste innlegget i serien tek vi dataa som er transformerte til eit maskinlesbart format i Azure Databricks, og lagrar dei i Azure SQL Database for seinare bruk i rapportering.

Viss du gjekk glipp av det, finn du ei lenkje til del 3 her.

All koden som blir brukt i dette innlegget, er tilgjengeleg på GitHub her.

Opprette ein logisk Azure SQL-server og database

Du kan hoppe over dette steget viss det ikkje er relevant. Elles må vi først opprette ein logisk Azure SQL-server som skal vere vert for databasen, og deretter opprette sjølve databasen.

  1. Opne ein PowerShell Core-terminal i Windows Terminal eller PowerShell Core som administrator, eller med utvida løyve.
  2. Installer Az PowerShell-modulen. Viss han alt er installert, kontrollerer du om det finst oppdateringar. Trykk A og Enter for å stole på repositoriet medan dei nyaste modulane blir lasta ned og installerte. Dette kan ta nokre minutt.
    ##Køyr viss Az PowerShell-modulen ikkje alt er installert
Install-Module Az -AllowClobber

##Køyr viss Az PowerShell-modulen alt er installert, men oppdateringskontroll er nødvendig
Update-Module Az
  3. Importer Az PowerShell-modulen i den gjeldande økta.
    Import-Module Az
  4. Autentiser mot Azure som vist nedanfor. Viss tenant eller subscription er ein annan enn standarden som er knytt til Azure AD-identiteten din, kan du angi dette på same kodelinje med parametrane -Tenant eller -Subscription. For å finne Tenant GUID opnar du Azure Portal, kontrollerer at du er pålogga rett tenant og går til Azure Active Directory. Directory GUID skal visast på startsida. For å finne Subscription GUID byter du til rett tenant i Azure Portal, skriv Subscriptions i søkjefeltet og trykkjer Enter. GUID-ane for alle subscriptions du har tilgang til i den tenant-en, blir viste.
    Connect-AzAccount
  5. La oss angi nokre variablar som skal brukast på nytt gjennom prosessen.
    $resourceGroup = '<same resource group som vi har brukt så langt>'
$serverName = $resourceGroup + '-sql'
$databaseName = 'reportingDatabase'
$location = '<same location som dei andre ressursane er distribuerte til, til dømes uksouth>'
## currentUser trengst for at vi kan logge på med gjeldande brukar 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 frå Azure Data Factory
$keyVault = '<Key Vault-en vi har brukt>'
  6. Deretter må vi angi variabelen som skal brukast for administratorlegitimasjonen.
    ##Du blir beden om å skrive inn eit brukarnamn og passord som blir lagra i denne variabelen som ein secure string

$sqlAdminCredential = Get-Credential
  7. No som alle variablane er angitte, kan vi byrje å 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 no oppretta. Deretter må vi leggje til ein firewall-regel på servernivå med public IP address-variabelen vi angav tidlegare, slik at vi kan kople til frå SQL Server Management Studio (SSMS). Viss du bruker public endpoint i staden for private endpoint, køyrer du den andre kodeblokka for å tillate at Azure Services koplar til SQL-serveren.
    New-AzSqlServerFirewallRule -FirewallRuleName 'AllowMyAccess' -StartIpAddress $publicIpAddress -EndIpAddress $publicIpAddress -ServerName $serverName -ResourceGroupName $resourceGroup

##Køyr berre viss du bruker eit public endpoint for SQL-serveren

New-AzSqlServerFirewallRule -AllowAllAzureServices 'AllowMyAccess' -ServerName $serverName -ResourceGroupName $resourceGroup
  9. Du skal no kunne kople til serveren i SSMS med Azure Active Directory-legitimasjonen (AAD). sqladmin-legitimasjonen vil ikkje fungere i dei seinare stega, fordi du må autentisere mot AAD når service principal-tilgangen for ADF skal konfigurerast.
  10. Gå tilbake til Windows Terminal, så held vi fram med å opprette databasen.
    ##Storleiken som blir vist, er 2 GB. Då dette blei skrive, var dette det minste Azure SQL Database-tilbodet i Basic-serien i DTU-prismodellen
New-AzSqlDatabase -DatabaseName $databaseName -ResourceGroupName $resourceGroup -ServerName $serverName -BackupStorageRedundancy 'Local' -Edition 'Basic' -LicenseType 'BasePrice' -MaxSizeBytes 2147483648
  11. Viss du går tilbake til SSMS Object Explorer og oppdaterer, skal du sjå den nyleg oppretta databasen. Han skal òg visast i resource group-en din i Azure Portal.

Tilordne rette løyve til Data Factory managed identity på SQL Server/databasen

  1. Opne ei ny spørring i konteksten til master-databasen, og køyr følgjande:
    --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. Opne ei ny spørring i konteksten til reportingDatabase-databasen.
    --Opprett databasebrukar for login-en som nettopp blei oppretta, i konteksten til reportingDatabase
CREATE USER [<Name of our data factory>] FROM LOGIN [<Name of our data factory>]

--Legg databasebrukaren 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 å køyre stored procedures som vi treng for å merge data fortløpande 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-økta.
  2. Køyr kommandoen nedanfor 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

Leggje Azure SQL Database til som ein linked service i Azure Data Factory

  1. Opne Data Factory-en du oppretta tidlegare.
  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. Vel Azure SQL Database.
  6. Gi linked service eit namn, til dømes LS_Reporting_SQL.
  7. Endre connection type til Azure Key Vault.
  8. Vel key vault linked service-en du oppretta tidlegare.
  9. Vel secret-en du nettopp oppretta i PowerShell.
  10. Kontroller at Authentication type er sett til Sql Authentication or Managed Identity.
  11. Klikk på Test connection for å kontrollere at Data Factory kan kople til databasen. Viss ikkje, må du kontrollere at rette firewall-reglar på SQL-serveren tillèt tilgang frå Data Factory.
  12. Klikk på Create.

Fylle databasen med dimensjonstabellar og opprette staging-/fact-tabellar

  1. For fact-datasettet vårt finst det nokre dimension-tabellar som hjelper oss å analysere dataa på ulike måtar: DimDate og DimHTTPCode. Her er koden eg bruker for å opprette og fylle desse tabellane.

    --Køyr 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 måndag som første dag i veka
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

--Køyr 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

  3. Viss vi lagra dataa i eit Kimball data warehouse, ville vi sikre at skjemaet følgjer eit star schema. Vi har delvis gjort dette ved å leggje til date- og HTTP code-dimensjonar med foreign key-relasjonar. For å følgje Kimball-metodikken fullt ut, ville vi òg oppretta dimensjonar for andre datapunkt, som User Action Type, Client OS, Client Type og Client City/State/Country. Dette optimaliserer datalagringa: Det tek til dømes langt mindre plass å lagre heiltal som foreign key i fact-tabellen enn ein string-verdi. SQL Server utfører òg joins på heiltalsverdiar betre enn joins på strings. I endelege fact-tabellar ville vi heller ikkje tillate nullable-verdiar.

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

I ADF kunne vi ganske enkelt brukt ei Copy data-oppgåve som overfører dei reinsa dataa frå Databricks direkte til fact-tabellen. Det er likevel stor risiko for duplisering, avhengig av kor ofte ADF-pipelinane blir køyrde og kva tidsperiode som er angitt i Log Analytics-function-en. I dette tilfellet opprettar vi ein stored procedure som vel postar som nyleg er kopierte til staging-tabellen i gjeldande pipeline-køyring. Han joiner deretter datasettet med relevante tabellar og kolonnar – her berre DimHTTPCode – for å hente foreign/surrogate key-verdien som skal brukast i fact-tabellen. Vi gir òg kolonnane nye namn slik at dei samsvarer med det endelege tabellskjemaet og koden blir ryddigare.

```sql
 --Køyr i konteksten til reportingDatabase

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

 --Vel alle nyleg tilførte postar inn i ein mellombels tabell og join mot rette postar i dimension-tabellane ved behov.
 --I dette tilfellet er det berre DimHTTPCode vi hentar ein verdi frå, fordi vi bruker primary/surrogate key-kolonnen frå tabellen i staden
 --for å skrive HTTP Code-verdien eksplisitt kvar gong. Vi joiner ikkje mot DimDate fordi dette er ein 1-til-1-join med same verdi på kvar
 --side av relasjonen. Vi gir òg kolonnane i den mellombelse tabellen nye namn slik at dei samsvarer med skjemaet til den endelege 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

 --No merger vi postar som ikkje allereie finst 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 storleiken på staging-tabellen ved å behalde avgrensa historikk, i dette tilfellet éi veke.

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

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

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

  1. Opne authoring mode i ADF Studio.
  2. Gå til Datasets.
  3. Klikk på plussikonet for å leggje til eit nytt datasett.
  4. Vel 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 meir effektivt enn JSON/CSV.
  6. Gi datasettet namnet DS_Datalake_Parquet.
  7. Klikk på OK.
  8. No parameteriserer vi file path slik at han kan brukast av fleire pipelines.
  9. Gå til fana Parameters.
  10. Opprett ein ny string-parameter kalla container.
  11. Opprett ein ny string-parameter kalla folderPath.
  12. Opprett ein ny string-parameter kalla fileName.
  13. Go back to the Connection tab and assign the container parameter to the File system field with the following dynamic content code
    @dataset().container
  14. Assign the folderPath parameter to the Directory field using the following dynamic content code
    @dataset().folderPath
  15. Assign the fileName parameter to the File field using the following dynamic content code
    @dataset().fileName
  16. Opprett eit nytt parquet-datasett etter same framgangsmåte som over, kalla DS_Datalake_Parquet_Wildcard.
  17. Go to the Parameters tab
  18. Create a new string parameter called container
  19. Create another string parameter called folderPath
  20. Go back to the Connection tab and assign the container parameter to the File system field with the following dynamic content code
    @dataset().container
  21. Assign the folderPath parameter to the Directory field using the following dynamic content code
    @dataset().folderPath
  22. Opprett enda eit datasett.
  23. Vel Azure SQL Database som data store, og klikk på Continue.
  24. Gi det namnet DS_Azure_SQL_Staging_Website_Stats.
  25. Angi tabellnamnet til staging-tabellen du oppretta tidlegare.
  26. Opne pipelinen i ADF.
  27. Vi må angi ein ny pipeline-variabel kalla outputFolderPath.
    • We can set this in the Variables tab at the bottom of the authoring window as before
    • Set the type to be String
  28. Legg til ein ny aktivitet av typen Set variable, og la han køyre etter at Databricks notebook-aktiviteten er fullført. Gi han namnet Set output folder path på fana General.
  29. Verdien vi skal angi er eit uttrykk. Klikk i verditekstfeltet og bruk lenkja Add Dynamic Content.
    @activity('Transform Source Data').output.runOutput
  30. Deretter må vi hente lista over filer i mappa som er angitt av variabelen. Dra ein aktivitet av typen Get Metadata inn i pipelinen, og legg han etter Set output folder path.
  31. Gi aktiviteten namnet Get files in output folder på fana General.
  32. Angi Dataset til DS_Datalke_Parquet_Wildcard på fana Settings.
  33. For parameteren container angir du tekstverdien loganalytics, som er namnet på containeren i data lake-en.
  34. For the folderPath parameter we will pass in the following dynamic content value
    @variables('outputFolderPath')
  35. Under Field list, click New
  36. Choose Child items
  37. Deretter legg vi til ein Filter-aktivitet etter Get files in output folder, slik at berre parquet-filer blir viste. Han ligg under overskrifta Iteration & conditionals.
  38. Name this activity Filter for parquet files
  39. In the I tems field, assign the following dynamic content value
    @activity('Get files in output folder').Output.childItems
  40. In the Condition field, assign the following dynamic content value
    @endswith(item().name,'snappy.parquet')
  41. Nokre gonger er datasets frå Databricks så store at det blir oppretta fleire parquet-filer. For å handtere dette legg vi til ein ForEach-aktivitet under Iteration & conditionals, i staden for éin Copy data-aktivitet. Han skal kome etter Filter for parquet files.
  42. Name this ForEach activity Copy to Staging Table in the General tab
  43. Go the Settings tab and tick the Sequential check box
  44. In the Items field enter the following dynamic content
    @activity('Filter for parquet files').output.Value
  45. Gå til fana Activities, og klikk på blyantikonet.
  46. Dra ein aktivitet av typen Copy data frå overskrifta Move & transform inn på canvaset.
  47. Gi aktiviteten namnet Copy to Azure SQL på fana General.
  48. Angi Source dataset til DS_Datalake_Parquet på fana Source.
  49. Angi den statiske strengen loganalytics for parameteren container.
  50. Angi følgjande dynamic content for parameteren folderPath:
    @variables('outputFolderPath')
  51. Angi følgjande dynamic content for parameteren fileName:
    @item().name
  52. Gå til fana Sink, og angi DS_Azure_SQL_Staging_Website_Stats.
  53. Gå til fana Mapping.
  54. Klikk på Import schemas. Dette føreset at du har køyrt Debug for pipelinen etter at Databricks blei implementert. Viss ikkje, set du eit breakpoint på Databricks-aktiviteten, køyrer Debug for pipelinen og fjernar breakpointet etterpå.
  55. Opne Azure Storage Explorer i nettlesaren eller skrivebordsprogrammet.
  56. Finn parquet-fila frå output-en av Databricks-køyringa, og kopier bana.
  57. Lim inn mappebana i feltet @variables(‘outputFolderPath’), og kontroller at ho sluttar med /.
  58. Lim inn filnamnet i feltet @item().name, og klikk på OK.
  59. Alle kolonnar skal vere tilordna bortsett frå adfPipelineRunId og adfCopyTimestamp.
  60. Kontroller at tilordninga for adfPipelineRunId er sett til kolonnen pipelineRunId.
  61. Fjern tilordninga for adfCopyTimestamp, sidan kolonnen alt har ein standardverdi.
  62. Gå tilbake til hovudpipelinen.
  63. Legg til ein aktivitet av typen Stored procedure frå overskrifta General, som ein etterfølgjar til ForEach-løkka Copy to Staging Table.
  64. Gi aktiviteten namnet Merge staging to fact table på fana General.
  65. Angi Linked Service til DS_Azure_SQL på fana Settings.
  66. Angi stored procedure-en vi oppretta tidlegare, i dette tilfellet sp_MergeWebsiteStatsToLive.
  67. Legg til ein ny stored procedure parameter kalla adfPipelineRunId, eller namnet du angav då stored procedure-en blei oppretta, av typen String.
  68. Angi følgjande dynamic content som verdi:
    @pipeline().RunId
  69. Klikk på Publish øvst i authoring-vindauget for å lagre endringane.
  70. Klikk på Debug.

Oppsummering

Skjermbilete av komplett ADF-pipeline der alle steg er vellukka

Då er vi i mål: Vi har no kopiert dataa til Azure SQL-databasen med ADF, klare for rapportering.

Med det er denne serien om å hente ut data frå Log Analytics avslutta.

Relatert innhald

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 | Byggje spurnaden og hente ut data med Azure Data Factory

Del 2: Rapportering på Log Analytics-data | Byggje spurnaden 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 | Setje opp det første miljøet

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

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

Azure Azure Data Factory Azure Data Lake