Connecting data from Databricks (as input) to Alteryx Desinger

[SachinCariappa]

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! 

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