cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Identity column definition lost using save as table

lizou
Contributor II

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;

1 ACCEPTED SOLUTION

Accepted Solutions

Hubert-Dudek
Esteemed Contributor III

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

View solution in original post

4 REPLIES 4

Hubert-Dudek
Esteemed Contributor III

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

Kaniz_Fatma
Community Manager
Community Manager

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
Not applicable

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
Contributor II

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.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!