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
requierebigquery.jobs.list
para el proyecto y está disponible para las funcionesProject Viewer
yBigQuery User
.JOBS_BY_PROJECT
requierebigquery.jobs.listAll
en el proyecto y está disponible para las funcionesProject Owner
yBigQuery Admin
.JOBS_BY_FOLDER
requierebigquery.jobs.listAll
en la carpeta superior y está disponible paraFolder Admin
yBigQuery Admin
de la carpeta.JOBS_BY_ORGANIZATION
requierebigquery.jobs.listAll
para la organización y está disponible para las funcionesOrganization bigquery.resourceAdmin
,Organization Owner
yOrganization Admin
. Ten en cuenta queJOBS_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
En Cloud Console, abre la página de BigQuery.
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()
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
En Cloud Console, abre la página de BigQuery.
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"
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
En Cloud Console, abre la página de BigQuery.
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
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
En Cloud Console, abre la página de BigQuery.
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"
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
- Para ver una descripción general de
INFORMATION_SCHEMA
, consulta Introducción aINFORMATION_SCHEMA
de BigQuery. - Aprende a usar
INFORMATION_SCHEMA
para obtener metadatos de reservas. - Aprende a usar
INFORMATION_SCHEMA
para obtener metadatos de transmisiones. - Aprende a usar
INFORMATION_SCHEMA
para obtener metadatos de conjuntos de datos. - Aprende a usar
INFORMATION_SCHEMA
para obtener metadatos de tablas. - Aprende a usar
INFORMATION_SCHEMA
para obtener metadatos de vistas.