Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SergioTorrinha
Resolver II
Resolver II

Lakehouse table column names not reflected in SQL Endpoint after a rename operation

Hi everyone!

Recently, I have renamed some of the columns on a delta table I have in my lakehouse.
The 'new' column names are being displayed exactly how I want in the lakehouse UI, but in the SQL Enpoint the 'old' column names are still being displayed.

At first, I thought this would be due to some momentairy slowness of the service, but after several hours passed between the moment I renamed the columns and the last time I checked this, the situation still remains the same.

Perhaps I have done something wrong and that is why the SQL endpoint is not showing the 'new column names, in which case I would like your help to understand.
Here's a sample of the code I used to rename my delta table columns:

 

 

 

%%sql
ALTER TABLE my_table_name_here SET TBLPROPERTIES (
    'delta.minReaderVersion' = '2',
    'delta.minWriterVersion' = '5',
    'delta.columnMapping.mode' = 'name'
  );

ALTER TABLE my_table_name_here RENAME COLUMN old_column_name TO new_column_name;

 

 

 


Apretiate your help in advance.

1 ACCEPTED SOLUTION

Hi @SergioTorrinha 

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.

vnikhilanmsft_7-1708082641892.png

 

 

vnikhilanmsft_8-1708082641894.png

 

 


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".

vnikhilanmsft_9-1708082659858.png

 

 


2)  Run the below given code by dropping the column required. You can also try renaming the column. Create a new table named list1.

vnikhilanmsft_10-1708082659859.png

 

3) Delete the original list table from the lakehouse. 

vnikhilanmsft_11-1708082675087.png

 


4) Now run the below code which will create a new table with name as List with the dropped column.

vnikhilanmsft_12-1708082675089.png

 

 

5) This table can be accessed from the SQL endpoint also.

vnikhilanmsft_13-1708082684203.png


We also have a Microsoft Idea where users requested for a feature to add or remove columns in a Lakehouse table without having to delete and recreate the table. Please upvote this.
Microsoft Idea

Hope this helps. Please let me know if you have any further questions. Glad to help.

 

View solution in original post

8 REPLIES 8
jjaeger94
Advocate I
Advocate I

It looks like this issue may relate to Delta Lake Interoperability . Specifically the following property:

'delta.columnMapping.mode' = 'name'

 

This is a feature I was hoping to leverage to remove "_" from column names. However, it appears that this isn;t supported. Is this on the Microsoft Roadmap?

 

@jjaeger94 I think you can edit a table without deleting it. I think you can use overwriteSchema in PySpark. See https://community.fabric.microsoft.com/t5/General-Discussion/Dropping-and-recreating-lakehouse-table...

SergioTorrinha
Resolver II
Resolver II

Thanks for the feedback @v-nikhilan-msft !
It's a pitty that we have to go through so much hassle for a simple column renaming.

I'm marking your answer as a solution, altought, it really does not solve the issue.

Thanks again for your efforts in clarifying this one.

Please vote for this related idea: Microsoft Idea

Thanks for sharing it @frithjof_v ! Already voted. 🙂
Cheers!

SergioTorrinha
Resolver II
Resolver II

Hi @v-nikhilan-msft !

Thanks for the feedback.
Yes, I understand that IN the sql endpoint  it's not allowed to do any schema updates, but that is not what I was aiming for.
I was aiming thowards having adjustments done over tables in lake house automatically reflected in the SQL endpoint. Because, what is the point in adjusting column names in delta tables an then not being able to query them in the sql endpoint using the adjusted column names?
Furthermore, does this means, it is not possible to reflect the adjusted column names in the SQL endpoint for further querying and so on?

Thank you.

Hi @SergioTorrinha 

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.

vnikhilanmsft_7-1708082641892.png

 

 

vnikhilanmsft_8-1708082641894.png

 

 


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".

vnikhilanmsft_9-1708082659858.png

 

 


2)  Run the below given code by dropping the column required. You can also try renaming the column. Create a new table named list1.

vnikhilanmsft_10-1708082659859.png

 

3) Delete the original list table from the lakehouse. 

vnikhilanmsft_11-1708082675087.png

 


4) Now run the below code which will create a new table with name as List with the dropped column.

vnikhilanmsft_12-1708082675089.png

 

 

5) This table can be accessed from the SQL endpoint also.

vnikhilanmsft_13-1708082684203.png


We also have a Microsoft Idea where users requested for a feature to add or remove columns in a Lakehouse table without having to delete and recreate the table. Please upvote this.
Microsoft Idea

Hope this helps. Please let me know if you have any further questions. Glad to help.

 

v-nikhilan-msft
Community Support
Community Support

Hi @SergioTorrinha 
Thanks for using Fabric Community.
You have written the code correctly. Currently rename a table in lakehouse, and ALTER table functions, adding new columns, renaming columns in a Lakehouse table is not supported in Fabric. 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.

Hope this helps. Please let me know if you have any further queries.

Helpful resources

Announcements
Sept Fabric Carousel

Fabric Monthly Update - September 2024

Check out the September 2024 Fabric update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.