Azure Synapse Analytics: Can I use non-unique column as hash column in hash distributed tables?

user_12732076 0
Viewed 809

I'm using Dedicated SQL Pools (AKA Azure Synapse Analytics). Trying to optimize a fact table and according to documentation FACT tables should be hash distributed for better performance.

Problems is:

  • My fact table has a composite primary key.
  • You can specify only column as hash distribution column.

Can I use one of those columns as distribution column? Any one of the columns would have duplicates, though they are all NOT NULL.

CREATE TABLE myTable
(
    [ITEM] [varchar](50) NOT NULL,
    [LOC] [varchar](50) NOT NULL,
    [MEASURE] [varchar](50) NOT NULL
 CONSTRAINT [PK] PRIMARY KEY NONCLUSTERED 
    (
        [LOC] ASC,
        [ITEM] ASC
    ) NOT ENFORCED 
)
WITH
(
    DISTRIBUTION = HASH([ITEM]),
    CLUSTERED COLUMNSTORE INDEX
)
azure-synapse indexing query-optimization tsql sql-server
1 Answers

Yes, you can! You can use any column as a hash distribution column, but be aware that this introduces a constraint into your table: you cannot drop the distribution column.

There are two reasons to use a hash distribution column: one is the to prevent data movement across distributions for queries, but the other is to ensure even distribution of data across your distributions to ensure all the workers are efficiently used in queries. Hash-distributing by a non-skewed column, even if not unique, can help with the second case.

However, if you do want to distribute by your primary key, consider creating a composite primary key by hashing together the different columns of your composite primary key. You can hash-distribute by your hashed key and this will also hopefully reduce data movement if you need to upsert on that hashed key later.

user_118233810
Related