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

[SergioTorrinha]

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.

[jjaeger94]

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?

[frithjof_v]

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

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.

[frithjof_v]

Please vote for this related idea: Microsoft Idea

[SergioTorrinha]

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

[SergioTorrinha]

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.

[v-nikhilan-msft · 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.

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. You can also try renaming the column. 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.

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]

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.