Vista de JOBS

La vista INFORMATION_SCHEMA.JOBS contiene los metadatos casi en tiempo real sobre todos los trabajos de BigQuery del proyecto actual.

Función requerida

Para obtener el permiso que necesitas para consultar la vista INFORMATION_SCHEMA.JOBS, pídele a tu administrador que te otorgue el rol de IAM Visualizador de recursos de BigQuery (roles/bigquery.resourceViewer) en tu proyecto. Para obtener más información sobre cómo otorgar roles, consulta Administra el acceso a proyectos, carpetas y organizaciones.

Este rol predefinido contiene el permiso bigquery.jobs.listAll que se requiere para consultar la vista INFORMATION_SCHEMA.JOBS.

También puedes obtener este permiso con roles personalizados o con otros roles predefinidos.

Para obtener más información sobre IAM de BigQuery, consulta Control de acceso con IAM.

Esquema

La columna creation_time particiona los datos subyacentes y project_id y user_email los agrupan. La columna query_info contiene información adicional sobre tus trabajos de consulta.

La vista INFORMATION_SCHEMA.JOBS tiene el siguiente esquema:

Nombre de la columna Tipo de datos Valor
bi_engine_statistics RECORD Si el proyecto está configurado para usar la interfaz de SQL de BI Engine, este campo contiene BiEngineStatistics. En caso contrario NULL.
cache_hit BOOLEAN Indica si los resultados de la consulta de este trabajo provienen de una caché. Si tienes un trabajo de declaración de varias consultas, el cache_hit para tu consulta superior es NULL.
creation_time TIMESTAMP Es la hora de creación de este trabajo (columna de partición). La partición se basa en la hora UTC de esta marca de tiempo.
destination_table RECORD Es la tabla de destino para los resultados (si hay alguno).
dml_statistics RECORD Si el trabajo es una consulta con una declaración DML, el valor es un registro con los siguientes campos:
  • inserted_row_count: La cantidad de filas que se insertaron.
  • deleted_row_count: La cantidad de filas que se borraron.
  • updated_row_count: La cantidad de filas que se actualizaron.
Para todos los demás trabajos, el valor es NULL.
Esta columna está presente en las vistas INFORMATION_SCHEMA.JOBS_BY_USER y INFORMATION_SCHEMA.JOBS_BY_PROJECT.
end_time TIMESTAMP La hora de finalización de este trabajo, en milisegundos, desde el ciclo de entrenamiento. Este campo representa el momento en el que el trabajo ingresa al estado DONE.
error_result RECORD Detalles de cualquier error como objetos ErrorProto.
job_creation_reason.code STRING Especifica el motivo de alto nivel por el que se creó un trabajo.
Los valores posibles son los siguientes:
  • REQUESTED: Se solicitó la creación de un trabajo.
  • LONG_RUNNING: La solicitud de consulta se ejecutó más allá del tiempo de espera definido por el sistema especificado por el campo timeoutMs en QueryRequest. Como resultado, se consideró una operación de larga duración para la que se creó un trabajo.
  • LARGE_RESULTS: los resultados de la consulta no pueden caber en la respuesta en línea.
  • OTHER: el sistema determinó que la consulta debe ejecutarse como un trabajo.
job_id STRING El ID del trabajo si se creó uno. De lo contrario, el ID de la consulta de una consulta que usa el modo de consulta breve. Por ejemplo, bquxjob_1234.
job_stages RECORD Etapas de consulta del trabajo.

Nota: Los valores de esta columna están vacíos para las consultas que leen de tablas con políticas de acceso a nivel de fila. Si deseas obtener más información, consulta las prácticas recomendadas para la seguridad a nivel de las filas en BigQuery.

