Dynamic Partition Value SQL Server Azure Synapse

Question

How can I set the Table with dynamic partition ? is it possible to do it on Azure Synapse?

This is my script

CREATE TABLE dashboard_table WITH (
    DISTRIBUTION = HASH(product_id), 
    PARTITION (partition_key RANGE RIGHT FOR 
        VALUES (20200101,20200102,20200103,20200104,20200105,20200106) AS (
            SELECT *
            FROM table_x
        )

Because when I try to create a partition by this example script it doesnt work on synapse

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000);

user_1527504 · Answer

Azure Synapse Analytics dedicated SQL pools (and Azure SQL Data Warehouse before it) do not support the CREATE PARTITION SCHEME or FUNCTION commands. Instead build the partition definition into your CREATE TABLE as in your first example. If you want to do this dynamically you will need to use dynamic SQL. This is the example from the documentation:

CREATE TABLE [dbo].[FactInternetSales]
(
    [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,   DISTRIBUTION = HASH([ProductKey])
,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                    (20000101,20010101,20020101
                    ,20030101,20040101,20050101
                    )
                )
);

I notice you are partitioning by day. Just remember less is more with Synapse partitioning. You need at least 1 million rows per distribution (ie 60 million) for it to become effective. See the documentation for notes on partition sizing:

When creating partitions on clustered columnstore tables, it is important to consider how many rows belong to each partition. For optimal compression and performance of clustered columnstore tables, a minimum of 1 million rows per distribution and partition is needed. Before partitions are created, dedicated SQL pool already divides each table into 60 distributed databases.

Any partitioning added to a table is in addition to the distributions created behind the scenes. Using this example, if the sales fact table contained 36 monthly partitions, and given that a dedicated SQL pool has 60 distributions, then the sales fact table should contain 60 million rows per month, or 2.1 billion rows when all months are populated. If a table contains fewer than the recommended minimum number of rows per partition, consider using fewer partitions in order to increase the number of rows per partition.

Dynamic Partition Value SQL Server Azure Synapse

1 Answers