All About Jobs Information Schema and BiqQuery Processing Costs

The BigQuery jobs information schema is a feature introduced to simplify the analysis of jobs (queries) within an organization. It allows users to access different scopes of job information based on their permission levels, such as by user, project, folder, or organization. The schema remains consistent across these views and includes fields like creation_time, user_email, job_type, start_time, end_time, query, and total_bytes_billed. This schema makes it easier to analyze processes in BigQuery, which previously required steps like billing export, logging, or using the BigQuery API.

In BigQuery, the processing cost is calculated based on the amount of data scanned, after the initial free tier, at a rate of $5 per TB. To calculate the cost of a query, you can use the total_bytes_billed field from the jobs information schema. For example, the SQL query provided in the text calculates the query cost by user over the last 30 days by summing the total_bytes_billed and multiplying by the cost per TB.

To find the most expensive BigQuery query in the last 30 days, you can use the following SQL query:

SELECT user_email, query, total_bytes_billed FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) AND CURRENT_TIMESTAMP() AND job_type = "QUERY" ORDER BY total_bytes_billed DESC LIMIT 1

This query selects the user email, the query text, and the total bytes billed for the most expensive query by ordering the results by total_bytes_billed in descending order and limiting the results to the top one.

BigQuery offers a variety of information schema views beyond job history. These include views for datasets, jobs, jobs timeline, reservations, routines, streaming, tables, and views. Each category has specific views that provide detailed information, such as INFORMATION_SCHEMA.SCHEMATA, INFORMATION_SCHEMA.JOBS_BY_USER, INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT, INFORMATION_SCHEMA.ROUTINES, and more. These views allow users to access metadata about different aspects of their BigQuery environment.