job_type STRING Es el tipo de trabajo. Puede ser QUERY, LOAD, EXTRACT, COPY o NULL. Un valor NULL indica un trabajo interno, como una evaluación de declaración de trabajo de secuencia de comandos o una actualización de vista materializada.
labels RECORD Array de etiquetas aplicadas al trabajo como pares clave-valor.
parent_job_id STRING ID del trabajo superior, si hay alguno.
priority STRING La prioridad de este trabajo. Los valores válidos incluyen INTERACTIVE y BATCH.
project_id STRING (Columna de agrupamiento en clústeres) el ID del proyecto.
project_number INTEGER El número del proyecto.
query STRING Texto de consulta de SQL. Solo la vista JOBS_BY_PROJECT tiene la columna de consulta.
referenced_tables RECORD Es el array de tablas a las que hace referencia el trabajo. Solo se propaga para los trabajos de consulta que no son aciertos de caché.
reservation_id STRING Nombre de la reserva principal asignada a este trabajo, en el formato RESERVATION_ADMIN_PROJECT:RESERVATION_LOCATION.RESERVATION_NAME.
En este resultado, se ilustra lo siguiente:
  • RESERVATION_ADMIN_PROJECT: Es el nombre del proyecto de Google Cloud que administra la reserva.
  • RESERVATION_LOCATION: Es la ubicación de la reserva.
  • RESERVATION_NAME: El nombre de la reserva
edition STRING Es la edición asociada con la reserva asignada a este trabajo. Para obtener más información sobre las ediciones, consulta Introducción a las ediciones de BigQuery.
session_info RECORD Detalles sobre la sesión en la que se ejecutó este trabajo, si corresponde.
start_time TIMESTAMP La hora de inicio de este trabajo, en milisegundos, desde el ciclo de entrenamiento. Este campo representa el momento en el que el trabajo pasa del estado PENDING a RUNNING o DONE.
state STRING Estado de ejecución del trabajo. Los estados válidos incluyen PENDING, RUNNING y DONE.
statement_type STRING El tipo de declaración de consulta. Por ejemplo, DELETE, INSERT, SCRIPT, SELECT o UPDATE. Consulta QueryStatementType para obtener una lista de valores válidos.
timeline RECORD Cronograma de consultas del trabajo. Contiene instantáneas de la ejecución de consultas.
total_bytes_billed INTEGER Si el proyecto está configurado para usar precios según demanda, este campo contiene el total de bytes facturados para el trabajo. Si el proyecto está configurado para usar precios de tasa fija, no se te facturará por los bytes y este campo solo es informativo.

Nota: Los valores de esta columna están vacíos para las consultas que leen de tablas con políticas de acceso a nivel de fila. Si deseas obtener más información, consulta las prácticas recomendadas para la seguridad a nivel de las filas en BigQuery.

total_bytes_processed INTEGER

Es el total de bytes procesados por el trabajo.

Nota: Los valores de esta columna están vacíos para las consultas que leen de tablas con políticas de acceso a nivel de fila. Si deseas obtener más información, consulta las prácticas recomendadas para la seguridad a nivel de las filas en BigQuery.

total_modified_partitions INTEGER Cantidad total de particiones que modificó el trabajo. Este campo se propaga para los trabajos LOAD y QUERY.
total_slot_ms INTEGER Son los milisegundos de ranura para el trabajo durante toda su duración en el estado RUNNING, incluidos los reintentos.
transaction_id STRING ID de la transacción en la que se ejecutó este trabajo, si corresponde. (Vista previa)
user_email STRING Es la dirección de correo electrónico o la cuenta de servicio del usuario que ejecutó el trabajo (columna de agrupamiento en clústeres).
query_info.resource_warning STRING Es el mensaje de advertencia que aparece si el uso de recursos durante el procesamiento de consultas está por encima del umbral interno del sistema.
Un trabajo de consulta exitoso puede tener el campo resource_warning propagado. Con resource_warning, obtienes datos adicionales para optimizar tus consultas y configurar la supervisión de las tendencias de rendimiento de un conjunto de consultas equivalente mediante query_hashes.
query_info.query_hashes.normalized_literals STRING Contiene los hashes de la consulta. normalized_literals es un hash STRING hexadecimal que ignora los comentarios, los valores de parámetros, las UDF y los literales.
Este campo aparece para las consultas correctas de GoogleSQL que no son aciertos de caché.
query_info.performance_insights RECORD Estadísticas de rendimiento del trabajo.
query_info.optimization_details STRUCT Las optimizaciones basadas en el historial para el trabajo.
transferred_bytes INTEGER Total de bytes transferidos para consultas de múltiples nubes, como trabajos de transferencia entre nubes de BigQuery Omni.
materialized_view_statistics RECORD Estadísticas de vistas materializadas que se consideran en un trabajo de consulta. (Vista previa)

