Microsoft Fabric Updates Blog

Automating Real-Time Intelligence Eventhouse deployment using PowerShell

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!

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

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

Related blog posts

Automating Real-Time Intelligence Eventhouse deployment using PowerShell

July 19, 2024 by Alex Lin

Change Data Capture (CDC) is a popular database feature for monitoring and recording all row-level changes to a table. It plays a significant role in handling transactional events, such as sales orders from an online store, and providing real-time status updates to customers. With Eventstream’s CDC connector, you can now achieve this seamlessly, capturing changes … Continue reading “Build real-time order notifications with Eventstream’s CDC connector “

June 14, 2024 by Guy Reginiano

Announcing triggers and alerts on Real-Time Analytics Dashboards.