topic Re: How to list Table Properties in Data Engineering

How to list Table Properties

DennesTorres — Sat, 05 Aug 2023 20:13:15 GMT

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

Re: How to list Table Properties

chetnachaudhari — Wed, 09 Aug 2023 01:10:18 GMT

@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

Re: How to list Table Properties

DennesTorres — Wed, 09 Aug 2023 01:49:26 GMT

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

Re: How to list Table Properties

DennesTorres — Wed, 09 Aug 2023 02:15:16 GMT

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

Re: How to list Table Properties

chetnachaudhari — Wed, 09 Aug 2023 02:41:59 GMT

You can check the value of particular property using following syntax

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

Regards,
Chetna

Re: How to list Table Properties

DennesTorres — Wed, 09 Aug 2023 03:00:55 GMT

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

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

One of my last attempts was this:

Kind Regards,

Dennes

Re: How to list Table Properties

chetnachaudhari — Wed, 09 Aug 2023 03:28:08 GMT

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

Re: How to list Table Properties

DennesTorres — Wed, 09 Aug 2023 12:58:47 GMT

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

Re: How to list Table Properties

charrington — Tue, 21 Nov 2023 20:42:30 GMT

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