Como programar consultas

Nesta página, você aprende a programar consultas recorrentes no BigQuery.

Visão geral

É possível programar consultas para que sejam executadas de maneira recorrente. As consultas programadas precisam ser escritas em SQL padrão, o que pode incluir instruções em linguagem de definição de dados (DDL, na sigla em inglês) e linguagem de manipulação de dados (DML, na sigla em inglês). A string de consulta e a tabela de destino podem ser parametrizadas, o que torna possível organizar os resultados da consulta por data e hora.

Antes de começar

Antes de criar uma consulta programada:

  • Nas consultas programadas, são usados recursos do serviço de transferência de dados do BigQuery. Verifique se você concluiu todas as ações necessárias em Como ativar o serviço de transferência de dados do BigQuery.

  • Se você estiver criando a consulta programada usando a IU da Web clássica do BigQuery, ative os pop-ups no seu navegador em bigquery.cloud.google.com para poder visualizar a janela de permissões. É preciso conceder ao serviço de transferência de dados do BigQuery a permissão para gerenciar a consulta programada.

Permissões exigidas

Antes de programar uma consulta:

  • Certifique-se de que a pessoa que está criando a transferência tenha as seguintes permissões obrigatórias no BigQuery:

    • Permissões bigquery.transfers.update para criar a transferência
    • Permissões bigquery.datasets.update no conjunto de dados de destino

    O papel predefinido do IAM bigquery.admin para envolvidos no projeto inclui as permissões bigquery.transfers.update e bigquery.datasets.update. Para mais informações sobre papéis do IAM no BigQuery, consulte Controle de acesso.

Opções de configuração

String de consulta

A string de consulta precisa ser válida e escrita em SQL padrão. Cada execução de uma consulta programada pode receber os parâmetros de consulta a seguir.

Parâmetros disponíveis

Parâmetro Tipo no SQL padrão Valor
@run_time TIMESTAMP Representado no horário UTC. Para consultas programadas regularmente, run_time representa o tempo de execução pretendido. Por exemplo, se a consulta programada for definida como "a cada 24 horas", a diferença de run_time entre duas consultas consecutivas será exatamente 24 horas, mesmo que o tempo de execução real possa variar um pouco.
@run_date DATE Representa uma data do calendário lógico.

Exemplo

Neste exemplo, o parâmetro @run_time faz parte da string de consulta, em que é consultado um conjunto de dados público chamado hacker_news.stories.

SELECT @run_time AS time,
  title,
  author,
  text
FROM `bigquery-public-data.hacker_news.stories`
LIMIT
  1000

Tabela de destino

Na configuração da consulta programada, se não houver uma tabela de destino para os resultados, o BigQuery tentará criá-la para você. Em caso de consulta DDL/DML, deixe a tabela de destino em branco.

Se houver uma tabela de destino, o respectivo esquema poderá ser atualizado com base nos resultados da consulta se você adicionar colunas ao esquema (ALLOW_FIELD_ADDITION) ou alterar o modo de uma coluna de REQUIRED para NULLABLE (ALLOW_FIELD_RELAXATION). Em todos os outros casos, as alterações no esquema da tabela entre execuções causam falha na consulta programada.

É possível que as colunas se refiram a tabelas de projetos e conjuntos de dados diferentes. Ao configurar a consulta programada, você não precisa incluir o conjunto de dados de destino no nome da tabela. Esse conjunto será especificado separadamente.

Preferência de gravação

A preferência de gravação selecionada determina como os resultados da consulta são gravados em uma tabela de destino criada. Em caso de consulta DDL/DML, deixe a preferência de gravação em branco.

  • WRITE_TRUNCATE: caso haja uma tabela, os respectivos dados serão substituídos no BigQuery.
  • WRITE_APPEND: caso haja uma tabela, os dados serão anexados a ela no BigQuery.

A criação, substituição ou anexação de uma tabela de destino só ocorre se a consulta for concluída com sucesso no BigQuery. As ações de criação, substituição e anexação ocorrem como uma atualização atômica na conclusão do job.

Opções de particionamento

Nas consultas programadas, pode ocorrer a criação de tabelas de destino particionadas ou não particionadas. Em caso de consulta DDL/DML, deixe o campo de particionamento em branco. Há dois tipos de particionamento de tabelas no BigQuery:

  • Tabelas particionadas por tempo de ingestão: particionamento com base no ambiente de execução da consulta programada.
  • Tabelas particionadas: tabelas que são particionadas com base em uma coluna TIMESTAMP ou DATE.

