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:

  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.