Let’s do some Kusto — Introduction

I work on Azure, primarily for app/mobile deployments and DevOps. But, Azure is so big. There are dozens of components and moving pieces. One such component is Kusto Query Language. I had used that a while ago and I got a chance to work on it again.

I thought I will put some Kusto tutorials here for reference.

Primary reference material — Azure Data Explorer Reference (Kusto).

What is Kusto?

Kusto or Kusto Query Language (KQL) is what it says on the tin. It’s a query language similar to SQL. You run these queries against your data store (I am intentionally avoiding using the word data-base) and get the results. Its possible that such things already existed before but for me, as far as Azure is concerned, this is pretty unique and cool.

Kusto works in conjunction with azure data explorer. Azure data explorer is like a intermediary. It can be fed data from multiple sources. It could be data from API endpoints, CSV files, standard databases (of all types — Microsoft SQL server, MySQL, Flat databases like NoSql and so on). Once the data is in azure data explorer, you can query them with one language — Kusto.

Further, you might be building an app that connects to Azure data explorer. You could connect via the standard REST API, or .NET or Node.JS. Then also, you would end up using these queries.

So, that is the simplicity and beauty of it. This is sort of what you might already be doing with PowerBI and Excel (importing data from different sources and formats). Kusto and Azure Data Explorer makes it even more accessible.

On a broader level, Azure Data Explorer connects with Azure Event Hubs.

Setup

There are two ways to get started with Kusto (in both cases you need to connect with an existing/running azure data explorer database).

1. Build your own Azure Data Cluster and database. This can be done in many ways. One such instruction is available here.
2. Use the free online sample database provided by Azure to practice KQL.

I would recommend this because Azure Data Cluster is not exactly cheap, even at the lowest price point. You can find the online free query window, here. note : you need to have a Microsoft account (live.com, outlook.com, msn.com and such) to be able to use this.

Kusto Reference and Beginner Tips

The sample database provided by Azure, has two tables.

ConferenceSessions — This provides details about Ignite and Build events that have happened over the years.

StormEvents — This provides details about storm events obtained from the weather department.

Tip 1 — When practicing, I would recommend starting off with the ConferenceSessions table. Its smaller and simpler. Once you have picked up on the basics, you can get started with StormEvents table.

Tip 2 — Also, don’t practice by running queries against the entire database. That will be a total waste of time. Its better to create a sample table of your own, and then practice against it. This saves time, and also resources consumed.

Use this query to get your sample table (you will understand what it does eventually)

let SampleTable = ConferenceSessions | take 10;
SampleTable;

and then run a query against this table like this. This query gets all the rows with the conference name and owner of that conference.

SampleTable
| project conference,owner;

Hello World

Okay, lets do a basic query.

Let’s say I want to get all the events that are conducted by “Rohan Kumar”.

//optional - create a sub table of the table for faster query running
let SampleTable = ConferenceSessions | take 10;
//
SampleTable
//I only want some columns, not everything.
| project owner,conference,session_type,session_title
//I want to pick only those rows that belong to Rohan Kumar
//the output rows from the previous expression is piped/fed into the next pipe
| where owner == "Rohan Kumar";

And, now, I want to get all the Ignite events.

//optional - create a sub table of the table for faster query running
let SampleTable = ConferenceSessions | take 10;
//
SampleTable
//I only want some columns, not everything.
| project owner,conference,session_type,session_title
//I want to pick only those rows that belong to Rohan Kumar
//the output rows from the previous expression is piped/fed into the next pipe
| where conference contains "Ignite";

The comments are self-explanatory and a simple introduction to Kusto.

I work as a full time freelance software developer and coding tutor. Hire me at UpWork or Fiverr. My personal website is here. I also have a Podcast. Thanks for reading.