Para particionar a tabela de destino em uma coluna, especifique o nome da coluna ao configurar a consulta programada. Deixe o campo Particionamento em branco para tabelas particionadas por tempo de processamento e tabelas não particionadas.

Saiba mais sobre o particionamento de tabelas em Introdução às tabelas particionadas.

Exemplos de particionamento

  • Tabela sem particionamento
    • Tabela de destino: mytable
    • Campo de particionamento - deixe em branco
  • Tabela particionada por tempo de ingestão
    • Tabela de destino: mytable$YYYYMMDD
    • Campo de particionamento - deixe em branco
  • Tabela particionada por coluna
    • Tabela de destino: mytable
    • Campo de particionamento: nome da coluna com TIMESTAMP ou DATE para o particionamento da tabela

Parâmetros disponíveis

Ao configurar a consulta programada, é possível especificar o modo de particionamento da tabela de destino com parâmetros de tempo de execução.

Parâmetro Tipo de modelo Valor
run_time Carimbo de data/hora formatado Na hora UTC, de acordo com a programação. Para consultas programadas regularmente, run_time representa o tempo de execução pretendido. Por exemplo, se a consulta programada for definida como "a cada 24 horas", a diferença de run_time entre duas consultas consecutivas será exatamente 24 horas, mesmo que o tempo de execução real possa variar um pouco.

Consulte TransferRun.runTime.
run_date String de data A data do parâmetro run_time no formato %Y%m%d. Por exemplo, 20180101. Este formato é compatível com tabelas particionadas por tempo de processamento.

Sistema de modelo

As consultas programadas são compatíveis com parâmetros de tempo de execução no nome da tabela de destino com uma sintaxe de modelos.

Sintaxe de modelos de parâmetros

A sintaxe de modelos é compatível com modelos básicos de strings e ajuste de horário. A referência aos parâmetros é feita nos seguintes formatos:

  • {run_date}
  • {run_time[+\-offset]|"time_format"}
Parâmetro Finalidade
run_date Este parâmetro é substituído pela data no formato YYYYMMDD.
run_time O parâmetro é compatível com as seguintes propriedades:


offset
Ajuste de horário expresso em horas (h), minutos (m) e segundos (s), nesta ordem.
Não é compatível com dias (d).
Decimais são permitidos. Por exemplo: 1.5h.

time_format
Uma string de formatação. Os parâmetros de formatação mais comuns são anos (%Y), meses (%m) e dias (%d).
Para tabelas particionadas, YYYYMMDD é o sufixo necessário, equivalente a "%Y%m%d".

Leia mais sobre a formatação de elementos datetime.

Observações sobre o uso:
  • Nenhum espaço em branco é permitido entre run_time, offset e time_format.
  • Para incluir as chaves literais na string, insira caracteres de escape como ‘\{‘ and ‘\}’.
  • Para incluir aspas literais ou uma barra vertical em time_format, como “YYYY|MM|DD”, insira-os como caracteres de escape na string de formatação como ‘\”’ ou ‘\|’.

Exemplos de modelos de parâmetros

Nestes exemplos, são demonstrados a especificação de nomes de tabelas de destino com formatos de tempo diferentes e o ajuste do tempo de execução.
run_time (UTC) Parâmetro modelado Nome da tabela de destino da saída
2018-02-15 00:00:00 mytable mytable
2018-02-15 00:00:00 mytable_{run_time|"%Y%m%d"} mytable_20180215
2018-02-15 00:00:00 mytable_+25h{run_time|"%Y%m%d"} mytable_20180216
2018-02-15 00:00:00 mytable_-1h{run_time|"%Y%m%d"} mytable_20180214
2018-02-15 00:00:00 mytable_+1.5h{run_time|"%Y%m%d;%H"}
,
mytable_+90m{run_time|"%Y%m%d;%H"}
mytable_2018021501
2018-02-15 00:00:00 {run_time+97s|"%Y%m%d"}_mytable_{run_time|"%H%M%s"} 20180215_mytable_000137

