Add or remove column in Lakehouse table

[fabricator1]

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.

[Anonymous · Accepted solution]

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.

[sdrfghjkl]

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. 

[MJ_Taft]

@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 list1

2. 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?

[fabricator1]

Thank you, I will try this procedure! 😀 

[Anonymous]

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.

[Anonymous]

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

[fabricator1 · Accepted solution]

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

[AndyDDC]

Hi @fabricator1 , not sure if this helps with your particular scenrio but you could try the column mapping feature which allows you to alter/drop/add columns in a Lakehouse table by following this (it's databricks but it works in Fabric too).  It's a one way operation on the table itself and can't be reversed.

Rename and drop columns with Delta Lake column mapping | Databricks on AWS

[fabricator1]

Thanks @AndyDDC, 

This was my original approach, however after I did that I wasn't able to use the table in the SQL Analytics Endpoint and Power BI semantic model.
I guess that's what's mentioned here:

Is it working on your side after making such changes?

[AndyDDC]

Ah OK, no the meta-data sync will not work for the SQL Endpoint/Semantic Model unfortunately.  I've used the above approach for a raw lakehouse layer (proof of concept) which did not require sql endpoint/semantic model

[Anonymous · Accepted solution]

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.