topic Re: Identity column definition lost using save as table in Data Engineering

Identity column definition lost using save as table

lizou — Sun, 08 May 2022 15:41:24 GMT

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;

Re: Identity column definition lost using save as table

Hubert-Dudek — Mon, 09 May 2022 06:54:53 GMT

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

Re: Identity column definition lost using save as table

Anonymous — Mon, 20 Jun 2022 17:22:11 GMT

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!

Re: Identity column definition lost using save as table

lizou — Thu, 10 Nov 2022 14:47:19 GMT

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.