In Databricks I have a existing delta table, In which i want to add one more column, as Id so that each row has unique id no and It is consecutive (how primary key is present in sql).
So far I have tried converting delta table to pyspark dataframe and have added new column as
from pyspark.sql.window import Window as W
from pyspark.sql import functions as F
df1 = df1.withColumn("idx", F.monotonically_increasing_id())
windowSpec = W.orderBy("idx")
F.row_number().over(windowSpec)).show()
I tried writing it back to delta table,
df.write.mode("append").format("delta").save(location/db.tablename)
It writes back but the data values After querying is null for the new id column. I read, overwrite mode will erase all previous data. How can I bring the column id data to delta table and keep incrementing the id column when data gets inserted?
I am trying to achieve adding a autoincrement column for delta table. The databricks runtime is 7.3.
