topic Re: Update data type of a column within a table that has a GENERATED ALWAYS AS IDENTITY-column in Data Engineering

Update data type of a column within a table that has a GENERATED ALWAYS AS IDENTITY-column

Abbe — Tue, 20 Dec 2022 15:04:50 GMT

I want to cast the data type of a column "X" in a table "A" where column "ID" is defined as GENERATED ALWAYS AS IDENTITY.

Databricks refer to overwrite to achieve this: https://docs.databricks.com/delta/update-schema.html

The following operation:

(spark.read.table('A')

.withColumn("X", col("X").cast("string"))

.write

.mode("overwrite")

.option("overwriteSchema", "true")

.saveAsTable('A')

)

Returns this error message:

AnalysisException: Providing values for GENERATED ALWAYS AS IDENTITY column ID is not supported.

How can I cast columns for tables with GENERATED ALWAYS AS IDENTITY-columns? The same applies to renaming columns.

Re: Update data type of a column within a table that has a GENERATED ALWAYS AS IDENTITY-column

Abbe — Tue, 20 Dec 2022 15:25:37 GMT

Looks like it works when using GENERATED BY DEFAULT AS IDENTITY instead. There's no way of updating the schema from GENERATED ALWAYS AS IDENTITY to GENERATED BY DEFAULT AS IDENTITY, right? I have to create a new table (and then insert it with data from old table)?

Re: Update data type of a column within a table that has a GENERATED ALWAYS AS IDENTITY-column

RajuBolla — Thu, 02 May 2024 13:32:16 GMT

Update is not working but delete is when i changed to DEFAULT property

AnalysisException: UPDATE on IDENTITY column "XXXX_ID" is not supported.