How to list Table Properties

[DennesTorres]

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

[charrington]

Hi

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

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: 

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

Thanks

[chetnachaudhari]

@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

[DennesTorres]

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

Kind Regards,

Dennes

[DennesTorres]

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?

Kind Regards,

Dennes

[chetnachaudhari]

You can check the value of particular property using following syntax

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

Regards,
Chetna

[DennesTorres]

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:

Kind Regards,

Dennes

[chetnachaudhari]

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

cheers,
Chetna

[DennesTorres]

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