Forum Discussion
Lakehouse Delta table changes are not reflecting in SQL Analytics Endpoint
When renaming columns in Delta tables within a lakehouse, the changes reflect in SQL Endpoint analytics after a brief delay of 1-2 minutes.
Following steps worked for me,
df = spark.sql("SELECT * FROM lakehouse_name.delta_table_name")
df = df.withColumnRenamed("old_col_name","new_col_name")
df.write.format("delta").mode("overwrite").option("overwriteSchema", "true").saveAsTable("table_name")
NOTE: overwriteschema-overwrites the entire schema and data of the table, mergeschema -merges the new schema with the existing schema, ensuring that all columns (old and new) are included
After these steps, the column name change will be applied, but it may take 1-2 minutes to appear in SQL Endpoint analytics due to metadata refresh.
Thanks Shreya_Barhate . Changes to delta tables are still not reflecting in the SQL Endpoint. I have tried with both Spark 3.4 and Spark 3.5 (public preview) in notebook workspace , still no results in last 1 hour.
If you have already applied column name mapping to your tables, i.e. something like this
ALTER TABLE table_name SET TBLPROPERTIES (
'delta.columnMapping.mode' = 'name',
'delta.minReaderVersion' = '2',
'delta.minWriterVersion' = '5')
then I think you will need to create a new table (or a new lakehouse) and don't use the enable column mapping command. Instead, just use overwriteSchema or mergeSchema if you need to edit the schema of your table. You can also use ALTER TABLE ADD COLUMN, but you cannot use other ALTER TABLE commands like RENAME COLUMN or DROP COLUMN.
Thanks Shreya_Barhate and frithjof_v , I am able to alter column name and data type changes using dataframe in lakehouse rather than ALTER TSQL.
When writing the dataframe with changed columns to a Lakehouse table, are you using .option("overwriteSchema", "true") or .option("mergeSchema", "true")? Or have you found another method?
I'm curious about this topic 😃
