How to Monitor GCP Costs for Users Utilizing the BigQuery Reports Extension [TEMPLATE]

In this article, we'll show you how to keep an eye on what you're spending on the Google Cloud Platform (GCP) with the help of the OWOX BI BigQuery Reports extension. This is important for organizations where employees run Queries and work with lots of data.

At OWOX BI, we're committed to being transparent and simplifying how you understand your use of our products. If you need to see who's running Queries and how much data they're processing, we've got a tool for you. It's a Google Sheets template called "OWOX BI Extension Usage Analysis based on GBQ logs." It gives you all the details on how the extension is used. We'll tell you how to get and set it up in this guide. 

Note:This template uses data from real-time metadata about all BigQuery jobs (INFORMATION_SCHEMA.JOBS) to give you extra information on your GCP spending. It's slightly different from the info on the OWOX BI Credits consumption page.

A quick look at the template 

First, let's take a quick look at what this template can do for you. It answers questions like:

How many Queries are run each day and how much data is processed?

The first two charts in the template provide an overview of Extension usage. You can see statistics on the number of launches, the volume of processed data, and the type of launch (manual or scheduled) by day.

01.png

 

Who are the top users processing lots of data?

The next two charts display statistics by users. You can identify under which account the most GB of data are processed. It also allows you to see in which GCP projects the most data is processed.

02.png

 

What's the monthly overview of usage by day and user?

Another sheet contains a pivot table grouping launch data by days and months. Here, you can see the Grand Total for the month for manual and automatic launches.

Expanding a row for a day, you will see a list of users who launched a Query that day, either manually or on a schedule.

03.png

 

Who's running Queries without actually processing data?

Another useful data segmentation is a table listing users who launch Queries but do not process any data as a result. Essentially, these are idle Queries that do not benefit you but are counted in the launch statistics. Ideally, this list should be empty.

04.png

 

The data source for template

This template pulls data from your BigQuery logs. Every job from the OWOX BI extension is labeled, and you can fetch this data into Google Sheets.

Some limits to know

  • You can only get data from the last 180 days, starting from February 25, 2024.
  • The user executing the Query needs to have the BigQuery Resource Viewer role. How to assign a role in GCP is discussed in this article.
  • The template is ideal if your data is stored in one location. For example, if your data is stored both in US and EU locations, you need to execute two different Queries to retrieve data from each location. Then, you need to merge these data in your Google Sheets document.

Step-by-step guide: setting up the template

Step 1. Open this template and make a copy of it.

05.png

Step 2. In your copy, go to Extensions → OWOX BI BigQuery Reports → Add a new report.

06.png

Step 3. Select your GCP project.

07.png

Step 4. Open the list of Queries and select “+ Add new query…

08_1.png

Step 5. Copy the SQL code, paste it into the Query editor, and replace the text “YOUR_GCP_PROJECT_ID” with the actual ID of your GCP project.

/* Settings block
Analyse queries triggered by OWOX BI Extension within GCP project or your own queries only
{scope default='JOBS_BY_PROJECT' values='JOBS_BY_PROJECT,JOBS_BY_USER,' type='select'} -- 

BigQuery jobs region https://cloud.google.com/bigquery/docs/locations#supported_locations
{region default='us' type='input'}

Your Time Zone
{timeZone default='Europe/Dublin' type='input'}

Start date for analysis
Data in logs exists only from Feb, 25 2024
{startDate default='2024-02-25' type='input'}

End date for analysis
{endDate default='2024-07-31' type='input'}
*/

WITH combinedLogFromProjects AS (
  -- add additionals projects here
  SELECT *  
  FROM (SELECT * FROM `YOUR_GCP_PROJECT_ID.region-{region}.INFORMATION_SCHEMA.{scope}`) -- first project
    --UNION ALL (SELECT * FROM `YOUR_GCP_PROJECT_ID.region-{region}.INFORMATION_SCHEMA.{scope}`) -- additional project, uncomment if needed
),

queryLog AS (
  SELECT
    project_id AS gcpProjectId,
    user_email AS userEmail, 
    job_id AS queryId,    
    total_bytes_processed AS bytesProcessed,
    DATE(creation_time, '{timeZone}') AS date,
    IF(error_result IS NULL, 'success', 'failed') AS status,
    (SELECT l.value FROM UNNEST(labels) l WHERE l.key = 'owox_bi_project_id') AS biProjectId,
    (SELECT l.value FROM UNNEST(labels) l WHERE l.key = 'owox_bi_data_mart_id') AS dataMartId,
    (SELECT l.value FROM UNNEST(labels) l WHERE l.key = 'owox_bi_run_type') AS runType
  FROM combinedLogFromProjects
  WHERE DATE(creation_time, '{timeZone}') BETWEEN '{startDate}' AND '{endDate}'
    AND job_type = 'QUERY'
    AND state = 'DONE'
    AND (SELECT l.value FROM UNNEST(labels) l WHERE l.key = 'owox_bi_requester') = 'owox_bi_extension'
)

SELECT
  DATE_TRUNC(date, MONTH) AS month,
  date,
  gcpProjectId,
  biProjectId,
  userEmail,
  runType,
  status,
  COUNT(DISTINCT queryId) AS queries,
  IFNULL(SUM(bytesProcessed), 0) AS bytesProcessed
FROM queryLog
GROUP BY month, date, gcpProjectId, biProjectId, userEmail, runType, status
ORDER BY date DESC, bytesProcessed DESC

If the query requires specifying multiple GCP projects, uncomment line 23 and enter the ID of your second GCP project.

08_2.png

Step 6. It's essential to change the Query title to – logs data.

IMPORTANTIf the Query is named differently, you will then need to adjust the formulas in the document, something we would prefer to avoid.

09.png

Step 7. Click the “Save&Run” button.

10_1.png

Step 8. After the Query successfully executes, a new sheet “logs data” with your real data will appear in the document.

10_2.png

Step 9. Delete the “Demo” sheet, as it is no longer needed because it contains demo data. After deletion, open the “[data] for charts” sheet, where you should see your real data.

10_3.png

✅ Step 10. You’re all set!

Now you can view the “Graphs”, “Pivot by date”, and “Query Runs w/o data” sheets.

 

Modifying the Query for Source Data

To edit the Query in the Extension panel, you will see a list of dynamic parameters that can be changed without opening the Query editor.

11.png

Setting up data auto-refresh in the template

To have the data in the template automatically refreshed, you can set up Scheduled refreshes by following these instructions.

12.png

Finding documents

Even though the template doesn't link directly to your Google Sheets documents, you can use Google Drive search to find them. Just use the right search phrase. For instance:

type:spreadsheet "OWOX BI Queries" "Result OK(2024" owner:john.snow@owox.com

13.png

Replace john.snow@owox.com with the required email. This search query will return a list of Google Sheets for this user, where successful Queries launches from the Extension in 2024 occurred. For more information on searching Google Sheets, read this article.

 

You might also find this useful:

  • Elevate your financial oversight with the BigQuery Budget Forecast Toolkit, a template designed to help you analyze, manage, and forecast your BigQuery expenses effectively.
  • Additionally, a complete list of Google Sheets documents is available on the Credits consumption page in OWOX BI.
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.