Add partition to existing Delta Table

[mrojze]

I have a Delta table with data. All columns have values.

I want to add a partition.

Is that possible? or I can only create partitions at creation time?

This is what I have tried and the error I am getting:

%%sql

ALTER TABLE Table1 ADD PARTITION (part_1)

Error: Found an empty partition key 'part_1'

[Anonymous]

Hi @mrojze ,
Thanks for using Microsoft Fabric platform.
In Delta Lake, partitions are typically defined when the table is initially created, and you cannot directly add or modify partitions to an existing Delta table using built-in SQL commands like "ALTER TABLE." However, you can achieve this by creating a new Delta table with the desired partitions and then inserting data from the existing table into the new one. Here's how you can do it:

1. Create a new Delta table with the desired partitions:

CREATE TABLE NewTable
USING delta
PARTITIONED BY (part_1 STRING, part_2 STRING)

Replace `(part_1 STRING, part_2 STRING)` with the actual partition columns you want to use.

2. Insert data from the existing table into the new table, specifying the partition columns:

INSERT INTO NewTable
SELECT *, 'partition_value' AS part_1, 'partition_value' AS part_2
FROM ExistingTable

Replace "partition_value" with the actual partition values you want to use.

This process effectively creates a new Delta table with the desired partitions and copies the data from the existing table into it. 

And the error which you are experiencing regarding empty partition can be corrected by using the below code:

ALTER TABLE Table1 ADD PARTITION (part_1 VALUES (2023-09-13));

You have to specify the value for the partition. But by using ALTER TABLE you cannot create a partition for an existing Delta table. Hence try to use the Insert and Select queries mentioned above for solving your issue.

Hope this helps. Let us know if you have any other questions.

[Anonymous]

Hi @mrojze ,

Following up to see if the above answer was helpful. If this answers your query, do click `Accept as Solution` . And, if you have any further query do let us know.

[mrojze]

Thanks. This makes sense now, but what I am trying to do it to save the effort of creating a table manually, especially if the table is wide.

Could I create a partitioned table from another existing table?

[puneetvijwani · Accepted solution]

@mrojze  you can try something like this 

CREATE TABLE NewTable
USING delta
PARTITIONED BY (part_1)
AS SELECT *, some_column AS part_1 FROM ExistingTable

or more simple

df = spark.read.table('ExistingTable')

df = df.withColumn('part_1', df.some_column)

df.write.partitionBy('part_1').format('delta').saveAsTable('NewTable')