23 Nov 20 · nVector · #Bigquery ·   Bookmark   ×

Monitoring Query Costs in BigQuery by user using SQL and the Query Logs

BigQuery is a SAAS Software, you get charged based on the amount of data scanned by your SQL Queries. Monitoring cost is very crucial in a cloud data warehouse. It helps us optimize the bad queries and reports and helps us get the maximum ROI from the data warehouse.  BigQuery recently introduced a new INFORMATION_SCHEMA views for viewing the query logs. This view will give us,

  • List of all the queries executed
  • Who and when was the query executed
  • Query metrics, including bytes processed, time taken, and the actual query itself

Example:

DECLARE timezone STRING DEFAULT "US/Eastern";
DECLARE gb_divisor INT64 DEFAULT 1024*1024*1024;
DECLARE tb_divisor INT64 DEFAULT gb_divisor*1024;
DECLARE cost_per_tb_in_dollar INT64 DEFAULT 5;
DECLARE cost_factor FLOAT64 DEFAULT cost_per_tb_in_dollar / tb_divisor;
SELECT
DATE(creation_time, timezone) creation_date,
FORMAT_TIMESTAMP("%F %H:%I:%S", creation_time, timezone) as query_time,
job_id,
ROUND(total_bytes_processed / gb_divisor,2) as bytes_processed_in_gb,
IF(cache_hit != true, ROUND(total_bytes_processed * cost_factor,4), 0) as cost_in_dollar,
project_id,
user_email
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
DATE(creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) and CURRENT_DATE()
ORDER BY
bytes_processed_in_gb DESC
  • feel free to replace JOBS_BY_PROJECT with JOBS_BY_USER or JOBS_BY_ORGANIZATION

Execute the Query in the Big Query Console:

Usage Notes:

  • the JOBS_BY_* views are regionalized, i.e. we must prefix the region (see region-us in the view specification) and must run the job in that region
  • The views will need additional IAM permission in order to work
  • The data in these views are currently only available for the past 180 days

nVector

posted on 23 Nov 20

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds