>> SQL Server 2019 big data clusters provide
Data Pools to optimize
query performance when reaching out to multiple sources.
Umachandar is here with us today to tell us all
about it today on Data Exposed.
[MUSIC].
>> Hi and welcome to another episode of Data Exposed.
I'm your host Jeroen and today we have you see the us
to talk about Data Pools in SQL big data clusters.
Hi Umachandar welcome to the show.
>> Hi, I'm happy to be here.
>> So Data Pools in big data clusters.
What is it? What do I need to know?
>> Sure yeah, let me quickly start
by showing what a Big Data Cluster is.
So this slide shows the deployment of
SQL Server 19 Big Data Cluster and today we're
going to talk about the Data Pools here which are basically
two SQL server instances which can be
used to offload some query [inaudible].
So let's see what is a Data Pool.
So it's basically SQL server storage and compute which is
provided to write the queries and store data in the Data Pool.
It can be manipulated by running
some external table operations on it and you populate data
into the Data Pool and later you can actually
offload some of your analytic queries over to this Data Pool.
So that is the primary purpose of the Data Pool.
So now let's may be quickly take a look at some of the scenarios.
So the common scenario is you're
getting data from different data sources,
some of them are fast,
others are slower in nature.
So now you want to basically optimize
this query so you could actually take that query
and store the results in the Data Pool and this allows you to
basically use that like
a staging area where you can run all your analytics.
The other example is if you're doing machine learning,
there are a lot of scenarios where you
would generate features and hundreds and
thousands of features and you want to basically store
the dataset in a place where you can do repeated analysis.
That's another place where the Data Pool helps.
>> So in that case it's not only for query optimization
but also for making sure you don't have to go
back to the data source all the time, right?
>> Yes.
>> If you want to reuse the data.
>> Yeah.
>> Cool, that sounds very cool.
So can you actually show us how this works?
>> Yeah, let's quickly go to assess how much is here.
So what I have here is
a connection to the SQL Server big data cluster.
I am connected to the SQL Server master instance.
So the way to create a Data Pool
is first by creating this data source object.
So here as this name indicates
here this basically says what type of a data source it is.
Then this I'm going to connect to a Data Pool.
>> Okay.
>> That's how you create it. After that
you define the table which you want to
store in the Data Pool and that's done
by running this external table statement.
>> Okay.
>> This looks like your regular create table statement,
there are columns which you need to define and in
addition since there are many SQL server instances on the backend.
>> Right.
>> You just need to say how you want to distribute the data.
>> So with this you can tell, okay,
so we got an even or approximately even distribution across.
>> Yeah.
>> Across the service.
>> Yes.
>> Okay.
>> So that's what the round robin does is basically distribute
the data across how many of Data Pool instances you have.
>> Okay.
>> The next thing you can do is like query the data.
So let's first look at some of
the sample data which in this example I have in
HDFS directory and basically
I want to take these clickstream data,
do some query and populate into the Data Pool.
The way you would do it is by running just an insert statement.
So the source of the insert statement is just a SQL query,
and you can simply run the query and populate the data.
I won't run through the inserts but let's quickly
see the data which is already populated in the Data Pool.
So this query should return about 2,000 rows, right?
So now just like any other table you can actually join
this table with other tables in your SQL Server and get
the results and transparently
this SQL Server instance is going to talk to
the Data Pool instances to run the queries and get the results.
So that's at a high level of what a Data Pool is.
>> Cool awesome.
That's very impressive. Thanks a lot man.
>> Cool yeah, thanks.
>> Okay. So thanks for watching.
Please like and subscribe,
comment on the video if you want us to
record something else and hope to see you next time. Thanks.
[MUSIC]