Crie instantâneos de tabelas com uma consulta agendada

Este documento descreve como criar instantâneos mensais de uma tabela através de uma conta de serviço que executa uma consulta DDL agendada. O documento explica o seguinte exemplo:

  1. No projeto PROJECT, crie uma conta de serviço com o nome snapshot-bot.
  2. Conceda à conta de serviço snapshot-bot as autorizações de que necessita para tirar capturas instantâneas de tabelas da tabela TABLE, que se encontra no conjunto de dados DATASET, e armazene as capturas instantâneas de tabelas no conjunto de dados BACKUP.
  3. Escreva uma consulta que crie instantâneos mensais da tabela TABLE e os coloque no conjunto de dados BACKUP. Uma vez que não pode substituir uma imagem instantânea de tabela existente, as imagens instantâneas de tabela têm de ter nomes exclusivos. Para o conseguir, a consulta anexa a data atual aos nomes das imagens instantâneas das tabelas; por exemplo, TABLE_20220521. As capturas de ecrã das tabelas expiram após 40 dias.
  4. Agende a execução da consulta pela conta de serviço snapshot-bot no primeiro dia de cada mês.

Este documento destina-se a utilizadores que estão familiarizados com o BigQuery e as cópias instantâneas de tabelas do BigQuery.

Autorizações e funções

Esta secção descreve as autorizações de gestão de identidade e de acesso (IAM) de que precisa para criar uma conta de serviço e agendar uma consulta, bem como as funções de IAM predefinidas que concedem essas autorizações.

Autorizações

Para trabalhar com uma conta de serviço, precisa das seguintes autorizações:

Autorização Recurso Tipo de recurso
iam.serviceAccounts.* PROJECT Projeto

Para agendar uma consulta, precisa da seguinte autorização:

Autorização Recurso Tipo de recurso
bigquery.jobs.create PROJECT Projeto

Funções

As funções predefinidas que fornecem as autorizações necessárias para trabalhar com uma conta de serviço são as seguintes:

Role Recurso Tipo de recurso
Qualquer uma das seguintes opções:

roles/iam.serviceAccountAdmin
roles/editor
roles/owner 		PROJECT Projeto

As funções predefinidas do BigQuery que fornecem as autorizações necessárias para agendar uma consulta são as seguintes:

Role Recurso Tipo de recurso
Qualquer uma das seguintes opções:

