Getting Started with KQL

I saw an episode of Data Exposed with my good friend, Hamish Watson. He talked about KQL (Kusto Query Language) being the next query language you need to learn. I was skeptical of the title, but I decided to give this a try.

In the episode, Hamish points out a cheat sheet from Microsoft, which I thought was a good resource. However, while watching the video, I browsed over to the demo site Microsoft has at https://aka.ms/lademo. You need an Azure account to log in, but this is a demo site where you can query some Log Analytics data. The new query window below is what appears when you go here:

2021-12-22 14_37_43-Logs - Microsoft Azure

Hamish shows some sample items, and I noted that to query a table, you just include the name. The query window in Azure has intellisense, and I used Hamish’ example to look at perf data.

2021-12-22 14_38_36-Logs - Microsoft Azure

The structure of queries is that you choose a table, then you can add a pipe (|) and add a where clause or other structures. I glanced at the cheat sheet, but really I played around a bit.

For example, I saw in the demo there was a “take” option, so I tried this:

Perf
| take 6

This gave me six results, so it’s like a TOP clause in T-SQL.

2021-12-22 14_42_19-Logs - Microsoft Azure

Next, I tried a WHERE. This is also structured as a line after a pipe. I picked a value I saw and then ran the query.

2021-12-22 14_45_03-Logs - Microsoft Azure

I noticed there was a summarize option, so I tried entering this. What I liked was that I had some intellisense options to help me with the various aggregates.

2021-12-22 14_46_15-Logs - Microsoft Azure

I ran this query, and got an average of the free space across all disks.

2021-12-22 14_47_04-Logs - Microsoft Azure

That isn’t a number that really means anything. I tried GROUP BY in a few ways, but eventually had to look at the cheat sheet to see just “by” is needed. I ended up with this query to get space by disk.

2021-12-22 14_48_54-Logs - Microsoft Azure

In some ways, I found KQL to be easier to work with than SQL. It’s cleaner, and logically follows what I’d do when writing a query. I build this in a way similar to how the query is executed, which is unlike how SQL works.

I don’t know if KQL is going to be that important to me, but I will play around with it a bit more.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

8 Responses to Getting Started with KQL

  1. JeffModen says:

    Heh… and I used to get ticked off at “line continuation characters” in VB6 and required semi-colons.

    Feels like a little too much PowerShell crept into this language. Lordy.

    Like

  2. JeffModen says:

    I hope you don’t mind with my vehement disagreement. Line continuation characters in the form of pipe characters, double == signs for simple assignment, TAKE at the bottom,. It’s different than SQL but that doesn’t make SQL “fairly broken as a language”. It also doesn’t make KQL superior and the line continuation characters are a thrill for me at all.

    Like

    • way0utwest says:

      == is comparison, which is how many languages do this. Personally, I think this was backwards. Whoever made the change early on should have left = as comparison, as it is in algebra, and used something else for assignment. A new character, or something like R with <-.

      I don't think necessarily think KQL is superior, but it is interesting, and modern tools add the pipe for you as intellisense, so that's not bad. It's easier to read.what is happening. The whole column list before you have an evaluation of what's taking place is weird. The CREATE v ALTER is also very broken from a code management standpoint with history. SQL is a mess in many ways.

      Liked by 1 person

  3. Pingback: Getting Started with KQL – Curated SQL

  4. JeffModen says:

    Got it but what makes you thing that SQL is a “fairly broken language”?

    Like

  5. richardldawson says:

    I meant to get back to this earlier, but well, life.
    In general, I have too many plates spinning to learn some new language for the sake of learning a new language. What particular problem does KQL solve? Is it a requirement for something?
    I like to learn as much as the next person who likes to learn, but there really does need to be a ‘need’ when it comes to something as complex and nuanced as a programming language.
    Sure, it’s cool and all, but does it make someone’s life easier??

    Good luck with that.
    RIchard

    Like

  6. way0utwest says:

    It’s used in Azure and some other cloud products, so it’s something you might encounter. Is it better or worse? No idea, but it’s there.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.