Monitor continuous queries

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

Note: To enroll in the continuous queries preview, fill out the request form. To give feedback or request support for this feature, contact [e-mail].

You can monitor BigQuery continuous queries by using the following BigQuery tools:

• INFORMATION_SCHEMA views
• Query execution graphs
• Job history

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.

Note: Data for a running continuous query is only retained for two days by the JOBS* INFORMATION_SCHEMA views.

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:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. 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:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. 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:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. 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.