Microsoft Fabric Updates Blog

Using APIs with Fabric Real-Time Intelligence: Eventhouse and KQL DB

Eventhouses with KQL Databases provide a solution for handling and analyzing large volumes of data, particularly in scenarios requiring real-time analytics and exploration. They’re designed to handle real-time data streams efficiently, which lets organizations ingest, process, and analyze data in near real-time.

Combining the Fabric APIs with the existing KQL APIs allows you to fully automate your deployment of Eventhouse with KQL Databases. With the Fabric APIs I’m able create/update/delete items in Fabric and with the KQL APIs I can access the data plane of a resource and do things like create tables, change policies, etc..

Example

Let’s first walk through an example to see how the APIs can be used for automation. If
you’re new to KQL in Fabric it will be good to brush up on:

In Real-Time Intelligence your data typically flows through Eventstream and then lands in an Evenhouse hosted KQL Database where it is stored in a table. An Eventhouse can have 1 to many databases. A database can have one to many tables. And a table has columns to store your data. Then you apply policies to either the database or table to control different behaviors such as the length of time data is kept.

Below we’ll utilize the Fabric APIs to create Eventhouse and KQL Database while using the KQL APIs to create a table and apply a few policies.

  1. Create an Eventhouse
  2. Add a KQL Database to the Eventhouse
  3. Get the connection string for the Eventhouse
  4. Create a table on the Database and configure it’s Caching and Retention Policy

Step 1: Creating Eventhouse

You can utilize the Fabric Create Eventhouse API to create a new Eventhouse

Request

POST https://api.fabric.microsoft.com/v1/workspaces/<workspaceId>/eventhouses

Body

{
  displayName: "SampleEventhouse",
}

Output

{
  "id": "<Item_Id>",
  "type": "Eventhouse",
  "displayName": "SampleEventhouse",
  "description": "",
  "workspaceId": "<Workspace_ID>"
}

Step 2: Create a KQL Database on this Eventhouse

You can utilize the Fabric Create KQL Database API to add a new database to this Eventhouse. You’ll need to have the Item ID from the output of the previous commad.

Request

POST https://api.fabric.microsoft.com/v1/workspaces/<workspaceId>/kqlDatabases

Body

{
  "displayName": "SampleDB",
  "creationPayload": {
  "databaseType": "ReadWrite",
  "parentEventhouseItemId": "<ItemID from Step1>"
}

Step 3: Get the Connection String for Eventhouse

In order to utilize the KQL APIs we need to get the Query URI for our Eventhouse. We can get this using the Fabric Get Eventhouse API.

Request

GET https://api.fabric.microsoft.com/v1/workspaces/<workspaceId>/eventhouses/<eventhouseId>

Output

{
  "id": "<Eventhouse_ID>",
  "type": "Eventhouse",
  "displayName": "SampleEventhouse",
  "description": "",
  "workspaceId": "<Workspace_ID>",
  "properties": {
    "queryServiceUri": "<Query URI>",
    "ingestionServiceUri": "<Ingestion URI>",
    "databasesItemIds": [
      <Array of DBs>
    ],
    "minimumConsumptionUnits": null
  }
}

For our purposes we will need the “queryServiceUri” property.

Step 4: Creating and Table and Configuring Policies

For this step you will utilize the KQL APIs. We would like to do three things

  • Create a table
  • Set the cache policy
  • Set the retention policy

We will utilize a KQL Database Script executed by a KQL Command.

Request

<queryServiceUri>/ v1/rest/mgmt.

Body

{
  'csl': '.execute database script with (ContinueOnErrors=true) <| .create-merge table T(a:string, b:string); .alter-merge table T policy retention softdelete = 10d; .alter table T policy caching hot = 3d',
  'db': '<DB Name or Item ID>'
}

Output

<Results of each command being executed>

Summary

Using the Fabric APIs along with the KQL APIs allow us to interact with both the control plane of Fabric along with the data plane of KQL. With the KQL API you are able to execute any command that is available on a Fabric KQL Database or Eventhouse.

Currently Available

Fabric API Support for Eventhouse

Action Document Link
Create Eventhouse Items – Create Eventhouse – REST API (Eventhouse) | Microsoft Learn
Delete Eventhouse Items – Delete Eventhouse – REST API (Eventhouse) | Microsoft Learn
Get Eventhouse Items – Get Eventhouse – REST API (Eventhouse) | Microsoft Learn
Update Eventhouse Items – Update Eventhouse – REST API (Eventhouse) | Microsoft Learn
List Items Items – List Eventhouses – REST API (Eventhouse) | Microsoft Learn

Fabric API Support for KQL DB

Action Document Link
Create KQL Database Items – Create KQL Database – REST API (KQLDatabase) | Microsoft Learn
Delete KQL Database Items – Delete KQL Database – REST API (KQLDatabase) | Microsoft Learn
Get KQL Database Items – Get KQL Database – REST API (KQLDatabase) | Microsoft Learn
Update KQL Database Items – Update KQL Database – REST API (KQLDatabase) | Microsoft Learn
List KQL Databases Items – List KQL Databases – REST API (KQLDatabase) | Microsoft Learn

Fabric API Support for KQL Queryset

Action Document Link
Delete KQL Queryset Items – Delete KQL Querysets – REST API (KQLDatabase) | Microsoft Learn
Get KQL Queryset Items – Get KQL Queryset – REST API (KQLQueryset) | Microsoft Learn
List KQL Queryset Items – List KQL Querysets – REST API (KQLQueryset) | Microsoft Learn
Update KQL Queryset Items – Update KQL Queryset – REST API (KQLQueryset) | Microsoft Learn

Fabric API Support for Dashboards

Action Document Link
List Dashboards Items – List Dashboards – REST API (Dashboard) | Microsoft Learn

KQL API Support for Fabric Eventhouse and Database

Action Document Link
Query/Management Query/management HTTP request – Azure Data Explorer & Real-Time Analytics | Microsoft Learn

Coming Soon

Eventhouse

  • Create Eventhouse will support setting the minimum capacity

KQL Database

  • Create KQL Database will support
    • Shortcut databases
    • Configuring Caching and Retention policy at the database level

C# SDK for all the current Fabric KQL API Capabilities

Related blog posts

Using APIs with Fabric Real-Time Intelligence: Eventhouse and KQL DB

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 “

July 1, 2024 by Surya Teja Josyula

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 … Continue reading “Automating Real-Time Intelligence Eventhouse deployment using PowerShell”