Visualização JOBS

A visualização INFORMATION_SCHEMA.JOBS contém os metadados quase em tempo real sobre todos os jobs do BigQuery no projeto atual.

Papel necessário

Para receber a permissão necessária para consultar a visualização INFORMATION_SCHEMA.JOBS, peça ao administrador para conceder a você o papel do IAM de Leitor de recursos do BigQuery (roles/bigquery.resourceViewer) no projeto. Para mais informações sobre como conceder papéis, consulte Gerenciar acesso.

Esse papel predefinido contém a permissão bigquery.jobs.listAll, que é necessária para consultar a visualização INFORMATION_SCHEMA.JOBS.

Também é possível conseguir essa permissão com papéis personalizados ou outros papéis predefinidos.

Para mais informações sobre as permissões do BigQuery, consulte Controle de acesso com o IAM.

Schema

Os dados subjacentes são particionados pela coluna creation_time e agrupados por project_id e user_email. A coluna query_info contém mais informações sobre os jobs de consulta.

A visualização INFORMATION_SCHEMA.JOBS tem o seguinte esquema:

Nome da coluna Tipo de dado Valor
bi_engine_statistics RECORD Se o projeto estiver configurado para usar a BI Engine SQL Interface, este campo conterá BiEngineStatistics. Se esse não for seu caso, NULL.
cache_hit BOOLEAN Se os resultados da consulta deste job eram de um cache. Se você tiver um job de instrução de várias consultas, o cache_hit da consulta mãe será NULL.
creation_time TIMESTAMP (Coluna de particionamento) horário da criação do job. O particionamento é baseado no horário UTC desse carimbo de data/hora.
destination_table RECORD Tabela de destino dos resultados (se houver).
dml_statistics RECORD Se o job for uma consulta com uma instrução DML, o valor será um registro com os seguintes campos:
  • inserted_row_count: o número de linhas que foram inseridas.
  • deleted_row_count: o número de linhas que foram excluídas.
  • updated_row_count: o número de linhas que foram atualizadas.
Para todos os outros jobs, o valor é NULL.
Essa coluna está presente nas visualizações INFORMATION_SCHEMA.JOBS_BY_USER e INFORMATION_SCHEMA.JOBS_BY_PROJECT.
end_time TIMESTAMP O horário de término do job, em milissegundos, desde o início. Esse campo representa o momento em que o job entra no estado DONE.
error_result RECORD Detalhes dos erros como objetos ErrorProto.
job_id STRING O ID do job. Por exemplo, bquxjob_1234.
job_stages RECORD Estágios de consulta do job.

Observação: os valores desta coluna estão vazios para consultas lidas em tabelas com políticas de acesso no nível da linha. Para mais informações, consulte práticas recomendadas para segurança no nível da linha no BigQuery.

job_type STRING O tipo de job. Pode ser QUERY, LOAD, EXTRACT, COPY ou NULL. Um valor NULL indica um job interno, como uma avaliação de instrução de job de script ou uma atualização de visualização materializada.
labels RECORD Matriz de identificadores aplicados ao job como pares de chave-valor.
parent_job_id STRING ID do job pai (se houver).
priority STRING A prioridade deste job. Os valores válidos incluem INTERACTIVE e BATCH.
project_id STRING (Coluna de clustering) é o ID do projeto.
project_number INTEGER O número do projeto.
query STRING Texto da consulta SQL. Apenas a visualização JOBS_BY_PROJECT tem a coluna de consulta.
referenced_tables RECORD Matriz de tabelas referenciadas pelo job. Preenchido apenas para jobs de consulta.
reservation_id STRING Nome da reserva principal atribuída a este job, no formato RESERVATION_ADMIN_PROJECT:RESERVATION_LOCATION.RESERVATION_NAME.
Nesta saída:
  • RESERVATION_ADMIN_PROJECT: o nome do projeto do Google Cloud que administra a reserva
  • RESERVATION_LOCATION: o local da reserva
  • RESERVATION_NAME: o nome da reserva
