Using the query engine

Libraries are available on Nuget - search for KustoLoco

Using the query engine from within an application is generally straightforward and follows a consistent pattern.

1. Create a KustoQueryContext that will hold the set of tables that describe your data
2. Add tables to the context
3. Issue a KQL query
4. Do something with the result of the query.
5. Goto 3

For trivial cases such as querying sets of records, the ContextHelper factor can be used.

Here's an example of reading a list of processes, running a query and rendering the result into a DataTable for presentation in a GridView....

var context = new KustoQueryContext();
context.WrapDataIntoTable("processes", processes);
var result = await context.RunQuery("process | where Name contains 'code');
var dataTable = result.ToDataTable();

Tables can easily be generated from:

• C# records or POCOs
• A number of standard file formats including
  • CSV
  • Parquet
  • JSON
  • Text
• code-based injection via the TableBuilder class

Adding data to a context

From standard file formats

Data can be loaded from a variety of table formats using instances of the ITableSerializer interface. For example

var settings = new KustoSettings();
settings.Set("csv.trimcells","off"); //allow leading/trailing whitespace in cells
var reporter = new ConsoleProgressReporter(); //send progress and errors to Console.WriteLine

var csvResult = await CsvSerializer.Default.LoadTable(@"c:\kusto\data.csv", "csvdata", reporter,settings);
if (csvResult.Error.IsNotBlank()) {... perform some error handling ...}

var parquetResult = await new ParquetSerializer().LoadTable(@"c:\kusto\data.parquet", "parquetdata", reporter,settings);
if (parquetResult.Error.IsNotBlank()) {... perform some error handling ...}


var context = new KustoQueryContext()
     .AddTable(csvResult.Table)
     .AddTable(parquetResult.Table);

From in-memory data

If the data being passed in will be "owned" by the the context, it is safe to use the WrapDataIntoTable method but if you just want to take a snapshot of data which may be mutated outside of the context, the CopyDataIntoTable method is safer.

See this page for a fuller discussion.

public readonly record struct MyThing(string Name,int Size);
public class MyMutableThing(string Name,int Size);

IReadOnlyCollection<MyThing> myThings = ... create a set of MyThings ...
IReadOnlyCollection<MyMutableThing> myMutableThings = ... create a set of MyMutableThings ...

var context = new KustoQueryContext()
                .WrapDataIntoTable("things",myThings)
                .CopyDataIntoTable("things2,myMutableThings);

Working with query results

Query results are encapsulated by a KustoQueryResult object. If the query fails (for example because of invalid syntax), the Error property will be non-blank and the results table will be empty. It is therefore always safe to render a KustoQueryResult since in case of an error an empty table or chart should be produced.

Rendering to a DataTable

The core library natively supports rendering to a System.Data.DataTable. This can be used to populate a WPF DataGrid...

var result = await context.RunQuery(....);
var dataTable = result.ToDataTable(maxRowsToDisplay);
myDataGrid.ItemsSource = dataTable.DefaultView;

Rendering to HTML (Charts)

The KustoLoco.Rendering library can be used to render results to html and display them in a WPF WebView control.

var result = await context.RunQuery(....);
var html = KustoResultRenderer.RenderToHtml(result);
webview.NavigateToString(html);

The default behaviour is to:

• render the error message if the query failed
• render the result as an html table if the query was successful but does not include a render operator
• render the result as a Vega chart if the render operator was used

Rendering to a table

For generalised text output, code similar to the following can be used to process the rows and cells of a result...

//display the results as a pretty Spectre.Console table
var table = new Table();

// Add columns with header names
foreach (var column in queryResult.ColumnDefinitions()) 
   table.AddColumn(column.Name);
    
// Add rows.  Note that cells could contain nulls in the general case
foreach (var row in queryResult.EnumerateRows())
{
   var rowCells = row.Select(CellToString).ToArray();
   table.AddRow(rowCells);
}
    
AnsiConsole.Write(table);

//local method for string conversion
string CellToString(object? cell) => cell?.ToString() ?? "<null>";

Rendering to POCOs

One of the challenges of turning a query result back into POCOs is that the KQL syntax allows the "shape" of a result to be quite different to the input. For example, project and extend operators can be used to change the names and number of columns in the output data.
However, if you know the form of the query output the ToRecords method can be used..

public record MyExpectedType(string Type,int Count);
var result = await context.RunQuery(....);
var records = result.ToRecords<MyExpectedType>();

Rendering to a serializable form

Sometimes you want to send the result of a query across the wire. For example you might be serving query results from an API. A KustoQueryResult can be turned into a serializable from using a KustoResultSerializer which marshals the data into a string for transfer. The recommended serializer is ParquetResultSerializer.Default which turns the result data into a Parquet stream...

// Server code - turn a KustoQueryResult into a POCO which can be serialized to JSON

KustoQueryResult result = await context.RunQuery(query);
KustoResultDto dto = await ParquetResultSerializer.Default.Serialize(result);
string json = JsonSerializer.Serialize(dto);


// client code - get back to a KustoQueryResult

string json = await client.ReadStringAsync(....) ;
KustoResultDto dto = JsonSerializer.Deserialize<KustoResultDto>(text) 
KustoQueryResult result = ParquetResultSerializer.Default.Deserialize(dto);

Note that if the query contained a render operator, the transferred KustoQueryResult will contain the necessary VisualisationState required to perform a client-side render.