Å lage mitt eget eksempeldatasett med Visual Studio, T-SQL og PowerShell

Du har hørt om AdventureWorks, ikke sant? Det er eksempeldatabasen for SQL Server som Microsoft har publisert, og som også er tilgjengelig som eksempel for Azure SQL Database. For noen måneder siden fikk jeg ideen om å lage mitt eget eksempeldatasett som jeg kunne bruke i kommende prosjekter. Det tok litt lengre tid enn planlagt å få ferdig – livet kom i veien – men nå er det endelig klart.

Dette blogginnlegget gir en overordnet gjennomgang av hvordan det ble laget. For mer teknisk informasjon og kildekode, se GitHub-repositoriet mitt.

La oss først se på caset bak datasettet: WingIt Airlines. WingIt Airlines er et fiktivt, mellomstort langdistanseflyselskap som flyr mellom basene sine i San Diego i California og London Heathrow i Storbritannia. Hovedkontoret ligger i Los Angeles i California. Målet er å lage en rapporteringsdatabase som kan gi både interne og eksterne interessenter bedre innsikt i hvordan flyselskapet presterer. For enkelhets skyld ser databaseskjemaet omtrent slik ut:

Med SQL Database Projects i Visual Studio bygde jeg skjemaet over som en DACPAC-løsning. Den kan lastes ned fra repository-lenken øverst på siden. Et skjema alene er imidlertid ikke nok; for å gjøre databasen mer realistisk må constraints som foreign keys, check constraints og default values håndheves. Alt dette ligger i DACPAC-en. Jeg har også skrevet scalar functions som blant annet beregner provisjonen et reisebyrå får per bestilling, og kontrollerer at det er nok kapasitet før transaksjonen commits til databasen. Arbeidet var stort sett rett fram, og ga meg nyttig erfaring med scalar functions og ulike måter å beregne verdier i kolonner på. I Airport-tabellen bruker jeg dessuten datatypen GEOGRAPHY til å lagre den nøyaktige plasseringen til hver flyplass, basert på lengde- og breddegrad, slik at flydistanser kan beregnes. Kildekoden finner du her.

Skjemaet er på plass, og nå må det fylles med data. Vi kunne ha brukt T-SQL-skript som kjøres manuelt i et verktøy som SQL Server Management Studio (SSMS), men en av utfordringene jeg satte meg i dette prosjektet var å automatisere genereringen av datasettet så mye som mulig.

Hvordan gjorde jeg det? Jeg laget fortsatt de samme T-SQL-skriptene, men lar dem nå kjøre via PowerShell med SqlServer-modulen installert. Du må kanskje også installere NuGet package provider dersom den ikke allerede er tilgjengelig.

Du finner modulen i PowerShell Gallery:

Install-Module SqlServer

Du må også opprette en SQLAuth-bruker med riktige rettigheter i databasen. Brukeren skal være medlem av db_spexecutor, db_datareader og db_datawriter. Skriptene forutsetter dessuten at SQL-instansen er standardinstansen, altså MSSQLSERVER. Bruker du en navngitt instans, må du tilpasse verdien som sendes til parameteren -ServerInstance.

Først kjører vi skriptet som fyller oppslagsdataene:

Set-Location '<path of cloned repository>\sample-datasets\WingItAirlines-Reporting\Data Population Scripts'
.\PopulateLookupData.ps1

Du blir bedt om brukernavn og passord for SQL Authentication-brukeren. Deretter fyller skriptet følgende tabeller. Du kan endre datointervallet i FlightSchedule-skriptet for å lage et mindre eller større datasett.

Agency
AgencyUser
AgencyCommission
Airplane
Airport
PassengerFareRate
TicketStatus
TicketType
Route
FlightSchedule

Dette bør være ferdig i løpet av et par minutter.

Deretter fyller vi faktadataene med:

.\CreateBulkBookings.ps1

Du blir bedt om brukernavn og passord for SQL Authentication-brukeren, og skriptet fyller deretter TicketSale-tabellen.

La oss se på hva skriptet gjør:

$databaseName = 'WingItAirlines-Reporting'

# Dette avhenger vanligvis av hvor mye regnekraft vertsmaskinen har
$maxConcurrentJobs = 2
$sqlServerCredential = Get-Credential

# Hent ID-en til reisebyråbrukeren som selger billetter på business class
$businessClassAgencyUser = `
Invoke-Sqlcmd `
-Credential $sqlServerCredential `
-Database $databaseName `
-Query "SELECT TOP 1`
AU.[Agency_User_Id] `
FROM [dbo].[AgencyUser] AU  `
INNER JOIN [dbo].[Agency] A ON AU.[Agency_Id] = A.[Agency_Id] `
WHERE A.[Agency_Name] = 'Christopher Columbus' "`
-ServerInstance $env:COMPUTERNAME

$businessClassAgencyUser = $businessClassAgencyUser.Agency_User_Id

# Hent ID-en til billettypen for business class
$businessClassTicketType = `
Invoke-Sqlcmd `
-Credential $sqlServerCredential `
-Database $databaseName `
-Query "SELECT `
[Ticket_Type_Id] `
FROM [dbo].[TicketType]  `
WHERE [Ticket_Type] = 'Business Class' "`
-ServerInstance $env:COMPUTERNAME

$businessClassTicketType = $businessClassTicketType.Ticket_Type_Id

# Hent ID-en til reisebyråbrukeren som selger billetter på economy class
$economyClassAgencyUser = `
Invoke-Sqlcmd `
-Credential $sqlServerCredential `
-Database $databaseName `
-Query "SELECT TOP 1`
AU.[Agency_User_Id] `
FROM [dbo].[AgencyUser] AU  `
INNER JOIN [dbo].[Agency] A ON AU.[Agency_Id] = A.[Agency_Id] `
WHERE A.[Agency_Name] = 'Sunchasers Ltd' "`
-ServerInstance $env:COMPUTERNAME

$economyClassAgencyUser = $economyClassAgencyUser.Agency_User_Id

# Hent ID-en til billettypen for economy class
$economyClassTicketType = `
Invoke-Sqlcmd `
-Credential $sqlServerCredential `
-Database $databaseName `
-Query "SELECT `
[Ticket_Type_Id] `
FROM [dbo].[TicketType]  `
WHERE [Ticket_Type] = 'Economy Class' "`
-ServerInstance $env:COMPUTERNAME

$economyClassTicketType = $economyClassTicketType.Ticket_Type_Id