Como configurar uma consulta programada

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.

    Acessar a IU da web do BigQuery

  2. Execute a consulta do seu interesse. Quando estiver satisfeito com os resultados, clique em Programar consulta e em Criar nova consulta programada.

    Agendar consulta na IU da Web do BigQuery

  3. As opções de consulta programada são exibidas no painel Nova consulta programada.

  4. No painel Nova consulta programada:

    • Em Nome da consulta programada, insira um nome, como My scheduled query. O nome pode ser qualquer valor para fácil identificação da consulta programada, caso você precise modificá-la posteriormente.
    • (Opcional) Em Opções de programação, deixe o valor padrão Diária (a cada 24 horas, com base na hora da criação) ou clique em Editar para alterar a hora. Também é possível alterar o intervalo para "Semanal", "Mensal" ou "Personalizado". Se você selecionar "Personalizado", especifique um horário em estilo "Cron", como every 3 hours. O menor período permitido é de 15 minutos. Veja o campo schedule em TransferConfig para saber outros valores de API válidos.
    • Em Nome do conjunto de dados, escolha o conjunto de dados de destino apropriado.
    • Em Tabela de destino:
      • Para uma consulta SQL padrão, insira o nome da tabela de destino.
      • Em caso de consulta DDL/DML, deixe esse campo em branco.
    • Em Preferência de gravação na tabela de destino, escolha WRITE_TRUNCATE para substituir a tabela ou WRITE_APPEND para anexar dados à tabela. Para uma consulta DDL ou DML, essa opção não está disponível.
    • (Opcional) Marque Enviar notificações por e-mail para permitir notificações sobre falhas na execução da transferência.

      Nova consulta programada

  5. Clique em Programar.

  6. Para visualizar o status de suas consultas programadas, clique em Consultas programadas no painel de navegação. Atualize a página para ver o status atualizado de suas consultas programadas. Clique em um status para ver mais detalhes sobre essa consulta programada.

    Listar consultas programadas

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Execute a consulta do seu interesse. Quando estiver satisfeito com os resultados, clique em Programar consulta.

    Agendar consulta na IU da Web clássica do BigQuery

  3. As opções de consulta programada são exibidas abaixo da caixa da consulta.

  4. Na página Nova consulta programada:

    • Em Conjunto de dados de destino, escolha o conjunto de dados apropriado.
    • Em Nome de exibição , insira um nome para a consulta programada, como My scheduled query . O nome pode ser qualquer valor para fácil identificação da consulta programada, se você precisar modificá-la posteriormente.
    • Em Tabela de destino:
      • Para uma consulta SQL padrão, insira o nome da tabela de destino.
      • Em caso de consulta DDL/DML, deixe esse campo em branco.
    • Em Preferência de gravação:
      • Para uma consulta SQL padrão, escolha WRITE_TRUNCATE para substituir a tabela de destino ou WRITE_APPEND para anexar dados à tabela.
      • Para uma consulta DDL/DML, escolha Não especificado.
    • (Opcional) Em Campo de particionamento:

    • (Opcional) Em Programação, deixe o valor padrão Diária (a cada 24 horas, com base na hora da criação) ou clique em Editar para alterar a hora. Também é possível alterar o intervalo para "Semanal", "Mensal" ou "Personalizado". Se você selecionar "Personalizado", especifique um horário em estilo "Cron", como every 3 hours. O menor período permitido é de 15 minutos. Veja o campo schedule em TransferConfig para saber outros valores de API válidos.

      Programar consulta

    • (Opcional) Expanda a seção Avançado e configure as notificações de execução da transferência. As notificações de execução de transferência estão na versão Alfa.

      • Em Tópico do Cloud Pub/Sub, digite o nome do tópico. Por exemplo, projects/myproject/topics/mytopic.
      • Marque Enviar notificações por e-mail para permitir notificações por e-mail sobre falhas na execução da transferência.

        Tópico do Cloud Pub/Sub

  5. Clique em Adicionar.

  6. Para visualizar o status de suas consultas programadas, clique em Consultas programadas no painel de navegação. Atualize a página para ver o status atualizado das suas consultas programadas. Clique em um status para ver mais detalhes sobre essa consulta programada.

    Listar consultas programadas

Como configurar uma execução manual em datas históricas

Além de agendar uma consulta para ser executada no futuro, também é possível acionar execuções imediatas manualmente. É necessário acionar uma execução imediata se sua consulta usar o parâmetro run_date e houver problemas durante uma execução anterior.