session_info RECORD Detalhes sobre a sessão em que este job foi executado, se houver. (Visualização)
start_time TIMESTAMP Horário de início do job, em milissegundos, desde o período. Esse campo representa o momento em que o job faz a transição do estado PENDING para RUNNING ou DONE.
state STRING Estado em execução do job. Os estados válidos incluem PENDING, RUNNING e DONE.
statement_type STRING O tipo de instrução de consulta. Por exemplo, DELETE, INSERT, SCRIPT, SELECT ou UPDATE. Consulte QueryStatementType para acessar uma lista de valores válidos.
timeline RECORD Cronograma de consulta do job. Contém snapshots de execução de consulta.
total_bytes_billed INTEGER Se o projeto estiver configurado para usar preços sob demanda, então esse campo conterá o total de bytes cobrados pelo job. Se o projeto estiver configurado para usar preços fixos, então você não será cobrado por bytes, e esse campo será apenas informativo.

Observação: os valores desta coluna estão vazios para consultas lidas em tabelas com políticas de acesso no nível da linha. Para mais informações, consulte práticas recomendadas para segurança no nível da linha no BigQuery.

total_bytes_processed INTEGER

Total de bytes processados pelo job.

Observação: os valores desta coluna estão vazios para consultas lidas em tabelas com políticas de acesso no nível da linha. Para mais informações, consulte práticas recomendadas para segurança no nível da linha no BigQuery.

total_modified_partitions INTEGER Número total de partições do job modificado. Esse campo é preenchido para jobs LOAD e QUERY.
total_slot_ms INTEGER Milissegundos de slot para o job durante toda a duração no estado RUNNING, incluindo novas tentativas.
transaction_id STRING ID da transação em que este job foi executado, se houver. (Visualização)
user_email STRING (Coluna de clustering) endereço de e-mail ou conta de serviço do usuário que executou o job.
query_info.resource_warning STRING A mensagem de aviso que aparece quando o uso de recursos durante o processamento da consulta está acima do limite interno do sistema.
Um job de consulta bem-sucedido pode ter o campo resource_warning preenchido. Com o resource_warning, você ganha mais pontos de dados para otimizar suas consultas e configurar o monitoramento das tendências de desempenho de um conjunto equivalente de consultas usando query_hashes.
query_info.query_hashes.normalized_literals STRING Contém os hashes da consulta. normalized_literals é um hash STRING hexadecimal que ignora comentários, valores de parâmetros, UDFs e literais.
Esse campo aparece para consultas bem-sucedidas do GoogleSQL que não são ocorrências em cache.
query_info.performance_insights RECORD Insights de desempenho para o job.
query_info.optimization_details STRUCT As otimizações baseadas em histórico do job.
transferred_bytes INTEGER Total de bytes transferidos para consultas entre nuvens, como jobs de transferência entre nuvens do BigQuery Omni.
materialized_view_statistics RECORD Estatísticas de visualizações materializadas consideradas em um job de consulta. (Visualização)

Ao consultar INFORMATION_SCHEMA.JOBS para encontrar um custo de resumo dos jobs de consulta, exclua o tipo de instrução SCRIPT. Caso contrário, alguns valores poderão ser contados duas vezes. A linha SCRIPT inclui valores de resumo para todos os jobs filhos que foram executados como parte desse job.

Job de consulta de várias instruções

Um job de consulta de várias instruções é um job de consulta que usa a linguagem de procedimentos. Os jobs de consulta de várias instruções geralmente definem variáveis com DECLARE ou têm instruções de fluxo de controle, como IF ou WHILE. Ao consultar INFORMATION_SCHEMA.JOBS, talvez seja necessário reconhecer a diferença entre um job de consulta de várias instruções e outros jobs. Um job de consulta de várias instruções tem as seguintes características:

  • statement_type = SCRIPT
  • reservation_id = NULL
  • Jobs filhos. Cada job filho de um job de consulta de várias instruções tem um parent_job_id que aponta para o próprio job de consulta de várias instruções. Isso inclui valores de resumo para todos os jobs filhos executados como parte deste job. Por esse motivo, se você consultar INFORMATION_SCHEMA.JOBS para encontrar um resumo de custo de jobs de consulta, exclua o tipo de instrução SCRIPT. Caso contrário, alguns valores, como total_slot_ms, poderão ser contados duas vezes.

Retenção de dados

Essa visualização contém os jobs em execução e o histórico dos últimos 180 dias.

Escopo e sintaxe

As consultas nessa visualização precisam incluir um qualificador de região. A tabela a seguir explica o escopo da região dessa visualização:

Nome da visualização Escopo do recurso Escopo da região
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.JOBS[_BY_PROJECT] Nível do projeto REGION
Substitua:

  • Opcional: PROJECT_ID: o ID do seu projeto do Google Cloud. Se não for especificado, o projeto padrão será usado.
  • REGION: qualquer nome da região do conjunto de dados. Por exemplo, region-us.

