Criar snapshots de tabelas com uma consulta programada

Neste documento, descrevemos como criar snapshots mensais de uma tabela usando uma conta de serviço que executa uma consulta DDL programada. O documento mostra o exemplo a seguir:

  1. No projeto PROJECT, crie uma conta de serviço chamada snapshot-bot.
  2. Conceda à conta de serviço snapshot-bot as permissões necessárias para capturar snapshots de tabela da tabela TABLE, que está localizada no DATASET e armazene os snapshots da tabela no conjunto de dados BACKUP.
  3. Grave uma consulta que crie snapshots mensais da tabela TABLE e os coloque no conjunto de dados BACKUP. Como não é possível substituir um snapshot de tabela existente, esses snapshots precisam ter nomes exclusivos. Para isso, a consulta anexa a data atual aos nomes de snapshot da tabela; por exemplo, TABLE_20220521; Os snapshots da tabela expiram após 40 dias.
  4. Programe a conta de serviço snapshot-bot para executar a consulta no primeiro dia de cada mês.

Este documento é destinado a usuários familiarizados com o BigQuery e com os snapshots da tabela do BigQuery.

Permissões e papéis

Esta seção descreve asPermissões de gerenciamento de identidade e acesso (IAM, na sigla em inglês) Você precisa criar uma conta de serviço e agendar uma consulta.papéis predefinidos do IAM que concede essas permissões.

Permissões

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

Permissão Recurso Resource type
iam.serviceAccounts.* PROJECT Projeto

Para programar uma consulta, você precisa desta permissões:

Permissão Recurso Resource type
bigquery.jobs.create PROJECT Projeto

Papéis

Os papéis predefinidos que fornecem as permissões necessárias para trabalhar com uma conta de serviço são os seguintes:

Papel Recurso Resource type
Qualquer um dos seguintes:

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

Os papéis predefinidos do BigQuery que fornecem as permissões necessárias para programar uma consulta são os seguintes:

Papel Recurso Resource type
Qualquer um dos seguintes:

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

Crie a conta de serviço snapshot-bot

Siga estas etapas para criar a conta de serviço snapshot-bot e conceder a ela as permissões necessárias para executar consultas. o projeto PROJECT:

Console

  1. No console do Google Cloud, acesse a página Contas de serviço.

    Acessar 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, digite snapshot-bot.

    3. Clique em Criar e continuar.

  4. Conceda à conta de serviço as permissões necessárias para executar jobs do BigQuery:

    1. Na seção Conceder acesso a essa conta de serviço ao projeto, selecione o papel Usuário do BigQuery.

    2. Clique em Concluído.

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

Para verificar se o BigQuery criou a conta de serviço com as permissões que você especificou, siga estas etapas:

Console

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

  1. No console do Google Cloud, acesse a página Contas de serviço.

    Acessar a página "Contas de serviço"

  2. Selecione o projeto PROJECT.

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

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

Verifique se o BigQuery concedeu à sua conta de serviço a permissão necessária para executar consultas:

  1. No console do Cloud, acesse a página Gerenciar recursos:

    Acessar "Gerenciar recursos"

  2. Clique em PROJECT.

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

  4. Na guia Permissões, expanda o nó Usuário do BigQuery.

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

Conceda permissões à conta de serviço

Nesta seção, descrevemos como conceder à conta de serviço snapshot-bot as permissões necessárias para criar snapshots de tabela da tabela DATASET.TABLE no conjunto de dados BACKUP.

Permissão para tirar snapshots da tabela base

Para conceder à conta de serviço snapshot-bot as permissões necessárias para capturar snapshots da tabela DATASET.TABLE, siga estas etapas:

Console

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

    Ir para o BigQuery

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

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

  4. Selecione a tabela TABLE.

  5. Clique em Compartilhar. O painel Compartilhar é aberto.

  6. Clique em Adicionar conta principal. O painel Conceder acesso é aberto.

  7. Em Novos participantes, insira o endereço de e-mail da conta de serviço: snapshot-bot@PROJECT.iam.gserviceaccount.com.

  8. No menu suspenso Selecionar um papel, selecione o papel Editor de dados do BigQuery.

  9. Clique em Salvar.

  10. No painel Compartilhar, 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. No Console do Google Cloud, ative o Cloud Shell:

    Ativar o Cloud Shell

  2. Digite o 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 vinculação de política foi adicionada.

Permissão para criar tabelas no conjunto de dados de destino

Conceda à conta de serviço snapshot-bot as permissões necessárias para criar snapshots de tabelas no conjunto de dados BACKUP da seguinte maneira:

