If one has followed the Microsoft training, webcasts and other resources, it becomes clear that one can't ignore the Kusto Query Language (KQL). The code can be tried in the online explorer (see [2]), which makes available several databases for playing around. The users might need to register beforehand.
KQL was created to query big tables, though 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 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 "//".
// 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.
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]
