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

• Real-time Analytics
• Real-Time Intelligence

• mai 28, 2024 av Brad Watts 64 549 Visninger

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

Fabric API Support for KQL DB

Fabric API Support for KQL Queryset

Fabric API Support for Dashboards

KQL API Support for Fabric Eventhouse and Database

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