Forum Discussion
Add or remove column in Lakehouse table
Can I add or remove a column from an existing Lakehouse table?
I tried doing that on a lakehouse table (by using notebook), but then the table was not visible in the SQL Endpoint and in the Default semantic model afterwards.
Hi fabricator1
Thanks for using Fabric Community.
Currently ALTER TABLE command is not supported for tables in Lakehouse and Warehouse. So when you try to modify the table using Spark code it might result in some issues.
But as a work around I would suggest the following steps:1) I have a table named List in my lakehouse with one of the column named "genre".
2) Run the below given code by dropping the column required. Create a new table named list1.3) Delete the original list table from the lakehouse.
4) Now run the below code which will create a new table with name as List with the dropped column.5) This table can be accessed from the SQL endpoint also.
Hope this helps. Please let me know if you have any further questions. Glad to help.I didn't have the time to try this procedure. I started on it, and one thing I noticed was that when creating a table using Notebook, the table name can only have small letters. I guess that's normal.
I had created the original table using Dataflows Gen 2, and the table name had capital letters.
Anyway I decided to delete the table and recreate the table using Dataflows Gen 2. For my current purpose, that was okay.
I hope there will be a feature to add or remove columns in a Lakehouse table without having to delete and recreate the table. I created it as an idea: Microsoft Idea
10 Replies
Hi fabricator1
Thanks for using Fabric Community.
Currently ALTER TABLE command is not supported for tables in Lakehouse and Warehouse. So when you try to modify the table using Spark code it might result in some issues.
But as a work around I would suggest the following steps:1) I have a table named List in my lakehouse with one of the column named "genre".
2) Run the below given code by dropping the column required. Create a new table named list1.3) Delete the original list table from the lakehouse.
4) Now run the below code which will create a new table with name as List with the dropped column.5) This table can be accessed from the SQL endpoint also.
Hope this helps. Please let me know if you have any further questions. Glad to help.Thank you, I will try this procedure! 😀
Thanks for the reply fabricator1 . Please do let me know if the work around helped you. Do let me know if you have any further questions.
Hi fabricator1
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. Otherwise, will respond back with the more details and we will try to help.
Thanks
Anonymous
that made no sense to me.
You mention the table list with the column named "genre"
1. Then you run code to drop a column - not "genre" but "id" and create a new table list12. Then delete the table list
3. Then run same code you ran in step 1 - including the df = df.drop("id") line??
4. Then you show a table list which includes the "id" column but not the "genre" column.
I have to assume you meant in step 1 that the column name you deleted should be "genre" and not "id".
If so, then it makes sense -- all except you including the df = df.drop("<column>") line in your code. Why would you include that and not just the SELECT * and df.write.format code lines?import pyspark.sql.functions as F
from delta.tables import *# Load the table
df = spark.sql("SELECT * FROM SparkNotebook.retail2_table")# Drop unwanted columns (replace 'column_name1', 'column_name2' with actual column names you want to remove)
columns_to_remove = ['column_name1', 'column_name2']
df_first_10 = df_first_10.drop(*columns_to_remove)# Overwrite the existing Delta table with the first 10 rows (with the removed columns)
df_first_10.write.format("delta").mode("overwrite").saveAsTable("SparkNotebook.retail2_table")
this code will help to remvoe likely columns.