Cuando consultes INFORMATION_SCHEMA.JOBS para encontrar un costo de resumen de los trabajos de consulta, excluye el tipo de declaración SCRIPT; de lo contrario, algunos valores podrían contarse dos veces. En la fila SCRIPT, se incluyen valores de resumen para todos los trabajos secundarios que se ejecutaron como parte de este trabajo.

Trabajo de consulta de varias instrucciones

Un trabajo de consulta de varias instrucciones usa el lenguaje de procedimiento. Los trabajos de consulta de varias instrucciones suelen definir variables con DECLARE o tienen instrucciones de flujo de control, como IF o WHILE. Cuando consultas INFORMATION_SCHEMA.JOBS, es posible que debas reconocer la diferencia entre un trabajo de consulta de varias declaraciones y otros trabajos. Un trabajo de consulta de varias instrucciones tiene las siguientes características:

  • statement_type = SCRIPT
  • reservation_id = NULL
  • Trabajos secundarios. Cada uno de los trabajos secundarios de un trabajo de consulta de varias instrucciones tiene un parent_job_id que apunta al trabajo de consulta de varias instrucciones. Esto incluye valores resumidos para todos los trabajos secundarios que se ejecutaron como parte de este trabajo. Por este motivo, si consultas INFORMATION_SCHEMA.JOBS para encontrar un costo de resumen de los trabajos de consulta, debes excluir el tipo de declaración SCRIPT; de lo contrario, algunos valores como total_slot_ms podrían contarse. dos veces.

Retención de datos

Esta vista contiene los trabajos que se están ejecutando y el historial de trabajos de los últimos 180 días.

Permiso y sintaxis

Las consultas realizadas a esta vista deben incluir un calificador de región. En la siguiente tabla, se explica el permiso de la región para esta vista:

Nombre de la vista Permiso del recurso Permiso de la región
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.JOBS[_BY_PROJECT] Nivel de proyecto REGION
Reemplaza lo siguiente:

  • Opcional: PROJECT_ID: el ID del proyecto de Google Cloud. Si no se especifica, se usa el proyecto predeterminado.
+ REGION: Cualquier nombre de región del conjunto de datos. Un ejemplo es region-us.

Ejemplos

Para ejecutar la consulta en un proyecto que no sea el predeterminado, agrega el ID del proyecto con el siguiente formato: 

`PROJECT_ID`.`region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
 Reemplaza lo siguiente:

  • PROJECT_ID: el ID del proyecto.
  • REGION_NAME: la región del proyecto.

Por ejemplo, `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Compara el uso del trabajo con los datos de facturación

Para los proyectos que usan la facturación según demanda, puedes usar la vista INFORMATION_SCHEMA.JOBS para revisar los cargos de procesamiento durante un período determinado. La siguiente consulta produce agregaciones diarias estimadas de tus TiB facturados y los cargos resultantes. En la sección limitaciones, se explica cuándo es posible que estas estimaciones no coincidan con tu factura.

Solo para este ejemplo, se deben configurar las siguientes variables adicionales. Aquí se pueden editar para facilitar su uso.

  • START_DATE: la fecha más temprana en la que se agregará (inclusivo).
  • END_DATE: la última fecha en la que se agregará (inclusivo).
  • PRICE_PER_TIB: el precio según demanda por TiB que se usa para las estimaciones de facturas.
CREATE TEMP FUNCTION isBillable(error_result ANY TYPE)
AS (
  -- You aren't charged for queries that return an error.
  error_result IS NULL
  -- However, canceling a running query might incur charges.
  OR error_result.reason = 'stopped'
);

-- BigQuery hides the number of bytes billed on all queries against tables with
-- row-level security.
CREATE TEMP FUNCTION isMaybeUsingRowLevelSecurity(
  job_type STRING, tib_billed FLOAT64, error_result ANY TYPE)
