Como conseguir metadados de jobs usando INFORMATION_SCHEMA

INFORMATION_SCHEMA é uma série de visualizações que fornecem acesso a metadados sobre conjuntos de dados, rotinas, tabelas, visualizações, jobs, reservas e dados de streaming.

Consulte a visualização INFORMATION_SCHEMA.JOBS_BY_* para recuperar metadados em tempo real sobre jobs do BigQuery. Essa visualização contém jobs em execução no momento, bem como os últimos 180 dias de histórico de jobs concluídos.

Permissões necessárias

A recuperação de metadados de jobs usando tabelas INFORMATION_SCHEMA requer permissões de escopo apropriado:

  • JOBS_BY_USER requer bigquery.jobs.list para o projeto e está disponível para os papéis Project Viewer, BigQuery User e .
  • JOBS_BY_PROJECT requer bigquery.jobs.listAll para o projeto e está disponível para os papéis Project Owner e BigQuery Admin.
  • JOBS_BY_FOLDER requer bigquery.jobs.listAll para a pasta pai e está disponível para Folder Admin e BigQuery Admin para a pasta.
  • JOBS_BY_ORGANIZATION requer bigquery.jobs.listAll para a organização e está disponível para os papéis Organization bigquery.resourceAdmin, Organization Owner e Organization Admin. Observe que JOBS_BY_ORGANIZATION está disponível apenas para usuários de organizações definidas do Google Cloud.

Para mais informações sobre permissões granulares do BigQuery, consulte papéis e permissões.

Schema

Quando você consulta as visualizações INFORMATION_SCHEMA.JOBS_BY_*, os resultados da consulta contêm uma linha para cada job do BigQuery.

  • INFORMATION_SCHEMA.JOBS_BY_USER retorna apenas os jobs enviados pelo usuário atual no projeto atual.
  • INFORMATION_SCHEMA.JOBS_BY_PROJECT retorna todos os jobs enviados no projeto atual.
  • INFORMATION_SCHEMA.JOBS_BY_FOLDER retorna todos os jobs enviados na pasta pai do projeto atual, incluindo os jobs em subpastas abaixo dele.
  • INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION retorna todos os jobs enviados na organização que está associada ao projeto atual.

A visualização INFORMATION_SCHEMA.JOBS_BY_* tem o seguinte esquema:

Nome da coluna Tipo de dados Valor
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.
project_id STRING (Coluna de clustering) ID do projeto.
project_number INTEGER Número do projeto.
folder_numbers RECORD IDs de pastas da Administração de Códigos das Contas do Google (GAIA, na sigla em inglês) em um ancestral do projeto, a partir da pasta de folha mais próxima do projeto. Essa coluna é preenchida apenas em JOBS_BY_FOLDER.
user_email STRING (Coluna de clustering) endereço de e-mail ou conta de serviço do usuário que executou o job.
job_id STRING ID do job. Por exemplo, bquxjob_1234.
job_type STRING O tipo de job. Pode ser QUERY, LOAD, EXTRACT, COPY, ou null. O tipo de job null indica um job interno, como avaliação da instrução do job de script ou atualização da visualização materializada.
statement_type STRING O tipo de instrução de consulta, se for válido. Por exemplo, SELECT, INSERT, UPDATE, ou DELETE.
priority STRING A prioridade deste job.
start_time TIMESTAMP Horário de início deste job.
end_time TIMESTAMP Horário de término deste job.
query STRING Texto da consulta SQL. Observação: apenas a visualização JOBS_BY_PROJECT tem a coluna query.
state STRING Estado em execução do job. Os estados válidos incluem PENDING, RUNNING, e DONE.
reservation_id STRING Nome da principal reserva atribuída a este job, se aplicável. Se o job for executado em um projeto atribuído a uma reserva, ele terá o seguinte formato: reservation-admin-project:reservation-location.reservation-name.
total_bytes_processed INTEGER Total de bytes processados pelo job.
total_slot_ms INTEGER Milissegundos de slot do job ao longo de sua duração.
error_result RECORD Detalhes do erro (se houver) como um ErrorProto.
cache_hit BOOLEAN Se os resultados da consulta deste job eram de um cache.
destination_table RECORD Tabela de destino dos resultados (se houver).
referenced_tables RECORD Matriz de tabelas referenciadas pelo job.
labels RECORD Matriz de rótulos aplicados ao job como strings key e value.
timeline RECORD Cronograma de consulta do job. Contém snapshots de execução de consulta.
job_stages RECORD Estágios de consulta do job.
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.

Retenção de dados

