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
frithjof_v
Impactful Individual
Impactful Individual

SQL ALTER command

Hi, is the sql ALTER TABLE (ADD/ALTER/DROP COLUMN) commands supported now?

 

I see ALTER TABLE ADD COLUMN is being used successfully in this GuyInACube video to add columns to a Lakehouse table (and the new column also appears in the SQL endpoint and semantic model in the video). That is great!

 

I think this didn't use to work before, and I read in this documentation that sql ALTER TABLE ADD/ALTER/DROP COLUMN is not supported in Fabric. Am I missing something, or is that part of the documentation not updated?

 

Does this mean that these commands are supported in Fabric now? 

 

Thank you!

2 ACCEPTED SOLUTIONS
AndyDDC
Solution Sage
Solution Sage

Hi @frithjof_v If you are using a Lakehouse, this is a case of the Fabric runtime version 1.1 vs 1.2 in a workspace.  If you try using ALTER TABLE in a Notebook in a workspace with Fabric 1.1 (spark 3.3) then the ALTER TABLE is inconsistent and doesn't sync properly with the sql endpoint.  If you use Fabric runtime 1.2 (spark 3.4) then ALTER TABLE should work.

 

It works for me, I can use:

 

%%sql

ALTER TABLE dimproducts

ADD COLUMN iscurrent int

 

and it's sync with the sql endpoint and the default semantic model

 

AndyDDC_0-1709822468286.png

 

View solution in original post

Hi @frithjof_v , @AndyDDC 

We have an update from internal team -

Note that there are two different SQL dialects in use in Fabric. In a Spark notebook you use Spark SQL. In the SQL Endpont you use T-SQL.



Inorder to clear everyone's confusion - we can use Alter commad in Notebook as it uses Spark SQL, but not T-SQL.

These limitations are with T-SQL - T-SQL surface area - Microsoft Fabric | Microsoft Learn

Hope this is helpful. Please let me know incase of further queries.

View solution in original post

9 REPLIES 9
frithjof_v
Impactful Individual
Impactful Individual

@AndyDDC @v-gchenna-msft 

 

I just want to provide a little update on the ALTER TABLE topic. It seems to me that adding columns to Lakehouse tables works, and the changes also get updated in the SQL Analytics Endpoint and the Direct Lake Semantic Model.

 

However I am not able to drop columns or rename columns.

 

This is a part of the error message I get in the notebook when running a ALTER TABLE drop column or rename column command:

 

 

 

DROP COLUMN is not supported for your Delta table. 
Please enable Column Mapping on your Delta table with mapping mode 'name'.
You can use one of the following commands.

If your table is already on the required protocol version:
ALTER TABLE table_name SET TBLPROPERTIES ('delta.columnMapping.mode' = 'name')

If your table is not on the required protocol version and requires a protocol upgrade:
ALTER TABLE table_name SET TBLPROPERTIES (
   'delta.columnMapping.mode' = 'name',
   'delta.minReaderVersion' = '2',
   'delta.minWriterVersion' = '5')

(...)

 

 

 

If I try to enable the Column Mapping (by running the code provided in the error message) then I am able to rename or drop columns in the Notebook interface, however it doesn't work together with the SQL Analytics Endpoint and Direct Lake Semantic Model.

 

In the Lakehouse explorer, the menu on the left-hand side is updated similarly to the notebook, however the data view in the Lakehouse explorer doesn't reflect the changes made:

frithjof_v_1-1711904861657.png

 

Here, I have renamed the Description column to Beskrivels, and I have dropped the Model and Category columns.

In the menu on the left-hand side, the updates I did in the notebook are reflected, but in the data view it's not.

The table also doesn't refresh in the SQL analytics endpoint and Direct Lake Semantic Model anymore.

 

Just something I noticed and thought I should share. It seems add columns is working, however drop columns and rename columns don't seem to be possible at the moment.

AndyDDC
Solution Sage
Solution Sage

Hi @frithjof_v If you are using a Lakehouse, this is a case of the Fabric runtime version 1.1 vs 1.2 in a workspace.  If you try using ALTER TABLE in a Notebook in a workspace with Fabric 1.1 (spark 3.3) then the ALTER TABLE is inconsistent and doesn't sync properly with the sql endpoint.  If you use Fabric runtime 1.2 (spark 3.4) then ALTER TABLE should work.

 

It works for me, I can use:

 

%%sql

ALTER TABLE dimproducts

ADD COLUMN iscurrent int

 

and it's sync with the sql endpoint and the default semantic model

 

AndyDDC_0-1709822468286.png

 

frithjof_v
Impactful Individual
Impactful Individual

Thank you @AndyDDC 

 

I agree, I hope MS can confirm this 👍 It would be good to have a separate documentation for SQL limitations within Spark, because this ALTER TABLE ADD COLUMN used to be a limitation also with Spark SQL but there was no separate documentation for the limitations with Spark SQL as far as I know. The documentation is just for the limitations with Warehouse T-SQL as far as I know.

 

Anyway, this is a nice feature and I am glad it is working now with the newest Spark runtime in Fabric 😃

v-gchenna-msft
Community Support
Community Support

Hi @frithjof_v ,

Thanks for using Fabric Community.
As per my knowledge though  ALTER TABLE ADD COLUMN commands work, these are not recommended to use.

At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.

Hi @frithjof_v ,

As per the documentation -

vgchennamsft_0-1709826501089.png


This command is also not executable in SQL Endpoint.

vgchennamsft_1-1709826574291.png


Without official confirmation this is not a recommended way to use.

Hope this is helpful.

Can you please link to that documentation? I believe it needs updating to differentiate between Warehouse (SQL) and Spark.

Hi @frithjof_v , @AndyDDC 

We have an update from internal team -

Note that there are two different SQL dialects in use in Fabric. In a Spark notebook you use Spark SQL. In the SQL Endpont you use T-SQL.



Inorder to clear everyone's confusion - we can use Alter commad in Notebook as it uses Spark SQL, but not T-SQL.

These limitations are with T-SQL - T-SQL surface area - Microsoft Fabric | Microsoft Learn

Hope this is helpful. Please let me know incase of further queries.

Glad to know that your query got resolved. Please continue using Fabric Community for your further queries.

Confirmation would be good. But that documentation is specifying Warehouse and not Lakehouse. You can't modify a table schema or data using the Lakehouse SQL Endpoint anyway and that's by design.

 

but issuing ALTER TABLE via Notebook on a Lakehouse table should be supported in Fabric runtime 1.2. Hoping MS can clarify

Helpful resources

Announcements
FabricCarousel_June2024

Fabric Monthly Update - June 2024

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

July Newsletter

Fabric Community Update - July 2024

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