Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have digging around for definitive answers but have not much to show for. I have read this
https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area#t-sql-surface-area
and
https://blog.fabric.microsoft.com/en-US/blog/announcing-fabric-warehouse-support-for-sp_rename/
The testing I have leads me to conclude:
1. I cannot add a column to an existing lakehouse table.
2. I cannot rename a column in an existing lakehouse table.
3. I cannot add a column to an existing warehouse table.
4. I cannot rename a column in an existing warehouse table (sp_rename doesn't seem to go the column level).
Any feedback about how correct (or not) I am would be appreciated!
Solved! Go to Solution.
Hi @ebjim
Thanks for using Fabric Community.
Yes you are right. Currently rename a table in lakehouse, and ALTER table functions, adding new columns in a Lakehouse table is not supported in Fabric.
sp_rename is supported for Tables, Stored Procedures , Views and Functions in Warehouse. It is supported only for objects not for columns.
Hope this helps. Please let me know if you have any further questions.
You can change the schema of a lakehouse table by using a notebook and advancing the schema if your table is a delta table. The lakehouse sql endpoint won't let you do any schema (or data) modifications.
It seems to me that you can use overwriteSchema for renaming columns (and other schema changes):
That implies doing a full overwrite of the table data as well.
For adding columns I think you can use this generic code:
%%sql
ALTER TABLE tableName
ADD COLUMN columnName columnType;
The way I understand it, this is just a metadata operation and does not imply a full overwrite of the data.
MSFT Fabric now has a solution to rename delta table columns.
Run this code in a Fabric notebook:
Careful with this though as if you set the table to name mapping mode, table changes won't be synced to the Lakehouse SQL Endpoint
It seems to be possible to add columns in Lakehouse table now by using notebook.
I am able to use the following type of command in a Notebook:
%%sql
ALTER TABLE tableName
ADD COLUMN columnName dataType
And the table will get updated also in SQL Analytics Endpoint and Direct Lake Semantic Model, something which was a problem before.
Ref. this thread:
https://community.fabric.microsoft.com/t5/General-Discussion/SQL-ALTER-command/m-p/3748079#M4861
However, I get an error if I try to rename or remove (drop) a column.
In my experience, you need to create a new table with the new column schema. (at least if you are planning to use the table in the SQL Analytics Endpoint and the Semantic Model).
I have done it like this:
1. Create a new table with the new schema.
2. Fill the new table with data.
Copy the data you want to keep from the old table (if needed), by using Dataflows Gen 2 or Notebook.
Or just fill the new table with new data from the source system.
(1. and 2. can be done in the same step).
3. Delete the old table
If you want the new table to have the same name as the old table, you need to do step 3 (delete old table) before step 1 and step 2 (create new table).
I have not been able to keep the version history (delta logs) of the original table.
I hope functionality will be added so we can make changes to the schema of an existing lakehouse table. The process now with deleting and creating new table is quite cumbersome, at least in my experience. Especially if using medallion architecture with delta tables in two or three layers.
Thank you both for the feedback!
Hi @ebjim
Glad that your query got resolved. Please continue using Fabric Community for any help regarding your queries.
You can change the schema of a lakehouse table by using a notebook and advancing the schema if your table is a delta table. The lakehouse sql endpoint won't let you do any schema (or data) modifications.
Could you please expand on this or provide links to additional resources. Thanks appreciate the insight.
Can you provide more information on how to use a notebook to add a column to a lakehouse table?
I am using a Dataflow Gen2 to get data from SQL Server into the lakehouse. Do you know if that is a delta table?
Hi @ebjim
Thanks for using Fabric Community.
Yes you are right. Currently rename a table in lakehouse, and ALTER table functions, adding new columns in a Lakehouse table is not supported in Fabric.
sp_rename is supported for Tables, Stored Procedures , Views and Functions in Warehouse. It is supported only for objects not for columns.
Hope this helps. Please let me know if you have any further questions.
Check out the October 2024 Fabric update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
3 | |
2 | |
1 | |
1 | |
1 |