Talk to Fabric Eventhouse using Azure AI Agents – Natural Language to KQL

One of the main use cases I am frequently asked about is providing users with the capability to query data intuitively and effectively, regardless of their technical background. The idea is to build a system where stakeholders at all levels can ask questions about their data in natural language and receive immediate, actionable insights. 

In response to this challenge, I will be guiding you on how to build a NL to KQL app.

Microsoft Fabric Eventhouse

Fabric Eventhouse offers an innovative solution for managing and analyzing high data volumes, especially in contexts that demand real-time analytics and data exploration. Engineered to efficiently manage real-time data streams, eventhouses enable organizations to ingest, process, and analyze data almost instantaneously, making them invaluable for scenarios where timely insights are critical.

Designed for time-based, streaming events, eventhouses adeptly manage structured, semi-structured, and unstructured data. They can seamlessly integrate data from a variety of sources through multiple pipelines—such as Eventstream, SDKs, Kafka, Logstash, and data flows—across diverse data formats. This data is then automatically indexed and partitioned according to ingestion time, facilitating efficient data organization and retrieval.

Azure AI Agents

Azure AI Agent Service is a fully managed service designed to empower developers to securely build, deploy, and scale high-quality, and extensible AI agents without needing to manage the underlying compute and storage resources. What originally took hundreds of lines of code to support client side function calling can now be done in just a few lines of code with Azure AI Agent Service.

Within Azure AI Foundry, an AI Agent acts as a "smart" microservice that can be used to answer questions (RAG), perform actions, or completely automate workflows. It achieves this by combining the power of generative AI models with tools that allow it to access and interact with real-world data sources.

The solution - How to build a NL to KQL app

Below, I will guide you through the process of building an NL to KQL application using LLMs and effective prompting strategies by using 2 approaches:

-            NL to KQL with Azure AI agents

-            NL to KQL without agent frameworks

I will be using a KQL in Microsoft Fabric (Eventhouse), but this code can be used to connect to any KQL DB also using ADX (Azure Data Explorer). You just need to change the way you authenticate to your KQL DB.

Building a Natural Language (NL) to KQL application involves creating a system that can understand human language and convert it into a KQL query that can be executed.

Leveraging large language models (LLMs) like Azure OpenAI's GPT-4o can significantly enhance the performance of such applications. 

Step 1: Understand the Database Schema

Before you can generate KQL queries, you need to understand the database schema. The schema includes the tables, fields, relationships, and constraints in the database. This information will be crucial for constructing accurate KQL queries.

Step 2: Choose a Large Language Model (LLM)

Select a suitable LLM for generating KQL queries. GPT-4o is a popular choice due to its advanced capabilities in understanding and generating human-like text based on the prompts provided.

Step 3: Define the Prompt Strategy

Prompt engineering is critical when using LLMs. You need to design prompts that effectively communicate the task to the model.

For NL to KQL, your prompt should include:

• A brief description of the task.
• The database schema in a concise format.
• The natural language query provided by the user.

In our case I am providing the table creation command. You can get this by running the following command in your KQL DB.

.show database schema as csl script

Get the table creation commands and add it to the schema prompt:

.create-merge table Stocks (Date:datetime, Open:real, High:real, Low:real, Close:real, AdjClose:real, Volume:string, Ticker:string) 

Step 4: Call the LLM API

Use the LLM's API with the prompt and receive the generated KQL query. 

I am sending a schema prompt as follows and replacing the ${tbl_creation_kql} parameter with the table creation command described in Step 3:

Below is the information for a KQL table.
Schema:
${tbl_creation_kql}
------------------------------------------------------

 

Step 5: Execute the KQL Query

After obtaining the KQL query from the LLM, execute it on your database using a suitable database connection.

Step 6: Handle Exceptions and Edge Cases

LLMs might not always produce perfect KQL queries. You should implement error handling to manage incorrect queries or exceptions. Also, consider edge cases like ambiguous queries.

Step 7: Refine and Iterate

Based on the performance of your NL to KQL application, refine your prompts and possibly retrain or fine-tune your LLM model for better accuracy and understanding of specific schema or domain language.

Conclusion

Building an NL to KQL application using LLMs involves a combination of understanding the database schema, prompt engineering, and leveraging the LLM's capabilities to generate KQL queries. By following these steps and continually refining your approach, you can create an effective NL to KQL system that helps users interact with your KQL DB using natural language.

Preparation

Creating the Fabric Eventhouse DB

 

In Microsoft Fabric – Create an Eventhouse and a KQL DB called “Stock”.

Click on the KQL Database called “Stock”

 

 

Click on the ... menu next to the DB name and then Get Data> Sample

 

 

Choose the Stock analytics sample data, this will fill our Stock DB with Data.

 

Click on the Query URI and copy the URL to connect to our DB.

 

Save the .env.template file as .env file and fill the Query URI we copied from our Eventhouse in Fabric.

# nl to kql configuration
NL_TO_KQL_KUSTO_URI = "<fabric Eventhouse cluster URI>"
NL_TO_KQL_KUSTO_DATABASE = "Stock"
NL_TO_KQL_KUSTO_TABLE = "Stocks"

