Create Partitions in External Tables in Azure Synapse SQL Database (Serverless)

Creating Partitions in External Tables

Now that we’ve covered the why, let’s get to the how! Creating partitions in external tables involves defining a partition scheme using the `CREATE EXTERNAL TABLE` statement. Here’s an example:

CREATE EXTERNAL TABLE sales_data (
    sales_date DATE,
    product_id INT,
    quantity INT,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (sales_date) (
    PARTITION p_2020 VALUES LESS THAN ('2021-01-01'),
    PARTITION p_2021 VALUES LESS THAN ('2022-01-01'),
    PARTITION p_2022 VALUES LESS THAN ('2023-01-01')
)
FORMAT = PARQUET
LOCATION = 'https://myblobstorage.blob.core.windows.net/salesdata/';

In this example, we’re creating an external table `sales_data` with four columns: `sales_date`, `product_id`, `quantity`, and `amount`. We’re partitioning the table by the `sales_date` column using a range partition scheme. Each partition is defined using the `PARTITION` keyword, and we’re specifying the partition values using the `VALUES LESS THAN` clause.

Range Partitioning

In the previous example, we used range partitioning to divide the data into partitions based on the `sales_date` column. Range partitioning is useful when you have a column with a continuous range of values, such as dates or numbers.

To create a range partition, you need to define the partition boundaries using the `VALUES LESS THAN` clause. For example:

PARTITION BY RANGE (sales_date) (
    PARTITION p_2020 VALUES LESS THAN ('2021-01-01'),
    PARTITION p_2021 VALUES LESS THAN ('2022-01-01'),
    PARTITION p_2022 VALUES LESS THAN ('2023-01-01')
)

In this example, the `p_2020` partition includes all data with a `sales_date` less than ‘2021-01-01’, the `p_2021` partition includes all data with a `sales_date` less than ‘2022-01-01’, and so on.

Hash Partitioning

Hash partitioning is another type of partitioning that distributes data across partitions based on a hash function. This type of partitioning is useful when you have a column with a large number of distinct values, such as a product ID or a customer ID.

To create a hash partition, you need to define the partition count using the `PARTITION BY HASH` clause. For example:

PARTITION BY HASH (product_id) (
    PARTITION p_1 NUMPARTITIONS = 4,
    PARTITION p_2 NUMPARTITIONS = 4,
    PARTITION p_3 NUMPARTITIONS = 4
)

In this example, we’re creating a hash partition with three partitions: `p_1`, `p_2`, and `p_3`. Each partition is divided into four sub-partitions using the `NUMPARTITIONS` clause.

Best Practices for Creating Partitions

When creating partitions in external tables, keep the following best practices in mind:

• Choose the right partition column**: Select a column that has a clear partitioning strategy, such as a date or an ID column.
• Define partitions based on data distribution**: Analyze your data distribution and define partitions that ensure each partition has a relatively even amount of data.
• Keep partitions small and manageable**: Aim for partitions with a size of 1-10 GB to ensure efficient query performance and data management.
• Use partitioning for data that changes frequently**: Partitioning is ideal for data that changes frequently, such as daily or monthly sales data.
• Avoid over-partitioning**: Too many partitions can lead to increased storage costs and decreased query performance.