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?
When you run dbt debug is everything fine? Just to check if there is really some problem with the profiles and the project file.
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.
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.
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?
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
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!
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
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!
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.
Ok, so the problem is a python error while creating the BigQuery client.
Sorry, I don’t know how to proceed now 
, maybe @joellabes or another one who knows the dbt-bigquery connection well may help.