Fazer streaming de atualizações da tabela com captura de dados alterados

A captura de dados alterados (CDC) do BigQuery atualiza as tabelas do BigQuery processando e aplicando as alterações transmitidas aos dados atuais. Essa sincronização é feita por meio de comando upsert e de exclusão de operações de linha transmitidas em tempo real pela API BigQuery Storage Write, que você já deve conhecer antes de continuar.

Antes de começar

Conceda papéis do Identity and Access Management (IAM) que dão aos usuários as permissões necessárias para executar cada tarefa neste documento e garantir que seu fluxo de trabalho atenda a cada pré-requisito.

Permissões necessárias

Para receber a permissão necessária para usar a API Storage Write, peça ao administrador para conceder a você o papel do IAM de Editor de dados do BigQuery (roles/bigquery.dataEditor). Para mais informações sobre a concessão de papéis, consulte Gerenciar o acesso a projetos, pastas e organizações.

Esse papel predefinido contém a permissão bigquery.tables.updateData, que é necessária para usar a API Storage Write.

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

Para mais informações sobre os papéis e as permissões do IAM no BigQuery, consulte Introdução ao IAM.

Pré-requisitos

Para usar o CDC do BigQuery, seu fluxo de trabalho precisa atender às seguintes condições:

  • Use a API Storage Write no stream padrão.
  • É necessário declarar as chaves primárias para a tabela de destino no BigQuery. Há compatibilidade com chaves primárias compostas que contêm até 16 colunas.
  • Os recursos de computação suficientes do BigQuery precisam estar disponíveis para executar as operações de linha do CDC. Se as operações de modificação de linha do CDC falharem, poderá reter acidentalmente dados que você pretende excluir. Para mais informações, consulte Considerações sobre dados excluídos.

Especificar alterações nos registros atuais

No CDC do BigQuery, a pseudocoluna _CHANGE_TYPE indica o tipo de mudança a ser processada para cada linha. Para usar o CDC, defina _CHANGE_TYPE ao fazer streaming de modificações de linha usando a API Storage Write. A pseudocoluna _CHANGE_TYPE aceita apenas os valores UPSERT e DELETE. Uma tabela é considerada ativada para CDC, enquanto a API Storage Write está transmitindo modificações de linha para a tabela dessa maneira.

Exemplo com valores UPSERT e DELETE

Considere a seguinte tabela no BigQuery:

ID Nome Salário
100 Charlie 2000
101 Tal 3000
102 Lee 5000

As seguintes modificações de linha são transmitidas pela API Storage Write:

ID Nome Salário _CHANGE_TYPE
100 EXCLUIR
101 Tal 8.000 UPSERT
105 Izumi 6.000 UPSERT

A tabela atualizada agora é a seguinte:

ID Nome Salário
101 Tal 8.000
102 Lee 5000
105 Izumi 6.000

Gerenciar inatividade da tabela

Por padrão, sempre que você executa uma consulta, o BigQuery retorna os resultados mais atualizados. Para fornecer os resultados mais recentes ao consultar uma tabela ativada para CDC, o BigQuery precisa aplicar cada modificação de linha transmitida até o horário de início da consulta para que a versão mais atualizada da tabela seja consultada. A aplicação dessas modificações de linha no ambiente de execução aumenta a latência e o custo da consulta. No entanto, se você não precisar de resultados de consulta totalmente atualizados, poderá reduzir o custo e a latência nas consultas definindo a opção max_staleness na tabela. Quando essa opção é definida, o BigQuery aplica modificações de linha pelo menos uma vez no intervalo definido pelo valor max_staleness, permitindo que você execute consultas sem esperar que as atualizações sejam aplicadas, ao custo de alguma inatividade dos dados.

Esse comportamento é útil principalmente para painéis e relatórios em que a atualização de dados não é essencial. Isso também é útil para gerenciar custos, porque oferece mais controle sobre a frequência com que o BigQuery aplica modificações de linhas.

Consultar tabelas com a opção max_staleness definida