AS (
  job_type = 'QUERY'
  AND tib_billed IS NULL
  AND isBillable(error_result)
);

WITH
  query_params AS (
    SELECT
      date 'START_DATE' AS start_date,  -- inclusive
      date 'END_DATE' AS end_date,  -- inclusive
  ),
  usage_with_multiplier AS (
    SELECT
      job_type,
      error_result,
      creation_time,
      -- Jobs are billed by end_time in PST8PDT timezone, regardless of where
      -- the job ran.
      EXTRACT(date FROM end_time AT TIME ZONE 'PST8PDT') billing_date,
      total_bytes_billed / 1024 / 1024 / 1024 / 1024 total_tib_billed,
      CASE statement_type
        WHEN 'SCRIPT' THEN 0
        WHEN 'CREATE_MODEL' THEN 50 * PRICE_PER_TIB
        ELSE PRICE_PER_TIB
        END AS multiplier,
    FROM `PROJECT_ID.region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  )
SELECT
  billing_date,
  sum(total_tib_billed * multiplier) estimated_charge,
  sum(total_tib_billed) estimated_usage_in_tib,
  countif(isMaybeUsingRowLevelSecurity(job_type, total_tib_billed, error_result))
    AS jobs_using_row_level_security,
FROM usage_with_multiplier, query_params
WHERE
  1 = 1
  -- Filter by creation_time for partition pruning.
  AND date(creation_time) BETWEEN date_sub(start_date, INTERVAL 2 day) AND end_date
  AND billing_date BETWEEN start_date AND end_date
  AND isBillable(error_result)
GROUP BY billing_date
ORDER BY billing_date

Limitaciones

  • BigQuery oculta algunas estadísticas para las consultas sobre tablas con seguridad a nivel de fila. La consulta proporcionada cuenta la cantidad de trabajos afectados como jobs_using_row_level_security, pero no tiene acceso al uso facturable.

  • Los precios de las consultas a pedido de BigQuery ML dependen del tipo de modelo que se crea. INFORMATION_SCHEMA.JOBS no realiza un seguimiento de qué tipo de modelo se creó, por lo que la consulta proporcionada supone que todas las declaraciones CREATE_MODEL crearon los tipos de modelo más facturados.

  • Los procedimientos de Apache Spark usan un modelo de precios similar, pero los cargos se informan como un SKU de pago por uso de la edición Enterprise de BigQuery. INFORMATION_SCHEMA.JOBS realiza un seguimiento de este uso como total_bytes_billed, pero no puede determinar qué SKU representa el uso.

Calcula el uso promedio de las ranuras

En el siguiente ejemplo, se calcula el uso promedio de las ranuras para todas las consultas de los últimos 7 días de un proyecto determinado. Ten en cuenta que este cálculo es más preciso para proyectos que tienen un uso de ranuras coherente durante la semana. Si tu proyecto no tiene un uso de ranuras coherente, es posible que este número sea inferior al esperado.

Para ejecutar la consulta, haz lo siguiente:

SELECT
  SUM(total_slot_ms) / (1000 * 60 * 60 * 24 * 7) AS avg_slots
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  -- Filter by the partition column first to limit the amount of data scanned.
  -- Eight days allows for jobs created before the 7 day end_time filter.
  creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY) AND CURRENT_TIMESTAMP()
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
  AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP();

El resultado es similar al siguiente:

+------------+
| avg_slots  |
+------------+
| 3879.1534  |
+------------+

Puedes verificar el uso de una reserva en particular con WHERE reservation_id = "…". Esto puede ser útil para determinar el porcentaje de uso de una reserva durante un período. En los trabajos de secuencia de comandos, el trabajo superior también informa el uso total de las ranuras de sus trabajos secundarios. Si deseas evitar el doble recuento, usa WHERE statement_type != "SCRIPT" para excluir el trabajo superior.

En cambio, si deseas verificar el uso de ranuras promedio para trabajos individuales, usa total_slot_ms / TIMESTAMP_DIFF(end_time, start_time, MILLISECOND).

Historial de trabajos de carga

En el siguiente ejemplo, se enumeran todos los usuarios o cuentas de servicio que enviaron un trabajo de carga por lotes para un proyecto determinado. Debido a que no se especifica ningún límite de tiempo, esta consulta analiza todo el historial disponible (por ejemplo, los últimos 30 días).

SELECT
  DISTINCT(user_email) AS user
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  job_type = 'LOAD';

El resultado es similar al siguiente:

+--------------+
| user         |
+--------------+
| abc@xyz.com  |
+--------------+
| def@xyz.com  |
+--------------+

Obtén la cantidad de trabajos de carga para determinar la cuota diaria de trabajo usada

En el siguiente ejemplo, se muestra la cantidad de trabajos por día, el conjunto de datos y la tabla para que puedas determinar qué cantidad de la cuota de trabajo diaria se usa.

SELECT
    DATE(creation_time) as day,
    destination_table.project_id as project_id,
    destination_table.dataset_id as dataset_id,
    destination_table.table_id as table_id,
    COUNT(job_id) AS load_job_count
 FROM
   region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE
    creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY) AND CURRENT_TIMESTAMP()
    AND job_type = "LOAD"
GROUP BY
    day,
    project_id,
    dataset_id,
    table_id
ORDER BY
    day DESC

Obtén los últimos 10 trabajos con errores

En el siguiente ejemplo, se muestran los últimos 10 trabajos con errores:

SELECT
   job_id,
  creation_time,
  user_email,
   error_result
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE error_result.reason != "Null"
ORDER BY creation_time DESC
LIMIT 10

Los resultados debería ser similar al siguiente:

+---------------+--------------------------+------------------+-----------------------------------------------------------+
| job_id        | creation_time            | user_email       | error_result                                              |
+---------------+--------------------------+------------------+-----------------------------------------------------------+
| examplejob_1  | 2020-10-10 00:00:00 UTC  | bob@example.com  | Column 'generate_metadata_snapshot' has mismatched type...|
| examplejob_2  | 2020-10-11 00:00:00 UTC  | bob@example.com  | Column 'generate_metadata_snapshot' has mismatched type...|
+---------------+--------------------------+------------------+-----------------------------------------------------------+

Consulta la lista de trabajos de ejecución larga

En el siguiente ejemplo, se muestra la lista de trabajos de ejecución larga que están en el estado RUNNING o PENDING durante más de 30 minutos:

SELECT
  job_id,
  job_type,
  state,
  creation_time,
  start_time,
  user_email
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE
    state!="DONE" AND
    creation_time <= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 MINUTE)
ORDER BY creation_time ASC;

El resultado es similar al siguiente:

+---------------+----------+---------+--------------------------------+--------------------------------+------------------+
| job_id        | job_type | state   | creation_time                  | start_time                     | user_email       |
+---------------+----------+---------+--------------------------------+--------------------------------+------------------+
| examplejob_1  | QUERY    | RUNNING | 2023-05-03 05:07:22.818000 UTC | 2023-05-03 05:07:22.905000 UTC | abc@example.com  |
| examplejob_2  | QUERY    | PENDING | 2023-05-01 02:05:47.925000 UTC | 2023-05-01 02:05:47.998000 UTC | xyz@example.com  |
+---------------+----------+---------+--------------------------------+--------------------------------+------------------+

Consultas que usan el modo optimizado de consulta corta

En el siguiente ejemplo, se muestra una lista de consultas que se ejecutaron en el modo optimizado de consulta breve para las que BigQuery no creó trabajos.

SELECT
 job_id,
FROM
 `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
 TIMESTAMP_TRUNC(creation_time, DAY) = '2024-06-12'
 AND job_creation_reason.code IS NULL
LIMIT
 10

Los resultados deberían verse así:

+------------------------------------------+
| job_id                                   |
+------------------------------------------+
| 2Lm09bHxDEsoVK8zwzWJomLHU_Ud%1910479b151 |
| j9_GVQf28jW2M1_RfTYGRPX1vq--!191047a135f | 
+------------------------------------------+

En el siguiente ejemplo, se muestra información sobre una consulta que se ejecutó en el modo optimizado de consulta breve para la que BigQuery no creó un trabajo.

SELECT
 job_id,
 statement_type,
 priority,
 cache_hit,
 job_creation_reason.code AS job_creation_reason_code,
 total_bytes_billed,
 total_bytes_processed,
 total_slot_ms,
 state,
 error_result.message AS error_result_message,
FROM
 `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
 TIMESTAMP_TRUNC(creation_time, DAY) = '2024-06-12'
 AND job_id = '2Lm09bHxDEsoVK8zwzWJomLHU_Ud%1910479b151' -- queryId

Nota: El campo job_id contiene el queryId de la consulta cuando no se creó un trabajo para esta consulta.

Los resultados deberían verse así:

+------------------------------------------+----------------+-------------+-----------+--------------------------+--------------------+-----------------------+---------------+-------+----------------------+
| job_id                                   | statement_type | priority    | cache_hit | job_creation_reason_code | total_bytes_billed | total_bytes_processed | total_slot_ms | state | error_result_message |
+------------------------------------------+----------------+-------------+-----------+--------------------------+--------------------+-----------------------+---------------+-------+----------------------+
| 2Lm09bHxDEsoVK8zwzWJomLHU_Ud%1910479b151 | SELECT         | INTERACTIVE | false     | null                     | 161480704          | 161164718             | 3106          | DONE  | null                 |
+------------------------------------------+----------------+-------------+-----------+--------------------------+--------------------+-----------------------+---------------+-------+----------------------+

En el siguiente ejemplo, se muestra una lista de consultas que se ejecutaron en el modo optimizado de consulta breve para las que BigQuery sí creó trabajos.

SELECT
 job_id,
 job_creation_reason.code AS job_creation_reason_code
FROM
 `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
 TIMESTAMP_TRUNC(creation_time, DAY) = '2024-06-12'
 AND job_creation_reason.code IS NOT NULL
 AND job_creation_reason.code != 'REQUESTED'
LIMIT
 10

Los resultados deberían verse así:

+----------------------------------+--------------------------+
| job_id                           | job_creation_reason_code |
+----------------------------------+--------------------------+
| job_LxOEwrJEffcOfjK7GBwWjO3RroOI | LARGE_RESULTS            |
+----------------------------------+--------------------------+

Bytes procesados por identidad del usuario

En el siguiente ejemplo, se muestra el total de bytes facturados por los trabajos de consulta por usuario:

SELECT
  user_email,
  SUM(total_bytes_billed) AS bytes_billed
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY
  user_email;

Nota: Consulta la advertencia de la columna total_bytes_billed en la documentación del esquema para las vistas JOBS.

Los resultados deberían verse así:

+---------------------+--------------+
| user_email          | bytes_billed |
+---------------------+--------------+
| bob@example.com     | 2847932416   |
| alice@example.com   | 1184890880   |
| charles@example.com | 10485760     |
+---------------------+--------------+

Desglose por hora de bytes procesados

En el siguiente ejemplo, se muestra el total de bytes facturados por los trabajos de consulta, en intervalos por hora:

SELECT
  TIMESTAMP_TRUNC(end_time, HOUR) AS time_window,
  SUM(total_bytes_billed) AS bytes_billed
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY
  time_window
ORDER BY
  time_window DESC;

El resultado es similar al siguiente:

+-------------------------+--------------+
| time_window             | bytes_billed |
+-------------------------+--------------+
| 2022-05-17 20:00:00 UTC | 1967128576   |
| 2022-05-10 21:00:00 UTC | 0            |
| 2022-04-15 20:00:00 UTC | 10485760     |
| 2022-04-15 17:00:00 UTC | 41943040     |
+-------------------------+--------------+

Trabajos de consulta por tabla

En el siguiente ejemplo, se muestra cuántas veces se hizo referencia a cada tabla consultada en my_project mediante un trabajo de consulta:

SELECT
  t.project_id,
  t.dataset_id,
  t.table_id,
  COUNT(*) AS num_references
FROM
  my_project.`region-us`.INFORMATION_SCHEMA.JOBS, UNNEST(referenced_tables) AS t
GROUP BY
  t.project_id,
  t.dataset_id,
  t.table_id
ORDER BY
  num_references DESC;

El resultado es similar al siguiente:

+------------+------------+----------+----------------+
| project_id | dataset_id | table_id | num_references |
+------------+------------+----------+----------------+
| my_project | dataset1   | orders   | 58             |
| my_project | dataset1   | products | 40             |
| my_project | dataset2   | sales    | 30             |
| other_proj | dataset1   | accounts | 12             |
+------------+------------+----------+----------------+

Cantidad de particiones modificadas por trabajos de consulta y carga por tabla

En el siguiente ejemplo, se muestra la cantidad de particiones modificadas por consultas con trabajos de carga y declaraciones DML por tabla. Ten en cuenta que esta consulta no muestra el total_modified_partitions para los trabajos de copia.

SELECT
  destination_table.table_id,
  SUM(total_modified_partitions) AS total_modified_partitions
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  DATE(creation_time, "America/Los_Angeles") = CURRENT_DATE()
GROUP BY
  table_id
ORDER BY
  total_modified_partitions DESC

Consultas más costosas por proyecto

En el siguiente ejemplo, se enumeran las consultas más costosas en my_project por tiempo de uso de ranura:

SELECT
 job_id,
 query,
 user_email,
 total_slot_ms
FROM `my_project`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE EXTRACT(DATE FROM  creation_time) = current_date()
ORDER BY total_slot_ms DESC
LIMIT 4

También puedes enumerar las consultas más costosas por datos procesados con el siguiente ejemplo:

SELECT
 job_id,
 query,
 user_email,
 total_bytes_processed
FROM `my_project`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE EXTRACT(DATE FROM  creation_time) = current_date()
ORDER BY total_bytes_processed DESC
LIMIT 4

El resultado de cualquiera de los ejemplos es similar al siguiente:

+--------------+---------------------------------+-----------------------+---------------+
| job_id       | query                           | user_email            | total_slot_ms |
+--------------+---------------------------------+--------------------------+------------+
| examplejob_1 | SELECT ... FROM dataset.table1  | bob@example.com       | 80,000        |
| examplejob_2 | SELECT ... FROM dataset.table2  | alice@example.com     | 78,000        |
| examplejob_3 | SELECT ... FROM dataset.table3  | charles@example.com   | 75,000        |
| examplejob_4 | SELECT ... FROM dataset.table4  | tina@example.com      | 72,000        |
+--------------+---------------------------------+-----------------------+---------------+

Obtén detalles sobre una advertencia de recurso

Si recibes el mensaje de error Resources exceeded, puedes realizar preguntas sobre las consultas en un período:

SELECT
  query,
  query_info.resource_warning
FROM
  `user_project.region-us`.INFORMATION_SCHEMA.JOBS
WHERE
 creation_time BETWEEN TIMESTAMP("2022-12-01")
 AND TIMESTAMP("2022-12-08")
 AND query_info.resource_warning IS NOT NULL
LIMIT
 50;

Supervisa las advertencias de recursos agrupadas por fecha

Si recibes el mensaje de error Resources exceeded, puedes supervisar la cantidad total de advertencias de recursos agrupadas por fecha para saber si hay cambios en la carga de trabajo:

WITH resource_warnings AS (
  SELECT
    EXTRACT(DATE FROM creation_time) AS creation_date
  FROM
    `user_project.region-us`.INFORMATION_SCHEMA.JOBS
  WHERE
    creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
    AND query_info.resource_warning IS NOT NULL
)
SELECT
  creation_date,
  COUNT(1) AS warning_counts
FROM
  resource_warnings
GROUP BY creation_date
ORDER BY creation_date DESC;

Estima el uso de las ranuras y el costo de las consultas

En el siguiente ejemplo, se calculan las ranuras promedio y la cantidad máxima de ranuras para cada trabajo mediante estimated_runnable_units.

El reservation_id es NULL si no tienes ninguna reserva.

SELECT
  project_id,
  job_id,
  reservation_id,
  EXTRACT(DATE FROM creation_time) AS creation_date,
  TIMESTAMP_DIFF(end_time, start_time, SECOND) AS job_duration_seconds,
  job_type,
  user_email,
  total_bytes_billed,

  -- Average slot utilization per job is calculated by dividing total_slot_ms by the millisecond duration of the job

  SAFE_DIVIDE(job.total_slot_ms,(TIMESTAMP_DIFF(job.end_time, job.start_time, MILLISECOND))) AS job_avg_slots,
  query,

  -- Determine the max number of slots used at ANY stage in the query.
  -- The average slots might be 55. But a single stage might spike to 2000 slots.
  -- This is important to know when estimating number of slots to purchase.

  MAX(SAFE_DIVIDE(unnest_job_stages.slot_ms,unnest_job_stages.end_ms - unnest_job_stages.start_ms)) AS jobstage_max_slots,

  -- Check if there's a job that requests more units of works (slots). If so you need more slots.
  -- estimated_runnable_units = Units of work that can be scheduled immediately.
  -- Providing additional slots for these units of work accelerates the query,
  -- if no other query in the reservation needs additional slots.

  MAX(unnest_timeline.estimated_runnable_units) AS estimated_runnable_units
FROM `region-us`.INFORMATION_SCHEMA.JOBS AS job
  CROSS JOIN UNNEST(job_stages) as unnest_job_stages
  CROSS JOIN UNNEST(timeline) AS unnest_timeline
WHERE project_id = 'my_project'
  AND statement_type != 'SCRIPT'
  AND DATE(creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE()
GROUP BY 1,2,3,4,5,6,7,8,9,10
ORDER BY job_id;

Visualiza las estadísticas de rendimiento de las consultas

En el siguiente ejemplo, se muestran todos los trabajos de consulta que tienen estadísticas de rendimiento de tu proyecto en los últimos 30 días, junto con una URL que vincula al gráfico de ejecución de consulta en la consola de Google Cloud.

SELECT
  `bigquery-public-data`.persistent_udfs.job_url(
    project_id || ':us.' || job_id) AS job_url,
  query_info.performance_insights
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  DATE(creation_time) >= CURRENT_DATE - 30 -- scan 30 days of query history
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND error_result IS NULL
  AND statement_type != 'SCRIPT'
  AND EXISTS ( -- Only include queries which had performance insights
    SELECT 1
    FROM UNNEST(
      query_info.performance_insights.stage_performance_standalone_insights
    )
    WHERE slot_contention OR insufficient_shuffle_quota
    UNION ALL
    SELECT 1
    FROM UNNEST(
      query_info.performance_insights.stage_performance_change_insights
    )
    WHERE input_data_change.records_read_diff_percentage IS NOT NULL
  );

Visualiza trabajos de actualización de metadatos

En el siguiente ejemplo, se enumeran los trabajos de actualización de metadatos:

SELECT
 *
FROM
 `region-aws-us-east-1.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE
 job_id LIKE '%metadata_cache_refresh%'
 AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 6 HOUR)
ORDER BY start_time desc
LIMIT 10;

Analiza el rendimiento en el tiempo para búsquedas idénticas

En el siguiente ejemplo, se muestran los 10 trabajos más lentos de los últimos 7 días que ejecutaron la misma consulta:

DECLARE querytext STRING DEFAULT(
  SELECT query
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE job_id = 'JOB_ID'
  LIMIT 1
);

SELECT
  start_time,
  end_time,
  project_id,
  job_id,
  TIMESTAMP_DIFF(end_time, start_time, SECOND) AS run_secs,
  total_bytes_processed / POW(1024, 3) AS total_gigabytes_processed,
  query
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  query = querytext
  AND total_bytes_processed > 0
  AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
ORDER BY 5 DESC
LIMIT 10;

Reemplaza JOB_ID por cualquier job_id que haya ejecutado la consulta que estás analizando.

Haz coincidir el comportamiento de uso de ranuras de los gráficos de recursos administrativos

Para explorar el comportamiento de uso de las ranuras, similar a la información en los gráficos de recursos administrativos, consulta la vista INFORMATION_SCHEMA.JOBS_TIMELINE.