1

I'm trying to modify the partition of existing delta table. I know how to do that using Data Frame API. I need to achieve similar thing using SPRAK SQL. anyone help me with this ?

when I tried running below command

ALTER TABLE MY_DB_NAME.MY_TABLE_NAME ADD PARTITION (PARTITION_COLUMN='PARTITION_VALUE')

I'm getting below error

AnalysisException: Operation not allowed: `ALTER TABLE ADD PARTITION` is not supported for Delta tables: `spark_catalog`.`my_db_name`.`my_table_name`;
AlterTableAddPartitionCommand `spark_catalog`.`my_db_name`.`my_table_name`, [(Map(partition_column -> partition_value),None)], false

will it be possible to do the same in SPARK SQL ?

Jacek Laskowski
  • 72,696
  • 27
  • 242
  • 420
MJ029
  • 151
  • 1
  • 12

2 Answers2

1

Will this work for you?

REPLACE TABLE MY_DB_NAME.MY_TABLE_NAME
  USING DELTA
  PARTITIONED BY (PARTITION_VALUE)
AS
 SELECT * FROM MY_DB_NAME.MY_TABLE_NAME

Link has more examples in python/scala and for external/not-managed tables.

Kashyap
  • 15,354
  • 13
  • 64
  • 103
0

You can't directly alter the partition of the delta table as this is something that is determined when you write the data. So if you really want to do it on SQL, you should try actually writing a new table :

CREATE OR REPLACE TABLE MY_DB_NAME.MY_TABLE_NAME_2 
USING DELTA 
PARTITION BY ("PARTITION_VALUE") 
AS 
  SELECT * FROM TABLE MY_DB_NAME.MY_TABLE_NAME
Kashyap
  • 15,354
  • 13
  • 64
  • 103
Xela
  • 158
  • 9
  • *"You can't directly alter the partition"* -- you can. – Kashyap Jun 12 '23 at 03:45
  • Can you elaborate @Kashyap ? The only way, for me, is to rewrite the entire table. Référence : https://stackoverflow.com/questions/54989165/databricks-how-to-change-a-partition-of-an-existing-delta-table – Xela Jun 12 '23 at 09:17
  • 1
    `REPLACE TABLE table1 AS SELECT * FROM TABLE table1`. Like the difference between `sed` and `sed -i`. Which re-writes the schema of existing table instead of creating a new one. – Kashyap Jun 12 '23 at 15:36
  • It's not actually altering a table then, it re-writes it all as I said above, you need to rewrite the entire table. Thanks for the answer, it's indeed better to use REPLACE than creating a new table. – Xela Jun 12 '23 at 16:48
  • "It's not actually altering a table then" I guess we can argue on the semantics. It re-writes behind the scenes, but from end user pov it's an update, just another entry in table's transaction history. – Kashyap Jun 12 '23 at 16:53