{"id":1508,"date":"2025-09-22T10:22:23","date_gmt":"2025-09-22T08:22:23","guid":{"rendered":"https:\/\/www.flip-design.de\/?p=1508"},"modified":"2025-09-22T10:22:23","modified_gmt":"2025-09-22T08:22:23","slug":"how-to-capture-stream-data-within-fabric-in-a-kql-database","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=1508","title":{"rendered":"How to capture stream data within Fabric in a KQL database"},"content":{"rendered":"\n

I extended the PowerShell script a bit so that it now submits a more complex JSON payload, and I also ensured that the script runs reliably every 10 seconds.
https:\/\/www.flip-design.de\/?p=1495<\/a><\/p>\n\n\n\n

<\/code><\/pre>\n\n\n\n
while ($true) {<\/p>\n\n\n\n
    [Reflection.Assembly]::LoadWithPartialName(„System.Web“)| out-null<\/p>\n\n\n\n
\u00a0 \u00a0 $URI=““<\/p>\n\n\n\n
    $Access_Policy_Name=“RootManageSharedAccessKey“<\/p>\n\n\n\n
\u00a0 \u00a0 $Access_Policy_Key=““<\/p>\n\n\n\n
    #Token expires now+300<\/em><\/p>\n\n\n\n
    $Expires=([DateTimeOffset]::Now.ToUnixTimeSeconds())+1800<\/p>\n\n\n\n
    $SignatureString=[System.Web.HttpUtility]::UrlEncode($URI)+ „`n“ + [string]$Expires<\/p>\n\n\n\n
    $HMAC = New-Object System.Security.Cryptography.HMACSHA256<\/p>\n\n\n\n
    $HMAC.key = [Text.Encoding]::ASCII.GetBytes($Access_Policy_Key)<\/p>\n\n\n\n
    $Signature = $HMAC.ComputeHash([Text.Encoding]::ASCII.GetBytes($SignatureString))<\/p>\n\n\n\n
    $Signature = [Convert]::ToBase64String($Signature)<\/p>\n\n\n\n
    $SASToken = „SharedAccessSignature sr=“ + [System.Web.HttpUtility]::UrlEncode($URI) + „&sig=“ + [System.Web.HttpUtility]::UrlEncode($Signature) + „&se=“ + $Expires + „&skn=“ + $Access_Policy_Name<\/p>\n\n\n\n
    $profiles = @(<\/p>\n\n\n\n
        @{<\/p>\n\n\n\n
            id       = „Machine01“<\/p>\n\n\n\n
            location = @{<\/p>\n\n\n\n
                city = „M\u00fcnchen“<\/p>\n\n\n\n
                zip  = „80331“<\/p>\n\n\n\n
                lat  = 48.13743<\/p>\n\n\n\n
                lon  = 11.57549<\/p>\n\n\n\n
            }<\/p>\n\n\n\n
        },<\/p>\n\n\n\n
        @{<\/p>\n\n\n\n
            id       = „Machine02“<\/p>\n\n\n\n
            location = @{<\/p>\n\n\n\n
                city = „Hamburg“<\/p>\n\n\n\n
                zip  = „20095“<\/p>\n\n\n\n
                lat  = 53.55034<\/p>\n\n\n\n
                lon  = 10.00065<\/p>\n\n\n\n
            }<\/p>\n\n\n\n
        },<\/p>\n\n\n\n
        @{<\/p>\n\n\n\n
            id       = „Machine03“<\/p>\n\n\n\n
            location = @{<\/p>\n\n\n\n
                city = „Berlin“<\/p>\n\n\n\n
                zip  = „10117“<\/p>\n\n\n\n
                lat  = 52.52001<\/p>\n\n\n\n
                lon  = 13.40495<\/p>\n\n\n\n
            }<\/p>\n\n\n\n
        }<\/p>\n\n\n\n
    )<\/p>\n\n\n\n
    $counterFile = Join-Path $PSScriptRoot ‚counter.txt‘<\/p>\n\n\n\n
    if (Test-Path $counterFile) {<\/p>\n\n\n\n
        $counter = [int](Get-Content -LiteralPath $counterFile)<\/p>\n\n\n\n
    } else {<\/p>\n\n\n\n
        $counter = 0<\/p>\n\n\n\n
    }<\/p>\n\n\n\n
    $idx     = $counter % $profiles.Count<\/p>\n\n\n\n
    $profile = $profiles[$idx]<\/p>\n\n\n\n
    $payload = @{<\/p>\n\n\n\n
        machine  = @{<\/p>\n\n\n\n
            id          = $profile.id<\/p>\n\n\n\n
            temperature = Get-Random -Minimum 60 -Maximum 80  # anpassen falls n\u00f6tig<\/em><\/p>\n\n\n\n
        }<\/p>\n\n\n\n
        location = $profile.location<\/p>\n\n\n\n
    }<\/p>\n\n\n\n
    $next = ($counter + 1) % $profiles.Count<\/p>\n\n\n\n
    Set-Content -LiteralPath $counterFile -Value $next<\/p>\n\n\n\n
        $json = $payload | ConvertTo-Json -Compress<\/p>\n\n\n\n
    $endpoint = „https:\/\/$URI\/messages?timeout=60&api-version=2014-01“<\/p>\n\n\n\n
    $headers  = @{<\/p>\n\n\n\n
        „Authorization“ = $SASToken<\/p>\n\n\n\n
        „Content-Type“  = „application\/json; charset=utf-8“<\/p>\n\n\n\n
    }<\/p>\n\n\n\n
    Invoke-RestMethod -Uri $endpoint -Method Post -Headers $headers -Body $json<\/p>\n\n\n\n
    Start-Sleep -Seconds 1<\/p>\n\n\n\n
}<\/p>\n\n\n\n
I believe that in a productive environment during development it makes sense to transmit a similar or even identical JSON structure. This way, the metadata can be leveraged to automatically create the table within the Kusto database, as well as to define all other ETL steps within the dataflow in Fabric Real-Time Intelligence.
I have now created a Fabric Event Stream to capture and process the data from the Azure Event Hub within Fabric.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
Then I created a data flow:<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
Then I connected this, for the data source, to the Azure Event Hub.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
If the script is then executed regularly, you can also see the incoming data.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
To be able to complete the next steps smoothly, it is recommended to publish this data flow. Otherwise, some functions, such as expanding hierarchies within JSON structures, will not appear fully. You will then have to switch back to edit mode.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
To extend the JSON, for example, it’s recommended to remove the arrow between the data and the target platform. To do this, we’ll add a task called Manage Fields, which can then be used to extend the structures.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
Now you can add a new field from the hierarchy structure. Finally, I added a data sink, in the form of an event house.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
Within this data sink I then created a KQL database.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
Back in the data flow, select the created data sink in the target. Since I want to store all fields in one table, I can simply create a target table.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
Now it’s time to save or publish this data flow. From then on, any transferred data is stored in the target table.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
In a meeting with Frank Geisler, I learned how to save the table data in OneLake. However, it’s important to note that since these are compressed Kusto files, the files won’t be visible for one or two days.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
To make the content visible, I used the free web tool:
https:\/\/parquetreader.com\/<\/a><\/p>\n\n\n\n
\"\"<\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"
I extended the PowerShell script a bit so that it now submits a more complex JSON payload, and I also ensured that the script runs reliably every 10 seconds.https:\/\/www.flip-design.de\/?p=1495 while ($true) {     [Reflection.Assembly]::LoadWithPartialName(„System.Web“)| out-null \u00a0 \u00a0 $URI=““   … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1508"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1508"}],"version-history":[{"count":1,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1508\/revisions"}],"predecessor-version":[{"id":1527,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1508\/revisions\/1527"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1508"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1508"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1508"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}