topic Re: Not able to insert into identity column through spark in Data Engineering

Not able to insert into identity column through spark

Shivaprasad — Fri, 13 Dec 2024 17:48:10 GMT

I have a delta table with identity column and not able to insert data using spark. I am using 15.4LTS. any idea what needed to be done

Table Name : account

Column Def: account_dimension_id BIGINT GENERATED BY DEFAULT AS IDENTITY,

df = spark.read.format("csv").load("abfss://databricks-storage@sa14127e1dv0101.dfs.core.windows.net/catalogs/data-relationship-management-drm/onprem_acct_dimension.csv");

df.write.format("delta").mode("append").insertInto("account")

I am able to insert through sql but not through spark

file has 7 columns but table has 8 columns including the identity column

Error: error: [DELTA_INSERT_COLUMN_ARITY_MISMATCH] Cannot write to 'data_relationship_managment_pdz.data_relationship_managment_schema.account', not enough data columns; target table has 8 column(s) but the inserted data has 7 column(s) SQLSTATE: 42802

Re: Not able to insert into identity column through spark

Walter_C — Fri, 13 Dec 2024 20:31:45 GMT

The error states that there is a missing column on the data that you are adding if you print the df and so a select on the table do you see that all the columns matches?

Re: Not able to insert into identity column through spark

Shivaprasad — Fri, 13 Dec 2024 21:08:30 GMT

My first column in the table is the identity column and my expectation is it should get auto incremented so it's not in my data frame. I am able to insert data through sql and identity column get auto incremented but not when I try to insert through spark.

Re: Not able to insert into identity column through spark

PiotrMi — Sat, 14 Dec 2024 17:43:17 GMT

Hey @Shivaprasad

Could you try and switch last part "insertInto" to "saveAsTable"?

df.write.mode("append").format("delta").saveAsTable( "account")

Re: Not able to insert into identity column through spark

Shivaprasad — Mon, 16 Dec 2024 02:18:18 GMT

Thanks. Still giving data mismatch error -

table : account
account_dimension_id BIGINT GENERATED BY DEFAULT AS IDENTITY,

df = spark.read.format("csv").option("header", "true").load("abfss://databricks-storage@sa14127e1dv0101.dfs.core.windows.net/catalogs/data-relationship-management-drm/onprem_acct_dimension.csv")
for col in df.columns:
df = df.withColumnRenamed(col, col.lower())
display(df)
df.write.format("delta").mode("append").option("mergeSchema", "true").saveAsTable("account")

Error:
[DELTA_FAILED_TO_MERGE_FIELDS] Failed to merge fields 'insert_date' and 'insert_date' SQLSTATE: 22005
File <command-2201570317935975>, line 8
df = df.withColumnRenamed(col, col.lower())

Re: Not able to insert into identity column through spark

PiotrMi — Mon, 16 Dec 2024 10:31:48 GMT

Hey @Shivaprasad

I think you have error due to different datatypes for column insert_date. Probably its coming as a string from CSV file. Please check what are both data types - in tables and in dataframe created based on file. Cast it if needed. Error should be fixed then.

Re: Not able to insert into identity column through spark

Shivaprasad — Mon, 16 Dec 2024 17:13:53 GMT

Thanks, that resolved the issue

Re: Not able to insert into identity column through spark

PiotrMi — Mon, 16 Dec 2024 17:42:58 GMT

Hey @Shivaprasad

Welcome :). Please consider to mark my last answer as a solution so others can easily find.