Tuesday, 10 September 2019

New to KQL? No problem its just like SQL

Well KQL is a query language used to query on Kusto, which is covered in the post Getting started with Kusto.




One Cluster can have multiple Databases and one Database can have multiple Tables. Before going forward let us assume we have a cluster named 'cluster1' and it has database with name 'database1' and database has tables with names 'table1', 'table2', 'table3','table4', 'table5'.

A kusto query usually comprises of  multiple operators and table or cluster name. Unlike SQL table name is not the first thing to be written in KQL. Below is a sample kusto equivalent of select top 5* from table1.
table1 | top 5

Since we are going to query on a big data source lets just add count or where clause to all our queries.

List of  5 basic operators to get started:

1. Project: To select the list of columns to show from the table.
example: table2 | project col1, col2 | take 10.

2. Count: To count the number of rows.
example: table1 | count.

3. Where: It is similar to where clause in SQL server. In KQL we use double equal, unlike SQL, for conditional check.
example: table2 | where col1 = = 'anyvalue'.

4. Take: It picks the number or rows mentioned. It takes rows in no specific order. It is suggested to sort the rows before using take. Limit can also be used for the same purpose.
example: table1 | take 5.

5. Sort (asc/desc): It is used for sorting the data in ascending or descending order, just like SQL.
example: table3 | take 5 col1 asc or table3 | top 5 col1 desc.


You can check out all the available operators/functions/entities at the Microsoft docs website.

Please share your thoughts in the comments below.

No comments:

Post a Comment