Examples

Para executar a consulta em um projeto diferente do projeto padrão, adicione o ID do projeto no seguinte formato: 

`PROJECT_ID`.`region-REGION_NAME`.INFORMATION_SCHEMA.JOBS
Substitua:

  • PROJECT_ID: o ID do projeto.
  • REGION_NAME: a região do projeto.

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

Calcular a utilização média de slots

O exemplo a seguir calcula a utilização média de slots para todas as consultas nos últimos sete dias de um determinado projeto. Esse cálculo é mais preciso para projetos que têm uso de slot consistente ao longo da semana. Se o projeto não tiver uso de slot consistente, esse número poderá ser menor do que o esperado.

Para executar a consulta, faça o seguinte:

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();

O resultado será semelhante ao seguinte:

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

Verifique o uso de uma reserva específica com WHERE reservation_id = "…". Isso pode ser útil para determinar o percentual de uso de uma reserva durante um período. Para jobs de script, o job pai também informa o uso total de slots dos jobs filhos. Para evitar a contagem duplicada, use WHERE statement_type != "SCRIPT" para excluir o job pai.

Se você quiser verificar a utilização média de slots de jobs individuais, use total_slot_ms / TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)

Carregar histórico de jobs

O exemplo a seguir lista todos os usuários ou contas de serviço que enviaram um job de carregamento em lote de um determinado projeto. Como nenhum limite de tempo é especificado, esta consulta examina todo o histórico disponível (por exemplo, os últimos 30 dias).

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

O resultado será semelhante ao seguinte:

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

Receber o número de jobs de carregamento para determinar a cota de jobs diárias usada

O exemplo a seguir retorna o número de jobs por dia, conjunto de dados e tabela, para que você possa determinar quanto da cota diária do job é usada.

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

Acessar os últimos 10 jobs que falharam

O exemplo a seguir mostra os últimos 10 jobs que falharam:

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

A resposta deve ficar assim:

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

Consultar a lista de jobs de longa duração

O exemplo a seguir mostra a lista de jobs de longa duração que estão no estado RUNNING ou PENDING por mais 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;

O resultado será semelhante ao seguinte:

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

Bytes processados por identidade do usuário

O exemplo a seguir mostra o total de bytes cobrados para jobs de consulta por usuário:

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;

Observação: confira a ressalva da coluna total_bytes_billed na documentação do esquema para as visualizações JOBS.

Os resultados vão ter a aparência abaixo:

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

Detalhamento por hora dos bytes processados

O exemplo a seguir mostra o total de bytes cobrados para jobs de consulta em 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;

O resultado será semelhante ao seguinte:

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

Jobs de consulta por tabela

O exemplo a seguir mostra quantas vezes cada tabela consultada em my_project foi referenciada por um job 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;

O resultado será semelhante ao seguinte:

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

Consultas mais caras por projeto

O exemplo a seguir lista as consultas mais caras em my_project por tempo de uso do slot:

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

Você também pode listar as consultas mais caras por dados processados usando o exemplo a seguir:

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

O resultado dos dois exemplos é semelhante ao seguinte:

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

Receber detalhes sobre um aviso de recurso

Se você receber uma mensagem de erro Recursos excedidos, investigue as consultas em uma janela de tempo:

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;

Monitorar avisos de recursos agrupados por data

Se você receber a mensagem de erro Recursos excedidos, será possível monitorar o número total de avisos de recursos agrupados por data para saber se há alguma alteração na carga de trabalho:

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;

Estimar o uso de slots e o custo de consultas

O exemplo a seguir calcula os slots médios e máximos para cada job usando estimated_runnable_units.

O reservation_id será NULL se você não tiver reservas.

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;

Conferir insights de desempenho para consultas

O exemplo a seguir retorna todos os jobs de consulta que têm insights de desempenho do seu projeto nos últimos 30 dias com um URL que leva ao gráfico de execução da consulta no console do 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
  );

Ver jobs de atualização de metadados

O exemplo a seguir lista os jobs de atualização de metadados:

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;

Analisar a performance de consultas idênticas ao longo do tempo

O exemplo a seguir retorna os 10 principais jobs mais lentos nos últimos sete dias que executaram a mesma 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;

Substitua JOB_ID por qualquer job_id que executou a consulta que você está analisando.