>> Learn why KQL is
the next query language you should learn and learn
how to get started this week on Data Exposed MVP edition.
[MUSIC].
>> Hi, I'm Anna Hoffman and welcome to
this episode of Data Exposed MVP edition.
Today, I'm joined once again by Hamish.
Hamish thanks so much for joining us today.
Before we get into the show,
can you tell us a little bit about what you do?
>> Yes, sure. Thanks again for having me along,
and it's a real privilege.
Good day, everyone. I'm Hamish Watson
from Christchurch, New Zealand.
I'm a Data Platform MVP and I run my own company as well as doing
a whole heap of stuff in the community and really
looking forward to just introducing you-all to KQL today.
>> Awesome. That brings us right into the topic which
is KQL the next query language you need to learn.
I'd love if you could tell us a little bit about what
KQL is and why you feel
like it's the next language folks should learn.
>> Sure. KQL is short for Kusto Query Language.
Basically, it uses the Kusto engine to query Big Data sets.
How I came across it was I use Azure
as my platform for spinning up resources
to both deal with
data as well as spinning up
web servers digital functions what have you.
Behind the scenes,
all of the diagnostic information
is feed into Azure Monitor for all those resources.
Azure SQL database,
the whole lot of the resources.
Being able to query that diagnostic information was
just an eye-opener for me because not
only could I query it using KQL,
but I could tune those queries into an alert,
so that if say,
my Azure SQL database had crossed a threshold
and CPU percentage used
or even the number of VCores that it was using,
then I could use that that query that I've
written and tune that into proactive actions, i.e,
being able to scale up my database or letting
us know that something is going above the threshold.
Over time, I learned more and more how to use KQL.
I've written into SQL for a while,
but I want to just take you to
a really cool page that helped me out
so much and there is the SQL to Kusto cheat sheet,
and so in here,
you will find all information,
ways to write your queries,
and it's awesome because it gives
the examples on the lift of how I would write it if I
was writing an SQL and on
the right-hand side how
that maps or translates to arriving in KQL.
My queries started very simple at the beginning,
but over time, they got more and more complex.
>> Awesome. Cool. This is really helpful.
I actually didn't know this page
existed so thank you for sharing with me,
I've just been hacking along,
but I could see how this would be much more useful.
Can you show us an example of using KQL in action.
>> Yeah. Absolutely. The really cool thing is that Microsoft has
given us a free portal to actually use.
All you need is an account in Azure which,
of course can be free, and it's a
really simple URL aka.ms/lademo.
This is a demo of log analytics up in Azure,
and so Microsoft have got a whole heap of data up
there that we can query.
That query could be as simple as looking at
some performance data and we can use
all the operators that we use to
where time-generated was I don't know.
Actually what we could do first of all it's just run.
Do we have any data in the last 24 hours?
This is performance data that we're looking at,
and what we can do is just like
any query we might
actually look at where we're searching for memory.
The awesome thing about this is that we have the ability to
build out our queries as complex as we
want and I guess my coming here,
it's limitless in terms of what you can get out
of the diagnostic information.
Now, this information relates to VMs,
but as I said before,
we can query on Azure SQL database,
we can do it on our app services,
our functions, Logic Apps, anything.
Because when we set up our resources in Azure,
we're going to set up
our diagnostic information to go into log analytics.
Again, this is a free place where you can come along and
actually test out how to write in Kusto Query Language.
As I said, the limit is just what it is.
One thing I will call out is let's say I had a query
where I wanted to create an alert,
so again, you basically run your query
here and you would hit "New Alert" rule.
Now, you can't do that because it's the free one,
but in Azure,
we can definitely do that.
That leads me on to the next thing that I used KQL with,
which was Azure Data Explorer.
You can go and visit
Microsoft's website and in
docs.Microsoft and look at Azure Data Explorer.
Basically, it is a platform it's
a cluster that allows us to ingest terabytes of
data really really quickly and we use
KQL over there to read that data and get information.
One thing I will say is that it is read-only.
We're not actually going to modify data,
we're just, again, ingesting it,
we're exploring it, and then we may actually pass
the results of our query onto other things.
I'm going to take you into
a really cool bit of one of the utilities Microsoft have.
This dataexplorer.azure.com.
This is the online version of being able
to utilize our Azure Data Explorer.
In the past, I used Azure Data Studio on my computer.
That doesn't mean that I won't,
but it means that I can use
this online version anywhere that I want.
Here, I have my cluster which I built earlier
today and I created a database in there.
It doesn't have any tables,
so we are going to create a table,
we've created a table storm events,
and what I'm going to do is induced from Blob storage,
some data that Microsoft have given us as an example.
If you do go to the Azure Data Explorer page
on docs.Microsoft.com,
there is a link that will show you the quick start
of how to create a cluster, create your database,
which is literally a clipboard create statement,
and doing what I'm doing here.
Well, it's finished. What we can
do is do a quick count of what we have on our table.
We got 59,066 rows,
and now what I'll do is actually show you
some really cool things of what we can do.
Just like the Log Analytics platform,
it has auto-complete,
which is great for people like myself,
and we can use "Shift
Enter" to run our command or we can click "Run".
This is showing us
the version of some information around our database,
I did the count before,
and as you'll see here,
I've just got some random examples of what we can do using KQL.
Probably, we all know what we're clauses are,
so the thing I'm going to say
here is that what you see here is very
similar to what we would use in other languages in
terms of the construct,
and what have you, in terms of the operators.
You just have to just do it a slightly different way.
I'm going to drop my table because I
don't need it. Buy table, go on.
Go and look at the cheat sheet.
The cheat sheet is really cool.
Look at docs.Microsoft.com, it's great,
and if you're like me,
you're going to spend hours just seeing,
what information you can get out of that data.
Again, we've showed Data Explorer is
a fantastic platform to do
the ingestion of massive amounts of data.
>> Awesome. Thanks so much, Hamish.
This has been a great introduction to KQL,
also I think it's given myself and maybe
our viewers some affirmation like,
hey, you can learn KQL,
your T-SQL skills,
translate very well and we even
have a guide that shows you how to do that,
and then it was cool to see some of
the different interfaces whether it's Azure Monitor,
where you're showing us some of the logs
where we can interact for free
or getting into an actual example and Azure Data Explorer,
we also know it's possible in Azure Data Studio.
Hamish, thanks so much.
This has been really useful.
If you had any final tips,
what would you tell our viewers?
>> My tip would be,
they just go out and try.
There's so many different free ways of trying some of this out,
you will find that you will have to be
using KQL at some stage, because generally,
when people need information from data,
they come to people that write queries
with those people who go and learn it.
It's a really cool tool to have in terms of
helping businesses manage their platforms way better.
>> Awesome. Well, thanks again, Hamish,
so much for being on the show,
to our viewers, thanks so much for joining us.
If you like this episode, go ahead and give it a like,
leave us a comment, and we hope to see you
next time on Data Exposed.
[MUSIC]