# Hent alle flyvninger uten bestillinger i TicketSales. Denne funksjonen gjør at du kan fortsette der du slapp, uten å slette alt og begynne på nytt
$flightSchedule = `
Invoke-Sqlcmd `
-Credential $sqlServerCredential `
-Database $databaseName `
-OutputAs DataTables `
-Query "SELECT `
[Flight_Schedule_Id] `
FROM [dbo].[FlightSchedule] `
WHERE NOT EXISTS ( `
SELECT `
[Flight_Schedule_Id] `
FROM [dbo].[TicketSale])" `
-ServerInstance $env:COMPUTERNAME

$flightSchedule = $flightSchedule.Flight_Schedule_Id

# Hent ID-en til reisebyråbrukeren som selger billetter på premium economy class
$premiumEconomyClassAgencyUser = `
Invoke-Sqlcmd `
-Credential $sqlServerCredential `
-Database $databaseName `
-Query "SELECT TOP 1`
AU.[Agency_User_Id] `
FROM [dbo].[AgencyUser] AU  `
INNER JOIN [dbo].[Agency] A ON AU.[Agency_Id] = A.[Agency_Id] `
WHERE A.[Agency_Name] = 'Suntours Vacaction LLC' "`
-ServerInstance $env:COMPUTERNAME

$premiumEconomyClassAgencyUser = $premiumEconomyClassAgencyUser.Agency_User_Id

# Hent ID-en til billettypen for premium economy class
$premiumEconomyClassTicketType = `
Invoke-Sqlcmd `
-Credential $sqlServerCredential `
-Database $databaseName `
-Query "SELECT `
[Ticket_Type_Id] `
FROM [dbo].[TicketType]  `
WHERE [Ticket_Type] = 'Premium Economy Class' "`
-ServerInstance $env:COMPUTERNAME

$premiumEconomyClassTicketType = $premiumEconomyClassTicketType.Ticket_Type_Id

# For hver flyvning i FlightSchedule-listen som ikke allerede har bestillinger
foreach($flight in $flightSchedule)
{
    # Kontroller om det kjører noen bakgrunnsjobber
    $running = @(Get-Job -State Running)
    {
        # Kontroller om grensen for maksimalt antall bakgrunnsjobber er nådd; vent i så fall til en plass blir ledig
        if($running.Count -ge $maxConcurrentJobs)
        {
            $null = $running | Wait-Job Any
        }
            # Start en bakgrunnsjobb som oppretter bestillinger for flyvningen med verdier fra tidligere spørringer
            Start-Job -Name CreateBulkBookings -ArgumentList $businessClassAgencyUser, $businessClassTicketType, $databaseName, $economyClassAgencyUser, $economyClassTicketType, $flight, $premiumEconomyClassAgencyUser, $premiumEconomyClassTicketType, $sqlServerCredential -ScriptBlock {
                param
                (
                    [int]$businessClassAgencyUser,
                    [int]$businessClassTicketType,
                    [string]$databaseName,
                    [int]$economyClassAgencyUser,
                    [int]$economyClassTicketType,
                    [int]$flight,
                    [int]$premiumEconomyClassAgencyUser,
                    [int]$premiumEconomyClassTicketType,
                    [pscredential]$sqlServerCredential
                )

                # Definer en funksjon som kan hente tilfeldige datoer mellom to angitte datoer
                function Get-RandomDateBetween{
                    [Cmdletbinding()]
                    param(
                        [parameter(Mandatory=$True)][DateTime]$StartDate,
                        [parameter(Mandatory=$True)][DateTime]$EndDate
                        )

                    process{
                       return Get-Random -Minimum $StartDate.Ticks -Maximum $EndDate.Ticks | Get-Date -Format "dd/MM/yyyy HH:mm:ss"
                    }
                }

                # Definer en funksjon som kan hente tilfeldige klokkeslett mellom to angitte tidspunkter
                function Get-RandomTimeBetween{
                       [Cmdletbinding()]
                      param(
                          [parameter(Mandatory=$True)][string]$StartTime,
                          [parameter(Mandatory=$True)][string]$EndTime
                          )
                      begin{
                          $minuteTimeArray = @("00","15","30","45")
                      }
                      process{
                          $rangeHours = @($StartTime.Split(":")[0],$EndTime.Split(":")[0])
                          $hourTime = Get-Random -Minimum $rangeHours[0] -Maximum $rangeHours[1]
                          $minuteTime = "00"
                          if($hourTime -ne $rangeHours[0] -and $hourTime -ne $rangeHours[1]){
                              $minuteTime = Get-Random $minuteTimeArray
                              return "${hourTime}:${minuteTime}"
                          }
                          elseif ($hourTime -eq $rangeHours[0]) { # hour is the same as the start time so we ensure the minute time is higher
                              $minuteTime = $minuteTimeArray | ?{ [int]$_ -ge [int]$StartTime.Split(":")[1] } | Get-Random # Pick the next quarter
                              # Hvis ingen hele kvarter er tilgjengelige (f.eks. 09:50), går vi videre til neste time (10:00)
                              return (.{If(-not $minuteTime){ "${[int]hourTime+1}:00" }else{ "${hourTime}:${minuteTime}" }})

                          }else { # hour is the same as the end time
                              # Ved å sortere arrayet velges 00 hvis det ikke finnes et nærliggende hele kvarter
                              $minuteTime = $minuteTimeArray | Sort-Object -Descending | ?{ [int]$_ -le [int]$EndTime.Split(":")[1] } | Get-Random
                              return "${hourTime}:${minuteTime}"
                          }
                      }
                  }

                # Hent flyet som er tildelt flyvningen; dette trengs for å kontrollere om bestillingen kan gjennomføres basert på kapasiteten i allerede solgte billetter
                $airplaneFlight = `
                Invoke-SqlCmd `
                -Credential $sqlServerCredential `
                -Database $databaseName `
                -Query "SELECT `
                [Airplane_Id] `
                FROM [dbo].[FlightSchedule] `
                WHERE [Flight_Schedule_Id] = $flight" `
                -ServerInstance $env:COMPUTERNAME

                $airplaneFlight = $airplaneFlight.Airplane_Id

                # Hent dato og klokkeslett for planlagt avgang
                $dateTimeFlight = `
                Invoke-SqlCmd `
                -Credential $sqlServerCredential `
                -Database $databaseName `
                -Query "SELECT `
                [Scheduled_Date_Time_Of_Departure_UTC] `
                FROM [dbo].[FlightSchedule] `
                WHERE [Flight_Schedule_Id] = $flight" `
                -ServerInstance $env:COMPUTERNAME

                $dateTimeFlight = $dateTimeFlight.Scheduled_Date_Time_Of_Departure_UTC

                # Hent ruten for flyvningen
                $routeToBook = `
                Invoke-SqlCmd `
                -Credential $sqlServerCredential `
                -Database $databaseName `
                -Query "SELECT `
                [Route_Id] `
                FROM [dbo].[FlightSchedule] `
                WHERE [Flight_Schedule_Id] = $flight" `
                -ServerInstance $env:COMPUTERNAME

                $routeToBook = $routeToBook.Route_Id

                # Sørg for at minst 65 % av setene på business class for flyvningen er solgt
                $businessClassBookingsCapacityUtilisation = Get-Random -Minimum 0.65 -Maximum 1
                $businessClassBookingsCapacityUtilisationRounded = [Math]::Round($businessClassBookingsCapacityUtilisation, 2)

                # Beregn hvor mange bestillinger på business class som skal opprettes, basert på den forrige verdien
                $businessClassBookingsToCreate = `
                Invoke-SqlCmd `
                -Credential $sqlServerCredential `
                -Database $databaseName `
                -Query "SELECT `
                CAST(ROUND([Business_Class_Seat_Count] * $businessClassBookingsCapacityUtilisationRounded, 0) AS INT) AS [Bookings_To_Create] `
                FROM [dbo].[Airplane] `
                WHERE [Airplane_Id] = $airplaneFlight" `
                -ServerInstance $env:COMPUTERNAME

                $businessClassBookingsToCreate = $businessClassBookingsToCreate.Bookings_To_Create

                # Sørg for at minst 65 % av setene på economy class for flyvningen er solgt
                $economyClassBookingsCapacityUtilisation = Get-Random -Minimum 0.65 -Maximum 1
                $economyClassBookingsCapacityUtilisationRounded = [Math]::Round($economyClassBookingsCapacityUtilisation, 2)

                # Beregn hvor mange bestillinger på economy class som skal opprettes, basert på den forrige verdien
                $economyClassBookingsToCreate = `
                Invoke-SqlCmd `
                -Credential $sqlServerCredential `
                -Database $databaseName `
                -Query "SELECT `
                CAST(ROUND([Economy_Class_Seat_Count] * $economyClassBookingsCapacityUtilisationRounded, 0) AS INT) AS [Bookings_To_Create] `
                FROM [dbo].[Airplane] `
                WHERE [Airplane_Id] = $airplaneFlight" `
                -ServerInstance $env:COMPUTERNAME

                $economyClassBookingsToCreate = $economyClassBookingsToCreate.Bookings_To_Create

                # Sørg for at minst 65 % av setene på premium economy class for flyvningen er solgt
                $premiumEconomyClassBookingsCapacityUtilisation = Get-Random -Minimum 0.65 -Maximum 1
                $premiumEconomyClassBookingsCapacityUtilisationRounded = [Math]::Round($premiumEconomyClassBookingsCapacityUtilisation, 2)

                # Beregn hvor mange bestillinger på premium economy class som skal opprettes, basert på den forrige verdien
                $premiumEconomyClassBookingsToCreate = `
                Invoke-SqlCmd `
                -Credential $sqlServerCredential `
                -Database $databaseName `
                -Query "SELECT `
                CAST(ROUND([Premium_Economy_Class_Seat_Count] * $premiumEconomyClassBookingsCapacityUtilisationRounded, 0) AS INT) AS [Bookings_To_Create] `
                FROM [dbo].[Airplane] `
                WHERE [Airplane_Id] = $airplaneFlight" `
                -ServerInstance $env:COMPUTERNAME

                $premiumEconomyClassBookingsToCreate = $premiumEconomyClassBookingsToCreate.Bookings_To_Create

                # Opprett bestillinger på business class
                while($businessClassBookingsToCreate -gt 0)
                {
                    # En bestilling kan opprettes opptil 272 dager før flyvningen og senest én dag før, med funksjonen Get-RandomDateBetween som ble definert over
                    $dateOfBooking = Get-RandomDateBetween -StartDate $dateTimeFlight.AddDays(-272) -EndDate $dateTimeFlight.AddDays(-1)
                    $dateOfBooking = [Datetime]::ParseExact($dateOfBooking, 'dd/MM/yyyy HH:mm:ss', $null)

                    # En bestilling kan bare opprettes i arbeidstiden. Det er ikke helt realistisk, men jeg ville ikke at dataene skulle bli for tilfeldige
                    $timeOfBooking = Get-RandomTimeBetween -StartTime "08:00" -EndTime "18:00"
                    $timeOfBooking = [System.Timespan]::Parse($timeOfBooking)

                    [datetime]$dateTimeTicketSale = $dateOfBooking.Add($timeOfBooking)

                    Invoke-SqlCmd `
                    -Credential $sqlServerCredential `
                    -Database $databaseName `
                    -Query " `
                    DECLARE @ticketSaleDateTime DATETIME2 `
                    SET @ticketSaleDateTime = (SELECT CAST('$dateTimeTicketSale' AS DATETIME2)) `
                    DECLARE @travelDateTime DATETIME2 `
                    SET @travelDateTime = (SELECT CAST('$dateTimeFlight' AS DATETIME2)) `
                    `
                    EXEC [dbo].[CreateBooking] @agencyUserId = $businessClassAgencyUser, `
                    @routeId = $routeToBook, `
                    @ticketSaleDateTime = @ticketSaleDateTime, `
                    @ticketTypeId = $businessClassTicketType, `
                    @travelDateTime = @travelDateTime" `
                    -ServerInstance $env:COMPUTERNAME

                    $businessClassBookingsToCreate = $businessClassBookingsToCreate - 1
                }

                # Opprett bestillinger på economy class
                while($economyClassBookingsToCreate -gt 0)
                {
                    # En bestilling kan opprettes opptil 272 dager før flyvningen og senest én dag før, med funksjonen Get-RandomDateBetween som ble definert over
                    $dateOfBooking = Get-RandomDateBetween -StartDate $dateTimeFlight.AddDays(-272) -EndDate $dateTimeFlight.AddDays(-1)
                    $dateOfBooking = [Datetime]::ParseExact($dateOfBooking, 'dd/MM/yyyy HH:mm:ss', $null)

                    # En bestilling kan bare opprettes i arbeidstiden. Det er ikke helt realistisk, men jeg ville ikke at dataene skulle bli for tilfeldige
                    $timeOfBooking = Get-RandomTimeBetween -StartTime "08:00" -EndTime "18:00"
                    $timeOfBooking = [System.Timespan]::Parse($timeOfBooking)

                    [datetime]$dateTimeTicketSale = $dateOfBooking.Add($timeOfBooking)

                    Invoke-SqlCmd `
                    -Credential $sqlServerCredential `
                    -Database $databaseName `
                    -Query " `
                    DECLARE @ticketSaleDateTime DATETIME2 `
                    SET @ticketSaleDateTime = (SELECT CAST('$dateTimeTicketSale' AS DATETIME2)) `
                    DECLARE @travelDateTime DATETIME2 `
                    SET @travelDateTime = (SELECT CAST('$dateTimeFlight' AS DATETIME2)) `
                    `
                    EXEC [dbo].[CreateBooking] @agencyUserId = $economyClassAgencyUser, `
                    @routeId = $routeToBook, `
                    @ticketSaleDateTime = @ticketSaleDateTime, `
                    @ticketTypeId = $economyClassTicketType, `
                    @travelDateTime = @travelDateTime" `
                    -ServerInstance $env:COMPUTERNAME

                    $economyClassBookingsToCreate = $economyClassBookingsToCreate -1
                }

                # Opprett bestillinger på premium economy class
                while($premiumEconomyClassBookingsToCreate -gt 0)
                {
                    # En bestilling kan opprettes opptil 272 dager før flyvningen og senest én dag før, med funksjonen Get-RandomDateBetween som ble definert over
                    $dateOfBooking = Get-RandomDateBetween -StartDate $dateTimeFlight.AddDays(-272) -EndDate $dateTimeFlight.AddDays(-1)
                    $dateOfBooking = [Datetime]::ParseExact($dateOfBooking, 'dd/MM/yyyy HH:mm:ss', $null)

                    # En bestilling kan bare opprettes i arbeidstiden. Det er ikke helt realistisk, men jeg ville ikke at dataene skulle bli for tilfeldige
                    $timeOfBooking = Get-RandomTimeBetween -StartTime "08:00" -EndTime "18:00"
                    $timeOfBooking = [System.Timespan]::Parse($timeOfBooking)

                    [datetime]$dateTimeTicketSale = $dateOfBooking.Add($timeOfBooking)

                    Invoke-SqlCmd `
                    -Credential $sqlServerCredential `
                    -Database $databaseName `
                    -Query " `
                    DECLARE @ticketSaleDateTime DATETIME2 `
                    SET @ticketSaleDateTime = (SELECT CAST('$dateTimeTicketSale' AS DATETIME2)) `
                    DECLARE @travelDateTime DATETIME2 `
                    SET @travelDateTime = (SELECT CAST('$dateTimeFlight' AS DATETIME2)) `
                    `
                    EXEC [dbo].[CreateBooking] @agencyUserId = $premiumEconomyClassAgencyUser, `
                    @routeId = $routeToBook, `
                    @ticketSaleDateTime = @ticketSaleDateTime, `
                    @ticketTypeId = $premiumEconomyClassTicketType, `
                    @travelDateTime = @travelDateTime" `
                    -ServerInstance $env:COMPUTERNAME

                    $premiumEconomyClassBookingsToCreate = $premiumEconomyClassBookingsToCreate -1
                }
        }
    }
}

# Vent til alle bakgrunnsjobber er ferdige før skriptet avsluttes
Get-Job | Wait-Job

Full ansvarsfraskrivelse: Dette er sannsynligvis en svært lite effektiv måte å generere denne mengden data på. For bedre ytelse burde det trolig kjøres i et flertrådet verktøy som Azure Databricks.

Jeg lot dette kjøre på en Azure VM i et par måneder. Opprinnelig brukte jeg ti samtidige jobber, men det gikk for sakte, så jeg reduserte antallet.

Ressurser

Hvis du ikke vil følge alle stegene og vente på at et større datasett skal genereres, har jeg publisert en databasebackup som kan tas i bruk med én gang. Du kan laste den ned her. Merk at du må bruke SQL Server 2022, siden backupen ble opprettet på en SQL Server 2022-instans.

GitHub repository

Takk

Takk til @emyann på GitHub for Gist-startkoden for å generere tilfeldige datoer og klokkeslett i PowerShell. Jeg har tilpasset den i koden som oppretter bulkbestillinger.

Takk til @farkoo på GitHub for eksempel-koden for å kontrollere kapasiteten på idrettsarenaer. Den ga inspirasjon til logikken for flykapasitet.

Å lage mitt eget eksempeldatasett med Visual Studio, T-SQL og PowerShell

Ressurser

Takk

Relatert innhold

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