>> Doing real-time interactive analytics on
terabytes and petabytes of data is challenging.
Today, we're going to look at how
Azure Data Explorer can help solve
this challenge and enable you to
analyze billions of rows in seconds.
Manoj Raheja is here to show us how today on Azure Friday.
[MUSIC].
>> Hello. This is Lara Rubbelke.
Today on Azure Friday,
we're going to learn all about Azure Data Explorer.
With me is Manoj Raheja,
who is the Principal Program Manager for
Azure Data Explorer. Welcome.
>> Thank you for having me here.
>> Great. Great to have you here.
So, tell us what is Azure Data Explorer.
>> Azure Data Explorer is
an analytical service that customers can use
for doing real-time analytics on data
that is streaming from IoT devices,
applications, websites, and other sources.
It includes a rich query engine which is optimized for
really low latency in JSON and lightning flush query performance.
It's in Cloud Service fully-managed.
>> Fantastic. Fantastic. What are the best types of
applications or data that this can support?
We have a lot of different data engines out there.
What are the types of data for this engine?
>> Yes. So, our customers can take advantage of this service
for building SaaS applications, IoT applications,
and do time series analytics on them,
or build solutions around
analyzing log and telemetry data at a really large-scale.
Those are the key scenarios,
but customers can exploit fits and other things,
but those are the three ones.
The system is optimized for querying over structured data,
which is like numbers and all,
and semi-structured data like JSON and XML,
and unstructured data like free-text.
So, it gives them different varieties
to handle in the same database.
So, you don't have to go to one database for searching,
go to other database for aggregations, and so on.
So, you get everything in one database
to go and run analytics against.
>> Great. Great. Well, do you have something you want to show us?
>> Yes, sure. So, onboarding and ingesting data is very easy.
So, customers can ingest data through streaming,
as well as batch sources.
So, what I'm going to show you is a quick demo
on how you can provision a service,
how to get started with one of the way of getting data,
but there are many different ways.
So, I've logged into Azure portal. Very simple.
You just go and create a resource here.
Search for Azure Data Explorer.
>> Oops!
>> You missed an R.
>> Typo. So, once you get the service, create,
and then now fill out the details,
the cluster name and which region you want,
and pick one of the skew that you'll be interested in.
For example, let's say, let's go to
West US two and then pick one of the skew.
So, we have storage optimized skews and compute optimized skews.
Again, there are two flavors within them.
We'll keep adding more and more skews as we move forward.
So, customers can pick and choose one of the skews.
Once you create a cluster,
it starts with at least two nodes.
Once a clusters is created, it's going to look like this.
This particular cluster, I just provision it,
and it's about two nodes.
Customers can scale it as needed depending
on the need or use the auto-scale capabilities of Azure.
>> So, it'll just automatically add
additional resources as it senses that it needs more too?
>> Yes.
>> Okay.
>> Yes.
>> Great.
>> So, the compute and storage are isolated.
So, what I'm showing you here is
how the computer will scale depending on the workload.
So, once the cluster is created,
now the next step is creating a database.
Just add a database here with database name and
then specify the retention period and caching period.
This is what makes it unique,
is you can specify how long you have to
keep data in the overall system and
also how long you want to keep data cached on the local SSD Nodes,
so, for faster querying.
So, customer needs to configure here.
So, these are the database level,
but we allow to set this configuration retrieval level as well.
>> Okay. Now, setting the cache period
to different periods of time,
will that affect the cost?
Obviously, it affects the performance over a period of time.
But what are some of
the considerations in how you would want to set the cache?
>> Yes. So, in most cases,
when you're doing analytics,
you need to understand your query pattern,
and is like how far uses are going.
Maybe in many cases,
they're just looking at last one week of Data,
last one month of data.
So, keep that in the hot cache, and rest of the data,
keep it into cold cache,
which is persistent on the Blob Storage.
So, if user has a scenario where they have to
go pull data from previous month or so,
they can still use the same query.
It'll be slower because we had to bring the data back from.
So, it's all happening behind the scene.
But how you choose the hot and cache policies,
it has impact on the cost,
because the data in cold cache,
it's just sitting in the Blob Storage without any compute,
so paying for the storage cost.
But what it is cached on
the compute is what is on the compute. Yes.
>> Make sense. Good.
>> So, once we create a database,
then there are different ways.
As I mentioned, you can stream data in or use batch ingestion.
So, we have made the integration
pretty easy for streaming scenarios,
where here I have created a database,
and I can go and easily hook it up to
an Event Hub by going through this workflow here,
going through Data Ingestion, add a connection,
and then I just need to specify my Event Hub details here.
Let's say, I pick some Event Hub that I have in
here and specify the destination table,
where it's going to go into the database.
So, table, as well as the format of the data that's coming in and
the mapping that tells us which data to map to which column.
Once this is setup,
it's all managed by bin,
and we just go pull data from
Event Hub and make it available in the table.
>> Okay.
>> So, again, I mentioned this is one of the options.
There are more options available.
I would encourage you to go through a documentation and [inaudible].
>> I have looked at the documentation.
I do want to call it really good documentation.
>> Thank you.
>> Some good tutorials,
lots of good samples, good getting started.
>> Yes.
>> Thank you.
>> Yes.
>> So, we looked on the provisioning side.
So, I want to show you how once you get the data,
what kind of analytics you can,
and that's a core value.
So, I'm switching to our WebUI.
So, this is one of the way to access data.
We have many other different ways,
and we'll talk about it as we move forward.
So, assuming you created a cluster,
created database, and loaded all the data, and know what to do.
The workload I'm showing you here is doing
ad-hoc analysis through our WebUI.
So, here I am connected to a demo cluster called demo12.
We have couple of databases,
and databases have tables, functions.
These are similar to views and
store procedures in relational data store.
So, I have table here called GithubEvents.
That's where we're going to go and do most of the querying.
So, this particular dataset is,
we download it from,
it's an open source for data that contains events of
users interacting with GitHub open source repos.
Anytime a user interacts with those repos it generates event,
so we cannot download it last one year off event.
It's one billion records,
about three terabytes in size.
It's pretty rich dataset to show this demo capabilities.
So, I'm running our first query here that goes and just
gets an aggregates of different types of events in the table.
So, I can see here I have
15 different type of events and their counts.
I can see within one second,
it generated this desegregated data.
>> How many rows of data was that?
>> Use of one billion records.
>> Okay. That was pretty fast.
>> It's pretty cool. The next one,
as I mentioned earlier,
the system is optimized for structured data like numbers,
semi-structured data like JSON and XML,
and unstructured data like free text.
So, there's an example of structured data.
You can do a time series analysis,
where you have bunch of data,
you generate count out of it,
and you are analyzing by a time variable.
How does the value change over
the time or look at from different dimensions.
So, that's what we're doing here.
>> That's a pretty common scenario,
especially in IoT's workloads as well.
>> Yes, definitely.
>> Yes.
>> In those cases, you want to go and
understand how are my devices performing,
how are they doing over the time,
is there a change in pattern, and so on.
So, we make it very easy to do those kind of analytics here.
So, this query starts with the table name.
Then we filter by a certain timeframe and generate an aggregation.
So, this is summarized count by bin,
by a timeframe and type.
Type, I showed you there are 15 different types
of events in the database.
So, one cool thing this does is,
this bin operator normalizes the data
into one, here, I'm using 1d.
That is one day. So, it normalizes this in one day and
then generate the chart.
So, this is going through
one billion records filtering out product time frame,
getting the summarization, and then charting those.
These are all the 15 different types and then the trend over time.
By one day, it's very easy to change the window as well.
One thing you may notice here is we had this render commands that
gives basic charting capability right
out of the box on the querying tool,
so that you don't have to copy paste.
Especially, when you're looking at trending,
you need some kind of charts to look at the trend.
>> Right. Right.
>> The Query Editors are full IntelliSense capabilities.
So, as you start typing queries,
it's going to guide you through
the syntax and makes it very easy for you to consume.
So, time series, we started with
very basic understanding the trend.
Now, want to understand, are there any anomalies in the system?
So, we have built-in anomaly detection operators.
So, the similar query and filtering by
timestamp filtering with additional,
I'm just focusing on one of the type of events,
and then doing a search here in a full-text searches.
So, I'm saying, "Just give me data where you see Azure in there."
Then, the next statement I am creating a time series data set.
So, in specifically, in IoT scenarios,
in time-series scenarios,
it's interesting to know that I have a certain pattern.
Also, it's interesting to know,
are there any gaps in my data?
>> Right.
>> Maybe devices sending some data but it suddenly stopped,
but you also want to identify those gaps.
So, this particular operator makes it easy helps us to build that,
identify gaps, and take action on the gaps,
and have everyone to fill with defaults and so on.
Doing anomalies and detecting anomalies on top of that data.
So, it just ran it,
we'd have the trend and it detected
these other anomalies and that data.
Moving on, similar capabilities,
it can do forecasting as well.
So, this is my actual data
and then that purple line is my forecast.
>> Okay.
>> So, those are all built-in.
So, I just showed you time-series, capabilities.
>> You have a number of different functions that-
>> Yes.
>> - the advanced functions that you're showing here or
there are others that might be interesting?
>> There are many. Maybe, I think there are
about 400 plus different operators that you can use.
>> Right.
>> So, all the basic thing that's when doing SQL is all available,
but there are more advanced capabilities like I just showed
you doing time series user analytics machine learning.
The other features that makes it very easy to do analytics,
and also the query language is built-in such a way that
the previous statement is
acts as an output for the next treatment.
From visibility perspective as it makes it
very easy and then you can also execute part of the query.
Don't have to execute the whole thing if
you want to troubleshoot the query.
So, now that concludes the time series analytics.
I just want to show you a next piece here is how do we
do analytics on a semi-structured data like JSON data.
So, this particular data set has couple of columns,
these are ID, Type column.
There are three columns which have JSON data in them,
and it's different schema for every different table.
So, we support column type called dynamic,
which makes it easy for us to
store JSON data natively and then query it runtime.
So, what we're going to do here is just do
a simple count of all the WatchEvents by RepoName,
and the RepoName is somewhere here in the JSON.
So, let's look at RepoName, just pass through that.
Okay. So, that's the RepoName,
and then the name is here.
So, I'm going to extract that value,
and generate some aggregations on top of it,
and then understand what are the top five watched repos.
So, let's run that. So, within few seconds, it went through,
filtered out data, generated that, extracted the value,
and then we have our top five repos by WatchEvent.
>> That's pretty fast to not just summarize the data,
but to apply some of these advanced functions and operators.
>> Yes. I mean, this is possible
because of the way data is indexed,
and compressed, and how fast we can search through the right data.
>> Right.
>> So, moving on,
this is another insights we can get from
this data is I want to do calculate a popularity index,
and this is something that is
a hypothetical scenario that we
came up to demonstrate this capability.
So, in the first portion of this query,
I'm filtering data for a WatchEvents,
and then generating the count by WatchEvents.
The second query, I'm generating the events
by generating the data for IssueComments and IssueEvents,
there's a different events here.
Then, we use Let statements to make the code more readable,
and reusable, and the next statement
here we're joining those two large data sets together.
Generating computed column with some computations,
and then figuring out what are the top five popular repos.
So, with that, we have the results in less than three seconds.
So, those are the top five repos,
and based on this hypothetical scenario.
It's amazing to see a VS Code,
I love this product and it's showing up in the top five.
So, yes, those are the things you can do and this
is just a fractional stuff that I'm showing you on JSON.
>> Right.
>> But there's lot other things that customers can do.
So, I showed you a structure data,
we looked at semi-structured data.
>> Yes.
>> Now, the last portion is unstructured data
and what would to do when you have unstructured data.
So, for that, I'm going to switch to a different data set.
This data set contains a telemetry data.
As about 41 billion rows, let's look at it.
So, this is one of the table that I have, is a sample data,
you have structured data,
and at the end I have this unstructured data, completely traces.
I have no clue what that is,
but I know I want to analyze and understand what's going on.
This data set is about 30 terabytes in size.
>> Okay.
>> So, here, scenario is I wanted to go and understand.
In telemetry, you are
getting telemetry from lots of different applications.
You want to understand what does impact
and who has been impacted, it is an impact.
So, here, I want to understand who's my most frustrated user,
and most frustrated user from
our definition is who's getting most error messages.
So, in this query, again,
we're using Let statement to make it easily readable,
and then filtering out by event text.
So, this is doing a full-text search here
and filtering the data by specific events.
Then, we extract the user ID
from the unstructured data and we're using rejects capabilities.
Then, join that with other data set that we query just for errors.
So, Level equal equal to two
is all the records that have error messages,
and then join this to large data sets that we generated
and understand who are the most frustrated users.
Then, within 2.5 seconds I have my results.
From here, I can go deep,
and dig deep, and then go look at my traces,
and then do additional investigation.
So, I showed you three different ways,
three different scenarios that you can and how you can query this.
Again, we have a lot of
documentation around this operators, IntelliSense, substitute-
>> Absolutely.
>> - just write the queries as you go.
We have online courses to help around.
Also, if somebody's interested,
you also support SQL language on top of this.
But you wouldn't get the richness that you
get through this language.
>> This is a very rich language.
A lot of opportunities to really dig in,
and understand what's happening,
and analyze the data.
So, it is a very rich query language.
>> Thanks for that. Thank you.
>> Now, you mentioned there are tutorials
and areas that you can go learn.
>> Yes.
>> Where would I go to find?
>> So, it's at
docs.microsoft.com and then look for Azure Data Explorer,
you'll find a bunch of documentation here with tutorials,
query example, and you can easily walk through those examples as
well as the documentation on query languages is pretty extensive.
As you can see here,
it's pretty deep documentation.
As well as we've been releasing blogs, and samples,
and other things to make it easy for
customers to try it out and adopt.
>> Great.
>> Today, we just looked at fraction of what system can do.
I just showed you web UI,
but we have other options to bring the data in,
two different ways, and also consume data in two different ways.
I encourage customers to go look at
the documentation and explore those options.
>> Great. Great. Well, thank you again for sharing all
of the great things that we can do with Azure Data Explorer.
Is there anything else you'd like to close with?
>> I think we're good. Thank you for having me here.
>> Great. Great. We've been learning
all about Azure Data Explorer today on Azure Friday.
>> [MUSIC]