Azure DataBricks custom connector with Spotfire

[Srinivas Guddanti]

Hi Team,

We have Spotfire application which is hosted in Azure Cloud. We are trying to validate the test connectivity of Azure Databricks data sources into Spotfire through custom connector.

I just want try to make this work "sign in with Microsoft Azure AD" instead of personal access tokens option.

I am following this document "Custom Connector For TIBCO Spotfire® To Connect To Azure Databricks"

I need some clarification on the above document in section 4. "App Registration Parameters" steps.

To make it work in both Analyst client and web player, what kind of app redirect URIs we should request to Azure AD team to register the Spotfire app?

my sample Spotfire Application URL : https://spot-cloud.com/

Please let me know what kind of formats we should request for app registration parameters for Analyst & web clients? If possible please provide the formats for my sample application URL as example.

[Peter McKinnis]

Srinivas,

The example URLs on page 12 and 13 of the documentation should be helpful. You will want to register the following URLs:

For analyst, assuming ports 55931 and 55932 are used when configuring the redirect ports in the Configuration Tool:

http://localhost:55931/authorize/code

http://localhost:55932/authorize/code

For Web Player, assuming your Spotfire Server is https://spot-cloud.com:

https://spot-cloud.com/spotfire/wp/oauth2/code

Regards,

Peter

[Srinivas Guddanti]

@Peter McKinnis​ Thanks for the update.

