topic Re: Getting data from Databricks into Excel using Databricks Jobs API in Community Articles

Getting data from Databricks into Excel using Databricks Jobs API

nk25 — Wed, 01 Jan 2025 00:37:16 GMT

If you have your data in Databricks, but like to analyse it in Excel, you can use Web API on Power Query. It allows you to not just query an existing table, but also trigger the execution of a PySpark notebook using Databricks Jobs API, and get the data back. This is something I developed for a client recently and thought this might be useful for many data professionals. I have explained the approach in full in my blog post here.

Note that there are other ways to get data from Databricks into Excel; e.g. installing an ODBC driver, or using an SQL endpoint. However, these were not available in my case, because installing a driver wasn't an option, and there was no SQL endpoint available. Using Power Query Web API didn't require any new installation, and also enabled triggering a notebook, which provided more flexibility than other options.

Re: Getting data from Databricks into Excel using Databricks Jobs API

NandiniN — Wed, 01 Jan 2025 06:05:27 GMT

Databricks does have integration options for Excel.

https://docs.databricks.com/en/integrations/excel.html

Re: Getting data from Databricks into Excel using Databricks Jobs API

nk25 — Wed, 01 Jan 2025 08:37:18 GMT

Yes but the approach I suggested above does not require installing an ODBC connector (which may not be an option in many companies), and uses Power Query Web API functionality. This gives you flexibility to not just query an existing table, but to execute a notebook and get data back into Excel.

Re: Getting data from Databricks into Excel using Databricks Jobs API

NandiniN — Wed, 08 Jan 2025 23:18:41 GMT

Got it, yes you have specified the same in your message. Thanks for sharing.