topic Re: How to enable/verify cloud fetch from PowerBI in Data Engineering

How to enable/verify cloud fetch from PowerBI

Erik — Sun, 30 Jan 2022 16:01:05 GMT

I tried to benchmark the Powerbi Databricks connector vs the powerbi Delta Lake reader on a dataset of 2.15million rows. I found that the delta lake reader used 20 seconds, while importing through the SQL compute endpoint took ~75 seconds.

When I look at the query profile in SQL compute I see that 50 seconds are spendt in the "Columnar To Row" step. This makes me rather suspicios, since I got the impression that with an updated PowerBI we would take advantage of "cloud fetch" which creates files containing Apache Arrow batches, which is a columnar format. So why the conversion to rows? Maybe it is not actually using cloud fetch? Is there any way to verify that I am actually using cloud fetch? Either in PowerBi logs or in the Databricks SQL compute endpoint web interface?

Re: How to enable/verify cloud fetch from PowerBI

Erik — Tue, 01 Feb 2022 14:28:20 GMT

Hey @Kaniz Fatma, you are usually so fast to write that the community will probably help, and otherwise you will find someone at Databricks to help. But now it's been several days. Is everything OK with you?

Re: How to enable/verify cloud fetch from PowerBI

Anonymous — Mon, 07 Feb 2022 16:28:30 GMT

@Erik Parmann - We are looking for someone to help you. Thank you for your continued patience.

Re: How to enable/verify cloud fetch from PowerBI

pichlerpa — Tue, 08 Feb 2022 09:44:42 GMT

Hi everyone, we are facing exactly the same problem, result fetching takes far too long by connecting remotely via R Studio and ODBC for interactive workloads. We made sure to use the latest version of the ODBC Databicks connector together with the latest Databricks runtime with Cloud Fetch enabled. Unfortunately, without any effect. We tried:

scaling up the cluster
tweaking the connector (RowsFetchedPerBlock, UseNativeQuery, etc.)
using Databricks Connect with Apache Arrow as an alternative (untraceable OOM error by transferring (collect) Spark DataFrame to local R)

Re: How to enable/verify cloud fetch from PowerBI

pichlerpa — Mon, 14 Feb 2022 10:19:16 GMT

WRT databricks-connect, we were able to fix the OOM error by increasing the memory of the local Spark driver instance which is used for the remote communication and runs in the background:

conf <- spark_config()

conf$`sparklyr.shell.driver-memory` <- "10G"

databricks_connect_spark_home <- system("databricks-connect get-spark-home", intern = TRUE)

sc <-

spark_connect(

method = "databricks",

spark_home = databricks_connect_spark_home,

config=conf

)

Re: How to enable/verify cloud fetch from PowerBI

Erik — Mon, 14 Feb 2022 11:20:20 GMT

Thanks, but I have read that as well, which is why I am looking for a way to confirm that cloud fetch is actually working.

Databricks representative said that if we are using an update powerbi desktop version (I am using "2.100.1401.0 64-bit (desember 2021)") then this includes an updated version of the ODBC driver which sould use cloud fetch. Source. Can you confirm if this is right or wrong?

This is important for us because we have many users on powerbi, and it is a big difference for us if we just need to update their powerbi innstalation vs installing a custom odbc driver.

Re: How to enable/verify cloud fetch from PowerBI

gbrueckl — Mon, 28 Feb 2022 14:52:53 GMT

So I am on the latest version of Power BI Desktop and if I go to ODBC-Drivers and check the Simba driver it still shows 2.06.16.1019 which does not yet support cloudfetch

Re: How to enable/verify cloud fetch from PowerBI

gbrueckl — Mon, 28 Feb 2022 15:10:52 GMT

just an update after re-installing Power BI Desktop (download-able version):

if you check under C:\Program Files\Microsoft Power BI Desktop\bin\ODBC Drivers\Simba Spark ODBC Driver

you will see that it is actually a more recent version (2.6.18.1030) which should support cloud fetch

I had an older version of Simba Spark driver installed manually before - dont know which version Power BI was using then - but I uninstalled this one now and now Power BI can only use the most recent one it comes with

Re: How to enable/verify cloud fetch from PowerBI