roles/bigquery.user
roles/bigquery.jobuser
roles/bigquery.admin` 		PROJECT Projeto

Crie a conta de serviço snapshot-bot

Siga estes passos para criar a snapshot-bot conta de serviço e conceder-lhe as autorizações de que precisa para executar consultas no projeto PROJECT:

Consola

  1. Na Google Cloud consola, aceda à página Contas de serviço:

    Aceda a Contas de serviço

  2. Selecione o projeto PROJECT.

  3. Crie a conta de serviço snapshot-bot:

    1. Clique em Criar conta de serviço.

    2. No campo Nome da conta de serviço, introduza snapshot-bot.

    3. Clique em Criar e continuar.

  4. Conceda à conta de serviço as autorizações de que necessita para executar tarefas do BigQuery:

    1. Na secção Conceda a esta conta de serviço acesso ao projeto, selecione a função Utilizador do BigQuery.

    2. Clique em Concluído.

O BigQuery cria a conta de serviço com o endereço de email snapshot-bot@PROJECT.iam.gserviceaccount.com.

Para verificar se o BigQuery criou a conta de serviço com as autorizações que especificou, siga estes passos:

Consola

Verifique se o BigQuery criou a conta de serviço:

  1. Na Google Cloud consola, aceda à página Contas de serviço:

    Aceda a Contas de serviço

  2. Selecione o projeto PROJECT.

  3. Clique em snapshot-bot@PROJECT.iam.gserviceaccount.com.

  4. Verifique se a mensagem Estado da conta de serviço indica que a sua conta de serviço está ativa.

Verifique se o BigQuery concedeu à sua conta de serviço a autorização de que necessita para executar consultas:

  1. Na Google Cloud consola, aceda à página Gerir recursos:

    Aceda a Gerir recursos

  2. Clique em PROJECT.

  3. Clique em Mostrar painel de informações.

  4. No separador Autorizações, expanda o nó Utilizador do BigQuery.

  5. Confirme se a sua conta de serviço snapshot-bot está listada.

Conceda autorizações à conta de serviço

Esta secção descreve como conceder à conta de serviço snapshot-bot as autorizações necessárias para criar instantâneos de tabelas da tabela DATASET.TABLE no conjunto de dados BACKUP.

Autorização para tirar instantâneos da tabela base

Para conceder à conta de serviço snapshot-bot as autorizações necessárias para tirar instantâneos da tabela DATASET.TABLE, siga estes passos:

Consola

  1. Na Google Cloud consola, abra a página BigQuery.

    Aceda ao BigQuery

  2. No painel Explorador, expanda o nó do projeto PROJECT.

  3. Expanda o nó do conjunto de dados DATASET.

  4. Selecione a tabela TABLE.

  5. Clique em Partilhar. É aberto o painel Partilhar.

  6. Clique em Adicionar diretor. O painel Conceder acesso é aberto.

  7. Em Novos membros, introduza o endereço de email da conta de serviço: snapshot-bot@PROJECT.iam.gserviceaccount.com.

  8. No menu pendente Selecionar uma função, selecione a função Editor de dados do BigQuery.

  9. Clique em Guardar.

  10. No painel Partilhar, expanda o nó Editor de dados do BigQuery e verifique se a conta de serviço snapshot-bot@PROJECT.iam.gserviceaccount.com está listada.

  11. Clique em Fechar.

bq

  1. Na Google Cloud consola, ative o Cloud Shell:

    Ative o Cloud Shell

  2. Introduza o seguinte comando bq add-iam-policy-binding:

    bq add-iam-policy-binding \
--member=serviceAccount:snapshot-bot@PROJECT.iam.gserviceaccount.com \
--role=roles/bigquery.dataEditor DATASET.TABLE

O BigQuery confirma que a nova associação de políticas foi adicionada.

Autorização para criar tabelas no conjunto de dados de destino

Conceda à conta de serviço snapshot-bot as autorizações de que necessita para criar instantâneos de tabelas no conjunto de dados BACKUP da seguinte forma:

Consola

  1. Na Google Cloud consola, aceda à página BigQuery.

    Aceda ao BigQuery

  2. No painel Explorador, expanda o nó do projeto PROJECT.

  3. Clique no menu do nó do conjunto de dados BACKUP e selecione Abrir.

  4. Clique em Partilhar conjunto de dados. É aberto o painel Autorizações do conjunto de dados.

  5. No campo Adicionar membros, introduza o endereço de email da conta de serviço: snapshot-bot@PROJECT.iam.gserviceaccount.com.

  6. No menu pendente Selecionar uma função, selecione a função Proprietário de dados do BigQuery.

  7. Clique em Adicionar.

  8. No painel Autorizações do conjunto de dados, verifique se a conta de serviço snapshot-bot@PROJECT.iam.gserviceaccount.com está listada no nó Proprietário de dados do BigQuery.

  9. Clique em Concluído.

A sua conta de serviço snapshot-bot tem agora as seguintes funções do IAM para os seguintes recursos:

Função Recurso Tipo do recurso Finalidade
Editor de dados do BigQuery PROJECT:DATASET.TABLE Tabela Tire fotos da mesa TABLE.
Proprietário de dados do BigQuery PROJECT:BACKUP Conjunto de dados Criar e eliminar instantâneos de tabelas no conjunto de dados BACKUP.
Utilizador do BigQuery PROJECT Projeto Execute a consulta agendada que cria as capturas instantâneas da tabela.

Estas funções fornecem as autorizações de que a snapshot-bot conta de serviço precisa para executar consultas que criam instantâneos de tabelas da DATASET.TABLE tabela e colocam os instantâneos de tabelas no conjunto de dados BACKUP.

Escreva uma consulta com várias declarações

Esta secção descreve como escrever uma consulta com várias declarações que cria uma imagem instantânea da tabela da tabela DATASET.TABLE através da CREATE SNAPSHOT TABLE declaração DDL. A captura de ecrã é guardada no conjunto de dados BACKUP e expira após um dia.

-- Declare variables
DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;

-- Set variables
SET expiration = DATE_ADD(current_timestamp(), INTERVAL 1 DAY);
SET snapshot_name = CONCAT(
                      "BACKUP.TABLE_",
                      FORMAT_DATETIME('%Y%m%d', current_date()));

-- Construct the query to create the snapshot
SET query = CONCAT(
              "CREATE SNAPSHOT TABLE ",
              snapshot_name,
              " CLONE mydataset.mytable OPTIONS(expiration_timestamp = TIMESTAMP '",
              expiration,
              "');");

-- Run the query
EXECUTE IMMEDIATE query;

Agende a consulta mensal

Agende a execução da consulta às 05:00 no primeiro dia de cada mês da seguinte forma:

bq

  1. Na Google Cloud consola, ative o Cloud Shell:

    Ative o Cloud Shell

  2. Introduza o seguinte comando bq query:

    bq query --use_legacy_sql=false --display_name="Monthly snapshots of the TABLE table" \
--location="us" --schedule="1 of month 05:00" \
--project_id=PROJECT \
'DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;
SET expiration = DATE_ADD(@run_time, INTERVAL 40 DAY);
SET snapshot_name = CONCAT("BACKUP.TABLE_",
  FORMAT_DATETIME("%Y%m%d", @run_date));
SET query = CONCAT("CREATE SNAPSHOT TABLE ", snapshot_name,
  " CLONE PROJECT.DATASET.TABLE OPTIONS(expiration_timestamp=TIMESTAMP \"",
  expiration, "\");");
EXECUTE IMMEDIATE query;'

  3. O BigQuery agenda a consulta.

A consulta com várias declarações no comando da ferramenta de linhas de comando bq difere da consulta que executou na consola da seguinte forma: Google Cloud

  • A consulta da ferramenta de linhas de comando bq usa @run_date em vez de current_date(). Numa consulta agendada, o parâmetro @run_date contém a data atual. No entanto, numa consulta interativa, o parâmetro @run_date não é suportado. Pode usar current_date() em vez de @run_date para testar uma consulta interativa antes de a agendar.
  • A consulta da ferramenta de linha de comandos bq usa @run_time em vez de current_timestamp() por um motivo semelhante: o parâmetro @run_time não é suportado em consultas interativas, mas current_timestamp() pode ser usado em vez de @run_time para testar a consulta interativa.
  • A consulta da ferramenta de linhas de comando bq usa uma barra e aspas duplas \" em vez de aspas simples ' porque as aspas simples são usadas para incluir a consulta.

Configure a conta de serviço para executar a consulta agendada

A consulta está atualmente agendada para ser executada com as suas credenciais. Atualize a consulta agendada para ser executada com as credenciais da snapshot-botconta de serviço da seguinte forma:

  1. Execute o comando bq ls para obter a identidade da tarefa de consulta agendada:

    bq ls --transfer_config=true --transfer_location=us

    O resultado tem um aspeto semelhante ao seguinte:

    name displayName dataSourceId state
    projects/12345/locations/us/transferConfigs/12345 Monthly snapshots of the TABLE table scheduled_query RUNNING

  2. Usando o identificador no campo name, execute o seguinte comando bq update:

    bq update --transfer_config --update_credentials \
--service_account_name=snapshot-bot@PROJECT.iam.gserviceaccount.com \
projects/12345/locations/us/transferConfigs/12345

O Cloud Shell confirma que a consulta agendada foi atualizada com êxito.

Verifique o seu trabalho

Esta secção descreve como verificar se a sua consulta está agendada corretamente, como ver se ocorreram erros quando a consulta foi executada e como verificar se as capturas de ecrã mensais estão a ser criadas.

Veja a consulta agendada

Para verificar se o BigQuery agendou a consulta de instantâneos da tabela mensalmente, siga estes passos:

Consola

  1. Na Google Cloud consola, aceda à página Consultas agendadas:

    Aceda a Consultas agendadas

  2. Clique em Instantâneos mensais da tabela TABLE.

  3. Clique em Configuração.

  4. Verifique se a string de consulta contém a sua consulta e se a execução da consulta está agendada para o primeiro dia de cada mês.

Veja o histórico de execuções da consulta agendada

Depois de a consulta agendada ter sido executada, pode ver se foi executada com êxito da seguinte forma:

Consola

  1. Na Google Cloud consola, aceda à página Consultas agendadas:

    Aceda a Consultas agendadas

  2. Clique na descrição da consulta, Instantâneos mensais da tabela TABLE.

  3. Clique em Histórico de execuções.

Pode ver a data e a hora em que a consulta foi executada, se a execução foi bem-sucedida e, caso contrário, que erros ocorreram. Para ver mais detalhes sobre uma execução específica, clique na respetiva linha na tabela Histórico de execuções. O painel Detalhes da execução apresenta detalhes adicionais.

Veja os resumos das tabelas

Para verificar se as imagens instantâneas das tabelas estão a ser criadas, siga estes passos:

Consola

  1. Na Google Cloud consola, aceda à página BigQuery:

    Aceda ao BigQuery

  2. No painel Explorador, abra o conjunto de dados BACKUP e verifique se foram criadas as capturas de ecrã TABLE_YYYYMMDD, onde YYYYMMDD é o primeiro dia de cada mês.

    Por exemplo:

    • TABLE_20220601
    • TABLE_20220701
    • TABLE_20220801

O que se segue?