Forum Discussion

DennesTorres's avatar
DennesTorres
Impactful Individual
3 years ago

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=sparksql

 

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
data engineering

8 Replies

  • chetnachaudhari's avatar
    chetnachaudhari
    Advocate I
    3 years ago

    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's avatar
      DennesTorres
      Impactful Individual
      3 years ago

      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's avatar
        DennesTorres
        Impactful Individual
        3 years ago

        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

  • charrington's avatar
    charrington
    Advocate V
    2 years ago

    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