Obtén metadatos de trabajos mediante INFORMATION_SCHEMA

INFORMATION_SCHEMA es una serie de vistas que proporcionan acceso a metadatos sobre conjuntos de datos, rutinas, tablas, vistas, trabajos, reservas y datos de transmisión.

Puedes consultar la vista INFORMATION_SCHEMA.JOBS_BY_* para recuperar metadatos en tiempo real sobre los trabajos de BigQuery. Esta vista contiene los trabajos que se están ejecutando y los últimos 180 días del historial de trabajos completados.

Permisos necesarios

Para recuperar los metadatos de trabajos mediante las tablas INFORMATION_SCHEMA, se requieren permisos con alcance adecuado:

  • JOBS_BY_USER requiere bigquery.jobs.list para el proyecto y está disponible para las funciones Project Viewer y BigQuery User.
  • JOBS_BY_PROJECT requiere bigquery.jobs.listAll en el proyecto y está disponible para las funciones Project Owner y BigQuery Admin.
  • JOBS_BY_FOLDER requiere bigquery.jobs.listAll en la carpeta superior y está disponible para Folder Admin y BigQuery Admin de la carpeta.
  • JOBS_BY_ORGANIZATION requiere bigquery.jobs.listAll para la organización y está disponible para las funciones Organization bigquery.resourceAdmin, Organization Owner y Organization Admin. Ten en cuenta que JOBS_BY_ORGANIZATION solo está disponible para usuarios con organizaciones de Google Cloud definidas.

Si deseas obtener más información acerca de los permisos de BigQuery en detalle, consulta las funciones y permisos.

Schema

Cuando consultas las vistas INFORMATION_SCHEMA.JOBS_BY_*, los resultados de la consulta contienen una fila para cada trabajo de BigQuery.

  • INFORMATION_SCHEMA.JOBS_BY_USER solo muestra los trabajos enviados por el usuario actual en el proyecto actual.
  • INFORMATION_SCHEMA.JOBS_BY_PROJECT muestra todos los trabajos enviados en el proyecto actual.
  • INFORMATION_SCHEMA.JOBS_BY_FOLDER muestra todos los trabajos enviados en la carpeta superior del proyecto actual, incluidos los trabajos en subcarpetas que contiene.
  • INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION muestra todos los trabajos enviados en la organización asociada con el proyecto actual.

La vista INFORMATION_SCHEMA.JOBS_BY_* tiene el siguiente esquema:

Nombre de la columna Tipo de datos Valor
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.
project_id STRING Es el ID del proyecto (columna de agrupamiento en clústeres).
project_number INTEGER Es el número del proyecto.
folder_numbers RECORD Son los ID de las Cuentas de Google y la Administración de ID (GAIA) de las carpetas en el principal de un proyecto para comenzar con la carpeta de hoja más cercana al proyecto. Esta columna solo se propaga en JOBS_BY_FOLDER.
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).
job_id STRING Es el ID del trabajo. Por ejemplo, bquxjob_1234.
job_type STRING Es el tipo de trabajo. Puede ser QUERY, LOAD, EXTRACT, COPY o null. El tipo de trabajo null indica un trabajo interno, como la evaluación de la declaración del trabajo de secuencia de comandos o la actualización de la vista materializada.
statement_type STRING Es el tipo de declaración de consulta, si es válido. Por ejemplo, SELECT, INSERT, UPDATE o DELETE.
priority STRING La prioridad de este trabajo.
start_time TIMESTAMP Es la hora de inicio de este trabajo.
end_time TIMESTAMP Es la hora de finalización de este trabajo.
query STRING Es el texto de la consulta de SQL. Nota: La vista JOBS_BY_ORGANIZATION no tiene la columna query.
state STRING Estado de ejecución del trabajo. Los estados válidos incluyen PENDING, RUNNING y DONE.
reservation_id STRING Es el nombre de la reserva principal asignada a este trabajo, si corresponde. Si tu trabajo se ejecutó en un proyecto asignado a una reserva, tendrá este formato: reservation-admin-project:reservation-location.reservation-name.
total_bytes_processed INTEGER Es el total de bytes procesados por el trabajo.
total_slot_ms INTEGER Son los milisegundos de ranura para el trabajo durante toda su duración.
error_result RECORD Son los detalles del error (si hay alguno), como ErrorProto.
cache_hit BOOLEAN Indica si los resultados de la consulta de este trabajo provienen de una caché.
destination_table RECORD Es la tabla de destino para los resultados (si hay alguno).
referenced_tables RECORD Es el arreglo de tablas a las que hace referencia el trabajo.
labels RECORD Es el arreglo de etiquetas aplicadas al trabajo como strings key, value.
timeline RECORD Cronograma de consultas del trabajo. Contiene instantáneas de la ejecución de consultas.
job_stages RECORD Etapas de consulta del trabajo.
total_bytes_billed INTEGER bytes totales facturados por el trabajo.