Running the code

Open the terminal in Vscode and run az login.

Make sure you are entering  a user that has permissions to both the Fabric workspace and the AI foundry project.

Az login

 

In this code snippet we connect to our Azure AI foundry project and use Azure credentials, we use default credentials by running "az login" for authentication.

try:
    credential = DefaultAzureCredential()
    token = credential.get_token("https://management.azure.com/.default")
except Exception as ex:
    print(ex)

 

Here we connect to our KQL DB in Fabric Eventhouse

from azure.kusto.data import KustoClient, KustoConnectionStringBuilder
from azure.kusto.data.exceptions import KustoServiceError
from azure.kusto.data.helpers import dataframe_from_result_table

kcsb = KustoConnectionStringBuilder.with_az_cli_authentication(KUSTO_URI)
print(kcsb)
client = KustoClient(kcsb)
kusto_db = KUSTO_DATABASE

 

Approach #1 - Using Azure AI Agents to talk to our KQL DB

The code for this approach can be found here.

Connecting to Azure AI Foundry to use agents

Go to Azure AI Foundry and deploy your gpt4o model – make sure to create a hub and project in a region where the Azure AI Agent service is supported (see here) (IMPORTANT!!)

Copy the Project connection string and paste it in the .env file

NL_TO_KQL_AZURE_AISTUDIO_PROJECT_CONN_STRING = "<your-azure-aistudio-project-conn-string>"

 

 

Using the Table Schema to be sent in the prompt so the Agent can understand our table to generate the right KQL

def get_table_creation_kql() -> str:
    table_schema = ".create-merge table Stocks (Date:datetime, Open:real, High:real, Low:real, Close:real, AdjClose:real, Volume:string, Ticker:string) "
    return table_schema

This is our prompt:

Below is the information for a KQL table.
Schema:
${tbl_creation_kql}

We use the function calling capabilities of our agent by binding a function. It is crucial to write descriptions of the functions purpose so the LLM will understand which functions to call back.

def read_from_db(kql_query:str) -> str:
    """
    run the query on the database and return the result
    :param kql_query (str): The kql to run on the database.
    :return: The query result
    """
    query_result = execute_query(kql_query)
    return query_result.to_json()

user_functions: Set[Callable[..., Any]] = {
    read_from_db
}

 

Calling the agent – returns the avg price for the tickers in February 2013.

call_agent("What is the average price for each stock symbol in the February 2013?")

 

The result:

Created thread, ID: thread_pYHDwG64mE404g2NwnWtntnw
Created message, ID: msg_IlFyIWhi3hsfbBwGXarmPHyg
Run finished with status: completed
USER: What is the average price for each stock symbol in the February 2013?
ASSISTANT: Here is the average closing price for each stock symbol in February 2013:

- AAPL: $16.31
- ABBV: $36.67
- ABC: $46.66
- ABT: $34.29
- ACGL: $15.99
- AEE: $33.09
- AEP: $45.33
- AFL: $25.11
- AIG: $38.36
- AIZ: $40.35
- AJG: $38.28
- AKAM: $37.78
- ALB: $63.73
- ALL: $45.55
- AMAT: $13.54
- AMCR: $9.32
- AMD: $2.62
- AMGN: $86.43
- AMP: $67.34
- AMT: $75.50
- AMZN: $13.18
- ANSS: $75.23
- AON: $57.82
- APA: $79.77
...
The list is truncated for brevity. If you're interested in a specific stock, feel free t

 

Approach #2 - Talk to our KQL DB - no agent frameworks

In order to talk to your KQL DB we will do the same steps as described above in the "How to" section but we will also use correction prompts to help the app get better KQL queries in the case of inaccurate queries returned by the LLM.

The secret sauce is in the prompts.

The user's question in Natural language is sent in the user prompt in ${question}. 

${schema_prompt}
Below is a question input from a user.
Generate a KQL query that pulls the necessary data to answer the question.
Question: ${question}
Return your answer ONLY in JSON format as follows:
{
  'explanation': '<explain what the query does>',
  'query': '<kql query>'
}

The schema prompt is used to have the LLM understand our DB by sending the tables schema in the ${tbl_creation_kql}.

Below is the information for a KQL table.
Schema:
${tbl_creation_kql}
------------------------------------------------------

 

After we get the KQL query from the LLM and running the query, we will use the final answer prompt to respond in NL.

${schema_prompt}
Below is a question, KQL query, explanation, and the result from executing the query. 
Use these pieces of information to answer the question.
{
    'question': ${question},
    'query': ${query},
    'explanation': ${explanation},
    'result': ${result}
}

 

If we detect errors when running the query, we will use the correction prompt to let the LLM correct the KQL query and retry.

${schema_prompt}
Given the following question, query, and kql error, fix the query.
{
        'Question': ${question},
        'Query': ${query},
        'KQL Error': ${sql_error_message}
}
Return your answer ONLY in JSON format as follows:
{
    'explanation': '<explain what the query does>',
    'query': '<kql query>'
}

 

So, that's it!!!

I hope you enjoyed this and got inspired to build your own agent to talk to your DB.

thanks