Monitor continuous queries
You can monitor BigQuery continuous queries by using the following BigQuery tools:
Due to the long running nature of a BigQuery continuous query, metrics that are usually generated upon the completion of a SQL query might be absent or inaccurate.
Monitor continuous queries by using INFORMATION_SCHEMA
views
You can use a number of the INFORMATION_SCHEMA
views to monitor continuous
queries and continuous query reservations.
Get continuous query job details
You can use the
ASSIGNMENTS
and
JOBS
views to get
continuous query job metadata.
Return the metadata for all running continuous queries:
In the Google Cloud console, go to the BigQuery page.
In the query editor, run the following query:
SELECT jobs.job_id, jobs.user_email, jobs.query, jobs.state, assignment.reservation_name FROM `ADMIN_PROJECT_ID.region-LOCATION.INFORMATION_SCHEMA.ASSIGNMENTS` AS assignment INNER JOIN `ADMIN_PROJECT_ID.region-LOCATION.INFORMATION_SCHEMA.JOBS` AS jobs ON (CONCAT('ADMIN_PROJECT_ID:LOCATION.', assignment.reservation_name) = jobs.reservation_id) WHERE assignment.assignee_id = 'PROJECT_ID' AND assignment.job_type = 'CONTINUOUS' AND jobs.state = 'RUNNING' GROUP BY 1, 2, 3, 4, 5;
Replace the following:
ADMIN_PROJECT_ID
: the ID of the administration project that owns the reservation.LOCATION
: the location of the reservation.PROJECT_ID
: the ID of the project that is assigned to the reservation. Only information about continuous queries running in this project is returned.
Get continuous query reservation assignment details
You can use the
ASSIGNMENTS
and
RESERVATIONS
views to get
continuous query reservation assignment details.
Return reservation assignment details for continuous queries:
In the Google Cloud console, go to the BigQuery page.
In the query editor, run the following query:
SELECT reservation.reservation_name, reservation.slot_capacity FROM `ADMIN_PROJECT_ID.region-LOCATION.INFORMATION_SCHEMA.ASSIGNMENTS` AS assignment INNER JOIN `ADMIN_PROJECT_ID.region-LOCATION.INFORMATION_SCHEMA.RESERVATIONS` AS reservation ON (assignment.reservation_name = reservation.reservation_name) WHERE assignment.assignee_id = 'PROJECT_ID' AND job_type = 'CONTINUOUS';
Replace the following:
ADMIN_PROJECT_ID
: the ID of the administration project that owns the reservation.LOCATION
: the location of the reservation.PROJECT_ID
: the ID of the project that is assigned to the reservation. Only information about continuous queries running in this project is returned.
Get continuous query slot consumption information
You can use the
ASSIGNMENTS
,
RESERVATIONS
, and
JOBS_TIMELINE
views to get
continuous query slot consumption information.
Return slot consumption information for continuous queries:
In the Google Cloud console, go to the BigQuery page.
In the query editor, run the following query:
SELECT jobs.period_start, reservation.reservation_name, reservation.slot_capacity, SUM(jobs.period_slot_ms) / 1000 AS consumed_total_slots FROM `ADMIN_PROJECT_ID.region-LOCATION.INFORMATION_SCHEMA.ASSIGNMENTS` AS assignment INNER JOIN `ADMIN_PROJECT_ID.region-LOCATION.INFORMATION_SCHEMA.RESERVATIONS` AS reservation ON (assignment.reservation_name = reservation.reservation_name) INNER JOIN `ADMIN_PROJECT_ID.region-LOCATION.INFORMATION_SCHEMA.JOBS_TIMELINE` AS jobs ON ( CONCAT('ADMIN_PROJECT_ID:LOCATION.', assignment.reservation_name) = jobs.reservation_id) WHERE assignment.assignee_id = 'PROJECT_ID' AND assignment.job_type = 'CONTINUOUS' AND jobs.period_start BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND CURRENT_TIMESTAMP() GROUP BY 1, 2, 3 ORDER BY jobs.period_start DESC;
Replace the following:
ADMIN_PROJECT_ID
: the ID of the administration project that owns the reservation.LOCATION
: the location of the reservation.PROJECT_ID
: the ID of the project that is assigned to the reservation. Only information about continuous queries running in this project is returned.
You can also monitor continuous query reservations using other tools such as Metrics Explorer and administrative resource charts. For more information, see Monitor BigQuery reservations.
Monitor continuous query performance by using a query execution graph
You can use a query execution graphs to get performance insights and general statistics for a continuous query. For more information, see View query performance insights.
Monitor continuous query execution by using job history
You can view job details in your personal job history or the project's job history. For more information, see View job details.
Be aware that the historical list of jobs is sorted by the job start time, so continuous queries that have been running for a while might not be close to the start of the list.