CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerytableau-apiinformation-schema
Vaibhav
Vaibhav

Reputation: 21

how can we identify big query queries ran using Tableau in big query information schema

Is there a way to identify queries ran where using Tableau in table INFORMATION_SCHEMA.JOBS_BY_PROJECT in GCP Big Query

Upvotes: 2

Views: 709

Answers (4)

Sander van den Oord
Sander van den Oord

Reputation: 12818

You can look at the job_id.

Most job_ids that start with job_ are originating from Tableau, Looker Studio or similar BI Tools.

Here's an example of how to categorize job's based on their job_id:

SELECT 

    CASE 
        WHEN STARTS_WITH(job_id, 'job_') 
             AND job_type = 'EXTRACT'
        THEN 'BQ UI data export to Google Drive'  -- this is an educated guess, please note that most job ids that start with 'job_' are made by Tableau or LookerStudio

        WHEN STARTS_WITH(job_id, 'job_')
        THEN 'tableau or lookerstudio job'  -- Tableau and Looker Studio seem to use job_ as a prefix

        WHEN STARTS_WITH(job_id, 'scheduled_query_')
        THEN 'scheduled_query'
    END

FROM `your_project_name.region-eu.INFORMATION_SCHEMA.JOBS`

Upvotes: 0

Sander van den Oord
Sander van den Oord

Reputation: 12818

You can enable query tagging:
https://help.tableau.com/current/api/rest_api/en-us/REST/rest_api_how_to_query_tagging.htm

See also:
https://community.tableau.com/s/question/0D58b00009m4H3qCAE/add-cost-labels-for-bigquery-jobs-to-map-query-costs-in-gcp-to-tableau-dashboards

Enabling query tagging will result in:

the related query has a string appended beginning with /* "tableau-query-origins": "..." */. That string includes the LUID of the originating workbook, dashboard, or view.

Although this user is having issues with setting queryTaggingEnabled=True through the Tableau REST API for Bigquery. This might be related to datasources that use oAuth:
https://github.com/tableau/rest-api-samples/issues/69

Upvotes: 0

Sander van den Oord
Sander van den Oord

Reputation: 12818

The best way is using a service account.

You can recognize some Tableau queries, because they use certain field names:

case when contains_substr(lower(query), 'sum_calculation_') 
          or contains_substr(lower(query), '__copy__')
     then 1 else 0 end

But you can't recognize all queries in that way unfortunately.

Upvotes: 0

Craig Cunningham
Craig Cunningham

Reputation: 85

If you look at the query column in INFORMATION_SCHEMA.JOBS_BY_PROJECT you'll see

SELECT'Custom SQL Query'
...

This is a way I've just discovered to ID Tableau running the query. Not concrete, but may give an indication/starting point.

Another way would be to use a service account to run the queries from Tableau and then pick it up from the user_email column.

Upvotes: 1

Related Questions