KQL and ADX – Is this really like SQL?

Hopefully, you’re aware that Azure Data Explorer can be accessed by anybody with a valid Microsoft Account Identity or MSA for short. If you need a one, then hop along to Outlook.com and pick up a free MSA.

After that head along to dataexplorer.azure.com and login with your MSA. Then, you’ll find that Azure Data Explorer is attached to a cluster that you did not create. This will be the ‘help’ cluster that Microsoft uses in order to get folks up-to-speed on Azure Data Explorer and KQL.

Your screen (browser) should look something like this.

Azure Data Explorer ‘help’ cluster

You’ll notice that you’re attached to the ‘help’ cluster. Which has 4 databases

  • ContosoSales
  • SampleLogs
  • SampleMetrics
  • Samples

And that an initial script is in the main window.

Notice too that right at the top of the main window (the one that contains the script) you can see the name of the ADX cluster and database that you are attached to.

ADX cluster and database name

The script that’s open in the main window is designed to run on the “@help/Samples” cluster / database.

Make sure you’re attached to that and highlight the first query (if you’re not on the correct database then you’ll just get an error)

Highlight the first KQL query

This is Kusto Query Language or KQL

This is basically the equivilent of a T-SQL query of the form

SELECT TOP(10) * FROM from table

You’ll also notice that the next query in the script looks something like this

A KQL query

And if you were to translate this into T-SQL, you’re basically asking for a computed / derived column and specifying a list of columns that you’d like back.

If you’ve worked with T-SQL for sometime, you’ll probably see some similarities but a lot of differences.

ADX and KQL have been built from the ground up with Data Analytics in mind.

Like most things Microsoft, it’s been developed internally and consequently seen great internal adoption. Hence, it’s being made available as a product to the rest of the world.

KQL is used heavily in Log Analytics, where we can join different tables of immense size in order to interrogate performance issues with our Azure systems – and get the answers really quickly, even when we have terabytes (or even petabytes) of data involved.

This is the type of interrogation that people might have used products such as Splunk for previously.

It’s worthwhile playing with this ‘help’ cluster and getting to know the KQL language, it’s looking like the adoption rate could be huge – and it’s always good to know your options, even if you find out that this is not for you.

Also, have a look at the connection string. Press the ‘Edit’ button in the bar that states the name of the ‘help’ cluster

ADX cluster properties (connection string and display name)

And we see the query string, and have the opportunity to change the display name of the cluster – if we like.

The ADX connection string

If you wish to query the cluster inside Azure Data Studio using the KQL extension then that’s possible too – see this post for details.

While KQL might be a little different to T-SQL, most database professionals will easily ‘acclimatise’ to the new language and be up and running quite quickly.

Watch this blog for more future tips.

Hope that helps somebody get up and running with ADX and KQL

Have a great day

Cheers

Marty.

Leave a Reply

Your email address will not be published.