Best approach for querying Databricks from Composable?

[botknoa]

What is the recommended solution for querying databricks from a dataflow?

[data-usr]

One approach is to configure an ODBC connection to Databricks within Composable so you can run queries from Composable resources (QueryViews, DataFlows, DataLabs, etc.).

The first thing you’ll need to do is download and install the 64-bit Simba Spark ODBC Driver.
This installation needs to be performed by your sysadmin on the server where Composable is running

Next, you’ll need to create a new ODBC Connection Settings Key in the Composable Key Vault.

Below are the settings that you’ll need to specify.

• Host: This is the same host name you use for accessing Databricks via the user interface (example: xyz.azuredatabricks.net).
• Username: token
• Password: To create an authentication token in Databricks, click on the profile icon in the upper right corner of Databricks. Under ‘User Settings’, click ‘Access Tokens’. Then click ‘Generate New Token’. Paste the token into this password field.
• SSL: 1
• transportMode: http
• ThriftTransport: 2 (2 corresponds to SparkThriftServer (Spark 1.1 and later))
• httpPath: This is the path to the cluster that will be used to execute the query. Click on your cluster in the Databricks UI. Then under ‘Configuration’, scroll down to ‘Advanced Options’. Then click on the tab ‘JDBC /ODBC’. There you will see a field called ‘HTTP Path’.
• AuthMech: 3 (3 corresponds to UserName and Password)
• UseSystemTrustStore: 1

If you receive the error ERROR [HY000] [Simba][ThriftExtension] (14) Unexpected response from server during a HTTP connection: SSL_connect: certificate verify failed., then set the UseSystemTrustStore value to 1. Otherwise, you can ignore the setting UseSystemTrustStore.

More info is available in a technical blog post at: https://blog.composable.ai/2022/03/30/configuring-databricks-odbc-connection-in-composable/

[botknoa]

What if user access tokens have been disabled in Databricks due to security constraints?