Determine which project I'm in and which are available

user_1024586 0
Viewed 54

I think I'm connected to BigQuery via ODBC:

isql -v BigQuery
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

My odbc.ini file looks similar to this:

[ODBC]
Trace=yes
TraceFile=/root/odbc.error


[BigQuery]
# 1 = User Authentication
Email=someemail@blah.iam.gserviceaccount.com
KeyFilePath=/service.json
Driver=/opt/simba/driver/lib/libgooglebigqueryodbc_sb64.so
OAuthMechanism=0
Catalog=someproject

With this ODBC connection, I assumed I would connect to project someproject. However, when I try to query a table I get:

SQL> select * from `someproject.ua.ua_user_daily` limit 10;
[37000][Simba][BigQuery] (100) Error interacting with REST API: Access Denied: Project someproject: User does not have bigquery.jobs.create permission in project someproject.
[ISQL]ERROR: Could not SQLPrepare

I wanted to know which projects are therefore available to me within the SQL terminal, but I could not find any command to do this.

How can I test my connection by looking at what projects I do have access to?

google-bigquery odbc
1 Answers

The Problem you are facing deals with roles, you need to grant the role bigquery.jobs.create to the service account. You can also add the role bigquery.user or bigquery.jobUser these two roles contain inside the role bigquery.jobs.create. Check all BigQuery roles that you can grant.

Additionally, if you want to use the same Service Account around multiple GCP projects see this post to see how you need to set it up.

You can Run the following Command in Cloud SDK to see which projects does your service account have access to:

gcloud projects list --impersonate-service-account=<your-service-account-email-address>

This command requires Cloud Resource Manager API to be enabled and resourcemanager.projects.list permission in your Service Account.

user_169293580
Related