BlazorData.net made a recent contribution to a Github project that contains code that will be incorporated into a future version on BlazorData.net. The Github project is kusto-loco and it provides an API that allows you to use Kusto Query Language (KQL) to create visualizations.

However, KQL can be hard to learn, so the contribution that BlazorData.net made was to incorporate OpenAI to help you create the KQL to create the visualizations.

The code is available here: https://github.com/NeilMacMullen/kusto-loco/tree/main/samples/Blazor-Server

 

Walk-Thru

Using Visual Studio, pull down the Github project from: https://github.com/NeilMacMullen/kusto-loco and right-click on the Samples/Blazor-Server project and set it as the Startup project.

Hit the F5 key to build and run the project.

 

The first step is to navigate to the CoPilot Settings page and enter your OpenAI key or Azure OpenAI settings.

 

Next, navigate to the Home page and upload a CSV file of data you want to visualize.

 

The data will load with a default query.

Click the CoPilot button.

 

Enter a description of what you want to visualize and click the Submit button.

 

The AI service will create an updated KQL query, and the updated visualization will be displayed.

 

You can click the CoPilot button to open the dialog and add further instructions to refine the results.

Using AI will allow you to easily develop complex queries.

 

OpenAI/Azure OpenAI Integration

The OpenAI and Azure OpenAI integration is provided by the Microsoft.Extensions.AI library.

This allows us to create code, that can call either service, yet returns a unified IChatClient:

 

    public IChatClient CreateAIChatClient(SettingsService objSettings)
    {
        string Organization = objSettings.Organization;
        string ApiKey = objSettings.ApiKey;
        string Endpoint = objSettings.Endpoint;
        string ApiVersion = objSettings.ApiVersion;
        string AIEmbeddingModel = objSettings.AIEmbeddingModel;
        string AIModel = objSettings.AIModel;
        ApiKeyCredential apiKeyCredential = new ApiKeyCredential(ApiKey);
        if (objSettings.AIType == "OpenAI")
        {
            OpenAIClientOptions options = new OpenAIClientOptions();
            options.NetworkTimeout = TimeSpan.FromSeconds(520);
            return new OpenAIClient(
                apiKeyCredential, options)
                .AsChatClient(AIModel);
        }
        else // Azure OpenAI
        {
            AzureOpenAIClientOptions options = new AzureOpenAIClientOptions();
            options.NetworkTimeout = TimeSpan.FromSeconds(520);
            return new AzureOpenAIClient(
                new Uri(Endpoint),
                apiKeyCredential, options)
                .AsChatClient(AIModel);
        }
    }

This allows us to use a single method, that accepts the query, and uses the IChatClient (passed below in the objSettings parameter) to return a result:

 

    public async Task<AIResponse> CallOpenAI(
        SettingsService objSettings,
        string AIQuery)         
    {
        try
        {
            // Create the AI chat client using the provided settings
            var chatClient = CreateAIChatClient(objSettings);
            // Send the system message to the AI chat client
            var response = await chatClient.CompleteAsync(AIQuery);
            // Check if the response contains any choices
            if (response.Choices == null || response.Choices.Count == 0)
            {
                // Optionally, log the absence of choices
                // or handle it as needed
                return new AIResponse() {
                    Code = "",
                    Error = "No choices returned in the AI response." };
            }
            // Extract the text from the first choice
            string jsonResponse = response.Choices[0].Text.Trim();
            // Remove ```json    and ```
            jsonResponse = jsonResponse.Replace("```json", "")
                .Replace("```", "");
            try
            {
                var parsedResponse = JsonConvert
                    .DeserializeObject<AIResponse>(jsonResponse);
                if (parsedResponse != null &&
                    !string.IsNullOrEmpty(parsedResponse.Code))
                {
                    return parsedResponse;
                }
                else
                {
                    return new AIResponse() {
                        Code = "",
                        Error = "Parsed response is null or " +
                        "missing the 'Code' field." };
                }
            }
            catch (JsonException jsonEx)
            {
                // Handle JSON parsing errors
                return new AIResponse() {
                    Code = "",
                    Error = $"Error parsing JSON response: " +
                    $"{jsonEx.Message}" };
            }
        }
        catch (Exception ex)
        {
            // Handle unexpected exceptions
            return new AIResponse() {
                Code = "",
                Error = $"An error occurred while testing access: " +
                $"{ex.Message}" };
        }
    }

 

The AI instruction, that takes the user’s request, and provides additional instructions, to produce the KQL query, uses the following template:

https://github.com/NeilMacMullen/kusto-loco/blob/main/samples/Blazor-Server/Templates/AITemplate.txt

This template contains these place holders:

 

• ## CURRENT SCHEMA ##
• ## CURRENT CODE ##
• ## CURRENT REQUEST ##

 

The following code is used to load the template, gather the data needed, and make the replacements, and call the AI service:

 

    // Load template from Templates\AITemplate.txt
    var AIQuery = OrchestratorMethods.GetTemplate();
    // Get Table Schema
    string schema = "";
    var sb = new StringBuilder();
    foreach (var table in kustoContext.Tables())
    {
        sb.AppendLine($"The table named '{table.Name}' has the following columns");
        var cols = table.ColumnNames.Zip(table.Type.Columns)
            .Select(z => $"  {z.First} is of type {z.Second.Type.Name}")
            .ToArray();
        foreach (var column in cols)
        {
            sb.AppendLine(column);
        }
        schema = sb.ToString();
    }
    // Replace ## CURRENT SCHEMA ##, ## CURRENT CODE ##, ## CURRENT REQUEST ##
    // with the current schema, code and request
    AIQuery = AIQuery.Replace("## CURRENT SCHEMA ##", schema);
    AIQuery = AIQuery.Replace("## CURRENT CODE ##", query);
    AIQuery = AIQuery.Replace("## CURRENT REQUEST ##", AIMessage);
    AIResponse result = await OrchestratorMethods.CallOpenAI(SettingsService, AIQuery);
    if (result.Error != "")
    {
        Message = result.Error;
    }
    else
    {
        query = result.Code;
        await RunQuery();
    }

 

Display The KQL Result

 

When we have a KQL query, created by the AI service, or entered manually, the RunQuery method is used to process it to create the visualization, as HTML, and bind it to the table variable:

 

 

The visualization is displayed by binding the HTML contents of the table variable to a simple IFrame:

 

    <div style="flex-grow: 1; overflow: auto;">
        <iframe style="width: 100%; height: 90%;" srcdoc="@table"></iframe>
    </div>

 

 

Links

kusto-loco

Kusto Query Language (KQL) overview

Microsoft.Extensions.AI library