topic Re: Open Delta Sharing and Deletion Vectors in Administration & Architecture

Open Delta Sharing and Deletion Vectors

DrK — Fri, 31 May 2024 14:01:14 GMT

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?

Re: Open Delta Sharing and Deletion Vectors

NandiniN — Sat, 01 Jun 2024 05:24:10 GMT

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.

Re: Open Delta Sharing and Deletion Vectors

DrK — Mon, 03 Jun 2024 06:43:07 GMT

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.

Re: Open Delta Sharing and Deletion Vectors

DarioBarbadillo — Fri, 06 Sep 2024 10:18:10 GMT

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

Re: Open Delta Sharing and Deletion Vectors

AndySkinner — Fri, 06 Sep 2024 10:21:42 GMT

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

Re: Open Delta Sharing and Deletion Vectors

F_Goudarzi — Wed, 30 Oct 2024 16:14:46 GMT

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.

Re: Open Delta Sharing and Deletion Vectors

NandiniN — Thu, 31 Oct 2024 09:12:40 GMT

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);

Re: Open Delta Sharing and Deletion Vectors

DarioBarbadillo — Thu, 31 Oct 2024 10:21:27 GMT

@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.

Re: Open Delta Sharing and Deletion Vectors

F_Goudarzi — Thu, 31 Oct 2024 13:54:51 GMT

@DarioBarbadillo

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