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

[bwatts]

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:

• Overview
• Eventhouse Overview
• KQL Database Overview
• Creating a table
• Caching Policy
• Retention Policy

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

Create KQL Dashboard	Creates a KQL dashboard in the specified workspace.
Delete KQL Dashboard	Deletes the specified KQL dashboard.
Get KQL Dashboard	Returns properties of the specified KQL dashboard.
Get KQL Dashboard Definition	Returns the specified KQL dashboard public definition.
List KQL Dashboards	Returns a list of KQL dashboards from the specified workspace.
Update KQL Dashboard	Updates the properties of the specified KQL dashboard.
Update KQL Dashboard Definition	Overrides the definition for the specified KQL dashboard.

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