Can not connect dbt cloud or dbt core to databricks

Question

I am having issue connecting my dbt cloud and dbt core to databricks

I have read these 4 links, but still can not connect

https://docs.databricks.com/integrations/prep/dbt-cloud.html#connect-to-dbt-cloud&language-Cluster

https://docs.databricks.com/integrations/prep/dbt.html

https://docs.getdbt.com/reference/warehouse-profiles/databricks-profile

https://github.com/databricks/dbt-databricks

on dbt-cloud:

When I test the connection during the project creation step, it passed the test. however when I use the connection to create a job and run, it returns this message:"Cannot set database in spark!"

Edit: the issue once has been fixed but it comes back again.

original fix: The dbt-core connection issue has been fixed. The issue is caused by the python certificate issue on MacOS. please refer to this link for the solution

on dbt-core: this how I set up my profiles.yaml file base on the documentations:

    databrick_dbt_lakehouse:
      outputs:
        dev:
          host: adb-755xxxxxxx7.7.azuredatabricks.net
          http_path: /sql/protocolv1/o/755xxxx7/0517-xxxxxx-xxxxxx
          schema: default
          threads: 1
          token: dapi<my token>
          type: databricks
      target: dev

Note:

for http_path i have tried both with or without backslash (/) before sql/...
I assume the schema means database name. I have tried 2, but none of them works
I use pipenv with python version 3.8.8

when I run dbt debug I got this message:

check failed:
   dbt was unable to connect to the specified database.
   The database returned the following error:

Runtime Error
   Database Error
   failed to connect

Please help, thanks

http_path seems to be different. I see /sql/1.0/endpoints/...... in all my endpoints. — Ganesh Chandrasekaran, May 18 '22 at 18:35
dbt-core connection fixed by install python certification on my MacBook — Julian Eccleshall, May 19 '22 at 12:13
was the issue resolved by a change to the `profiles.yml`? or by installing the `certification` library? — Anders Swanson, May 19 '22 at 18:34

score 1 · Answer 1 · edited May 19 '22 at 18:28

1

Your http_path seems wrong. Here is an extract from my profiles.yml

databricks_dbt_demo:
  outputs:
    dev:
      host: adb-redacted.azuredatabricks.net

      http_path: /sql/1.0/endpoints/redacted  # for SQL Endpoint
      # http_path: /sql/protocolv1/o/{ORG-ID}/{CLUSTER-ID}  # for All-Purpose Cluster
      schema: your_database_here
      token: your_personal_access_token_here
      type: databricks
  target: dev

Are you using a cluster (e.g. All Purpose Cluster) or a SQL Endpoint?

edited May 19 '22 at 18:28

Anders Swanson

3,637
1
18
43

answered May 19 '22 at 11:02

Bilal and Olga

3,211
6
30
33

I am using all propose cluster. your path looks like a sql endpoint. on their documentation it should support both. – Julian Eccleshall May 19 '22 at 11:22
@JulianEccleshall if you're using an all-purpose cluster, `/sql/protocolv1/o/{ORG-ID}/{CLUSTER-ID}` – Anders Swanson May 19 '22 at 18:26
yes, this is the setup in my profile – Julian Eccleshall May 20 '22 at 01:21

Julian Eccleshall · Answer 2 · 2022-05-24T13:16:26.977

Edit: This issue is came back again. I am writing this comment on the 3rd day after the original fix

When I run dbt run or dbt snapshot the returns this error message:

Encountered an error:
Runtime Error
   Database Error
      failed to connect

original fix:

The issue is caused by the python certificate issue on MacOS. please refer to this link for the solution.

The dbt cloud issue is caused by the incorrect schema name in the yaml file

Can not connect dbt cloud or dbt core to databricks

2 Answers2

Linked