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.

Getting started with Kusto

What is kusto?

Well that looks like a difficult question to answer and its not answered clearly (atleast I was not able to find any answer for it). There are many versions of it.Some says its a tool, some says its a query language, docs says its a service. Lets, go with the docs version. Its a service to query over Big Data.

"It is based on relational database management systems, supporting entities such as databases, tables, and columns, as well as providing complex analytics query operators (such as calculated columns, searching and filtering or rows, group by-aggregates, joins)."

What is KQL?
 
KQL is acronym for Kusto Query Language, which can be used to query Azure Application Insight data. There is an open data source available online to query, it can be located at LogAnlytics.



How to Query?

Well to query you first need to learn the basics of KQL but dont worry if you already have worked with SQL or know about T/SQL, its easy to use. The syntax is little similar but there are few new keywords which might need to learn. New way of querying, it feels like mixture of SQL and Powershell to me. All the keywords are separated with Pipes '|' and output from the first query is input to the next one.

"A Kusto query is a read-only request to process Kusto data and return the results of this processing, without modifying the Kusto data or metadata. Kusto queries can use the SQL language, or the Kusto query language."

Where to write Query?

The next part is where can you write the query? Well kusto provides many ways/channels to query the data. All of them are mentioned below. Please select the one that suits your requirement. 😊
As I have mentioned in the article before about LogAnalytics. You can use a web UI to query or you can download Query Explorer. Well you can query over the application insight of your azure subscription also.

There are client libraries available in few languages, There is also an option to connect via Rest API, Client SDK's and KustoExplorer is an available native tool.

    .NET SDK
    Python SDK
    Java SDK
    Node SDK
    PowerShell
    R 

The content for Kusto can be found under names of Azure Data Explorer, Azure Log Analytics etc.
Please share your thoughts in the comments below.