Support Databricks Unity Catalog

[josek]

Please add support for Databricks' Unity Catalog

Currently, when selecting a Databricks-connection in the “Connect In-DB”-tool, and opening the “Query Builder”, only tables in the catalog named “hive_metastore” are listed. That is, Alteryx submits the following SQL query to Databricks:

Listing tables 'catalog : hive\_metastore, schemaPattern : %, tableTypes : null, tableName : %'

However, with Unity Catalog in Databricks the namespace is three-tier and there may be multiple catalogs (and not just the "hive_metastore" catalog), see https://docs.microsoft.com/en-gb/azure/databricks/lakehouse/data-objects#--what-is-a-catalog

I reached out to Alteryx support, which replied that you currently have a feature request for implementing this change (ID TDCB-4056) and they furthermore suggested that I post here.

Thanks in advance.

[phongp]

🙏

[phongp]

Do we know if this function will come to Alteryx? 

We have a connection to databricks, but by default it will just connect to hive_metastore. 

[peterma]

What happens if you manually write a statement on the SQL editor in alteryx that refers to a table using the unity catalog?

i.e "select * from catalog.schema.table_name"

Does it work like that? 🤞

I'm starting to get worried as we may be forced to swap to the catalog soon - Would it just be a case that the query builder view doesn't work as it cannot view the catalog, but if you manually write SQL in the editor referring a table using the catalog system it works as intended?

I would appreciate any responses here, or workarounds 😓 It may cause significant impact to our processes.

[phongp]

Hi Peterma, 

that work-around give us the desired results "select * from catalog.schema.table_name".

However - we need to use Databricks to find the tables we want to query. Many of our alteryx user doesnt know how to use databricks. Only user who can use databricks will know which catalog, schema and table_name they should query. Downside of the workaround :( 

[peterma]

Thank you for the quick response! You have just saved me from a near future of restless nights 😴

Although this isn't necessarily ideal, it's better than not working at all.  Lets hope Alteryx adds in proper support soon 🤞 This idea page has my upvote

[renat_isch]

HI @peterma ,

Thanks for your idea suggestion. My name is Renat, I am Alteryx PM responsible for data connectors. Just meant to get back to you and confirm that Alteryx 2023.2 release added support for Databricks Unity Catalog. You can find additional details about Dbx UC at this page: https://help.alteryx.com/current/en/designer/what-s-new-in-designer.html#databricks-unity-catalog-65...

Let me know if you have any questions about the implementation.

Best,

Renat 

[avanibhatnagar]

@phongp & @peterma  - Were you guys able to make this work with select query? What is the catalog name before schema and table name supposed to be?

[peterma]

@avanibhatnagar This should work with select queries on in DB connect tool, you may just need to append on a "cataloguename." to the table name (enthesis may, it seems Alteryx can detect a default catalogue if set by IT on azure, i think?). I don't know what your catalogue will be called as its going to depend on your business setup.