(electronic music)
- Coming up on a special edition of How We Built It,
we're joined by UK-based data engineering consultant endjin,
who specialize in big data analytics solutions
for their customers in ocean research,
financial services and retail industries
to see how they're evaluating Azure Synapse
for on-demand serverless compute and querying.
So I'm joined today by Jess Panni,
Principal and Data Architect at endjin.
Welcome to Microsoft Mechanics.
- Thank you, it's great to be on the show.
- Thanks for joining us today,
and let's take a step back though.
Before we get into this, just a quick recap.
If you're new to Azure Synapse,
this is Microsoft's limitless analytics platform
that really brings together
enterprise data warehousing and big data processing
into a single service.
In fact, you can learn more by watching
our introduction to Azure Synapse
with John McIntyre recently
at aka.ms/mechanicssynapse.
All right, so Jess, your company endjin,
really started your journey with Azure Synapse
for data exploration.
You're using things like SQL serverless
for kind of on-demand compute.
And also the data visualization capabilities,
but can you tell us what's behind this?
- Yeah, so we've been working
with a number of different customers
with their big data challenges
across a range of different industries.
We try and make their data more accessible
and to take away the friction
of getting insights from their data.
Now we also look after their security
and governance processes and needs as well.
So we've been putting SQL serverless
through its paces
on some of their most demanding workloads
to see how it sort of stacks up
with other services and technologies.
- All right so Jess, let's make this real for people.
Can you give us an example about how this would work?
- Yeah absolutely sure.
I can give you an example.
So imagine you're an ISP or a Telco
and you're providing broadband services to people.
And you want to be able to identify
who and where problems are occurring
on your network so that you can prioritize
where to send your network engineers.
Now, clearly Telco doesn't have a direct connection
into your home,
but there's a network of interconnected devices
as part of their network infrastructure
between you and them,
which introduces multiple points of failure.
Now these devices are constantly producing telemetry
which we typically could store in an Azure Data Lake,
which in this particular case
we're collecting about 60 gigabytes of data per day.
We then need to provision a Spark environment,
configure it, secure it,
and connect it to the Data Lake.
And only then can we spin up a cluster
which would usually comprise of at least three VMs
for the duration of the exploration and development.
And of course if you're new to all this,
you need to get skilled up
in something like Python or Scala.
- And that's really familiar territory for me
because in IT then we have to use that,
it's a dependency on us
which might mean additional time
or cost to get those resources spun up.
- Yeah absolutely, that can take days, weeks, months even.
And once you've found, you know,
the insights and how to extract those insights
you need to operationalize the solution
and build data pipelines
that you're continuously monitoring data as it arrives
so that you can measure and find issues in the future.
- Right, and those data pipelines
also themselves pose a challenge.
Because they might be running on different
infrastructures and then bringing all of that together
can be hard.
So how do things then change with Azure Synapse?
- Yeah I mean, I can show you how you can achieve
all of this securely
from a single environment
without spinning up any pre-provisioned resources,
and using the language that you probably already know.
Now there are a number of steps
we need to go through,
and the first of those steps is understanding
what data we have.
So here we are in Synapse studio,
and one of the great things about Synapse studio
is we can now browse the files and folders
within our Data Lake.
And you can see here the contents
of one particular folder.
And we can see we've got a network telemetry folder
which looks interesting.
So let's go and have a look inside there.
Now we can see we have data partitioned by date,
and then partitioned by hour,
and finally we can see the raw files
which are in this case in Parquet format.
Now if I wanted to analyze these files
traditionally I'd have to run a Spark cluster,
or I'd need to download those files.
But with SQL serverless all I need to do
is go and select the files,
right click on them and I can select top 100 rows.
So I'm just going to run this,
and you can see we're running a SQL query over,
you know, one or more files
in the Data Lake without any provisioned servers.
But what if we wanted to query
over all our telemetry
without worrying about individual files
or where they live in the Lake.
And I can show you an example.
So here we've got a view that I created called Radius Raw.
It represents all our network telemetry.
And I can use this query to find
the total number of events
that we have received on a single day.
So if I run this,
we can see that we've received about
50 million events on that day.
Now this view is completely reusable
we've hidden all the details
of the individual files,
and even the formats of the data that we're querying.
And as I say all of this is being done
without using any provisioned resources.
It's fully serverless
and I didn't have to spin up a single thing.
Well, I've got a slightly more interesting example
that I can show you.
So here's a query that returns network issues
for a given piece of equipment
by the number of customers who were impacted.
And we're joining these issues
over another set of files
which provide more information about
the total exchange of the devices
including geographical location.
So, we are limiting this entire data set
down to a single day.
And also just down to the top sort of 100 customers
that are impacted.
So if I run this,
I just want to point out that the exchanges view
is actually reading as CSV file,
whereas the other view we're querying over
is raw Parquet,
so we're actually joining data stored
in completely different formats.
And as you can see we've got some results quite quickly.
Now the next thing to do is to start
sort of you know, visualizing it,
seeing what's in that data set,
how can we represent that table in a better way.
Now what I can do
is I can go and have a look at down in the results view
and change the view to a chart view.
And I'm just going to make a couple of changes here.
I'm just going to remove some of these columns
and just leave the number of events
and the number of distinct customers.
And now what I've got
is I've got a timed-sheared series
showing when peak issues are occurring,
the number of distinct customers when that occurs,
and obviously the number of events.
So, now that we've discovered
how to extract insights we need to operationalize these.
And to do this,
we would typically run this query
continuously as new data arrives
and we store the data in the Data Lake
to make it easier for upstream processes and visualizations.
So I can show you that here as well.
What we've got is we've got an expression,
a CTAS expression,
and if you are familiar with SQL Database
or SQL Data Warehouse you may have seen this in the past.
Now what this is doing is it's running the same query,
but it's writing the output
to a location in the Data Lake.
We're writing to a folder called Output,
and within that a subfolder called Network Health.
And now what we can do
is we can go back into our Data Lake,
I'm just going to drop out
and you can see we've got a new folder called Output,
a new folder called Network Health,
and here are all our issues ready to go.
- So now you've kinda got the data,
the visualizations to really see
where those different points of failures are
on the network to be able to dispatch
the right field techs but what would I do next?
- Yeah, so the next thing we need to do
is we need to make sure that our field engineers
have good visualizations that continuously running,
pointing them at the issues
so they can react based on customer impact.
Now Power BI is a great data visualization tool
and the good news is that it comes integrated with Synapse,
but one of the first challenges is,
how do we get data into Power BI.
Now Power BI comes with a whole bunch
of great connectors and because we're just talking SQL here,
we can use the existing SQL connectors
with SQL serverless without any changes at all.
So let's have a look in our Develop hub.
In our Develop hub you'll see
we now have a Power BI option
and we can see our Power BI workspace that we set up.
And from there, we can go and directly query
the Power BI data sets within Synapse.
So I'm going to add a new data set
and you can see that
from Power BI's perspective
SQL serverless looks like any regular SQL database.
And I've got all my views in a database called TelcoDemo
and I can download what's called a PBIDS file,
or PBIDS file, which is a Power BI file
which has all the connection detail setup,
so I can immediately start querying
our SQL serverless views and tables
without any other problems or delay.
So, to save a bit of time,
what I've done is I've already done that
and I've pushed up a data set with our network issues.
And what I can do is
I'm going to create a new Power BI report.
And you'll see the report experience is now embedded in.
Here's our network health data from before,
and I'm just going to go and drop a map
onto our report and we're going to start looking that up.
So I'm going to pull the latitude in,
the longitude in,
and I'm going to make the size of my data points
the distinct custom account.
So you can see now we've got a nice hot spot graph.
So we going to make this a little bit more interesting,
we're just going to drop in a tree map for some navigation.
A little bit bigger.
And we're going to do that by exchange
and again by the number of customers impacted.
And now what I can do
is I can start to navigate around
the various different exchanges
and see exactly where my issues
are occurring at any given time.
- And this is really great, but I know a lot
of people watching are probably using Spark tables,
so in that case, do I have to spin up
and kind of run a Spark cluster against that data,
or can I use SQL serverless to do that?
- Right, that's a really good question.
So, Synapse comes with its own
managed Spark environment
which is great if you've got existing Spark assets
and skills, but SQL on-demand also plays
quite well with it.
So I can show you an example of how that all works.
So what I'm going to do is I'm just going to go back
to my Data hub,
and you'll see that we've got our databases
and if I open that up you'll see
we've got a special database there called default Spark.
Now if I open that up,
you'll see I've got a bunch of tables.
Now these are Spark tables.
These are not views, these are not SQL serverless views.
These are Spark views
and that last view there, radius raw,
is a Spark table that I previously set up and created.
Now in the past what I would have to do
is spin up a cluster to actually view the contents
of that table, but with SQL serverless
all I need to do is start querying it straight away.
So here we're doing it again,
doing another select top 1,000 rows
and here I am querying a Spark table
without a cluster running at all.
- All right, so we've been talking a lot about
serverless capabilities really for that on-demand compute,
but when would we start using provisioned resources then?
- So it depends really.
We're finding with SQL serverless
that we're getting really good performance
and given its pay-per-query,
it also allows us to attribute cost-to-business units.
But you know, there may be a time
when you need the performance of SQL pools
and because we're talking SQL in both cases,
it actually makes it easy to transition
from SQL serverless to SQL pools.
Remember the query I ran before?
I can show the top here
where we're switching between SQL serverless environments
and our SQL pool environments.
I'm just going to run that
and while that's running
I'm just going to explain what I've done.
I pulled the same raw data into a SQL pool.
And I've created a similar set of views
in the SQL pool environment.
And now I'm able to simply switch,
run my existing original query
and I'm getting exactly the same results,
but this time using pre-provisioned resources.
- All right, this is really great stuff.
Thanks so much for sharing
kind of what you've been doing with Azure Synapse, Jess,
but any tips that you would give people
that are watching from home
about how they can start using Azure Synapse?
- Yeah, sure, well historically analytics
has been seen as big scary, big budget
kind of exercise,
and Synapse provides a path to get you
productive really quickly,
but also allows you to graduate
to more advanced services as and when necessary.
So I suppose I'd say look for the simple opportunities
within your data and grow
your analytic solutions from there.
- Good tips.
Thanks again for joining us today, Jess,
and also if you liked hearing from endjin,
please check out the rest of our How We Built It series
at aka.ms/AzureSynapseSeries
to learn from early adopters
who are also kicking the tires with Azure Synapse.
That's all the time we have for this show.
Thanks for watching.
We'll see you next time.
(electronic music)