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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

6 REPLIES 6
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Expanding the Synapse Forums

New forum boards available in Synapse

Ask questions in Data Engineering, Data Science, Data Warehouse and General Discussion.

MayFabricCarousel

Fabric Monthly Update - May 2024

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

Top Kudoed Authors