Connecting data from Databricks (as input) to Alteryx Desinger

Hello Together,

I have a scenario in connecting Databricks (as input connection) to Alteryx. In this scenario, please advice if we have the tool under In-Database tool pallet? If Yes, please assist as how we connect to databricks unity catalog (having a generic / service account) and what is the tool name. Thank you!

In Database

In-Database

Accepted answers

apathetichell

O.k. - let's go for what I have here:

checked:

Use Native Query

Fast SQL Perpare

Driver Config Take Precedence

Unicode SQL Character Types

Show System Table

Auto Reconnect

Unchecked:

Use Async Exec

Get Tables With Query

Use Only SSPI

Invalid Session Auto Receover

Enable Translation for CTAS

I also have:

hive.resultset.use.unique.column.names=false in my server side properties (that's manually configured to allow duplicate columns)

I'm on 2021.4 and Simba Spark ODBC Driver 2.06.23.1039 (so old stuff)

All comments

alexnajm

Yes Databricks is available through the Connect IN-DB tool - see the documentation here to see the setup! https://knowledge.alteryx.com/index/s/article/How-To-Configure-a-Databricks-Connection-1583461555625

apathetichell

Note - to write to databricks you use the datastream in tool - I use the IN-DB pallet extensively with databricks but write- I bring it on canvas via Datastream Out and then write via Datastream In. Later versions of Alteryx may support different features but may have their own bugs/issues.

avanibhatnagar

@alexnajm We are unable to see Unity Catalog using this In-Db connection. We are not on 2023 version of alteryx yet.

apathetichell

you don't need to be on 2023 to see Unity Catalog (I'm on 2021.4). There are lots of reasons why your Databricks connection could be wrong - I've posted a slew on this - but first - can you confirm that your connection works in ODBC 64 (ie if you hit Test - will it connect?) If not handle that first.

alexnajm

@apathetichell I am interested in this, can you post those discussions you mentioned and/or outline what you've done in the past to get tables to display?

apathetichell

@alexnajm - Unity Catalog specific or DB? We have two DB setups - and I believe the one we use less frequently is Unity Catalog. I do not remember any secondary setups that we did.

Hpitcockaa

Did you ever run into an issue where all of your test connections worked, but as soon as you selected the DB in the Connect In-DB tool your application stopped responding? I feel like I must be doing something wrong, but don't know what

apathetichell

set up your connection in odbc 64 for logging - see the logs? Assuming your connection is via token (vs SSO/active directory) the only time I've seen that kind of behavior in setting up an In-DB connection is when the initial metadata caching (ie the tables/schema/etc) takes 10-20 minutes. that would be if you have a huge number of tables/views/etc...

Is your root catalog set up as default?

Hpitcockaa

I set up the logs and got three reports. The last line of the final report is:

Jun 19 12:01:28.448 INFO 24276 Simba::ODBC::Connection::ExecuteCatalogFunction: SQLTables("hive\_metastore", NULL, "%", "TABLE,VIEW")

I'm not sure what a root catalogue is or how to change what it is set up as. Thank you for such a quick response.

apathetichell

hmm... this seems like a pretty obscure issue. can you confirm you are using token/PAT for your authorization? This looks like you may be able to connect - but may not be able to retrieve your metadata. This could be a driver setup, a databricks set up, a permission issue with your user/token or other. So there are a ton of potential issues here.

Here's what I'd start with:

1) When you connect via Test from ODBC - can you look at your logs? how are they different (ie do you see that issue)

2) What version of Alteryx/Databricks driver are you using

3) Can you check on your user/token's permission to a) start your cluster b) retrieve/read tables/schema objects metadata

4) catalog should be under database in your odbc64 config main driver screen it should be default

5) can you share a screenshot of your advanced settings in your odbc set up?

Hpitcockaa

I am using a token created through my DataBricks account.

1. The logs look fairly different because ODBC64 is just starting and cancelling requests, but I don't see noticeable differences when the connection is initializing or anything.