Console

  1. No console do Google Cloud, acesse a página do BigQuery.

    Ir para o BigQuery

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

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

  4. Clique em Compartilhar conjunto de dados. O painel Permissões do conjunto de dados é aberto.

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

  6. No menu suspenso Selecionar um papel, selecione o papel Proprietário de dados do BigQuery.

  7. Clique em Adicionar.

  8. No painel Permissõ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.

Sua conta de serviço snapshot-bot agora tem os seguintes papéis do IAM para os seguintes recursos:

Papel Recurso Tipo de recurso Finalidade
Editor de dados do BigQuery PROJECT:DATASET.TABLE Tabela Tire snapshots da tabela TABLE.
Proprietário de dados do BigQuery PROJECT:BACKUP Conjunto de dados Crie e exclua snapshots da tabela no conjunto de dados BACKUP.
Usuário do BigQuery PROJECT Projeto Execute a consulta programada que cria os instantâneos da tabela.

Esses papéis fornecem as permissões necessárias para que a conta de serviço snapshot-bot execute consultas que criam snapshots de tabela da tabela DATASET.TABLE e colocam os snapshots de tabela no conjunto de dados BACKUP.

Criar uma consulta de várias instruções

Nesta seção, descrevemos como escrever uma consulta de várias instruções que cria um snapshot de tabela da tabela DATASET.TABLE usando a instrução DDL CREATE SNAPSHOT TABLE. O snapshot é salvo 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;

Programar a consulta mensal

Programe a consulta para ser executada às 5h do primeiro dia de cada mês da seguinte maneira:

bq

  1. No Console do Google Cloud, ative o Cloud Shell:

    Ativar o Cloud Shell

  2. Digite o comando bq query a seguir:

    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. A consulta é programada no BigQuery.

A consulta de várias instruções no comando da ferramenta de linha de comando bq é diferente da consulta executada no console do Google Cloud da seguinte maneira:

  • A consulta da ferramenta de linha de comando bq usa @run_date em vez de current_date(). Em uma consulta programada, o parâmetro @run_date contém a data atual. Mas, em uma consulta interativa, o parâmetro @run_date não é compatível. É possível usar current_date() em vez de @run_date para testar uma consulta interativa antes de programá-la.
  • A consulta da ferramenta de linha de comando bq usa @run_time em vez de current_timestamp() por um motivo semelhante. O parâmetro @run_time não é compatível com consultas interativas, mas current_timestamp() pode ser usado em vez de @run_time para testar a consulta interativa.
  • A consulta da ferramenta de linha de comando bq usa barras e aspas duplas \" em vez de aspas simples ' porque as aspas simples são usadas para incluir a consulta.

Configurar a conta de serviço para executar a consulta programada

A consulta está programada para ser executada usando suas credenciais. Atualize a consulta programada para executar com as credenciais da conta de serviço snapshot-bot da seguinte maneira:

  1. Execute o comando bq ls para receber a identidade do job de consulta programada:

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

    A resposta será semelhante a:

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

  2. Com 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 programada foi atualizada.

Confira suas tarefas

Esta seção descreve como verificar se a consulta está programada corretamente, como conferir se houve algum erro quando a consulta foi executada e como verificar se os snapshots mensais estão sendo criados.

Ver a consulta programada

Para verificar se o BigQuery programou sua consulta de snapshots de tabela mensal, siga estas etapas:

Console

  1. No console do Google Cloud, acesse a página Consultas programadas:

    Ir para Consultas programadas

  2. Clique em Snapshots mensais da tabela TABLE.

  3. Clique em Configuração.

  4. Verifique se a string de consulta contém sua consulta e se ela está programada para ser executada no primeiro dia de cada mês.

ver o histórico de execução da consulta programada;

Após a execução da consulta programada, é possível conferir se ela foi executada com sucesso da seguinte maneira:

Console

  1. No console do Google Cloud, acesse a página Consultas programadas:

    Ir para Consultas programadas

  2. Clique na descrição da consulta, Snapshots mensais da tabela TABLE.

  3. Clique em Histórico de execução.

É possível conferir a data e a hora em que a consulta foi executada, se a execução foi bem-sucedida e, se não, quais erros ocorreram. Para consultar mais detalhes sobre uma execução específica, clique na linha dela na tabela Histórico de execução. O painel Detalhes da execução exibe mais detalhes.

Ver os snapshots da tabela

Para verificar se os snapshots da tabela estão sendo criados, siga estas etapas:

Console

  1. No console do Google Cloud, acesse a página do BigQuery.

    Ir para o BigQuery

  2. No painel Explorer, abra o conjunto de dados BACKUP e verifique se os snapshots TABLE_YYYYMMDD foram criados, em que YYYYMMDD é o primeiro dia de cada mês. .

    Por exemplo:

    • TABLE_20220601
    • TABLE_20220701
    • TABLE_20220801

A seguir