How to get the cluster's JDBC/ODBC parameters programmatically?

Question

Databricks documentation shows how get the cluster's hostname, port, HTTP path, and JDBC URL parameters from the JDBC/ODBC tab in the UI. See image:

_{(source: databricks.com)}

Is there a way to get the same information programmatically? I mean using the Databricks API or Databricks CLI. I am particularly interested in the HTTP path which contains the Workspace Id.

Algid

Answer 1

You can use the Get operation of the SQL Analytics REST API (maybe together with List) - it returns the JDBC connection string as a part of response (jdbc_url field):

{
  "id": "123456790abcdef",
  "name": "My SQL endpoint",
  "cluster_size": "Medium",
  "min_num_clusters": 1,
  "max_num_clusters": 10,
  "auto_stop_mins": 30,
  "num_clusters": 5,
  "num_active_sessions": 30,
  "state": "RUNNING",
  "creator_name": "[email protected]",
  "jdbc_url":"jdbc:spark://<databricks-instance>:443/default;transportMode=http;ssl=1;AuthMech=3;httpPath=/sql/protocolv1/o/0123456790abcdef;",
  "odbc_params": {
    "host": "<databricks-instance>",
    "path": "/sql/protocolv1/o/0/123456790abcdef",
    "protocol": "https",
    "port": 443
  }
}

HTTP Path is also there, as path part of the odbc_params object.

Halibut

Awesome, looks like this is what I am after. However, I am getting: {"error_code":"FEATURE_DISABLED","message":"SQL Gateway is not supported for Org XXXX} Why listing endpoints is in preview? – Algid

interesting... Do you have SQL Analytics enabled for this workspace? I just checked my own Azure workspace, and API works just fine – Halibut

I think this is only applicable to sql endpoints not clusters. Kombajn zbożowy answer is more correct when it comes to Azure databricks. – Necessarily

Answer 2

Another way is to go to Databricks console

1. Click compute icon Compute in the sidebar.
2. Choose a cluster to connect to.
3. Navigate to Advanced Options.
4. Click on the JDBC/ODBC tab.
5. Copy the connection details.

More details here

Transcendent

Question was about programmatic access to that data :-) – Halibut

Above comment is true, this didn't answer the question asked, but I was still glad to find it here; easier/more direct path to achieve something I only need to do once. – Tacet

Answer 3

It's not available directly from Databricks API, but this is the template for cluster JDBC connection string:

jdbc:spark://<db-hostname>:443/default;transportMode=http;ssl=1;httpPath=sql/protocolv1/o/<workspace-id>/<cluster-id>;AuthMech=3;UID=token;PWD=<personal-access-token>

• db-hostname is your hostname of your instance URL
• workspace-id is the long numeric in your hostname (https://adb-1234512345123456.2.azuredatabricks.net/). It is available as workspaceId in output of az databricks workspace list or you can parse it from hostname
• cluster-id the cluster you want the connection string for
• personal-access-token is the token used for authentication

So all of above you already have or can get programmatically and substitute into template. It's a bit cumbersome, but that's the best we can do.

Uela

Answer 4

Adding to the solution offered by @Kombajn zbożowy

I used the following to get the cluster Id and workspace Id to build the Http Path dynamically.

cluster_id = spark.conf.get("spark.databricks.clusterUsageTags.clusterId")

workspace_id =spark.conf.get("spark.databricks.clusterUsageTags.clusterOwnerOrgId")

http_path = f"sql/protocolv1/o/{workspace_id}/{cluster_id}"

Ansley