Retención de datos

Por el momento, solo se retienen los últimos 180 días del historial de trabajos en vistas de trabajos de INFORMATION_SCHEMA.

Regionalidad

Las vistas de trabajos de INFORMATION_SCHEMA de BigQuery están regionalizadas. Para consultar estas vistas, debes usar un calificador de región.

Ejemplos

Ejemplo 1: 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 en un proyecto que no sea el predeterminado, agrega el ID del proyecto en el siguiente formato: `project_id`.`region-region_name`.INFORMATION_SCHEMA.view; por ejemplo, `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Para ejecutar la consulta, haz lo siguiente:

Console

  1. En Cloud Console, abre la página de BigQuery.

    Ir a la página de BigQuery

  2. En el cuadro Editor de consultas, ingresa la siguiente consulta de SQL estándar. INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en Cloud Console.

    SELECT
 SUM(total_slot_ms) / (1000*60*60*24*7) AS avg_slots
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
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 end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()

  3. Haz clic en Ejecutar.

bq

Usa el comando query y especifica la sintaxis de SQL estándar con las marcas --nouse_legacy_sql o --use_legacy_sql=false. Se requiere la sintaxis de SQL estándar para las consultas INFORMATION_SCHEMA.

Para ejecutar la consulta, ingresa lo siguiente:

bq query --nouse_legacy_sql \
'SELECT
   SUM(total_slot_ms) / (1000*60*60*24*7) AS avg_slots
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 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 debería ser similar a lo 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)

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

Para ejecutar la consulta en un proyecto que no sea el predeterminado, agrega el ID del proyecto en el siguiente formato: `project_id`.`region-region_name`.INFORMATION_SCHEMA.view; por ejemplo, `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Para ejecutar la consulta, haz lo siguiente:

Console

  1. En Cloud Console, abre la página de BigQuery.

    Ir a la página de BigQuery

  2. Ingresa la siguiente consulta de SQL estándar en la casilla Editor de consulta. INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en Cloud Console.

    SELECT
 DISTINCT(user_email) AS user
FROM
 `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
 job_type = "LOAD"

  3. Haz clic en Ejecutar.

bq

Usa el comando query y especifica la sintaxis de SQL estándar con las marcas --nouse_legacy_sql o --use_legacy_sql=false. Se requiere la sintaxis de SQL estándar para las consultas INFORMATION_SCHEMA.

Para ejecutar la consulta, ingresa lo siguiente:

bq query --nouse_legacy_sql \
'SELECT
   DISTINCT(user_email) AS user
 FROM
   `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE
   job_type = "LOAD"'

El resultado debería ser similar a lo siguiente:

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

Ejemplo 3: Trabajos más costosos

En el siguiente ejemplo, se muestra cómo encontrar los cinco trabajos que escanearon la mayor cantidad de bytes en una organización para el día actual. Puedes filtrar aún más en statement_type para consultar información adicional, como cargas, exportaciones y consultas.

