Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to Solution.
I think it would be better to use STRING data types instead of CHAR
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()
or
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!
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()
or
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!
I think it would be better to use STRING data types instead of CHAR
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
Check out the November 2024 Fabric update to learn about new features.
User | Count |
---|---|
16 | |
12 | |
9 | |
8 | |
6 |