The Fork Operator

When working with the Kusto engine and developing the queries for analytics and intelligence, it is good to have several options to manipulate the data.

From the SQL world, we can execute several statements at the same time to get more than one output. In KQL it gets a little more difficult.

This blogpost tries to help you get started with this usefull little trick of getting more resultsets from the same query.

From the SQL world

In SQL we can execute this kind of query:

1
2
3
4
5

create procedure dbo.GetData as
begin
    select * from dbo.SalesOrderHeader;
    select * from dbo.SalesOrderLine;
end

Above SQL statement will give you something like this:

In the KQL world

From the KQL standard query we can also execute one query at the time. If we need to execute more queries we can do almost like above:

SamplePowerRequirementHistorizedData
| take 10;

SamplePowerRequirementHistorizedData
| take 10;

This will give us two outputs in the UI which looks something like this:

The two above result sets are the same, so here you will only see the first one.

But wait, there is more

Given the title of this post, there is more possibilities in KQL than just executing two queries at the same time.

We also have a special function named fork. You can read the documenation on the kql reference guide on kql.how right here.

This function helps us to define differnet outputs based on the same KQL query. The difference here is that the SQL way and the copy in KQL requires us to write to independant queries to get the desired result.

Let me show you an example on how this works:

Given a table in KQL: SamplePowerRequirementHistorizedData - (This is taken from the free to use cluster from Microsoft named HELP).

We can write the following KQL query:

SamplePowerRequirementHistorizedData
| take 100

This gives us a resultset with the first 100 rows found by the Kusto engine. Quite standard and nothing new to that.

But what if we wanted to get some calculations going on this table:

SamplePowerRequirementHistorizedData
| where twinId  <> "p_sol_01" | summarize count() by twinId, name

Above gives this result:

And now I also want to calculate the number of rows in the same table grouped by the week of year from the timestamp column.

Normally you would write something like this:

SamplePowerRequirementHistorizedData
| where twinId  <> "p_sol_01" | summarize count() by twinId, name;

SamplePowerRequirementHistorizedData
| summarize count() by WeekOfYear = week_of_year(timestamp);

This gives us a result, which actually works:

But we can do smarter than that

Use the fork operator

Given above needs and outputs, we can use the fork operator to get the desired output - and this all in the same KQL query without having to execute two queries.

Let me give you the example from above and use the fork operator:

SamplePowerRequirementHistorizedData
| fork
    Dataset2 = (where twinId  <> "p_sol_01" | summarize count() by twinId, name)
    Dataset3 = (summarize count() by WeekOfYear = week_of_year(timestamp))

This gives the exact same output as the two queries - just written in one single statement.

Also notice, that I can name my result sets to what ever I need. Here named Dataset2 and Dataset3.

We can use almost all the known features of the KQL language inside every single “sub” result set. For instance, we cannot use the join operator inside a sub-statement. This is not allowed by the engine.

Final thoughts

This method of working with the result sets is only usable for the KQL world. The resultset cannot be used by other services, like Power BI or Excel.

The result is a downright error due to the output mechanisms of the engine and the way the result set is generated.

But it is still very usable when working with your data and you want to get insights on the same dataset wihtout having to execute several statements to get the desired results.

If you want to read the syntax documentation and learn more, then the reference guide for KQL can be found here: kql.how.

Happy coding…