No momento, somente os últimos 180 dias do histórico de job são retidos em visualizações de job INFORMATION_SCHEMA.

Regionalidade

As visualizações de job INFORMATION_SCHEMA do BigQuery são regionalizadas. Para consultar essas visualizações, é preciso usar um qualificador de região.

Exemplos

Exemplo 1: 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 em um projeto diferente do seu projeto padrão, inclua o ID do projeto no seguinte formato: `project_id`.`region-region_name`.INFORMATION_SCHEMA.view. Por exemplo, `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Para executar a consulta, faça o seguinte:

Console

  1. No Console do Cloud, abra a página do BigQuery.

    Acesse a página do BigQuery

  2. Na caixa Editor de consultas, insira a seguinte consulta SQL padrão. INFORMATION_SCHEMA requer sintaxe SQL padrão. O SQL padrão é a sintaxe padrão do Console do Cloud.

    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. Clique em Executar.

bq

Use o comando query e especifique a sintaxe SQL padrão usando a sinalização --nouse_legacy_sql ou --use_legacy_sql=false. A sintaxe SQL padrão é obrigatória para consultas INFORMATION_SCHEMA.

Para executar a consulta, insira:

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

Os resultados terão a aparência abaixo:

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

Você verifica 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)

Exemplo 2: carregar histórico do job

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

Para executar a consulta em um projeto diferente do seu projeto padrão, inclua o ID do projeto no seguinte formato: `project_id`.`region-region_name`.INFORMATION_SCHEMA.view. Por exemplo, `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Para executar a consulta, faça o seguinte:

Console

  1. No Console do Cloud, abra a página do BigQuery.

    Acesse a página do BigQuery

  2. Insira a consulta SQL padrão a seguir na caixa Editor de consultas. INFORMATION_SCHEMA requer sintaxe SQL padrão. O SQL padrão é a sintaxe padrão do Console do Cloud.

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

  3. Clique em Executar.

bq

Use o comando query e especifique a sintaxe SQL padrão usando a sinalização --nouse_legacy_sql ou --use_legacy_sql=false. A sintaxe SQL padrão é obrigatória para consultas INFORMATION_SCHEMA.

Para executar a consulta, insira:

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

Os resultados terão a aparência abaixo:

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

Exemplo 3: jobs mais caros

No exemplo a seguir, demonstramos como encontrar os cinco jobs que verificaram a maioria dos bytes em uma organização no dia atual. É possível filtrar mais em statement_type para consultar outras informações, como cargas, exportações e consultas.

Para executar a consulta em um projeto diferente do seu projeto padrão, inclua o ID do projeto no seguinte formato: `project_id`.`region-region_name`.INFORMATION_SCHEMA.view. Por exemplo, `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Para executar a consulta, faça o seguinte:

Console

  1. No Console do Cloud, abra a página do BigQuery.

    Acesse a página do BigQuery

  2. Na caixa Editor de consultas, insira a seguinte consulta SQL padrão. INFORMATION_SCHEMA requer sintaxe SQL padrão. O SQL padrão é a sintaxe padrão do Console do Cloud.

    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. Clique em Executar.

bq

Use o comando query e especifique a sintaxe SQL padrão usando a sinalização --nouse_legacy_sql ou --use_legacy_sql=false. A sintaxe SQL padrão é obrigatória para consultas INFORMATION_SCHEMA.

Para executar a consulta, insira:

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'

Os resultados terão a aparência abaixo:

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

Exemplo 4: jobs pendentes e em execução

No exemplo a seguir, listamos os jobs que foram iniciados pelo usuário atual e estão agora no estado pendente ou em execução.

Para executar a consulta em um projeto diferente do seu projeto padrão, inclua o ID do projeto no seguinte formato: `project_id`.`region-region_name`.INFORMATION_SCHEMA.view. Por exemplo, `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Para executar a consulta, faça o seguinte:

Console

  1. No Console do Cloud, abra a página do BigQuery.

    Acesse a página do BigQuery

  2. Na caixa Editor de consultas, insira a seguinte consulta SQL padrão. INFORMATION_SCHEMA requer sintaxe SQL padrão. O SQL padrão é a sintaxe padrão do Console do Cloud.

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

  3. Clique em Executar.

bq

Use o comando query e especifique a sintaxe SQL padrão usando a sinalização --nouse_legacy_sql ou --use_legacy_sql=false. A sintaxe SQL padrão é obrigatória para consultas INFORMATION_SCHEMA.

Para executar a consulta, insira:

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

Os resultados terão a aparência abaixo:

  +--------------+--------------+----------------------------------------------+
  | 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óximas etapas