Obtén metadatos de trabajos con INFORMATION_SCHEMA

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

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 para el proyecto y está disponible para las funciones Project Owner y BigQuery Admin.
  • 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_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 (Columna de partición) Hora de creación de este trabajo
project_id STRING (Columna de agrupamiento en clústeres) ID del proyecto.
project_number INTEGER Número del proyecto
user_email STRING (Columna de agrupamiento en clústeres) Dirección de correo electrónico o cuenta de servicio del usuario que ejecutó el trabajo.
job_id STRING ID del trabajo. Por ejemplo, “bquxjob_1234”
job_type STRING El tipo de trabajo. Puede ser QUERY, LOAD, EXTRACT, COPY o UNKNOWN.
statement_type STRING El tipo de declaración de consulta, si es válido. Por ejemplo, SELECT, INSERT, UPDATE o DELETE.
start_time TIMESTAMP Hora de inicio de este trabajo
end_time TIMESTAMP Hora de finalización de este trabajo
query STRING Texto de 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 Nombre de la reserva principal asignada a este trabajo, si corresponde.
total_bytes_processed INTEGER Total de bytes procesados por el trabajo
total_slot_ms INTEGER Milisegundos de ranura para el trabajo durante toda su duración.
error_result RECORD Detalles del error (si corresponde).
cache_hit BOOLEAN Indica si los resultados de la consulta se almacenaron en caché.
destination_table RECORD Tabla de destino para los resultados (si corresponde).
referenced_tables RECORD Arreglo de tablas a las que hace referencia el trabajo.
labels RECORD Arreglo de etiquetas aplicadas al trabajo.
timeline RECORD Cronograma de consultas del trabajo. Contiene instantáneas de la ejecución de consultas.
job_stages RECORD Etapas de consulta del 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 anteponer un nombre de región relevante con el siguiente formato: `region-region_name`.INFORMATION_SCHEMA.view.

Por ejemplo:

  • Para consultar datos en la multirregión de EE.UU., usa `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  • Para consultar datos en la multirregión de la UE, usa `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  • Para consultar datos en la región asia-northeast1, usa `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

Puedes encontrar la lista de regiones disponibles aquí.

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.

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 IU web de BigQuery.

    Ir a Cloud Console

  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
 job_type = "QUERY"
 AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()

  3. Haz clic en Ejecutar.

CLI

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
   job_type = "QUERY"
   AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()'

Los resultados deberían verse así:

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

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 escanea 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 IU web de BigQuery.

    Ir a Cloud Console

  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.

CLI

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 IU web de BigQuery.

    Ir a Cloud Console

  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.

CLI

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 IU web de BigQuery.

    Ir a Cloud Console

  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.

CLI

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