topic Re: error during delta table merge with CHAR data type in Data Engineering

error during delta table merge with CHAR data type

Winnie2024 — Sat, 03 Aug 2024 09:14:06 GMT

I need to create one delte table. And it will be upserted many times as the time on. I have the code like below:

from delta.tables import *

DeltaTable.createIfNotExists(spark) \
.tableName("test") \
.addColumn("id", "VARCHAR(15)")\
.addColumn("code", "CHAR(3)")\
.execute()

dt_test = DeltaTable.forName(spark, "test")

dt_test_update = spark.createDataFrame([
("1","001"),
("2","002"),
],
schema=["id","code"]
)

dt_test.alias('test') \
.merge(
dt_test_update.alias('updates'),
'test.id = updates.id'
) \
.whenNotMatchedInsertAll()\
.whenMatchedUpdateAll()\
.execute()

df = spark.sql("select * from test")
display(df)

got the error below :

"Resolved attribute(s) id#33730,code#33731 missing from id#33631,code#33633,_metadata#33735 in operator !Project [id#33730, staticinvoke(class org.apache.spark.sql.catalyst.util.CharVarcharCodegenUtils, StringType, readSidePadding, code#33731, 3, true, false, true) AS code#33732]. Attribute(s) with the same name appear in the operation: id,code. Please check if the right attribute(s) are used.;"

But if I change '.addColumn("code", "CHAR(3)")\' to '.addColumn("code", "VARCHAR(3)")\' when i create the delta table.

It will be OK.

Any idea about this?

Re: error during delta table merge with CHAR data type

SachinNandanwar — Sat, 03 Aug 2024 09:57:25 GMT

I think it would be better to use STRING data types instead of CHAR

Re: error during delta table merge with CHAR data type

Anonymous — Mon, 05 Aug 2024 08:20:28 GMT

Hi @Winnie2024

Thanks to @SachinNandanwar 's suggestion. Based on my test, it is very helpful!

PySpark doesn’t have a direct CHAR or VARCHAR type, you can use StringType() or "STRING" to represent the VARCHAR(15) and CHAR(3) data types.

Try

from delta.tables import DeltaTable from pyspark.sql.types import StringType # Create a Delta table with two columns DeltaTable.createIfNotExists(spark) \ .tableName("test") \ .addColumn("id", StringType()) \ .addColumn("code", StringType()) \ .execute()

from delta.tables import * from pyspark.sql.types import * DeltaTable.createIfNotExists(spark) \ .tableName("test") \ .addColumn("id", "STRING") \ .addColumn("code", "STRING") \ .execute()

Based on my test, both of the above work for your later merge test code without any error.

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!