Por exemplo: todos os dias às 9h, você consulta uma tabela de origem para as linhas que correspondem à data atual. No entanto, você acha que os dados não foram adicionados à tabela de origem nos últimos três dias. Nessa situação, é possível definir a consulta para ser executada em dados históricos em um período que você especificar. Sua consulta é executada usando combinações de run_date e run-time que correspondem às datas que você configurou na consulta programada.

Depois de configurar uma consulta programada, veja como executar a consulta usando um período histórico:

Console

Depois de clicar em Programar para salvar a consulta, clique no botão Consultas programadas para ver todas as consultas relacionadas. Clique em qualquer nome de exibição para ver os detalhes da programação de consulta. No canto superior direito da página, clique em Programar preenchimento para especificar um período histórico.

botão "Programar preenchimento"

Os tempos de execução escolhidos estão todos dentro do intervalo selecionado, incluindo a primeira data e excluindo a última data.

definir datas históricas

Exemplo 1

Sua consulta agendada está definida para ser executada every day 09:00 no horário do Pacífico. Você está perdendo dados de 1º de janeiro, 2 de janeiro e 3 de janeiro. Escolha o seguinte período histórico:

Start Time = 1/1/19
End Time = 1/4/19

Sua consulta é executada utilizando os parâmetros run_date e run_time que correspondem aos seguintes horários:

  • 01/01/19 às 9h, horário do Pacífico
  • 02/01/19 às 9h, horário do Pacífico
  • 03/01/19 às 9h, horário do Pacífico

Exemplo 2

Sua consulta programada está definida para ser executada every day 23:00 no horário do Pacífico. Você está perdendo dados de 1º de janeiro, 2 de janeiro e 3 de janeiro. Escolha os seguintes períodos históricos (as datas posteriores são escolhidas porque o UTC tem uma data diferente às 23h, horário do Pacífico):

Start Time = 1/2/19
End Time = 1/5/19

Sua consulta é executada utilizando os parâmetros run_date e run_time que correspondem aos seguintes horários:

  • 02/01/19 às 9h UTC ou 01/01/2019 às 23h, horário do Pacífico
  • 03/01/19 às 9h UTC ou 02/01/2019 às 23h, horário do Pacífico
  • 04/01/19 às 9h UTC ou 03/01/2019 às 23h, horário do Pacífico

Depois de configurar execuções manuais, atualize a página para vê-las na lista de execuções.

IU clássica

Depois de clicar em Adicionar para salvar a consulta programada, você verá os respectivos detalhes. Abaixo desses detalhes, clique no botão Iniciar execuções manuais para especificar um período histórico.

botão iniciar execuções manuais

Refine ainda mais o período para ter um horário de início e término ou deixe os campos de horário como 00:00:00.

definir datas históricas

Exemplo 1

Se a consulta programada estiver definida para ser executada every day 14:00 e você aplicar o seguinte intervalo de datas histórico:

Start Time = 2/21/2018 00:00:00 AM
End Time = 2/24/2018 00:00:00 AM

a consulta será executada nos seguintes horários:

  • 21/2/2018 14:00:00
  • 22/2/2018 14:00:00
  • 23/2/2018 14:00:00

Exemplo 2

Se a consulta programada estiver definida para ser executada every fri at 01:05 e você aplicar o seguinte intervalo de datas histórico:

Start Time = 2/1/2018 00:00:00 (uma quinta-feira);
End Time = 2/24/2018 00:00:00 AM (também uma quinta-feira).

a consulta será executada nos seguintes horários:

  • 2/2/2018 01:05:00
  • 9/2/2018 01:05:00

Cotas

Uma consulta programada é executada com as credenciais e o projeto do criador, como se você estivesse executando a consulta por conta própria. A consulta programada está sujeita a todas as cotas e limites do BigQuery em jobs de consulta.

Limitações e problemas conhecidos

Regiões

Não há suporte para consultas entre regiões, e a tabela de destino da consulta programada precisa estar na mesma região dos dados que estão sendo consultados. Para mais informações sobre regiões e multirregiões, veja os locais dos conjuntos de dados.

Google Drive

É possível consultar os dados do Google Drive em uma consulta programada. Se você estiver programando uma consulta atual, talvez precise clicar em "Atualizar credenciais" na tela de detalhes da consulta. Aguarde de dez a vinte minutos para que a alteração entre em vigor. Pode ser necessário limpar o cache do navegador. As credenciais são atualizadas automaticamente para novas consultas programadas.

Atualizar credenciais

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.