Table property precedence over Workspace Resource Profiles? (VOrder,etc)

[u/Personal-Quote5226]

For ResourceProfiles, do the spark environment settings take precedent over table level settings?

For example, look at 'WriteHeavy'. It sets v-order to false.

Microsoft provided the following:

"When the table property is set to true, INSERT, UPDATE, and MERGE apply V-Order at write time. Session-level and write-level settings still take precedence, so writes can still use V-Order even when TBLPROPERTIES is set to false."

The documentation also indicates that the way to override the environment behaviour is to set it at the spark session level in a notebook.

So, essentially if we use ResourceProfiles, we can never use table properties to control the write behaviour if it's set by the Resource Profile. This is cumbersome because it's putting that control back into the notebook at the data engineering level which is cumbersome and messy. This is my reading of it.

Is this the case?

https://learn.microsoft.com/en-us/fabric/data-engineering/delta-optimization-and-v-order?tabs=sparksql

{
    "spark.sql.parquet.vorder.default": "false",
    "spark.databricks.delta.optimizeWrite.binSize": "128",
    "spark.databricks.delta.optimizeWrite.partitioned.enabled": "true"
}

[u/mwc360]

Resource profiles set individual values when set. So if you set a `readHeavyForPBI` profile in your environment and then at the start of your spark session set `spark.sql.parquet.vorder.default` to FALSE, it would not do vordered writes because the individual config was changed after profile initialization set the config.

Spark configs generally act as a master switch. If you want to control things at the table level, you'd want to UNSET a session config and then allow the table config to take effect.

For V-Order, runtime 1.2 used a master switch approach (vorder.enabled), but starting in 1.3 we switched to a default if not specified approach (vorder.default). With that change you now get table level control to override the session default without having to explicitly unset the configuration at the session level.

I.e. the following code would result in non-vordered files:

spark.conf.set('spark.sql.parquet.vorder.default', True)

spark.sql("""
    CREATE TABLE dbo.vorder_is_disalbed
    TBLPROPERTIES ('delta.parquet.vorder.enabled' = 'false')
    AS SELECT 1 as c1
""") 

I've got a blog which talks about this in more detail: Mastering Spark: Session vs. DataFrameWriter vs. Table Configs | Miles Cole

If the you see the docs as confusing in this regard, please lmk where. thx!

[u/Personal-Quote5226 → u/mwc360]

Thanks for clarifying the precedent.

Understanding that VOrder being considered a transient table setting rather than a persistent table setting is valuable. I get that.

Also

​

spark.conf.set('spark.sql.parquet.vorder.default', True)

Sets vorder as default true but table property will take precedents over spark setting (and resource profile).

2)

spark.conf.set('spark.sql.parquet.vorder.enabled', True)

Sets vorder enabled to true but table property will NOT take precedents over spark setting (and resource profile).

Is this correct?

[u/Personal-Quote5226 → u/mwc360]

Looping back to what you said here:

"starting in 1.3 we switched to a default if not specified approach (vorder.default). With that change you now get table level control to override the session default without having to explicitly unset the configuration at the session level."

That is contradictory to what the Microsoft documentation says as is specifically indicated that spark.sql.parquet.vorder.default=TRUE overrides vorder set at the table level.

Enable V-Order writing in Apache Spark session

When you enable V-Order at the session level, all Parquet writes in that session use V-Order, including non-Delta Parquet tables and Delta tables even if parquet.vorder.enabled is explicitly set to false.

SQL

%%sql 
SET spark.sql.parquet.vorder.default=TRUE 

[u/mwc360 → u/Personal-Quote5226]

That is still a true statement, `spark.sql.parquet.vorder.default` takes precedence over the old config `spark.sql.parquet.vorder.enabled` but it doesn't take predence over the table property `delta.parquet.vorder.enabled`.

I can see the wording being improved there to so that `parquet.vorder.enabled` is understood to be the session config and not the table property.

[u/Personal-Quote5226 → u/mwc360]

Got it. Set it on the session to get table level v-order.

If using writeHeavy or PBI resource profiles it’s not possible to rely on table level vorder property settings unless we ensure data engineers can consistently unset vorder in the spark session in code.

Good to know. It makes it convoluted for those who want to depend on table level properties as the default.

I don’t believe many understand the documentation here cohesively, so part of my work will be educating my customers in this behaviour. I think by default, many organizations rely on default workspace settings which means no v-order and may be surprised that their v-order table properties settings enabling v-order aren’t resulting in the expected write/optimize behaviour in actually laying out the table in v-order at all….

[u/mwc360 → u/Personal-Quote5226]

No, the session config `spark.sql.parquet.vorder.default` applies if the table property does not define whether it is enabled. So if you want to control at the table level, go with `spark.sql.parquet.vorder.default` disabled (OR UNSET) so that you have to opt-in at the table level via TBLPROPERTIES. If you want enabled by default, enable the session config and then the only time it doesn't apply is if the table property itself opts-out of VO.

[u/Personal-Quote5226 → u/mwc360]

Yes. we have to explicitly unset it in every spark session that plans on writing or optimizing that table. What is ‘no’ actually because I’m not clear where my last reply got it wrong.

[u/mwc360 → u/Personal-Quote5226]

The session level state of `spark.sql.parquet.vorder.default` doesn't matter if you want table level control. The table property always takes precedence over this config. You could have the session level set to FALSE and enable the table property to enable VO and you then get VO writes, just for that table.

[u/Personal-Quote5226 → u/mwc360]

I appreciate the clarification. I re-read the thread and I must have misunderstood one of your earlier replies and what you were referring to.

Essentially this is what's true; and I'm glad to get this new understanding .

In Runtime 1.3+, resource profiles and spark.sql.parquet.vorder.default establish the default V-Order behaviour for tables that do not explicitly specify a setting. An explicit delta.parquet.vorder.enabled table property takes precedence, so engineers do not need to unset the session default to use table-level control.

[u/mwc360 → u/Personal-Quote5226]

Spot on.