topic Re: Add or remove column in Lakehouse table in Data Engineering

Add or remove column in Lakehouse table

fabricator1 — Wed, 27 Dec 2023 12:09:58 GMT

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.

Re: Add or remove column in Lakehouse table

Anonymous — Wed, 27 Dec 2023 18:51:37 GMT

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.

Re: Add or remove column in Lakehouse table

fabricator1 — Wed, 27 Dec 2023 19:35:53 GMT

Thank you, I will try this procedure! 😀

Re: Add or remove column in Lakehouse table

Anonymous — Wed, 27 Dec 2023 19:45:08 GMT

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.

Re: Add or remove column in Lakehouse table

Anonymous — Tue, 02 Jan 2024 10:30:15 GMT

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

Re: Add or remove column in Lakehouse table

fabricator1 — Tue, 02 Jan 2024 12:40:36 GMT

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

Re: Add or remove column in Lakehouse table

AndyDDC — Tue, 02 Jan 2024 15:08:16 GMT

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

Re: Add or remove column in Lakehouse table

fabricator1 — Tue, 02 Jan 2024 15:50:03 GMT

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?

Re: Add or remove column in Lakehouse table

AndyDDC — Tue, 02 Jan 2024 16:12:42 GMT

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

Re: Add or remove column in Lakehouse table

MJ_Taft — Mon, 07 Oct 2024 18:59:21 GMT

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

Re: Add or remove column in Lakehouse table

sdrfghjkl — Fri, 10 Jan 2025 08:48:33 GMT

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.