Automating Real-Time Intelligence Eventhouse deployment using PowerShell

[sujosyul]

Overview

Real-Time Intelligence in Microsoft Fabric lets users connect to host of Microsoft, and cross cloud streaming sources along with CDC from databases. It provides capability to create an end to end streaming architecture with ease and caters to all personas in an organization.

Eventhouses in Fabric Real-Time Intelligence provide a solution for handling and analyzing your big data regardless of how large the data volume is - Gigabytes, Terabytes or Petabytes. Eventhouse provides log, timeseries, geospatial, sequence, graph or vector analysis and search capabilities using Kusto Query Language (KQL). Often, when moving from one environment to another, we need to rely on an automated system to move our artifacts seamlessly with less manual effort and less errors.

Let's build a PowerShell script to automate the deployment of Eventhouse, KQL Database, Tables, Functions and Materialized Views into a workspace in Microsoft Fabric. Once end to end script is ready, we can then simply call the script using parameters.

The following article uses APIs that are available for Eventhouse entities - Items - REST API (Eventhouse) | Microsoft Learn.

Before executing any PowerShell script, please make sure that you have Azure Modules installed.

Install-Module Az

Step 1: To begin, log into Fabric

$tenantId = "1234-11111-2222-123141"
Connect-AzAccount -TenantId $tenantId | Out-Null

Step 2: Get the token to authenticate to Fabric

$baseFabricUrl = "https://api.fabric.microsoft.com"
# Get authentication token
    $fabricToken = (Get-AzAccessToken -ResourceUrl $baseFabricUrl).Token
# Setup headers for API call
    $headerParams = @{'Authorization'="Bearer {0}" -f $fabricToken}
    $contentType = @{'Content-Type' = "application/json"}

Step 3: Create Eventhouse

$eventhouseName = "EH_PSTest11"

# Create body of request
$body = @{
'displayName' = $eventhouseName
} | ConvertTo-Json -Depth 1

# Create Eventhouse API
$eventhouseAPI = "https://api.fabric.microsoft.com/v1/workspaces/$workspaceId/eventhouses"

# Call Eventhouse create API
$eventhouseCreate = Invoke-RestMethod -Headers $headerParams -Method POST -Uri $eventhouseAPI -Body ($body) -ContentType "application/json"
$eventhouseId= ($eventhouseCreate.id).ToString()

Step 4: Create KQL Database in above Eventhouse

$kqlDBName = "KQLDB_PSTest11"

# Create body of request
$body = @{       
            'displayName' = $kqlDBName;
            'creationPayload'= @{
            'databaseType' = "ReadWrite";
            'parentEventhouseItemId' = $eventhouseId}
             } | ConvertTo-Json -Depth 2

# Create KQLDB API
$kqlDBAPI = "https://api.fabric.microsoft.com/v1/workspaces/$workspaceId/kqlDatabases"

# Call KQL DB create API
Invoke-RestMethod -Headers $headerParams -Method GET -Uri $kqlDBAPI -ContentType "application/json" -verbose

Step 5: Create Database entities using a script file

The following code extracts script for a .csl or .kql or .txt file kept locally in the same folder of this PowerShell script and runs the creation of all database entities like Tables, Functions and Materialized Views

# Function to get current directory
function Get-ScriptDirectory {
if ($psise) {
			Split-Path $psise.CurrentFile.FullPath
			}
else {
		$PSScriptRoot
		}
}

# Read script file contents
$DBScriptPath=Get-ScriptDirectory
$DBScriptloc = (Join-Path $DBScriptPath $dbScriptName)
$DBScript=Get-content -Path $DBScriptloc

Authenticating to KQL DB data plane to avoid 401 unauthorized error

# Create Kusto Url to authenticate
$kustoUrl = "https://api.kusto.windows.net"
$kustoToken = (Get-AzAccessToken -ResourceUrl $kustoUrl).Token
$headerParams = @{'Authorization'="Bearer {0}" -f $kustoToken}

# Create Kusto API
$queryAPI = "$queryUri/v1/rest/mgmt"
$DBScript=$DBScript | Out-String

# Create body of the request
$body = @{
'csl' = $DBScript;
'db'= $kqlDBName
} | ConvertTo-Json -Depth 1

# Call Kusto API to run entities creation script
Invoke-RestMethod -Headers $headerParams -Method POST -Uri $queryAPI -Body ($body) -ContentType "application/json; charset=utf-8"

Step 6: Eventhouse and all its entities are created!

Automating_Real-Time_Intelligence_Eventhouse_deployment_using_PowerShell

Conclusion

By constructing a simple PowerShell script, we are able to automate creation of Fabric Real-Time Intelligence Eventhouse items. We can modify the PowerShell script to take parameters as inputs, handle errors using try/catch blocks and wrap it up to run as single command. You can visit SuryaTejJosyula/FabricRTI_Accelerator (github.com) to get a full PowerShell script and instructions of running it.

Learn More

• Read about Eventhouse announcement - Exciting News: Eventhouse is Now Generally Available! | Microsoft Fabric Blog | Microsoft Fabric

• To find out more about Real-Time Intelligence, read Yitzhak Kesselman’s announcement. As we launch our preview, we’d love to hear what you think and how you’re using the product. The best way to get in touch with us is through our community forum or submit an idea. For detailed how-tos, tutorials and other resources, check out the documentation.

This is part of a series of blog posts that dive into all the capabilities of Real-Time Intelligence. Stay tuned for more!