Quando você consulta uma tabela com a opção max_staleness definida, o BigQuery retorna o resultado com base no valor de max_staleness e no horário em que o último job de aplicação ocorreu, representado pelo carimbo de data/hora upsert_stream_apply_watermark da tabela.

Considere o exemplo a seguir, em que uma tabela tem a opção max_staleness definida como 10 minutos e o job de aplicação mais recente ocorreu em T20:

O tempo de execução da consulta ocorre no intervalo máximo de inatividade dos dados.

Se você consultar a tabela em T25, a versão atual da tabela ficará cinco minutos desatualizada, o que é inferior ao intervalo max_staleness de 10 minutos. Nesse caso, o BigQuery retorna a versão da tabela em T20, ou seja, os dados retornados também estão desatualizados em cinco minutos.

Quando você define a opção max_staleness na tabela, o BigQuery aplica modificações de linha pendentes pelo menos uma vez no intervalo max_staleness. Em alguns casos, o BigQuery pode não concluir o processo de aplicação dessas modificações de linhas pendentes dentro do intervalo.

Por exemplo, se você consultar a tabela em T35 e o processo de aplicação de modificações de linha pendentes não tiver sido concluído, a versão atual da tabela será de 15 minutos desatualizada, o que é maior que o intervalo max_staleness de 10 minutos. Nesse caso, no ambiente de execução da consulta, o BigQuery aplica todas as modificações de linha entre T20 e T35 para a consulta atual, o que significa que os dados consultados estão completamente atualizados, à custa de uma latência adicional de consulta. Isso é considerado um job de combinação de tempo de execução.

O tempo de execução da consulta ocorre fora do intervalo de tempo máximo de inatividade dos dados.

O valor max_staleness de uma tabela geralmente precisa ser o maior dos dois valores a seguir:

  • A inatividade máxima de dados tolerável para seu fluxo de trabalho.
  • O dobro do tempo máximo necessário para aplicar as alterações inseridas na tabela, além de um buffer extra.

Para calcular o tempo necessário para aplicar as alterações inseridas em uma tabela existente, use a seguinte consulta SQL para determinar a duração do 95º percentil dos jobs de aplicação em segundo plano, além de um buffer de sete minutos para permitir a Conversão do armazenamento otimizado para gravação (buffer de streaming) do BigQuery.

SELECT
  project_id,
  destination_table.dataset_id,
  destination_table.table_id,
  APPROX_QUANTILES((TIMESTAMP_DIFF(end_time, creation_time,MILLISECOND)/1000), 100)[OFFSET(95)] AS p95_background_apply_duration_in_seconds,
  CEILING(APPROX_QUANTILES((TIMESTAMP_DIFF(end_time, creation_time,MILLISECOND)/1000), 100)[OFFSET(95)]*2/60)+7 AS recommended_max_staleness_with_buffer_in_minutes
