>> In this episode, you'll learn about big data clusters in a nutshell from Ben Weissman. Join us on this episode of
Data Exposed MVP edition. [MUSIC]. >> Hi. I'm Anna
Hoffman and welcome to this episode of Data
Exposed MVP edition. Today I'm joined by Ben Weissman, a data passionist from Germany. Ben, thanks for joining us. Can you tell us a little
bit more about what you do? >> Sure. Thank you for having me. Yeah, I'm running a
small consultancy firm here in Nuremberg,
Germany called Solisyon. We're mainly focused on data
warehousing, data integration. We've been doing this
for quite a while. SQL Server 6.5 is when I started. So I think you've been at
elementary school at that point. >> Potentially. >> So yeah, integration services, all kinds of data warehousing
and then BI, the full-stack. One part of that that we're
currently looking at, and that I'm very passionate
about is big data clusters, a new technology that came
with SQL Server 2019. Yeah, I started loving
from the very beginning. >> Awesome. Yeah. I know from working with you throughout
the past few years, you've definitely been on
it since the beginning. So today, you're going to tell
us about it in a nutshell. I'm really interested to see how
you're going to pull this off. So I'm just going to
pass it over to you. >> All right. So a big data cluster, I think Trevor's right
came up with that, it's not your grandpa SQL Server. This is a pretty great
description on it. So first of all, it only runs on Linux, which is amazing because
if we just think a couple of years back there
wasn't SQL on Linux at all, and now we've got a feature that only runs in containers and
only runs on Linux. So it all runs on the
Kubernetes platform. So it is a whole new approach how SQL applications are
getting deployed. So that's one thing that's
super exciting about it. I want to give you first a little nutshell introduction on the whole architecture
of the whole thing, and then we're going to get
our hands on it and see how that actually works in real life. So the cool thing is, as complex as this looks, there is one guy in
that whole picture, which is the SQL Server master. That is just a regular
SQL Server 2019 with the only exception
that it runs on Linux, which may not be the typical thing. Behind that, there's a
couple of so-called pools. These pools are what makes big
data clusters so exciting. The first one where basically everything is funneled
through is the compute pool. What the compute pool
does, it scales queries, so it boosts SQL Server
queries to performance, it hasn't been there before. From there, it either goes to data that sits outside of SQL Server, that's called data virtualization. SQL Server Is using
PolyBase for that. But again, not your grandpa's
PolyBase from SQL Server 2016. But now you can get data from
Oracle, Teradata, MongoDB, other SQL Servers, all
kinds of stuff and bring them into SQL Server and query
them just like local tables. Then there's the data pool up there. The idea of the data pool is, you have not one SQL
Server, but multiples. In my picture it's two, but
it could be four or eight. The idea is you take your fact data. So think of a big fact table
with a 100 million rows. You push it to the data
pool with two nodes, you end up with two
identical looking tables, but each of them only
hold 50 million rows. So it's getting split in half. The theory being, well, if it's only half the data, it should be twice as fast. That's the whole idea
of the data pool. That's also something
that has never been there in SQL Server in that way. The other component
is the storage pool. It combines HDFS, so a distributed file system with
Spark and SQL all in one component. All that we see here when you deploy a big data cluster is being
deployed for you at once. So as complex as it looks, you're only deploying one product, and SQL Server is making sure
everything talks to each other, and you only talk to the master. So you only got one endpoint
that you need to talk to. Only exception for that is the
app pool, the application pool. We're not going to go
into detail with that because it does pretty much
everything and nothing. You could use it to deploy
machine learning models, AI models, stuff like that, integration services, jobs, so yeah. It is super powerful, but it doesn't do
much out-of-the-box. So what I would like to do today is, we will go to GitHub, get us some data around COVID-19, which is the reason why we're not sitting in the same studio today, but each of us are
sitting at our homes. Get some COVID data from John Hopkins and also
some population data, which isn't an open dataset, because that's another thing that
people regularly underestimate, in my opinion, that
there's so much data available out there freely
for everyone to use. You will get that using
Spark and Python. Push it to the data pool,
then to the storage pool. These files coming from John Hopkins, they have one issue. They're super up-to-date, so
they've been getting updated daily. But they're very wide because
what John Hopkins does is they add a column for each
day instead of adding rows. So what we're going to do is using
a very simple Python function, all in Azure Data Studio and all connected to our big data cluster, we will transform this
to long Parquet files. Parquet clustered column
store index for CSVs, it's just way faster. We're going to take these,
merge them together, and push them to the
data pool for caching. We need some local mapping table
because the country names in that population CSV and the way
they come from John Hopkins, unfortunately, they're not identical. Then we're just going to
run a query across them. So we basically take one SQL query that combines data
from the storage pool, from the master instance, and from the data pool all using regular SQL because all
that data, these CSVs, these Parquet files,
and everything on the data pool for me as an end user, this looks just like
a regular SQL table. >> Awesome. Ben, this
has been great so far. I mean, you've really given us a wide range of capabilities that are available
on big data clusters. I'm really interested to see
how we're going to do all of this by just using SQL
and just connecting to, I think you said we're
just going to connect to the master instance for this. >> Correct. >> Awesome. Cool. Well,
let's take a look. >> All right. So let's jump
right into Azure Data Studio. For those that have not been working with Azure
Data Studio before, first of all, you really should
because it's an amazing tool. It's not taking your Management
Studio or anything away, but it has a lot of features that
Management Studio just hasn't. One of these features
are so-called Notebooks. That's a super cool
feature because Notebooks combine the idea that
I can put in text, I could put them in graphics, and I could put in code, and they also save the results. So the first notebook
that I've prepared you, we're not going to run this,
but it's all on GitHub. So if you want to try out that
demo for yourself, just get them. That first thing we'll just create the database
and everything for you including all the schemas that we're going to need and all the end points. So let me close this. But if you want to get started
and get your hands on this, just download it from GitHub
and run that first Notebook and it will prepare and
set up everything for you. This is where it gets interesting. As you can see, Anna, we're still just
connected to my endpoint, to my Ben big data cluster here, which is the same endpoint that
we're going to see over and over again all across any
kind of these demos. This is where we're
getting the data from, and I'm just using Spark here, Spark and Pandas actually. So I'm reading that
CSV file from GitHub, filtering that on 2018 because that's the last year that has population
data for every single country. Then I'm just saving this
as a CSV file to my HDFS. That's one of the other
cool things here in Azure Data Studio because Azure Data Studio actually exposes the HDFS right here for
a big data cluster. So I can basically use
Azure Data Studio as my file browser for my HDFS
within my big data cluster. So this is really my
one-stop shop for everything that's
happening in my BDC. So I just ran that script, so this updated my CSV
file on my storage pool. The next one is a bit
more interesting. So let me just open this
on GitHub real quick. What John Hopkins does here, and this is from two hours
ago, on a daily basis, they just provide
CSVs globally and for the US for confirmed death and so on. Like I said before, you get it per country,
including coordinates, and then they just add a column for every single day for of these cases. While this might be convenient for them and an easy way
for them to provide it, it's not ideal for a SQL. So if you would do this
using traditional SQL, what you would need to
do is you would write some Dynamic SQL and then
you have to unpivot it. So what we do instead is, we will use Pandas and Spark again. There's a very neat function here
in Pandas, which is called melt. What Melt does is basically
a dynamic unpivot. If we put that in this function here, I can just call it
for the three files that I'm downloading: recovered,
deaths, and confirmed. What this will do
is, it will download the three files and convert them all into Parquet files and
convert them from wide into long. So it also gives me a little preview, just the first five rows
of each of these files. So we see, okay, data is as of
June 24th, that's yesterday. If we expand our HDFS, you will see the CSV
and the Parquet files. I'm usually saving CSV and Parquet. Even though I'm only going to be
using the Parquet in that case, but CSV is just easier to read
because it's unencrypted, it's uncompressed and all that. What you can already see here, every single file here is divided in two parts because I'm running
two nodes on my storage pool, so there's going to be to actual
machines doing the work for me. Since I'm more of a SQL guy, what I can just do now is, I can take all this, still connected to my Ben BDC, and I can just say, "Select top 10 for my Parquet file. COVID_recovered." I'm querying a Parquet file on my storage pool as if it
was a regular SQL table. I can take that data in another
query and push it to my data pool. So I'm pushing this to DP as a
data pool COVID development. Again, I can just select count on this just like a regular SQL table. So from a user perspective,
it really doesn't matter. I can even look beyond the covers. So if I do this, I can see I'm running that
query at my data pool. So what this will do is, it will run the query
at every single node. Again, there's two of them. So what you will see here is, it's not exactly evenly
distributed because the data pool does the
round-robin node record wise, but it does it in chunks. But as you can see, we're almost
equally distributed here. So again, when I'm
running this query, I will just have two nodes each of them doing half
the work therefore, getting way better performance. Then I can do all of these kinds of queries coming from the data pool. Since I'm in Azure Data Studio, I can also make this a chart and say, "What's my COVID development
for Germany, for example?" But then, that's the
actual real cool stuff. I can go ahead here and run
a query that is combining my data pool with my mapping table on my master instance with a CSV. So I'm live querying a CSV against a table that is actually sitting on two
distributed SQL servers against the local table, and they all look just like
a regular SQL Server query, and they come back in no time. Still, for those that say, "Well, T-SQL is not
really what I'm doing." I can also connect to
the same endpoint, and just do the same analysis
and the same querying in Python. That's the beauty of BDC. You pick your language and you-all
talk to the same endpoint, and you can do it all in
one tool as you like it. But still, since this is
a regular SQL Server, this could be exposed
to any kind of BI tool. I could connect to that using Excel, I could connect to that
using Power BI, or whatever, and just keep working and
wrangling with that data as if it was a regular SQL
Server, but it's not. >> Wow, Ben, that's
pretty powerful stuff. You've shown us how you are
getting data from GitHub, leveraging tools like Spark, using just T-SQL, and joining all
these different files together. You've also shown us how we can manage our File System in
HDFS from Azure Data Studio. There's a lot of stuff going in here and a lot of capabilities here. So thank you for sharing
all this with us. For our viewers, we'll
make sure we put a link to Ben's GitHub in the description. So we encourage you
to go check this out. This is super relevant data
especially, in these times. Thank you so much Ben for being here. >> Thank you for having
me. It was awesome. >> Awesome. For our viewers, if you like this video, please like this video or leave a comment for what you're
doing with big data clusters. We'll see you next
time on Data Exposed. [MUSIC]
