{"id":98261,"date":"2023-09-20T17:00:16","date_gmt":"2023-09-20T17:00:16","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=98261"},"modified":"2024-09-03T20:15:18","modified_gmt":"2024-09-03T20:15:18","slug":"microsoft-fabric-checking-and-fixing-tables-v-order-optimization","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/microsoft-fabric-checking-and-fixing-tables-v-order-optimization\/","title":{"rendered":"Microsoft Fabric: Checking and Fixing Tables V-Order Optimization"},"content":{"rendered":"

Download the notebook used on this blog<\/a><\/p>\n

I explained in a previous article how the Tables in a lakehouse are V-Order optimized<\/a>. We noticed this configuration depends on our settings, which can be enabled or not.<\/p>\n

One question remains: How could we check if the tables are V-Order optimized or not?<\/em><\/strong><\/p>\n

The tables we will use in this example are the same provided by Microsoft as a sample and we described in details how to load them in our article about lakehouse<\/a>.<\/p>\n

You can check more details about V-ORDER Optimization on this link<\/a><\/p>\n

Checking tables configuration<\/h2>\n

We can use the following PySpark<\/strong> script to check the configuration of the tables in our lakehouse<\/strong>:<\/p>\n

<\/p>\n

\n
import<\/span> pyarrow.dataset<\/span> as<\/span> pq<\/span>\r\nimport<\/span> os<\/span>\r\ndef<\/span> show_metadata<\/span>(delta_file_path,tablename):\r\n \r\n\r\n # Print schema metadata<\/span>\r\n\r\n print<\/span>(f\"<\/span>\\n<\/span>Schema Properties for : {tablename}\"<\/span>)\r\n\r\n print<\/span>(\"--------------------\"<\/span>)\r\n\r\n schema_properties =<\/span> pq.<\/span>dataset(delta_file_path).<\/span>schema.<\/span>metadata\r\n\r\n if<\/span> schema_properties:\r\n\r\n     for<\/span> key, value in<\/span> schema_properties.<\/span>items():\r\n         print<\/span>(f\"{key.decode('utf-8')}: {value.decode('utf-8')}\"<\/span>)\r\n\r\n else<\/span>:\r\n\r\n    print<\/span>(\"No schema properties found.\"<\/span>)\r\n\r\n\r\n # Test the function with a path to your delta file#<\/span>\r\n\r\nfull_tables =<\/span> os.<\/span>listdir('\/lakehouse\/default\/Tables'<\/span>)\r\n\r\nfor<\/span> table in<\/span> full_tables:\r\n  \r\n  show_metadata('\/\/lakehouse\/default\/Tables\/'<\/span> +<\/span> table,table)\r\n<\/pre>\n<\/div>\n
\"A<\/p>\n
This script is inspired on a script provided by a user in the Fabric Community<\/a> forums.<\/p>\n
What we should notice about how this code works:<\/p>\n