I have followed the same steps and provided API permissions too. But when I accessed from Analyst client (connect to --> Azure Data bricks (custom) -->New connection --> Microsoft Sign in option

We have granted API permissions for the Azure Databricks / user_Impersonation and Microsoft_Graph /User.Read in Azure AD.

But not sure why still some below message is getting. Could you please help me on how to resolve this popup screen?

Much appreciated your support on this.

Regards,

Srinivas G

[Peter McKinnis]

Srinivas,

An Azure AD Admin needs to grant consent to the application you have registered in Azure AD. Even though the API Permissions page may have Admin Consent required as no, the statement the Configured permissions states: The "Admin consent required" column shows the default value for an organization. However, user consent can be customized per permission, user, or app. This column may NOT reflect the value in your organization, or in organizations where this app will be used. Then there is a link to this page: https://learn.microsoft.com/en-us/azure/active-directory/manage-apps/configure-user-consent

Hope that helps. The admin consent stuff can be confusing in Azure, since the information on the API Permissions page can be wrong.

Regards,

Peter

[Srinivas Guddanti]

Hi Peter,

Thanks for your timely response.

Yes, really confusing the documentation on Azure steps which we need to take care.

We have given API permissions like the below in Azure AD. Still we need to do some settings need to change from Azure AD?

Regards,

Srinivas G

[Peter McKinnis]

Srinivas,

I assume with the settings as currently shown in the screenshot you are still having issues. Is that correct?

You may still need to have Admin consent granted in your Azure AD environment at some over level. Because each customer's Azure AD setup can be different, the admin consent steps are not something I have done myself. The admin consent settings can be confusing. Other customers have had similar issues with Azure AD. This article, which you may have already read, may be helpful: Learn more about permissions and consent.

Regards,

Peter

[Srinivas Guddanti]

Hi Peter,

Thanks for the update. After couple of modifications from user consent option, now it's working with Microsoft sign in option.

Now we can make new connection --> Microsoft Sign In Option --> Authentication Success --> Providing Databricks data source connection details --> Only getting default catalog only.

Not sure why we are not getting all other unity catalogs through Spotfire custom Azure data bricks connector? Also we tried to install "SimbaSparkODBC-2.6.26.1045" and configured, through load ODBC option also tried, but still users getting only default catalog only.

Not sure why from Spotfire it is not getting unity catalogs through Data bricks connector/ SIMBA ODBC driver.

Could you please let me know your thoughts on this.

Regards,

Srinivas G

[Peter McKinnis]

Srinivas,

Our first thoughts are that the application registered may need to be granted access to the unity catalogs, but we don't know for sure. I am not familiar enough with unity catalogs to know if there might be a permissions issue or if the query for databases available isn't returning them.

Is it possible to login to the Azure Databricks environment using any of the authentication options available with the built-in Azure Databricks Data Connector which uses the Apache Spark SQL Connection? Authentication methods that might be relevant are username and password or Azure HDInsight Service. The custom connector is built off of the built-in connector so it would be good to know where the issue is.

We might need to get on a call and discuss as well.

Thanks,

Peter

[Srinivas Guddanti]

Hi Peter,

Please be informed that, connection seems to be routed to default catalog always, thus unable to connect on catalog level. I think this is something need to be checked with Azure Data bricks team.

We are following this work around: Existing data source connection can be used to query other catalogs and schemas with the custom query option in Spotfire.

I will keep you posted if any questions on this data bricks integration part.

Much appreciated your support on this.

Regards,

Srinivas G

[Srinivas Guddanti]

Hi Peter,

We have requested one dummy project from Azure Databricks SQL, and I have access to one catalogue as well. But still while I make connection from Spotfire end, I couldn't see any catalogues listing after successful connection. Please find the screenshot for the reference.

Business user who is having access to default "hive_metastore" catalogue from Azure Databricks end, that user alone can see the default table which is residing in hive_metastore catalogue.

By any chance, Spotfire Azure Databricks custom connector is always trying to look for default catalogue "hive_metastore" ? If users who doesn't have access to hive_metastore, they couldn't able to view the catalogue options post successful connection?

If possible, we may connect over call during CET business hours?

Regards,

Srinivas G

[Peter McKinnis]

Srinivas,

Are you able to try the built-in Azure Databricks Connector and connect via username and password to your datasource? I am asking because this will let us know if this a product issue or an issue with the custom connector. The Azure Databricks Custom Connector is based on the connector that comes with the product but adds the ability to connect via OpenID.

Thanks,

Peter

[Dave Leigh]

Hi Srinivas

The list of databases is retrieved using the SQL Statement "show schemas" executed directly using the authentication context previously defined on the dialog, a combination of your OpenID token and the connection details for the Databricks instance. Here is an example from my simple cluster setup:

SQLExecDirect:

In: hstmt = 0x0000000000609EF0, szSqlStr = "show schemas", 

cbSqlStr = -3

Return: SQL_SUCCESS=0

Get Data All:

"databaseName"

"default"

1 row fetched from 1 column.

There are no parameters to this call and the Databricks documentation doesn't describe what permissions might be needed in order for this call to succeed.

https://docs.databricks.com/sql/language-manual/sql-ref-syntax-aux-show-schemas.html

I would suggest that you try running this SQL on your Databricks instance and see what permissions are required to have the call return the schema that the user in question has access to.

Cheers

Dave

[Srinivas Guddanti]

Hi Peter,

Sorry for the late response.

I am trying to connect through build in connector [ Apache Spark SQL] connector, but it is failing with some error. As we don't have username and password, we have only Personal access tokens.

I tried directly connected the Azure SQL warehouse and it's connected through SSO. So not sure about username and password how to connect it.

Also, trying from ODBC driver- DSN setup with this SQL data warehouse details, but that test connection also failing.

External error:

ERROR [HY000] [simba][ThriftExtension] (14) Unexpected response from server during a HTTP connection: connect() failed: errno = 10061.

ERROR [HY000] [simba][ThriftExtension] (14) Unexpected response from server during a HTTP connection: connect() failed: errno = 10061.

As we configured for Azure Databricks custom connector option, this one is connecting with Microsoft Signin Option [this means it is going with my email as the username].

Not sure how to connect through this build in connector option without username and password.

Regards,

Srinivas G

[Srinivas Guddanti]

Hi Dave,

I have opened Microsoft Azure Databricks console and executed query:

--> In Databricks SQL editor I can see multiple Schemas, but I have access to only one schema [ lab_ssa ]. Attached screenshot [pic: Databricks-Schemas View].

--> I guess default Schema is : "hive_metastore" and when I ran the "show databases" query, it is fall back to default and no results displaying. As I don't have access to hive_metastore. [ attached : hive_metastore-view ].

--> when I connected to lab_ssa schema and ran the "show databases" query, it returns the databases. attached screnshot [lab_ssa-Schema view]. Which is expected result as I do have access to this lab_ssa schema alone.

From Spotfire --> Azure Databricks custom connector --> Microsoft sign in option --> Authentication with my email ID - OIDC protocol --> Authenticated --> Connecting with SQL warehouse hostname & ODBC http path --> Unable to view the databases.

Which means Spotfire to SQL warehouse, it is always pointing to default schema - hive_meta store and not returning any databases as I don't have access to it ??

Please let me know if anything needs to be validated.

Regards,

Srinivas G

[Dave Leigh]

Hi Srinivas

I think it would be best to take a look at your problem over a web meeting. Please message me directly (click on my name above and then choose Message) so I can send you my email address.

Cheers

Dave

[Dave Leigh]

The underlying issue was identified as the use of the new Unity Catalog within Databricks which introduces the concept of a Catalog into the hierarchy.

The Connector was updated to allow the user to specify a Catalog when connecting. This is only required when using Unity Catalog mode. The User Guide describes how to specify the Catalog in the Advanced Options dialog. v16 of the Connector is now available on the download page.

Huge thanks to Srinivas and his coworkers for assisting us in identifying and resolving this issue!

Cheers

Dave