FROM `region-us`.INFORMATION_SCHEMA.JOBS AS job
WHERE
  project_id = 'PROJECT_ID'
  AND DATE(creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE()
  AND job_id LIKE "%cdc_background%"
GROUP BY 1,2,3;

Substitua PROJECT_ID pelo ID do projeto que contém as tabelas do BigQuery que estão sendo modificadas pelo CDC do BigQuery.

A duração dos jobs de aplicação em segundo plano é afetada por vários fatores, incluindo o número e a complexidade das operações de CDC emitidas no intervalo de inatividade, o tamanho da tabela e a disponibilidade de recursos do BigQuery. Para mais informações sobre a disponibilidade de recursos, consulte Dimensionar e monitorar reservas BACKGROUND.

Criar uma tabela com a opção max_staleness

Para criar uma tabela com a opção max_staleness, use a instrução CREATE TABLE. No exemplo a seguir, criamos a tabela employees com um limite max_staleness de 10 minutos:

CREATE TABLE employees (
  id INT64 PRIMARY KEY NOT ENFORCED,
  name STRING)
  CLUSTER BY
    id
  OPTIONS (
    max_staleness = INTERVAL 10 MINUTE);

Modificar a opção max_staleness para uma tabela existente

Para adicionar ou modificar um limite max_staleness em uma tabela, use a instrução ALTER TABLE. No exemplo a seguir, o limite max_staleness da tabela employees foi alterado para 15 minutos:

ALTER TABLE employees
SET OPTIONS (
  max_staleness = INTERVAL 15 MINUTE);

Determinar o valor atual de max_staleness de uma tabela.

Para determinar o valor max_staleness atual de uma tabela, consulte a visualização INFORMATION_SCHEMA.TABLE_OPTIONS. O exemplo a seguir verifica o valor atual de max_staleness da tabela mytable:

SELECT
  option_name,
  option_value
FROM
  DATASET_NAME.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE
  option_name = 'max_staleness'
  AND table_name = 'TABLE_NAME';

Substitua:

  • DATASET_NAME: o nome do conjunto de dados em que a tabela ativada para CDC reside.
  • TABLE_NAME: o nome da tabela ativada por CDC.

Os resultados mostram que o valor max_staleness é de 10 minutos:

+---------------------+--------------+
| Row |  option_name  | option_value |
+---------------------+--------------+
|  1  | max_staleness | 0-0 0 0:10:0 |
+---------------------+--------------+

Monitorar o progresso da operação de upsert de tabela

Para monitorar o estado de uma tabela e verificar quando as modificações de linha foram aplicadas pela última vez, consulte a visualização INFORMATION_SCHEMA.TABLES para conferir o carimbo de data/hora upsert_stream_apply_watermark.

O exemplo a seguir verifica o valor upsert_stream_apply_watermark da tabela mytable:

SELECT upsert_stream_apply_watermark
FROM DATASET_NAME.INFORMATION_SCHEMA.TABLES
WHERE table_name = 'TABLE_NAME';

Substitua:

  • DATASET_NAME: o nome do conjunto de dados em que a tabela ativada para CDC reside.
  • TABLE_NAME: o nome da tabela ativada por CDC.

O resultado será semelhante ao seguinte:

[{
 "upsert_stream_apply_watermark": "2022-09-15T04:17:19.909Z"
}]

As operações de manutenção são realizadas pela conta de serviço bigquery-adminbot@system.gserviceaccount.com e aparecem no histórico do job do projeto que contém a tabela ativada para CCD.

Gerenciar ordens personalizadas

Ao fazer streaming de inserções no BigQuery, o comportamento padrão de ordenar registros com chaves primárias idênticas é determinado pelo horário do sistema do BigQuery em que o registro foi ingerido no BigQuery. Em outras palavras, o registro ingerido mais recentemente com o carimbo de data/hora mais recente tem precedência sobre o registro ingerido anteriormente com um carimbo de data/hora mais antigo. Isso pode não ser suficiente para determinados casos de uso, como nos casos em que inserções muito frequentes ocorrem na mesma chave primária em um período muito curto ou em que a ordem de inserção não é garantida. Para esses cenários, uma chave de pedido fornecida pelo usuário pode ser necessária.

Para configurar chaves de ordenação fornecidas pelo usuário, a pseudocoluna _CHANGE_SEQUENCE_NUMBER é usada para indicar a ordem em que o BigQuery aplica os registros, com base no _CHANGE_SEQUENCE_NUMBER maior entre dois registros correspondentes. com a mesma chave primária. A pseudocoluna _CHANGE_SEQUENCE_NUMBER é uma coluna opcional e aceita apenas valores em um formato fixo STRING.

Formato _CHANGE_SEQUENCE_NUMBER

A pseudocoluna _CHANGE_SEQUENCE_NUMBER aceita apenas valores STRING, escritos em formato fixo. Esse formato fixo usa valores STRING escritos em hexadecimal, separados em seções por uma barra /. Cada seção pode ser expressa em, no máximo, 16 caracteres hexadecimais, e até quatro seções são permitidas por _CHANGE_SEQUENCE_NUMBER. O intervalo permitido do _CHANGE_SEQUENCE_NUMBER aceita valores entre 0/0/0/0 e FFFFFFFFFFFFFFFF/FFFFFFFFFFFFFFFF/FFFFFFFFFFFFFFFF/FFFFFFFFFFFFFFFF. Os valores _CHANGE_SEQUENCE_NUMBER aceitam caracteres maiúsculos e minúsculos.

Expressar chaves de ordem básicas pode ser feito usando uma única seção. Por exemplo, para ordenar chaves apenas com base no carimbo de data/hora de processamento de um registro de um servidor de aplicativos, use uma seção: '2024-04-30 11:19:44 UTC', expressa como hexadecimal convertendo o carimbo de data/hora nos milissegundos do Epoch, '18F2EBB6480' neste caso. A lógica para converter dados em hexadecimal é responsabilidade do cliente que emite a gravação no BigQuery usando a API Storage Write.

Aceitar várias seções permite combinar diversos valores lógicos de processamento em uma chave para casos de uso mais complexos. Por exemplo, para ordenar chaves com base no carimbo de data/hora de processamento de um registro de um servidor de aplicativos, um número de sequência de registro e o status do registro, use três seções: '2024-04-30 11:19:44 UTC' / '123' / 'complete', cada uma expressa como hexadecimais. A ordem das seções é uma consideração importante para classificar sua lógica de processamento. O BigQuery compara os valores de _CHANGE_SEQUENCE_NUMBER comparando a primeira seção e, em seguida, a próxima seção somente se as seções anteriores forem iguais.

O BigQuery usa a _CHANGE_SEQUENCE_NUMBER para fazer a ordenação comparando dois ou mais campos _CHANGE_SEQUENCE_NUMBER como valores numéricos não assinados. Considere os seguintes exemplos de comparação de _CHANGE_SEQUENCE_NUMBER e os resultados de precedência:

  • Exemplo 1:

    • Registro 1: _CHANGE_SEQUENCE_NUMBER = '77'
    • Registro 2: _CHANGE_SEQUENCE_NUMBER = '7B'

    Resultado: o registro 2 é considerado o mais recente porque "7B" > "77" (ou seja, "123" > "119")

  • Exemplo 2:

    • Registro 1: _CHANGE_SEQUENCE_NUMBER = 'FFF/B'
    • Registro 2: _CHANGE_SEQUENCE_NUMBER = 'FFF/ABC'

    Resultado: o registro 2 é considerado o mais recente porque "FFF/ABC" > "FFF/B" (ou seja, "4095/2748" > "4095/11")

  • Exemplo 3:

    • Registro 1: _CHANGE_SEQUENCE_NUMBER = 'BA/FFFFFFFF'
    • Registro 2: _CHANGE_SEQUENCE_NUMBER = 'ABC'

    Resultado: o registro 2 é considerado o mais recente porque "ABC" > "BA/FFFFFFFF" (ou seja, "2748" > "186/4294967295")

  • Exemplo 4:

    • Registro 1: _CHANGE_SEQUENCE_NUMBER = 'FFF/ABC'
    • Registro 2: _CHANGE_SEQUENCE_NUMBER = 'ABC'

    Resultado: o registro 1 é considerado o mais recente porque "FFF/ABC" > "ABC" (ou seja, "4095/2748" > "2748")

Se dois valores _CHANGE_SEQUENCE_NUMBER forem idênticos, o registro com o tempo de ingestão mais recente do sistema BigQuery terá precedência sobre os registros ingeridos anteriormente.

Configurar uma reserva do BigQuery para uso com o CDC

Use as reservas do BigQuery para alocar recursos de computação dedicados do BigQuery para operações de modificação de linhas do CDC. As reservas permitem que você defina um limite para o custo de executar essas operações. Essa abordagem é útil principalmente para fluxos de trabalho com operações frequentes do CDC em tabelas grandes, que de outra forma teriam altos custos sob demanda devido ao grande número de bytes processados ao realizar cada operação.

Os jobs de CDC do BigQuery que aplicam modificações de linha pendentes no intervalo max_staleness são considerados jobs em segundo plano e aproveitam o tipo de atribuição BACKGROUND, em vez do tipo de atribuição QUERY. Por outro lado, as consultas fora do intervalo max_staleness que exigem que as modificações de linha sejam aplicadas no tempo de execução da consulta usam o tipo de atribuição QUERY. Os jobs em segundo plano da CDC do BigQuery realizados sem uma atribuição BACKGROUND aproveitam os preços sob demanda. Essa consideração é importante ao projetar sua estratégia de gerenciamento de carga de trabalho para o CDC do BigQuery.

Para configurar uma reserva do BigQuery para uso com o CDC, comece comprando um compromisso de capacidade e configurando uma reserva na região em que as tabelas do BigQuery estão localizadas. Para orientações sobre o tamanho da reserva, consulte Dimensionar e monitorar reservas BACKGROUND. Depois de criar uma reserva, atribua o projeto do BigQuery à reserva e defina a opção job_type como BACKGROUND executando a seguinte:instrução CREATE ASSIGNMENT:

CREATE ASSIGNMENT
  `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID`
OPTIONS (
  assignee = 'projects/PROJECT_ID',
  job_type = 'BACKGROUND');

Substitua:

  • ADMIN_PROJECT_ID: o ID do projeto de administração que contém a reserva.
  • LOCATION: o local da reserva.
  • RESERVATION_NAME: o nome da reserva.
  • ASSIGNMENT_ID: o ID da atribuição. Ele precisa ser exclusivo do projeto e do local, começar e terminar com uma letra minúscula ou um número e conter apenas letras minúsculas, números e traços.
  • PROJECT_ID: o ID do projeto que contém as tabelas do BigQuery que estão sendo modificadas pelo CDC do BigQuery. Este projeto está atribuído à reserva.

Dimensionar e monitorar reservas BACKGROUND

As reservas determinam a quantidade de recursos de computação disponíveis para executar as operações de computação do BigQuery. O subdimensionamento de uma reserva pode aumentar o tempo de processamento das operações de modificação de linha do CDC. Para dimensionar uma reserva com precisão, monitore o consumo histórico de slots do projeto que executa as operações do CDC consultando a visualização INFORMATION_SCHEMA.JOBS_TIMELINE:

SELECT
  period_start,
  SUM(period_slot_ms) / (1000 * 60) AS slots_used
FROM
  REGION.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
WHERE
  DATE(job_creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND CURRENT_DATE()
  AND job_id LIKE '%cdc_background%'
GROUP BY
  period_start
ORDER BY
  period_start DESC;

Substitua REGION pelo nome da região em que o projeto está localizado. Por exemplo, region-us.

Considerações sobre dados excluídos

  • As operações de CDC do BigQuery aproveitam os recursos de computação do BigQuery. Se as operações do CDC estiverem configuradas para usar o faturamento sob demanda, elas serão executadas regularmente usando recursos internos do BigQuery. Se as operações do CDC estiverem configuradas com uma reserva BACKGROUND, elas estarão sujeitas à disponibilidade de recursos da reserva configurada. Se não houver recursos suficientes disponíveis na reserva configurada, o processamento de operações do CDC, incluindo a exclusão, poderá levar mais tempo do que o previsto.
  • Uma operação DELETE do CDC é considerada aplicada somente quando o carimbo de data/hora upsert_stream_apply_watermark passa o carimbo de data/hora em que a API Storage Write fez o streaming da operação. Para mais informações sobre o carimbo de data/hora upsert_stream_apply_watermark, consulte Monitorar o progresso da operação de upsert de tabela.
  • Para aplicar operações DELETE CDC que chegam fora de ordem, o BigQuery mantém uma janela de retenção de exclusões de dois dias. As operações da tabela DELETE são armazenadas para esse período antes do início do processo padrão de exclusão de dados do Google Cloud. As operações DELETE na janela de retenção de exclusão usam os preços de armazenamento do BigQuery padrão.

Limitações

  • O CDC do BigQuery não realiza a aplicação de chaves, por isso é essencial que as chaves primárias sejam exclusivas.
  • As chaves primárias não podem ultrapassar 16 colunas.
  • As tabelas com CDC ativadas não podem ter mais de 2.000 colunas de nível superior definidas por o esquema da tabela.
  • As tabelas ativadas para CDC não são compatíveis com:
  • As tabelas ativadas por CDC que executam jobs de mesclagem de tempo de execução porque o valor max_staleness da tabela é muito baixo não são compatíveis com o seguinte:
  • As operações de exportação do BigQuery em tabelas ativadas por CDC não exportam modificações de linhas transmitidas recentemente que ainda não foram aplicadas por um job em segundo plano. Para exportar a tabela completa, use uma instrução EXPORT DATA.
  • Se a consulta acionar uma mesclagem de ambiente de execução em uma tabela particionada, a tabela inteira será verificada, independentemente de a consulta estar restrita a um subconjunto das partições.
  • Se você usaEdição Standard, reservas BACKGROUND não estão disponíveis, portanto, aplicar modificações de linha pendentes usa omodelo de preços sob demanda. No entanto, é possível consultar tabelas ativadas para CDC, independentemente da sua edição.
  • As pseudocolunas _CHANGE_TYPE e _CHANGE_SEQUENCE_NUMBER não podem ser consultadas ao executar uma leitura de tabela.
  • Não é possível misturar linhas que têm valores UPSERT ou DELETE para _CHANGE_TYPE com linhas que têm INSERT ou valores não especificados para _CHANGE_TYPE na mesma conexão. Isso resulta no seguinte erro de validação: The given value is not a valid CHANGE_TYPE.

Preços do CDC do BigQuery

O CDC do BigQuery usa a API Storage Write para ingestão de dados, armazenamento do BigQuery para armazenamento de dados e computação do BigQuery para operações de modificação de linhas, e todas geram custos. Para mais informações sobre preços, consulte Preços do BigQuery.

Estimar custos de CDC do BigQuery

Além de práticas recomendadas gerais de estimativa de custos do BigQuery estimar os custos do CDC do BigQuery pode ser importante para fluxos de trabalho com grandes quantidades de dados, uma baixa configuração max_staleness ou dados que mudam com frequência.

Os preços de ingestão de dados do BigQuery e de armazenamento do BigQuery são calculados diretamente com base na quantidade de dados ingeridos e armazenados, incluindo pseudocolunas. No entanto, os preços de computação do BigQuery podem ser mais difíceis de estimar, porque estão relacionados ao consumo de recursos de computação usados para executar jobs de CDC do BigQuery.

Os jobs do CDC do BigQuery são divididos em três categorias:

  • Jobs de aplicação em segundo plano: jobs executados em segundo plano em intervalos regulares definidos pelo valor max_staleness da tabela. Esses jobs aplicam modificações de linha transmitidas recentemente à tabela ativada por CDC.
  • Jobs de consulta: consultas do GoogleSQL executadas na janela max_staleness e lidas somente na tabela de valor de referência do CDC.
  • Jobs de mesclagem de tempo de execução: jobs acionados por consultas ad hoc do GoogleSQL executadas fora da janela max_staleness. Esses jobs precisam executar uma mesclagem instantânea da tabela de valor de referência do CDC e das modificações de linha transmitidas recentemente no ambiente de execução da consulta.

Os três tipos de jobs de CDC do BigQuery usam o clustering do BigQuery, mas apenas os jobs de consulta aproveitam o particionamento do BigQuery. Os jobs de aplicação em segundo plano e os de mesclagem de ambiente de execução não podem usar o particionamento porque, ao aplicar as modificações de linha transmitidas recentemente, não há garantia a qual partição de tabela as entradas transmitidas recentemente são aplicadas. Em outras palavras, a tabela de valor de referência completa é lida durante jobs de aplicação em segundo plano e de mesclagem de tempo de execução. Entender a quantidade de dados que está sendo lida para realizar operações de CDC é útil para estimar o custo total.

Se a quantidade de dados lidos do valor de referência da tabela for alta, use o modelo de preço de capacidade do BigQuery, que não se baseia na quantidade de dados processados.

Práticas recomendadas de custo do CDC do BigQuery

Além das práticas recomendadas gerais de custo do BigQuery, use as técnicas a seguir para otimizar os custos das operações de CDC do BigQuery:

A seguir