topic Re: How do I setup a connection to get data from unity catalog into MS Access? in Warehousing & Analytics

How do I setup a connection to get data from unity catalog into MS Access?

Brammer88 — Mon, 10 Jun 2024 11:13:19 GMT

Dear community,

Im trying to setup a connection to get data from a catalog / underlying tables from that catalog and load it directly into MS Access 365 (latest version). Is it possible to do this? What would be the best practice?

Thanks,

Bram

Re: How do I setup a connection to get data from unity catalog into MS Access?

Yeshwanth — Mon, 10 Jun 2024 14:18:38 GMT

@Brammer88 have you tried using PowerBI? Do you think it can help with your use case?

I am attaching the link which has the steps to connect with PowerBI: https://docs.databricks.com/en/partners/bi/power-bi.html#connect-power-bi-to-databricks

Re: How do I setup a connection to get data from unity catalog into MS Access?

Brammer88 — Mon, 10 Jun 2024 14:25:14 GMT

Thanks, yes we are using powerbi too, but we have a specific requirement for which we should be able to connect directly to our legacy MS access databases too.

Re: How do I setup a connection to get data from unity catalog into MS Access?

jakubk — Fri, 21 Jun 2024 05:43:03 GMT

I have set it up in Excel where users can pull data from databricks directly

Unfortunately it looks like access doesn't easily support DSNless connections via a URL so you're gonna have to set up a user or system odbc DSN using the simba spark driver

https://www.databricks.com/spark/odbc-drivers-download

Then to use it in access - New data source > From other sources > odbc database > select your simba databricks odbc connection

the connection string (assuming azure databricks and SSO/MFA)

Driver=Simba Spark ODBC Driver;Host=[redacted].azuredatabricks.net;Port=443;HttpPath=/sql/1.0/warehouses/[redacted];SSL=1;AuthMech=11;Auth_Flow=2;Catalog=samples;Schema=default

how you configure it depends on how you want to connect. the personal access token way is described here - https://stackoverflow.com/questions/75147926/how-do-i-create-dsn-of-azure-databricks

we use SSO via azure AD so i selected oauth2.0 and set oauth type to browser based auth code

I quickly tested this and it worked fine, i was able to import databricks tables into access

Hope that helps

Re: How do I setup a connection to get data from unity catalog into MS Access?

Brammer88 — Wed, 26 Jun 2024 14:49:53 GMT

Hi @jakubk ,

Thanks for your response, I have tried to make a connection via spark simba odbc driver and it works. So that is great, thanks. I also have been able to connect via oauth2.0.

The only thing I cannot achieve via access is to change to tables from inside the unity catalog, i.e. I can only see tables in the hive_metastore and cannot change the default / or to any other catalog. I cannot find where I need to configure the connection string, can you help me to achieve this?

Btw from Excel I do see all catalogs if I connect via the same odbc driver. Also, I would be keen to learn how you setup a DSNless connection via Excel, if you could give me any pointers to documentation that would be much appriciated.

Thanks again!

Bram

Re: How do I setup a connection to get data from unity catalog into MS Access?

jakubk — Fri, 28 Jun 2024 00:35:06 GMT

I can't help with the hive metastore only in access - are you sure you're using a databricks endpoint that has unity catalog support? I could see my UC in both access and excel

dsnless in excel

From ODBC > advanced options > paste in your connection string

I just choose custom/default as the auth type for oauth2.0 and leave the login/password box blank

Re: How do I setup a connection to get data from unity catalog into MS Access?

njcheng — Wed, 28 Aug 2024 21:33:39 GMT

Hi! @jakubk:

I have the same issue that I cannot achieve via access is to change to tables from inside the unity catalog, I can only see tables in the hive_metastore and cannot change the default / or to any other catalog. The same DSN is working fine to connect UC in excel. Which is the version of your ms access db? thanks

Re: How do I setup a connection to get data from unity catalog into MS Access?

Stefania003 — Sat, 31 Aug 2024 23:35:20 GMT

I have the same issue. Has anyone been able to find a solution?

Re: How do I setup a connection to get data from unity catalog into MS Access?

Stefania003 — Sun, 01 Sep 2024 01:11:51 GMT

I have the same issue as well. The odbc connection works fine in excel but when I use it with ms access all I can see is the hive_metastore. Has anyone here found a solution? @Brammer88 did you resolve your issue? If you found a solution I'd really appreciate if you could share it. Thanks so much.

Re: How do I setup a connection to get data from unity catalog into MS Access?

Luise7x7 — Thu, 03 Oct 2024 07:35:51 GMT

Thank you so much. I've been beating my head against this issue for ... years. Your solution worked, and has saved me massive amounts of further head beating. You rule.

Re: How do I setup a connection to get data from unity catalog into MS Access?

njcheng — Thu, 03 Oct 2024 20:16:44 GMT

Interesting! I found a workaround worked:

Add any table in hive_metastore.
Update the connection string of the ODBC data source in Linked Table Manager, as mentioned by @jakubk . Make sure the catalog and schema are one with target table in unity catalog:

Driver=Simba Spark ODBC Driver;Host=[redacted].azuredatabricks.net;Port=443;HttpPath=/sql/1.0/warehouses/[redacted];SSL=1;AuthMech=11;Auth_Flow=2;Catalog=XXX;Schema=XXX

3. Click the table in linked table manager, then click relink to update with target table name in unity catalog

4. Rename table name in All Access Object Panel

Re: How do I setup a connection to get data from unity catalog into MS Access?

Stefania003 — Wed, 09 Oct 2024 14:19:28 GMT

Thanks so much. It just does not work for me. I can't find a place in Access where I can paste in the connection string. I found a workaround by connecting to the table in the unit catalog via Excel and then linking the Excel table in Access. Ugly but it works

Re: How do I setup a connection to get data from unity catalog into MS Access?

jakubk — Fri, 11 Oct 2024 00:49:59 GMT

@njcheng @Brammer88

I went back to have another look - i had the same experience where i couldn't see the UC tables in any tool that used the oauth connection type (back in early sept) - but tried it again just now and i can see UC tables again in access

Which endpoint are you connecting to? I'm using sql warehouse classic

Did you try using a PAT instead of oauth?

I dont really have any other suggestions, sorry

Re: How do I setup a connection to get data from unity catalog into MS Access?

atrev66 — Mon, 04 Nov 2024 15:51:30 GMT

Hey guys,

See here for a solution to this issue. You can specify the catalog when you setup the odbc driver.

ODBC data source to connect to a Databricks catalo... - Databricks Community - 96354