Microsoft Azure Data Warehouse and Distribution Keys

I am in the process of increasing the window of time of a data propagation line from a week to 12 weeks. The issue experienced is that the loading time for the process was not scaling so well, at least  until yesterday. In trying to optimize the process, I have learned a number of important things about Azure SQL Data Warehouse.

An OLTP database developer or DBA cannot think of Azure SQL Warehouse in the same way as an OLTP or Monolithic system. The most important thing to understand about Azure Warehouse is that when a developer engages it, they are leveraging up to 60 hidden database systems behind the scenes through 1 to many database controllers (controlled by the DWU). DWU stands for Data Warehousing Unit - a standardized metric which groups all hardware resources together in a relative manner and increase them proportionally to the increase in the DWU configuration of the cloud based warehouse (used for billing). This distributed processing and storage is a very important factor when choosing a distribution key for a table and designing objects and mechanisms for bulk operations.

A distribution key, one of many distribution options, is a table creation option which tells Azure HOW to distribute the data for a given table ACROSS those 60 databases. So if one chooses to distribute 100 million records on low cardinality field containing only 2 distinct values and NULLs, then those 100 million records will be spread across only 3 databases. Therefore, at the very least, a high cardinality field or a surrogate key should be chosen to distribute the data EVENLY across the nodes to leverage the true power of parallel processing.

If a developer is smart, they will make sure the distribution key they select is the same as those of other tables. To explain what this means, I will need to mention how the database performs distribution. The database will use a hash function on the value in the selected column to relegate the record to one of sixty "bucket" databases, this means that a pk/fk value shared by 2 tables can be hashed and thus the database will physically store the records of those 2 tables within the same node. Why does this matter? Well, it means when those 2 tables are joined, the database engine will not need to break context and contact a different node in search of records qualifying the join - it merely carries out a like for like join within its own node. Multiply this by 60, and one has 60 database performing disparate joins independently and in parallel and returning records to the Azure Warehouse controller as they're matched. Pretty Neat.

As for the distributions of data and skewing (a term used to highlight the case of unevenly distributed data), I have a number of queries which detail the problem and I will be posting them in time.

A more descriptive post (including visuals) can be found here

Database Source Code Output

Today the development databases are inaccessible. The network team perform a migration over the weekend and now most of the development teams are unable to do their work. I've taken this opportunity to note something down. Its a mechanic I've used for a number of years when working in a Windows environment.

First, a bit of context. I spent several years working with Oracle in Linux and Unix. For those years, I made use of parametrized shell scripting and SQLPLUS to make short work analytical queries for the purposes of resolving domain/business problems quickly without the need to write out SQL. I had become accustomed to scripting in a Linux environment and automating my tasks. Moving to a Windows environment effectively clipped my wings as I was no longer able to use scripts and work with immediacy. Or so I thought. Within a week of working with SQL server, I made a fantastic transition into using Linux shell scripts in the windows environment using Linux emulator and SQLCMD. One of the positive effects is that I can make use of native Windows applications through the shell script. One of these little inventions is detailed below. This method can also be used with SQLPLUS in Windows.

Enter SourceDump
The sourcedump function is one that takes in a module name and dumps out the entire obejct into a .sql file. Once dumped, it is opened with Notepad++.
1. The first part required is a script that dumps the module out. This is a SQL database so I make use of sys.all_sql_modules instead of Oracle’s dba_source.

set nocount on
use $(Database)
go

select top 1 
       definition
  from sys.all_sql_modules (nolock)
 where object_name(object_id) like '%$(search_phrase)%'
   and object_id > 0

I place this query into a file called
c:\mine\sql\r_sql_source_dump.sql

2. The next task is to have a folder to store the files output from the script.
c:\mine\sourcedump

Once these 2 requirements are met, I move onto the shell configuration and functions.
 
 
3. In the Linux emulator, open the .profile file with vim editor
[rayb.] ➤ vim $HOME/.profile

2. Adjust the $PATH variable to increase its scope to key SQL directories (SQLCMD + DLL's) and include a text editor (Notepad++ is what I make use of)
export PATH=$PATH:"/drives/c/Program Files/Notepad++":"/drives/c/Program Files (x86)/Microsoft SQL Server/Client SDK/ODBC/130/Tools/Binn/":"/drives/c/Program Files (x86)/Microsoft SQL Server/130/Tools/Binn/":"/drives/c/Program Files (x86)/Microsoft SQL Server/130/DTS/Binn/":"/drives/c/Program Files (x86)/Microsoft SQL Server/130/Tools/Binn/ManagementStudio/":"/drives/c/Program Files/TortoiseSVN/bin":"/drives/c/Users/rayb/AppData/Local/Microsoft/WindowsApps"

3. Add the following shell function to the .profile file
sourcedump()
{
  sqlcmd -y 0 -w 65000 -S "<SERVER_IP>,<INSTANCE_PORT>" -v Database="$1" -U sa -P <PASSWORD> -v search_phrase="$2" -o "c:\\mine\\sourcedump\\$2.sql" -i c:\\mine\\sql\\r_sql_source_dump.sql
  notepad++ "c:\\mine\\sourcedump\\$2.sql"
}

4. Resource the .profile file
[2017-05-22 09:14.04]  ~
[rayb.       ] ➤ source $HOME/.profile

[2017-05-22 09:14.09]  ~
[rayb.        ] ➤

5. Output a module