Data Engineer starter pack (Part 2)
In this article we are going to discuss about
- Dedicated SQL Pools
- Types of distributions
- When to use which distribution
- Best fit column for hash distribution
- Worst fit column for hash distribution
- Row Store index
- Column Store index
- Usage of these indices
Dedicated SQL Pools
In general Dedicated SQL pool refers to the enterprise data warehousing features that are available in Azure Synapse Analytics. In a cloud data solution data is ingested into big data stores from a lot of resources and from here Hadoop, spark and ml algorithms trains the data. Now we want to store this data in an efficient manner for further proceedings. Here comes dedicated SQL Pools.
When all this data is ready for complex analysis dedicated SQL Pools are used to store this data. It uses PolyBase to query the big data stores. Polybase is the most efficient and scalable way to get data from different databases. These dedicated sql pools stores data in columnar storage (will be discussed later in this article). Once data is stored we can run analytics at massive scale. Compared to traditional databases its performance is unimaginable.
So what makes this dedicated SQL Pools efficient?
The way dedicated sql pools store data and the nodes present in it and mainly Massively Parallel Processing (MPP) makes it efficient.
Storage of data:
In dedicated sql pools each distribution is a Azure sql database. These distributions contains distributed tables. Here the rows of a table are segregated into different distributions. There are mainly 3 methods to do this segregations.
i) Hash distribution
While distributing the rows using hash distributions we have to choose a column on which hash has to be applied. For every new entry the hash of the column will be calculated and the row is moved to a particular distribution. Columns with same data type and value will always be mapped to same distribution.
When processing queries involves distributed tables the query is internally executed on each distribution in parallel. Result of the query will provided only after completion of execution in all nodes. So the execution speed of the query is as fast as the execution speed of the slowest distribution. So we have make sure that there is less skewness in the distribution and this happens only by choosing the hashing column wisely.
ii) Round Robin distribution
While distributing the rows using Round Robin distributions choosing a specific column is not needed. First row is randomly inserted into one of the distribution and remaining rows are inserted in round robin fashion. This method provides skewness ~ 0 i.e rows are evenly distributed across each distribution.
This method helps in faster loads compared to hash distributed method as there is no overhead of calculating the hash of the column. But one of the main disadvantage is there will be a lot of data movement at the time of query execution.
In general dimension tables or the tables that does not have any column that can distribute rows evenly will use round robin distribution.
iii) Replicated tables
Replicated tables are those tables that are stored in all the compute nodes. This type of storage is used for small tables or look up tables where all the other distributed tables look for some columns here. This decreases the data movement operations with a trade off of storage. But the DML operations becomes slow in replicated tables as data has to be updated in every replication of it.
When to use which distribution?
To use a hash distributed table we need a column that should have atleast 6o distinct values so corresponding rows of each column hash might be distributed evenly across each distribution. We should consider using hash distribution when the size of the table is more than 2GB and if the table has frequent insert, update and delete operations.
Get Pavan Krishna’s stories in your inbox
Join Medium for free to get updates from this writer.
If a table doesn’t have any column on which we can distribute columns we can use round robin distributions and if a table is a dimension table or a big look up table these tables might join with many other fact tables so optimizing for one join might not be the best idea, in this type of cases we can use round robin distributions.
If we are unsure about which type of distribution to use we can always start with round robin distribution and if required we can easily redistribute data with a hash key using CTAS (create table as select) methods.
Best fit column for hash distribution:
Let us consider a case with a fact table having many columns and is distributed on application_id and a dimension table also having a column application_id with same data type and also distributed on application _id in this case all the rows with same application_id will be mapped to same distribution so when we use a join operation on these tables there will be no data movement ( a costly operation) this is because rows of fact table will join only with the rows of dim table that are present in same distribution.
Similarly we can choose the column which is frequently used in group by or distinct or having clauses as the distribution column.
Worst fit column for hash distribution:
i) Considering date column for hash:
In general for analyzing data we use specific condition to get some required data and mostly we use date column like we need data from x date to y date. If we distribute rows based on date then all rows with same date will be moved to single distribution. So whenever we perform operations related to date most of the load will be on less number of nodes and remaining nodes will stay ideal. This leads to performance degradation. So this is not the correct way to choose a column that is to be hashed.
ii) Considering columns having less unique values:
Generally, there are 60 distributions in a dedicated SQL pools and columns with same values are mapped to same distributions. If there are less than 6o unique values then some distributions will not have any work. So there should be a minimum of 60 distinct values for a column that is considered for hashing.
iii) Considering columns having many null values:
Similarly columns with many null values should not be considered for hashing because all those rows that are hashed on null will reside in single distribution and there will be a lot of load on that node which leads to increase in performance issues.
Index in dedicated SQL Pools:
In general databases we store data using row store indices but with these OLAP databases we mainly use column store indices.
So what are these indices?
Row Store index (row storage):
In a database deep down the rows are stored in 8KB files if the size exceeded then rows will be stored in a new 8KB file and it goes on. Without indices searching of a particular row takes a lot of time as it uses sequential search. Then came indices into play. We generally apply index on a column (possible with multiple columns) then all the rows are sorted with respect to that column and whenever a new row is inserted it finds its place in the sorted order and it gets stored there. So if we want to get some specific rows with a where condition on the column we used as index on, we get the result quickly because internal program now uses binary search instead of sequential search. This index is called clustered row store index.
If we want to query some rows with some other column used in where condition then it again uses sequential search. So to avoid this we have some other index called as non-clustered row store index. When we use this index a separate storage is created for this column values and are sorted there and this storage also contains an address that maps to remaining part of the row in the 8KB file. So if we want to search on the column on which non-clustered index is applied, it first checks the value in the new storage and then gets the full row from the 8kb file with the address present in that new storage. In this way it gets the benefit of binary search.
Column store index (columnar storage):
In row store type these 8KB files are filled with entire row but when coming to column store type entire column values are stored in one 8KB file if space limit exceeded a new file is created for this column and all columns are stored separately. When we are using clustered column store index all the column values are stored in this manner but when using non clustered column stored index we have to specify the columns on which we want to apply column store and remaining data is stored in row store type.
Usage of these indices:
When we are using OLTP databases where we require all the information of a particular row we use row store index because we get all the data at a time and there is no need to check for many files to get data of all the columns but when we are analyzing the data we require the data of an entire column(s) and not the data of all columns, so we use column storage type where entire column data resides at single place.
One of the best benefit of using Column store index is compression of data. It compresses the data up to 10 times. We mostly use column storage type for fact tables where columns data is generally repeated. If we use row storage for these type of table where we require only values of some columns, entire table is loaded into memory and gives us only the columns we require so a lot of resources are wasted. So Column store indices are best fit for these type of fact tables.
This sums up the basics of dedicated SQL Pools.
If you found this article helpful. Do follow me on medium. Any type of constructive comments or feedback is encouraged.
Let’s get connected via Linkedin for any further discussions on big data and data engineering topics.
References:
