dbt cli on gcp cloudshell

Help
1

The problem I’m having

I’ve installed dbt cli bigquery on GCP cloud shell and cloned my github repo. Now when I’m trying to run a dbt model it throws error as -

Encountered an error:
Database Error
  expected str, bytes or os.PathLike object, not NoneType

I’ve created profile.yml in ~/.dbt folder with the following structure -

default:
  target: dev
  outputs:
    dev:
      dataset: my_dataset
      job_execution_timeout_seconds: 1000
      job_retries: 1
      location: EU
      method: service-account
      priority: interactive
      project: my_project
      threads: 4
      type: bigquery
      keyfile_json:
        type: service_account
        project_id: XXXXXXX
        private_key_id: vasljasgjaspogjsapodgjasaklgjsagjs
        private_key: -----BEGIN PRIVATE KEY-----\nYYYYYYY\n-----END PRIVATE KEY-----\n
        client_email: XXXXXXXXX.iam.gserviceaccount.com
        client_id: 9577623947510934752394752
        auth_uri: https://accounts.google.com/o/oauth2/auth
        token_uri: https://oauth2.googleapis.com/token
        auth_provider_x509_cert_url: https://www.googleapis.com/oauth2/v1/certs
        client_x509_cert_url: https://www.googleapis.com/EWPOTEOIGH;LSGJJ;LJgcpsecuritykey-gbl-ww-pd.iam.gserviceaccount.com

The dbt_profile.yml file -

# Name your project!
name: 'my_new_project'
version: '1.0.0'
config-version: 2



# This setting configures which "profile" dbt uses for this project.
profile: 'default'


# These configurations specify where dbt should look for different types of files.
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"  
.......
......
.......

Run command → dbt run --select model_name

Can anyone please help me point what I’m doing wrong?

2

When you run dbt debug is everything fine? Just to check if there is really some problem with the profiles and the project file.

3

Yes brunoszdl, you are right the dbt debug gives the error message -

Running with dbt=1.4.4
dbt version: 1.4.4
python version: 3.9.2
python path: /home/dbt_dive/dbt/dbt/bin/python
os info: Linux-5.15.65+-x86_64-with-glibc2.31
Using profiles.yml file at /home/dbt_dive/.dbt/profiles.yml
Using dbt_project.yml file at /home/dbt_dive/git_repo/dbt_project.yml

Configuration:
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]

Required dependencies:
 - git [OK found]

Connection:
  method: service-account
  database: gcp-project-name
  schema: dbt_dive
  location: EU
  priority: interactive
  timeout_seconds: 1000
  maximum_bytes_billed: None
  execution_project: gcp-project-name
  job_retry_deadline_seconds: None
  job_retries: 1
  job_creation_timeout_seconds: None
  job_execution_timeout_seconds: 1000
  gcs_bucket: None
  Connection test: [ERROR]

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

  >Database Error
  expected str, bytes or os.PathLike object, not NoneType

Check your database credentials and try again. For more information, visit:
https://docs.getdbt.com/docs/configure-your-profile

I don’t know what’s the problem here? The database & execution_project are same as the GCP poject name “gcp-project-name”. Can anyone spot something wrong here? Please help.

4

The ‘method’ key is incorrectly indented or it was just a copy-and-paste problem?

And the only thing your profiles differ from mine is that client_id and private_key are between single quotes ' 'on my side.

Try that and see if it works.

5

Hi brunoszdl, thanks for your quick response!

‘method’ key was a copy & paste error, I’ve corrected that.
I tried wrapping client_id and private_key in ’ ’ single quotes but still it didn’t work. I really don’t know what I’m doing wrong?

6

So, let’s take a step back to better analyze where the problem is. Configure your profile with just the keys needed, without the optional ones

and then try dbt debug. Then we can see if the credentials problem is in the optional ones or not.

my-bigquery-db:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: service-account-json
      project: [GCP project id]
      dataset: [the name of your dbt dataset]
      threads: [1 or more]

      # These fields come from the service account json keyfile
      keyfile_json:
        type: xxx
        project_id: xxx
        private_key_id: xxx
        private_key: xxx
        client_email: xxx
        client_id: xxx
        auth_uri: xxx
        token_uri: xxx
        auth_provider_x509_cert_url: xxx
        client_x509_cert_url: xxx
7

No, I think the problem is in my credentials as it’s still giving me the same error. But the same credentials works for dbt cloud!

8

Let me see just one more thing, can you paste here the log from log/dbt.log related to the debt debug?
It will be at the bottom

9

Hi brunoszdl,

This is the logs generated, I’ve pasted the last few lines

05:16:00.543072 [debug] [MainThread]: Acquiring new bigquery connection 'master'
05:16:00.545125 [debug] [ThreadPool]: Acquiring new bigquery connection 'list_'
05:16:00.545828 [debug] [ThreadPool]: Opening a new connection, currently in state init
05:16:00.546292 [debug] [ThreadPool]: BigQuery adapter: Got an error when attempting to create a bigquery client: 'expected str, bytes or os.PathLike object, not NoneType'
05:16:00.547996 [debug] [MainThread]: Connection 'master' was properly closed.
05:16:00.548340 [debug] [MainThread]: Connection 'list_' was properly closed.
05:16:00.548646 [info ] [MainThread]:
05:16:00.549211 [info ] [MainThread]: Finished running  in 0 hours 0 minutes and 0.01 seconds (0.01s).
05:16:00.550158 [error] [MainThread]: Encountered an error:
Database Error
  expected str, bytes or os.PathLike object, not NoneType
05:16:00.551455 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0xxxxx0>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0xxxxx0>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0xxxxx0>]}
05:16:00.552013 [debug] [MainThread]: Flushing usage events

Output of dbt --version

Core:
  - installed: 1.4.4
  - latest:    1.4.4 - Up to date!

Plugins:
  - bigquery: 1.4.1 - Up to date!
10

There one more thing I’ve discovered, if I write a simple python program to execute a query it works -

>>> from google.cloud import bigquery
>>> client = bigquery.Client ()
>>> QUERY = ("select col1,col2 from project.dataset.table")
>>> query_job = client.query(QUERY)

I checked the python path → 

dbt_dive@cloudshell:~ (gcp-project)$ which python
/usr/bin/python

(dbt)dbt_dive@cloudshell:~$ which python
/home/dbt_dive/dbt/bin/python

The python paths are different. Could this be the reason? Please help in correcting this.

11

Ok, so the problem is a python error while creating the BigQuery client.

Sorry, I don’t know how to proceed now :frowning: , maybe @joellabes or another one who knows the dbt-bigquery connection well may help.

12

Hey @dbt_dive ,

Not sure if you spotted this, but you have the wrong method in the file. Instead of service-account you should use service-account-json.

1 Like
13

Waw that’s really works! Thanks a lot