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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
DennesTorres
Solution Supplier
Solution Supplier

How to list Table Properties

Hi,

A table can have multiple properties, such as the v-order optimization.

Some statements related to these properties are explained on the link https://learn.microsoft.com/en-us/fabric/data-engineering/delta-optimization-and-v-order?tabs=sparks...

 

We can use CREATE TABLE/ALTER TABLE/OPTIMIZE 

 

However, the link doesn't explain how to use SHOW.

The traditional statement should be something like:

SHOW TBLPROPERTIES dimension_employee;
I tried many variations, with no success.

So, my questions are:

How to discover the property values for a table?

Is it possible to discover the same using the SQL Endpoint, or only using notebooks?
 
The v-order optimization can be enabled for each write operation. Could this lead to a situation where some parquet files are optimized and some are not? In this case, the table property may work as a general guidance but some parquet files may not be optimized inside the table? How to identify this situation?

Kind Regards,
 
Dennnes
8 REPLIES 8
charrington
Advocate III
Advocate III

Hi

I tried also the GUI Optimizer, never noticed that before, when you right click on a table from the Fabric Lakehouse:

charrington_0-1700599160395.png

It ran witth success (it's still running spark behing the scenes), and even I can see my report is responding faster, and I am able to see parquet files have been merged and optimized.

 

But still the VOrder property doesn't show: 

charrington_1-1700599249676.png

I wonder why...
Any updates on your side?

Thanks

chetnachaudhari
Frequent Visitor

@DennesTorres The traditional statement to see table properties is working fine from notebooks.

I just verified it using below code snippet

%%sql
SHOW TBLPROPERTIES dim_calendar
 
 

Hi,

I just tried it again and it's not working. Maybe I'm doing something wrong? I get a simple syntax error as feedback

DennesTorres_0-1691545661299.png


Kind Regards,

Dennes

Hi,

After some more tests, I solved the problem of the SHOW statement, it was a silly mistake I did. %%sql only works if it's on the first line of the code block.

However, the final intended result didn't work. I was looking for discovering if the table was created using vorder optimization or optimizewrite, I was sure some of the tables were created with these features, while others were not, but the SHOW TBLPROPERTIES never show these properties.

What could I be doing wrong?

DennesTorres_1-1691547287164.png

 

Kind Regards,

 

Dennes

You can check the value of particular property using following syntax

chetnachaudhari_0-1691548859862.png

%%sql
SHOW TBLPROPERTIES dim_calendar ('delta.parquet.vorder.enabled');

 

Regards,
Chetna

Hi,

I tried as well. It always result that the table doesn't have this property.

I tried to drop and create the table again, in many different ways. I followed the guidance on https://learn.microsoft.com/en-us/fabric/data-engineering/delta-optimization-and-v-order?tabs=sparks....

All my attempts always result in "The table doesn't have this property".

One of my last attempts was this:

DennesTorres_0-1691550015045.png

Kind Regards,

Dennes

 

Yes, it is bit unclear on how it picks the tblproperties, I tried few ways they didn't show the property value, when I tried below it works

chetnachaudhari_0-1691551673520.png

cheers,
Chetna

Hi,!

Very interesting. I will need to investigate more. After all, this involves the v-order optimization of the files and if it doesn't work in some ways, there are more than one Microsoft tutorial with bad mistakes. 

Thank you!

Kind Regards,

Dennes

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

March 2024  FBC Gallery Image

Fabric Monthly Update - March 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Kudoed Authors