CODEWITHSUNDEEP
google-bigquery
Alex
Alex

Reputation: 1849

Query returns empty results with BigQuery/User permissions but correct results with BigQuery/Admin permissions

When I perform query under service account with BigQuery/User permissions I get empty results, there are no errors but results are empty. If I change permissions of service account to BigQuery/Admin I get correct results back. If permissions are insufficient I would expect error pop-up instead of empty results. I can't post the query. Query consists of one DECLARE/SET operator and one SELECT with ARRAY_AGG and subquery.

Upvotes: 0

Views: 997

Answers (1)

aemon4
aemon4

Reputation: 1112

The issue seems to be with permissions but may be more complex that I initially thought.

To check my theory I created a service account with the role BigQuery user and ran the following commands from a new instance:

gcloud auth activate-service-account [ACCOUNT] --key-file=[KEY_FILE]

bq query --nouse_legacy_sql 'SELECT
   COUNT(*)
 FROM
   `bigquery-public-data`.samples.shakespeare'

it worked well and returned the proper result.

I checked that I was using the proper account by running:

gcloud auth list

Furthermore, in the documentation it states that "For any job you create, you automatically have the equivalent of the bigquery.jobs.get and bigquery.jobs.update permissions for that job." Meaning, if you are able to run a query you should be able to retrieve the results for it.

Can you check if querying public data yields the proper result for you or still blank results while using the BQ User service account?

Note: I realized that specifying a service account in the bq query call had been deprecated, so I had to recheck doing it the proper way

Upvotes: 1

Related Questions