Identity column definition lost using save as table

[lizou]

I found an issue:

For a table with an identity column defined.

when the table column is renamed using this method, the identity definition will be removed.

That means using an identity column in a table requires extra attention to check whether the identity column is still there, and the current seed value.

code example

spark.read.table(...) \

.withColumnRenamed("dateOfBirth", "birthDate") \

.write \

.format("delta") \

.mode("overwrite") \

.option("overwriteSchema", "true") \

.saveAsTable(...)

https://docs.databricks.com/delta/delta-batch.html#explicit-schema-update

example of a table with an identity column

CREATE TABLE table_with_identity_col (

RowKey bigint not null GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) comment 'identity',

UserName string

) USING DELTA;

[Hubert-Dudek · Accepted solution]

@lizou​ , Identity id is stored in schema and using overwriteSchema will for sure kill it ;-(

[Kaniz]

Hi @lizou​ , Just a friendly follow-up. Do you still need help, or @Hubert Dudek (Customer)​ 's response help you to find the solution? Please let us know.

[Anonymous]

Hey there @lizou​ 

Hope all is well! Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help. 

We'd love to hear from you.

Thanks!

[lizou]

try to avoid reload table, I found we can upgrade table version, and use rename column command

ALTER TABLE test_id2 SET TBLPROPERTIES (

  'delta.columnMapping.mode' = 'name',

  'delta.minReaderVersion' = '2',

  'delta.minWriterVersion' = '6')

ALTER TABLE test_id2 rename column a to a1;

the last issue looks like no way to avoid recreating the table if we need change the partition. change partition will require a reset identity column, GENERATED ALWAYS option, which is not ideal if the column is a key for a dimension and is being referenced by other tables.