Hello @Menaka Murugesan ,
If you are using the identity column, I believe you would have created the table as below, (starts with value 1 and step 1)
CREATE TABLE my_table (
id INT IDENTITY (1, 1) PRIMARY KEY,
value STRING
)
You can insert values in the identity table as
INSERT INTO my_table (value) VALUES ('Value 1'), ('Value 2'), ('Value 3')
The id column values will be automatically generated by the identity column with values of 1, 2, and 3 respectively.
Alternatively, you can also use Spark SQL to insert data into the Delta table with an identity column. Here's an example of how to do this:
spark.sql("INSERT INTO my_table (value) VALUES ('Value 1'), ('Value 2'), ('Value 3')")
To write data into a Delta table with an identity column using the df.write API in PySpark
data = [("Value 1",), ("Value 2",), ("Value 3",)]
columns = ["value"]
df = spark.createDataFrame(data, columns)
df.write.format("delta").option("path", "/path/to/my_table").option("overwriteSchema", "true").option("idCol", "id").save()
The id column will be auto generated.
I think you are getting mismatched columns error because you are trying to insert the value for id? Please clarify further on the error and how are you inserting the value.
Hope this helps.