Hey, Cloud Gurus.
Welcome to this lesson on distributing data.
In this lesson, we're going to start off
by introducing distribution.
We're then going to talk about the different types
such as round robin, hash-distributed, and replicated,
and wrap everything up in a review.
To start off,
let's introduce what we mean by distributing data.
Azure Synapse Analytics spreads your data
across 60 underlying databases or distributions.
This is done for you automatically
as part of being a massively parallel processing system.
And it's something that you don't manage
or even have access to.
This is not the same as partitioning,
which is more about grouping similar data
to be accessed together.
Distribution can be thought of more
as a hardware storage concept.
The goal behind it is to distribute
in such a way that data movement is minimized.
And what we mean when we say data movement,
is shuffling data around and fetching it across the network
from different distributions in order to satisfy
the results of a query, such as a join.
If we don't get this right, then it can lead
to negative effects such as network inefficiencies,
increased I/O, and ultimately, slow queries.
And so this is definitely something
we want to take advantage of and get right.
There are a few different types of distribution
available to you.
As you may have guessed from the introduction slide,
we have round-robin-distributed tables,
and that is where the data is distributed evenly
in a random fashion.
Hash-distributed tables,
where data is distributed deterministically
by using a hash function.
And replicated, where we place a full copy of the table
on every single compute node.
Let's start off with round-robin-distributed tables.
This is the easiest way to get started,
and it's the default option if you don't specify one.
As we mentioned before,
this is an even distribution across the databases.
It assigns rows randomly at first
and then sequentially thereafter.
That's a very efficient way to get them there,
but it also means that the data
on any particular distribution may have no relation
to the other data that is there with it.
This gives us fast performance for the loads.
As we said, there's very little work in row assignment.
And so that makes it great for things like staging tables,
where you just want to load the data in there
as quickly as possible.
You can, however, have slower read performance.
That's because there is a great potential
for data movement here because the data
is not placed in logical groupings.
So when it goes to satisfy a query,
there's a good chance it's going to have to pull
from multiple distributions
in order to obtain the query results.
This type of distribution is best
for when there is no clear distribution key.
If you don't have something specific
that would work really well for hash-distributed,
then round robin is a great option.
If you don't have frequent joins with other tables
and therefore aren't pulling
from a bunch of different areas,
then it will be just fine.
If you want a uniform distribution,
round robin accomplishes that for you.
As we said, staging tables are great.
It's going to quickly load that information in there
as it randomly throws it wherever it needs to go.
And if you just need a simple starting point,
let's say that you're just beginning to experiment
with distribution, give round robin a try.
If it suits your needs, then you're off to the races.
But if you determine later
that a different strategy is better,
you can always start again.
But it's great for getting started quickly
with distribution.
Our next type is hash-distributed.
And this is like creating signs
that tell the rows which way to go.
It is deterministic,
and that means data is distributed
by the hash value of a column.
This is a column that you choose,
and so rows with the same data value and data type
for the hash key will always be assigned
to the same distribution.
This is because their hash value is the same.
The distribution column cannot be changed later,
so once you've chosen it,
you would have to recreate the table in order to change it.
For that reason and others,
it's important to select a column wisely.
Remember, you're trying to minimize data movement
while also distributing evenly.
And so a few tips for how to choose a great column.
Pick one that has many unique values.
This is going to help spread those rows
to many distributions.
Pick one that has few or no nulls.
Since null values all evaluate to the same hash value,
they're going to get dumped into the same bucket.
And so you could end up with very unevenly distributed rows.
Very similar for date columns.
You don't want to create hotspots if you have a lot of data
that's on one day and not on another.
And so it's probably best to avoid date columns.
Hash-distributed is best for large tables.
And when we say large, we mean that the table size on disk
is larger than 2 gigabytes.
Examples of these are large facts tables,
historical transaction tables,
and even large dimension tables.
It's also great for tables
with frequent inserts, updates, and deletes.
Here, we have a quick example
of creating a table with distribution.
You can see we have a number of columns.
And inside the WITH statement, we set our distribution,
in this case, equal to Hash.
And we're using the product Key
as the column for the hash function.
Our final type of distribution is replicated.
And this is quite different than the other two.
In this scenario, we have a full table
that we copy to every node.
This eliminates the need to transfer the data
back and forth for queries.
It does have a storage impact
because by copying into all of the nodes,
of course, you're taking up extra storage,
and it incurs overhead for writes.
Instead of being an entire solution,
it's really just 1 piece of the puzzle.
It's normally used in conjunction with the other methods.
And so what it's best for
is small lookup or dimension tables
that are frequently joined with larger tables.
I hate to say that's all it's good for.
It feels wrong to only have 1 check mark on a slide,
but it's a very specialized case.
Normally, you'll be using one of the other two,
either round robin or hash-distributed.
But replicated can also save you
from having to copy information back and forth
for the small tables that are frequently used.
By way of review,
Azure Synapse Analytics automatically creates and manages
60 distributions for your data.
You will normally choose between round robin,
which distributes randomly,
or hashed, which distributes deterministically.
And in some scenarios, replicated tables can be useful
for small amounts of information
that are frequently joined with larger tables.
That's it for this lesson.
I hope this helps you wrap your mind
around distribution in Azure Synapse.
If you have any questions,
please feel free to reach out to me.
And when you're ready for the next lesson,
I'll see you there.