BigQuery and Data Studio - Extracting the value of @DS_USER_EMAIL in a query

user_536485 0
Viewed 410

So in the BigQuery console you can see the queries that have been run by your users. Data Studio supplies a parameter called @DS_USER_EMAIL that contains the email of the user that made the query.

We need that email for billing reasons: We need to bill/notify people based on their usage.

An example query of this, logged in BQ:

select @DS_USER_EMAIL as user_email from test_table;

So I can only see the variable, not the resolved value. Was hoping the logged query would be actual query run like so:

select 'test@test.com' as user_email from test_table;

Any way around this? If not, what are our options for getting the email?

google-data-studio google-bigquery
1 Answers

From my understanding, @DS_USER_EMAIL can be used to create dynamic reports, so certain users get access to specific data (Ref)

If you are looking for a way to check total bytes processed based on the user e-mail you can try using the INFORMATION_SCHEMA tables and JOBS_BY_ORGANIZATION (Documentation Link)

An example:

SELECT
 job_id,
 creation_time,
 user_email, 
 total_bytes_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE state == "DONE"
user_73405210
Related