Para ejecutar la consulta en un proyecto que no sea el predeterminado, agrega el ID del proyecto en el siguiente formato: `project_id`.`region-region_name`.INFORMATION_SCHEMA.view; por ejemplo, `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Para ejecutar la consulta, haz lo siguiente:

Console

  1. En Cloud Console, abre la página de BigQuery.

    Ir a la página de BigQuery

  2. En el cuadro Editor de consultas, ingresa la siguiente consulta de SQL estándar. INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en Cloud Console.

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

  3. Haz clic en Ejecutar.

bq

Usa el comando query y especifica la sintaxis de SQL estándar con las marcas --nouse_legacy_sql o --use_legacy_sql=false. Se requiere la sintaxis de SQL estándar para las consultas INFORMATION_SCHEMA.

Para ejecutar la consulta, ingresa lo siguiente:

bq query --nouse_legacy_sql \
'SELECT
   job_id,
   user_email,
   total_bytes_processed
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
 WHERE EXTRACT(DATE FROM  creation_time) = current_date()
 ORDER BY total_bytes_processed DESC
 LIMIT 5'

El resultado debería ser similar a lo siguiente:

  +--------------+--------------+---------------------------+
  | job_id       |  user_email  |  total_bytes_processed    |
  +--------------+--------------+---------------------------+
  | bquxjob_1    |  abc@xyz.com |    999999                 |
  +--------------+--------------+---------------------------+
  | bquxjob_2    |  def@xyz.com |    888888                 |
  +--------------+--------------+---------------------------+
  | bquxjob_3    |  ghi@xyz.com |    777777                 |
  +--------------+--------------+---------------------------+
  | bquxjob_4    |  jkl@xyz.com |    666666                 |
  +--------------+--------------+---------------------------+
  | bquxjob_5    |  mno@xyz.com |    555555                 |
  +--------------+--------------+---------------------------+

Ejemplo 4: Trabajos pendientes y en ejecución

En el siguiente ejemplo, se enumeran los trabajos que inició el usuario actual y que están en estado pendiente o en ejecución.

Para ejecutar la consulta en un proyecto que no sea el predeterminado, agrega el ID del proyecto en el siguiente formato: `project_id`.`region-region_name`.INFORMATION_SCHEMA.view; por ejemplo, `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Para ejecutar la consulta, haz lo siguiente:

Console

  1. En Cloud Console, abre la página de BigQuery.

    Ir a la página de BigQuery

  2. En el cuadro Editor de consultas, ingresa la siguiente consulta de SQL estándar. INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en Cloud Console.

    SELECT
 job_id,
 creation_time,
 query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE state != "DONE"

  3. Haz clic en Ejecutar.

bq

Usa el comando query y especifica la sintaxis de SQL estándar con las marcas --nouse_legacy_sql o --use_legacy_sql=false. Se requiere la sintaxis de SQL estándar para las consultas INFORMATION_SCHEMA.

Para ejecutar la consulta, ingresa lo siguiente:

bq query --nouse_legacy_sql \
'SELECT
   job_id,
   creation_time,
   query
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
 WHERE state != "DONE"'

El resultado debería ser similar a lo siguiente:

  +--------------+--------------+----------------------------------------------+
  | job_id       |  creation_time            |  query                          |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_1    |  2019-10-10 00:00:00 UTC  |  SELECT ... FROM dataset.table1 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_2    |  2019-10-10 00:00:01 UTC  |  SELECT ... FROM dataset.table2 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_3    |  2019-10-10 00:00:02 UTC  |  SELECT ... FROM dataset.table3 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_4    |  2019-10-10 00:00:03 UTC  |  SELECT ... FROM dataset.table4 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_5    |  2019-10-10 00:00:04 UTC  |  SELECT ... FROM dataset.table5 |
  +--------------+--------------+----------------------------------------------+

Próximos pasos