If one has followed the Microsoft training, webcasts and other resources, it becomes clear that the Kusto Query Language (KQL) can't be ignored as it's useful in various scenarios thar deal with large volumes of data.
Even if KQL was created to query big tables, at least for the first steps it's recommended to start with a small data model, and when the basics were understood one can move to bigger databases. Moreover, it's easier to validate the logic when using small datasets.
Probably, the most important aspect before starting is that KQL is case-sensitive and this applies to everything – table and column names, operators, functions, etc. The below code can be tried in the online explorer (see [2], [3]), which makes available several databases for playing around. The users might need to register beforehand.
The following queries are based on the ContosoSales database (available in the above mentioned link). First, here are some simple projections. Each query is preceded by its short description in which the text was commented via "//" and must be run individually.
// selecting all records Customers // selecting all records Customers | take 10 // multiple filters Customers | where CityName == 'Berkeley' | where Occupation != 'Professional' | take 10 // multiple filters on the same column Customers | where Occupation == 'Professional' and Occupation == 'Clerical') | take 10 // multiple filters on the same column Customers | where Occupation in ('Professional','Clerical') | take 10 // multiple filters on the same column Customers | where not(Occupation in ('Professional','Clerical')) | take 10 //subset of columns Customers | take 5 | project ContinentName, CityName, FirstName, LastName, Gender
Here are some example for the selection of unique values, the equivalent of SELECT DISTINCT from SQL:
//distinct values used Customers | distinct Occupation //distinct values used sorted ascendingly Customers | distinct Occupation | sort by Occupation asc //combinations of values used Customers | distinct Occupation, Education | sort by Occupation asc, Education asc
When further data is needed, one needs to resume to grouping values, the equivalent of GROUP BY:
// record count Customers | count // record count for constraint Customers | where CityName == 'Berkeley' | count // record count for constraint: returns 0 records (KQL is case sensitive) Customers | where CityName == 'BERKELEY' | count // numnber of records by occupation Customers | summarize occupations_count = count() by Occupation // numnber of records by occupation with bar chart visual Customers | summarize occupations_count = count() by Occupation | render barchart
The last query renders the data directly to a bar chart, which is a cool feature, especially when is needed to understand the distribution of values. Executing the query without the last line renders the initial dataset.
|
| Azure Data Explorer - Chart Example |
Here are some first impressions:
1) The language is relatively simple,
though the transition from SQL to KQL requires time, even if the thinking
process of writing the code is not that far away. For those with no SQL
knowledge, the transition might be a bit more challenging, though practice
makes perfect!
2) One can try to run the code line by line to understand
the steps used.
3) There are also some online code converters from SQL to
KQL (see for example msticpy).
4) The intellisense capabilities facilitate the overall
experience.
5) Unfortunately, there seems to be no code formatter
to HTML for KQL, so one needs to compromise in one way or another.
6) For a comparison between SQL and KQL see [4].
Happy coding!
References:
[1] Microsoft Learn (2024) Kusto Query Language learning resources [link]
[2] TestingSpot Blog (2021) Learning Kusto Query Language - A tool for
performance test engineers [link]
[3] Azure Data Explorer (2025] Create a free cluster to explore your
data [link]
[4] Microsoft Learn (2024) SQL to Kusto Query Language cheat sheet [link]
Resources:
[R1] GitHub repository (2022) Sentinel Queries [link]
[R2] GitHub repository (2022) Kusto Query Language Grammar [link]
[R3] GitHub repository (2024) The Definitive Guide to KQL source code
[link]
[R4[ Git Hub repository (2022) Learning Kijo [link]
[R5] Kusto gamification [link]