Anonymous — Mon, 07 Mar 2022 00:13:50 GMT

@Erik Parmann - Does @Gerhard Brueckl's answer help?

Re: How to enable/verify cloud fetch from PowerBI

Erik — Mon, 07 Mar 2022 11:02:22 GMT

Thanks for the tip! I ventured into the powerbi folder (inside WindowsApps), and in the subfolder "bin\ODBC Drivers\Simba Spark ODBC Driver" I found the version by running "cat SparkODBC_sb64.dll | findstr Version". It printed "ProductVersion2.6.18.1030".

So this *should* support cloudfetch, but I still see the odd performance characteristics as described above. So my question still stands ( @Piper Wilson ), is there any way to *confirm* that cloud fetch has been used? This really seems like a thing one should be able to see some traces of in the Query Profile inside databricks.

Re: How to enable/verify cloud fetch from PowerBI

Erik — Mon, 07 Mar 2022 11:02:48 GMT

It helps, but it did not solve it. See my reply to him.

Re: How to enable/verify cloud fetch from PowerBI

Prabakar — Wed, 18 May 2022 09:41:56 GMT

Hi @Erik Parmann did you have a chance to look at this document?

https://docs.databricks.com/integrations/bi/jdbc-odbc-bi.html#arrow-serialization-in-odbc

Re: How to enable/verify cloud fetch from PowerBI

Erik — Tue, 24 May 2022 18:31:09 GMT

Yes, thanks. In my case we are using Azure databricks, and I am not able to find an equally detailed description of cloud fetch on azure databricks, and if there are any settings we might have which dissables it.

Re: How to enable/verify cloud fetch from PowerBI

Erik — Tue, 24 May 2022 18:39:07 GMT

@Arjun Kaimaparambil Rajan can you maybe check the query with ID 01ecdb90-5d68-1f39-a597-c1ce377fab5a with

Start time: 2022-05-24 20:36:03.058 (UTC+2)

End time: 2022-05-24 20:37:37.461 (UTC+2)

Re: How to enable/verify cloud fetch from PowerBI

arjun_kr — Wed, 25 May 2022 22:12:06 GMT

@Erik Parmann Yes. This query result fetch has cloud fetch enabled.

Re: How to enable/verify cloud fetch from PowerBI

cchalc — Thu, 23 Jun 2022 07:53:05 GMT

You would need to set EnableQueryResultsDownload Flag to 0 (zero) which will disable cloud fetch.

Re: How to enable/verify cloud fetch from PowerBI

cchalc — Thu, 23 Jun 2022 07:54:20 GMT

So why is ColumnarToRow required?

Re: How to enable/verify cloud fetch from PowerBI

pichlerpa — Wed, 26 Oct 2022 11:47:04 GMT

Hi everyone, check out my latest blog post to verify whether or not cloudfetch is actually used, maybe you also find some other optimizations there:

https://medium.com/creative-data/boosting-databricks-odbc-driver-be2cf08a7a4a?sk=bd814e0c3d6a9b32beb233496fcba242

Re: How to enable/verify cloud fetch from PowerBI

pulkitm — Mon, 27 Feb 2023 15:24:33 GMT

Guys, is there any way to switch off CloudFetch and fall back to ArrowResultSet by default irrespective of size? using the latest version of Spark Simba ODBC driver?

Re: How to enable/verify cloud fetch from PowerBI

datadrivenangel — Thu, 23 Jan 2025 15:40:56 GMT

I'm troubleshooting slow speeds (~6Mbps) from Azure Databricks to the PowerBI Service (Fabric) via dataflows.

Drivers are up to date. ✅ PowerBI is using Microsoft's Spark ODBC driver Version 2.7.6.1014, confirmed via log4j.
HybridCloudStoreResultHandler is being used. ✅ Confirmed via log4j.
MapPartitionInternals is NOT using CloudStoreCollector. ⚠️ The Spark DAG ends with mapPartitionsInternal at HybridResultCollector.scala.

Does this mean that CloudFetch is not enabled here?

In the neo4j logs I also see CloudStoreBasedResultHandler receiving and responding to getNextCloudStoreBasedSet which I interpret as cloudFetch being enabled?