Open Delta Sharing and Deletion Vectors

[DrK]

Hi,

Just experimenting with open delta sharing and running into a few technical traps.  Mainly that if deletion vectors are enabled on a delta table (which they are by default now) we get errors when trying to query a table (specifically with PowerBI).

i.e. "Table features delta.enableDeletionVectors are found in table version: 5. For DeletionVectors, use DBR with version 14.1(14.2 for CDF and streaming) or higher, or delta-sharing-spark with version 3.1 or higher, and set option (\"responseFormat\", \"delta\") to query the table. "

How do you effect the version of DBR (i.e. to use 14.2) what compute is delta-sharing using to be able to change the spec?  

[NandiniN]

Hi @DrK ,

From the error mentioned. 

If you try to set option("responseFormat", "delta") in the query, does it succeed?

To answer your question - In Databricks Runtime 14.2 and above, tables with deletion vectors support row-level concurrency. 

Databricks recommends using Databricks Runtime 14.3 LTS and above to write tables with deletion vectors to leverage all optimizations. 

[DrK]

Hi,

Thanks for the response, however, I don`t think you can set such parameters with the PowerBI native connector for delta, only parameter is a max row count.

[DarioBarbadillo]

I am also experiencing this issue when connecting through Power BI connector. @DrK did you manage to solve it?

[AndySkinner]

@DarioBarbadillo hi, not technically no, the only solution I`ve got is to remove the deletion vectors feature from the delta table!

[F_Goudarzi]

Hi Drk, Have you found a solution for this issue yet? I'm facing the same problem, and the only workaround I found was to set enableDeletionVectors to false.

[NandiniN]

Hi @F_Goudarzi 

Updating the DBR version on the cluster should resolve the issue with querying tables that have deletion vectors enabled.

Additionally, ensure that you set the option ("responseFormat", "delta") when querying the table, as mentioned in the error message.

If you encounter any further issues, you might also consider disabling deletion vectors on the table as a temporary workaround using the following command:

ALTER TABLE <table_name> SET TBLPROPERTIES (delta.enableDeletionVectors=false);

[DarioBarbadillo]

@NandiniN we are talking of PowerBI connection, so you cannot set that option.

@F_Goudarzi I have just tried out with PBI Desktop Version: 2.132.1053.0 and it is running (I did not disable Deletion Vectors into my table.) I also tried with last version: 2.137.1102.0 and is also running properly.

It may be related to Delta Sharing compute Runtime, as DrK pointed out, but I believe you cannot change that Runtime on your own.

[F_Goudarzi]

@DarioBarbadillo 

Yes, you were right. I upgraded my PowerBi Desktop and problem solved. Thank you so much!