GCP : Monitoring Google BigQuery Costs for Each SQL Query

Google BigQuery is a powerful tool for analyzing large datasets, but it’s also important to keep track of costs to ensure that you stay within your budget. This tutorial will guide you through the process of monitoring the costs for each SQL query you run in Google BigQuery.

Step 1: Enable Billing Export to BigQuery

To track the costs associated with each query, you need to set up a BigQuery dataset to store your billing data. Here’s how to do it:

1. Go to the Google Cloud Console: Navigate to the Google Cloud Console and select your project.
2. Open the billing page: On the left-hand menu, click on “Billing.”
3. Navigate to Billing Export: Click on the navigation menu (three horizontal lines in the upper left corner), then navigate to Billing > Billing Export.
4. Enable BigQuery export: Click on “Edit Settings” under “BigQuery Export”. Select the project where you want to store your billing data, create a new BigQuery dataset or choose an existing one, then click on “Enable BigQuery Export”.

This will start exporting your daily cost data to the specified BigQuery dataset. Note that it may take 24 to 48 hours for the data to appear in the dataset.

Step 2: Query the Billing Data

Once you’ve enabled billing export to BigQuery, you can write SQL queries to analyze the cost data. Each row in the billing data corresponds to a specific usage event in your project. Importantly, the labels field includes a key-value pair with the key goog-bigquery-job-id that matches the ID of the BigQuery job that ran the query.

Here is an example of a SQL query that will sum up the total cost for each BigQuery job:

SELECT
  labels.key as job_id,
  SUM(cost) as total_cost
FROM
  `freshers-in.viewership.webviews`,
  UNNEST(labels) as labels
WHERE
  labels.key = "goog-bigquery-job-id"
GROUP BY
  job_id
ORDER BY
  total_cost DESC

freshers-in.viewership.webviews  [freshers-in is the project-id, viewership is the dataset-id and webviews   is the table-id

This will give you a table where each row corresponds to a BigQuery job and the total cost associated with that job. Keep in mind that this cost includes not just the cost of running queries, but also the cost of storing data and other BigQuery-related charges.

Step 3: Find the SQL Query Associated with a BigQuery Job ID

Now that you have the cost associated with each BigQuery job ID, you may want to find the SQL query that was run for a particular job. Here’s how you can do this:

1. Go to the Google Cloud Console: Navigate to the Google Cloud Console and select your project.
2. Open the BigQuery page: On the left-hand menu, click on “BigQuery.”
3. Open the Query History tab: On the BigQuery page, click on “Query History” on the left-hand side.
4. Search for the Job ID: In the Query History tab, you can search for a specific job ID in the “Filter by job ID, user, or label” search box.
5. Find the SQL query: Once you’ve found the job, you can click on it to see more details, including the SQL query that was run.