>> Hello. This is Lara Rubbelke
with Azure Friday, and today,
we're talking about Hive LLAP or interactive query,
and I've got MK, who is with
our engineering team over on HDInsight,
to share some of the things that we
can do with Hive LLAP.
So, you want to tell us a little bit
about what you guys are focused on right now.
>> Absolutely. Hey folks.
First of all, this is a feature of HDInsight.
HDInsight, as you know, is
the open source pass platform that we offer in Azure,
and Hive LLAP basically takes it to the next level.
What you can do if you can
think about a traditional analytical warehouse,
what you do is you take data from
a lot of different sources.
It could be a log files.
It could be custom maps or your real time/IoT data.
It's flowing in through data factory and everything else.
Eventually, it ends up in a storage,
like typically Azure storage.
From then, you've got to do a lot of
processing if you want to do any sort of queries on it.
So, you do through ETL and probably several hours later,
and then yes, you can do a SQL query on it.
What Hive LLAP does,
this is an Apache project that we now create
a platform around within HDInsight
that you start querying those files ASAP.
So, you shock in this entire time
from data to insights using Hive LLAP.
>> Right, and that's all around that concept of
schema later or applying
the schema at the query time, right?
>> That's correct. So, you can actually create
these Hives external tables, if you will.
You don't have to load or populate the tables.
These are just files sitting in
your data lake and you're just querying on them.
>> That's fantastic. Fantastic. What would
you like to show us?
>> Yeah. So, just let me give
you a quick intro into
what LLAP is so when you see the demos,
you kind of know what's happening.
>> Great.
>> So, first of all,
a super fast performance.
So, imagine querying petabyte scale files in seconds.
So, this shows some demo with a 100 terabyte database.
Commodity storage. You don't need
any specialized storage. None of that stuff.
It's just regular raw data lake.
ACID. It actually supports transactions if you
do updates. Then concurrency.
You've done 64 concurrent corrections,
that's the benchmark we have published on the web.
It's super fast compared to
every other open source engine out
there and particularly tuned for Azure,
and the other cool part is a lot of our customers,
pretty big customers are trying it where they put
a load balancer in front and they were able
to scale to hundreds of users.
>> Fantastic.
>> Right, and, finally, security.
Security is paramount in any enterprise,
and this is fully integrated
to the Apache Ranger framework,
so you can make sure that anything that's
cached or anything that's queryable is fully supported,
so backed by security.
Of course, it's super easy to use.
It's just SQL. I will show you that simple demo on that.
On the right side here, you see
the simple trivial of picture software.
You have the storage sort of data lake.
You have the META storage of the high Metastore.
>> By Metastore, that is?
>> Just the metadata.
>> Here's the file or here's potentially
the schema of that file. Let's get it done.
>> That allows you to reuse
that metadata across different kinds of course.
>> That is correct. Across engines, too.
The high metastore is kind of a common store
that Spark uses,
the Hive uses, Hive LLAP uses.
All this, if you look at
sort of the LLAP dim and the memory SSD cache,
that kind of architecture,
they are all at saying is if you
spin up a Hive LLAP cluster,
it's basically trying to auto
pull these files into the cache.
It automatically takes care of cache.
You don't have to do anything.
If the files kind of a new file you add to the storage,
it'll automatically pull it up.
If the file is changed,
you need a lot of money to pull it out,
and it caches it in memory and
SSD drives on those machines,
so that way, you get super fast performance.
>> Great. Now, you mentioned commodity storage and you've
got- you talked about how we can
connect to Azure Blob or ADLS.
Are formats or file formats that we
were restricted to for using?
>> No. This is the cool part of it.
So, you can imagine CSV files that
you get just of a popular format.
You can just start querying them ASAP.
You don't have to convert them into any format.
Now if you can read that ORC,
it's faster because it's a columnar format,
it can do sort of pruning and everything.
But if you just want to query
raw files, absolutel, you can do it.
>> JSON and Parquet.
>> Yes, that's correct.
>> Well, that's great.
>> That's great. So, you can use
that data as it's been ingested.
You don't have to do all the conversion.
You can get the immediate benefits.
>> That is correct.
>> Great. That's great. Let's see more.
>> All right. Let me jump into the demo.
Before that, this is kind of
typically what you go through.
You create the HDI cluster.
We have a special offering called interactive query.
We don't call it LLAP, we call it interactive query,
and all you have to do is just upload
your data and start querying.
>> Okay.
>> We'll see the demo right now.
>> So, here's the Azure portal
as you're familiar with the Azure portal.
So, basically, what I have,
I have a cluster here called HDInsight cluster,
and this is an interactive cluster
so I'm just going to go to the cluster dashboard.
So, this is probably
familiar for people who know HDInsight,
and we offer a bunch of cluster dashboards,
upline notebooks and so on.
We want to open up the cluster dashboard,
and this is a familiar Ambari service
which kind of shows you all of the nodes in there.
In this system, you can see the disk usage,
the health of all the various components,
and for people who are familiar,
there is the Hive U2.0,
and if I open up the Hive
U2.0 this is kind of the syntax that you
got which shows you all the meta data that it's aware of.
Remember, these are just tables.
These are file sitting in the data link.
>> Right.
>> Right, and what I'm going to do right now,
I'm going to create a new table.
Let's just call it Demo AFB,
and I'm going to upload the data.
So, here's if you see the first
it says it's a file, it's a CSV file.
I'm just going to say
the first row is header information.
So, this is the file I'm going to upload.
So, this is basically just seeing what's the forecast
for the- how home prices to change in the various states.
So, you can see it's just a CSV file with
the states and potential forecast.
So, if I go back- so, I'm just going to say it's
a CSV file. The first was a header.
I'm just going to say you can either
upload from an HDFS file
if it already exists in other sources or a local file.
So, in this case, I'm going to
choose from the local file,
and, basically, if I go to the documents,
so you see this all reasons for public CSV,
and see, immediately, it just read the CSV file.
It showed that this is the scheme that it
should sign the CSV file.
So, here, potentially, you can
go and change any of these things,
like you feel the region names should be
maybe an integer and sort of a string.
You can do all that. If you don't like some of
the columns, you can delete that, too.
So, that's all I have to do, and actually,
if you can see, it even shows a preview of the file.
This is the same file that it just showed you right now.
So, I'm going to say "Create".
What this is doing, it's uploading that.
It's doing a bunch of things.
Firstm it's creating the actual table.
When I say "Create the Table", it's just
a metadata role in the hype schema store.
Then it's uploading the data
from my local file all the way up.
In this case, I've asked it to
also make it into an ORC format.
So, it's kind of doing that as well.
>> That gets you the advantages
of performance while you're querying.
>> That's right.
>> So, it does the conversion during the update.
>> The only problem is I forgot
to change the name of the table.
So, I already created the table.
So, let me call that
"Demoallregions," and I just say "Create."
So, it's creating the actual table.
It's going to upload it, put the files in there,
and you start to converting to an ORC format
because I've asked you to do so.
So, the way it also does,
this is a characteristic of Hive.
It first puts the data into
a temporary table just
so that like in case errors happen,
you don't mess up the data,
and then it actually then inserts the real table.
It's the ORC format, and now it's done.
>> Okay.
>> So, it's loading the table information.
As you can see, it has actually created a real table.
This is the detail it actually did. You just wait a sec.
So, this is a "Demoallregions" and you
can see this is the table we've actually created,
and you can see what sort of format in.
It's saying the format is actually an ORC format.
ORC is the column optimized format if you remember,
and then this is just a bunch of storage information,
more detail information on
the size of the files and statistics.
It's one file of 34,000 rows and so on and so forth.
>> Great.
>> That's kind of the table information.
Now let's start having some sort of- let's start
doing some queries and see where we go.
So, let's see.
The table was select star from- we call it table.
>> Demoallregions I believe.
>> Yes. We call that all regions. All regions for demo.
>> Demoallregions.
>> Oh, yeah. There you go.
Yes. Demoallregions. Let's limit
the demo rows so that we can see how the results are.
>> So, SQL.
>> It's just plain SQL.
>> It's just SQL.
>> Right. You saw that. It's a select star.
I can put overclass in there,
and so it's just plain SQL.
Imagine, I didn't have to do any DL.
All I did was upload the file
and you got the results back.
Now, few things I wanted to show you. This is one thing.
So, this is kind of a Hive 2.0 sort of you
were and if you like this sort
of new grade, you can use this.
But often more often than not,
you probably have data scientists who
want to start using notebooks,
but they're really familiar with that.
They want to start exploring the data, and so,
what the cool part about this Hiva LLAP
is fully integrated with the notebook experience.
So, here you go, the same,
we call that demo for
public if I remember right, demoallregions.
Let's run the query here,
and you can see I can start doing some data exploration.
So, for example, this is
the forecast a fair bit of value in here.
You immediately see the values like a visual experience.
>> That all right in that same cluster.
>> That is correct. I did not
have to do anything different,
and so a data scientist can use that same data.
Again, we did not curated it.
We didn't move it anywhere and you see the response time.
This is Andrada. So, be
sure the sort of Notebook experience,
the Hive 2 experience,
and obviously being an open source code,
we also work with all the open source tools
on their there.
Other cool one that you're familiar with is
a Visual Studio code
and we integrated with the Visual Studio code as well.
So, there is an extension for each Dean site.
So, if you sort of download that extension,
then you can start doing all of these queries.
>> Great.
>> So, here's a final example I want to show you.
As you know, TPC-DS is a big benchmark.
So, this is a 100 terabyte file
that I'm going to demo you with,
and look at the SQL complex, SQL query.
By the way, these are like real TPC-DS benchmark queries.
We've not modified any of that.
So, all you can do, I'm just going to right
click and say HDInsight
interactive and this is
that same "Do Not Delete"
LLAP cluster that we demonstrated.
The files are just sitting in there,
and you see it's now running the command.
This is the command it's doing and you can
see this is the verbose output of saying,
"Okay, what's the size of that data stream?"
That's about 100 terabytes.
"How do I optimize it?"
So, you saw the results come out right
there. All it took 12 minutes.
It's really 4.9 seconds
to do the entire 100 terabyte file scan.
>> That's pretty fast right.
>> Because you asked that question before,
which is if you see here, it says,
"Use TP-DS ORC.", at the top,
and ORC is the columnar format.
>> Right.
>> Right? I'm going to show you the same thing with CSV.
Imagine you had a 100 terabyte CSV file.
I can do the same query.
I did not change any of the query.
So, I'm just going around the same, interactive.
I'm going to go against this cluster,
and you see it's going to start executing the query.
Fit from the schema, it's running the job,
and let's see how long this is going to take.
Last one was 4.9 seconds - 5.6 seconds.
>> Great.
>> But your query didn't change answers are right there.
>> Right.
>> So, that's the beauty of LLAP,
and we in HDInsight have one of
the fastest LLAP implementations out there.
>> Oh great.
>> Fully managed.
>> Fully managed?
>> Yes.
>> So, as a developer,
I can just start loading data,
I could keep my data in whatever format I'm comfortable,
I can simply point my cluster to
that data and define some kind of
schema to apply over
the data so that I can query
it and start getting insights.
>> That is correct.
>> Pretty easy.
>> With the SQL, and it also
integrates like I said with all the tools,
Notebook experiences.
So, you can- it's going to be a developer,
it can be a data scientists. We got it covered.
>> Great. Where can we go to learn more?
>> You can go to HDInsight if you go to
Azure HDInsight and just search for interactive query.
You will have all the documents in there.
>> Great. Well, thank you so much for joining us
today and sharing all the great things we can do
with Hive interactive query or LLAP.
It is great to see all the progress that's been made in
the open source community and how that's
coming to life in HDInsight.
So, everybody can start
taking advantage of these great services.
>> Sounds good.
>> So, we're learning all about Hive LLAP
or interactive query today on Azure Friday.