Transmite actualizaciones de tablas con captura de datos modificados

La captura de datos modificados (CDC) de BigQuery actualiza las tablas de BigQuery mediante el procesamiento y la aplicación de cambios transmitidos a los datos existentes. Esta sincronización se realiza a través de las operaciones de inserción y actualización y de eliminación de filas que se transmiten en tiempo real a través de la API de BigQuery Storage Write, con la que debes estar familiarizado antes de continuar.

Antes de comenzar

Otorga roles de Identity and Access Management (IAM) que les den a los usuarios los permisos necesarios para realizar cada tarea de este documento y asegúrate de que tu flujo de trabajo cumpla con cada requisito.

Permisos necesarios

Para obtener el permiso que necesitas para usar la API de Storage Write, pídele a tu administrador que te otorgue el rol de IAM de editor de datos de BigQuery (roles/bigquery.dataEditor) Para obtener más información sobre cómo otorgar roles, consulta Administra el acceso.

Este rol predefinido contiene el permiso bigquery.tables.updateData, que se requiere para usar la API de Storage Write.

También puedes obtener este permiso con roles personalizados o con otros roles predefinidos.

Para obtener más información sobre los roles y permisos de IAM en BigQuery, consulta Introducción a IAM.

Requisitos previos

Para usar la CDC de BigQuery, tu flujo de trabajo debe cumplir con las siguientes condiciones:

  • Debes usar la API de Storage Write en la transmisión predeterminada.
  • Debes declarar claves primarias para la tabla de destino en BigQuery. Se admiten claves primarias compuestas que contengan hasta 16 columnas.
  • Los recursos de procesamiento de BigQuery suficientes deben estar disponibles para realizar las operaciones de fila de la CDC. Ten en cuenta que, si las operaciones de modificación de filas de la CDC fallan, podrías retener de forma involuntaria los datos que deseas borrar. Para obtener más información, consulta Consideraciones sobre datos borrados.

Especificar cambios en los registros existentes

En la CDC de BigQuery, la seudocolumna _CHANGE_TYPE indica el tipo de cambio que se procesará para cada fila. Para usar la CDC, configura _CHANGE_TYPE cuando transmitas modificaciones de filas mediante la API de Storage Write. La seudocolumna _CHANGE_TYPE solo acepta los valores UPSERT y DELETE. Una tabla se considera habilitada por los CDC mientras la API de Storage Write son transmisiones de modificaciones de las filas de la tabla de esta manera.

Ejemplo con valores UPSERT y DELETE

Considera la siguiente tabla en BigQuery:

ID Nombre Salario
100 Factura 2000
101 Lucy 3000
102 Ethan 5000

La API de Storage Write transmite las siguientes modificaciones de filas:

ID Nombre Salario _CHANGE_TYPE
100 BORRAR
101 Lucy 8,000 UPSERT
105 Máx. 6000 UPSERT

La tabla actualizada ahora es la siguiente:

ID Nombre Salario
101 Lucy 8,000
102 Ethan 5000
105 Máx. 6000

Administra la obsolescencia de la tabla

De forma predeterminada, cada vez que ejecutas una consulta, BigQuery muestra los resultados más actualizados. A fin de proporcionar los resultados más recientes cuando se consulta una tabla habilitada para la CDC, BigQuery debe aplicar cada modificación de fila transmitida hasta la hora de inicio de la consulta, de modo que se consulte la versión más reciente de la tabla. Aplicar estas modificaciones de filas al momento de ejecutar la consulta aumenta la latencia y el costo de la consulta. Sin embargo, si no necesitas resultados de consulta completamente actualizados, puedes reducir el costo y la latencia en tus consultas mediante la configuración de la opción max_staleness en la tabla. Cuando se configura esta opción, BigQuery aplica modificaciones de fila al menos una vez dentro del intervalo que define el valor max_staleness, lo que te permite ejecutar consultas sin esperar a que se apliquen las actualizaciones y esto genera cierta obsolescencia de los datos.

Este comportamiento es especialmente útil para los paneles y los informes en los que la actualidad de los datos no es esencial. También es útil para la administración de costos, ya que te brinda más control sobre la frecuencia con la que BigQuery aplica modificaciones de fila.

Consultar tablas con la opción max_staleness configurada

Cuando consultas una tabla con la opción max_staleness establecida, BigQuery muestra el resultado según el valor de max_staleness y la hora en que se produjo el último trabajo de aplicación, que se representa mediante la marca de tiempo upsert_stream_apply_watermark de la tabla.

Considera el siguiente ejemplo, en el que una tabla tiene la opción max_staleness establecida en 10 minutos y el trabajo de aplicación más reciente ocurrió en T20:

El tiempo de ejecución de la consulta se produce dentro del intervalo máximo para la inactividad de los datos.

Si consultas la tabla en T25, la versión actual de la tabla está obsoleta por 5 minutos, lo que es menor que el intervalo de max_staleness de 10 minutos. En este caso, BigQuery muestra la versión de la tabla en T20, lo que significa que los datos que se muestran también están obsoletos 5 minutos.

Cuando configuras la opción max_staleness en tu tabla, BigQuery aplica las modificaciones de filas pendientes al menos una vez dentro del intervalo max_staleness. Sin embargo, en algunos casos, es posible que BigQuery no complete el proceso de aplicación de estas modificaciones de filas pendientes dentro del intervalo.

Por ejemplo, si consultas la tabla en T35 y el proceso de aplicación de modificaciones de filas pendientes no se completó, la versión actual de la tabla estará obsoleta 15 minutos, lo que supera el intervalo de max_staleness de 10 minutos En este caso, en el momento de la ejecución de la consulta, BigQuery aplica todas las modificaciones de filas entre T20 y T35 para la consulta actual, lo que significa que los datos consultados están completamente actualizados, a costa de una latencia adicional en la consulta. Esto se considera un trabajo de combinación del entorno de ejecución.

El tiempo de ejecución de la consulta se produce fuera del intervalo máximo de tiempo de inactividad de los datos.

Por lo general, el valor max_staleness de una tabla debe ser el más alto de los dos valores siguientes:

  • La obsolescencia máxima tolerable de los datos para tu flujo de trabajo.
  • El doble de tiempo que lleva aplicar cambios actualizados en tu tabla, más algún búfer adicional.

Para calcular el tiempo que lleva aplicar cambios actualizados a una tabla existente, usa la siguiente consulta de SQL a fin de determinar la duración del percentil 95 de los trabajos de aplicación en segundo plano, más un búfer de siete minutos para permitir la conversión de almacenamiento optimizado para escritura de BigQuery (búfer de transmisión)

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;

Reemplaza PROJECT_ID por el ID del proyecto que contiene las tablas de BigQuery modificadas por la CDC de BigQuery.

La duración de los trabajos de aplicación en segundo plano se ve afectada por varios factores, como la cantidad y la complejidad de las operaciones de CDC emitidas dentro del intervalo de inactividad, el tamaño de la tabla y la disponibilidad de recursos de BigQuery. Para obtener más información sobre la disponibilidad de los recursos, consulta Ajusta el tamaño y supervisa las reservas de BACKGROUND.

Crea una tabla con la opción max_staleness

Para crear una tabla con la opción max_staleness, usa la declaración CREATE TABLE. En el siguiente ejemplo, se crea la tabla employees con un límite de 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);

Modifica la opción max_staleness para una tabla existente

Para agregar o modificar un límite max_staleness en una tabla existente, usa la declaración ALTER TABLE. En el siguiente ejemplo, se cambia el límite max_staleness de la tabla employees a 15 minutos:

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

Determina el valor actual de max_staleness de una tabla

Para determinar el valor actual de max_staleness de una tabla, consulta la vista INFORMATION_SCHEMA.TABLE_OPTIONS. En el siguiente ejemplo, se verifica el valor actual de max_staleness de la tabla mytable:

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

Reemplaza lo siguiente:

  • DATASET_NAME: el nombre del conjunto de datos en el que reside la tabla habilitada para la CDC.
  • TABLE_NAME: el nombre de la tabla habilitada para la CDC.

Los resultados muestran que el valor de max_staleness es de 10 minutos:

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

Supervisa el progreso de la operación de inserción y actualización de tablas

Para supervisar el estado de una tabla y verificar cuándo se aplicaron las modificaciones más recientes, consulta la vista INFORMATION_SCHEMA.TABLES a fin de obtener la marca de tiempo upsert_stream_apply_watermark.

En el siguiente ejemplo, se verifica el valor upsert_stream_apply_watermark de la tabla mytable:

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

Reemplaza lo siguiente:

  • DATASET_NAME: el nombre del conjunto de datos en el que reside la tabla habilitada para la CDC.
  • TABLE_NAME: el nombre de la tabla habilitada para la CDC.

El resultado es similar al siguiente:

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

La cuenta de servicio bigquery-adminbot@system.gserviceaccount.com realiza operaciones de inserción y actualización y aparecen en el historial de trabajos del proyecto que contiene la tabla habilitada para la CDC.

Configura una reserva de BigQuery para usarla con la CDC

Puedes usar reservas de BigQuery a fin de asignar recursos de procesamiento dedicados de BigQuery para las operaciones de modificación de filas de la CDC. Las reservas te permiten establecer un límite para el costo de realizar estas operaciones. Este enfoque es particularmente útil para flujos de trabajo con operaciones frecuentes de CDC en tablas grandes, que, de lo contrario, tendrían costos de demanda altos debido a la gran cantidad de bytes procesados cuando se realiza cada operación.

Los trabajos de CDC de BigQuery que aplican modificaciones de filas pendientes dentro del intervalo max_staleness se consideran trabajos en segundos plano y aprovechan el tipo de asignación BACKGROUND, en lugar del tipo de asignación QUERY. Por otro lado, las consultas fuera del intervalo max_staleness que requieren que se apliquen modificaciones de filas en el momento de la ejecución de la consulta aprovechan el tipo de asignación QUERY. Los trabajos en segundo plano de la CDC de BigQuery que se realizan sin una asignación BACKGROUND aprovechan los precios a pedido. Esta consideración es importante cuando diseñas tu estrategia de administración de cargas de trabajo para la CDC de BigQuery.

Si deseas configurar una reserva de BigQuery para usarla con la CDC, primero debes comprar un compromiso de capacidad y configurar una reserva en la región en la que se encuentran tus tablas de BigQuery. Para obtener información sobre el tamaño de tu reserva, consulta Cambia el tamaño y supervisa las reservas de BACKGROUND. Una vez que hayas creado una reserva, asigna el proyecto de BigQuery en la reserva y configura la opción job_type como BACKGROUND mediante la ejecución de la siguiente sentencia CREATE ASSIGNMENT:

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

Reemplaza lo siguiente:

  • ADMIN_PROJECT_ID por el ID del proyecto de administración que posee la reserva.
  • LOCATION por la ubicación de la reserva.
  • RESERVATION_NAME por el nombre de la reserva.
  • ASSIGNMENT_ID por el ID de la asignación. El ID debe ser único para el proyecto y la ubicación, debe comenzar y terminar con una letra minúscula o un número, y contener solo letras en minúscula, números y guiones.
  • PROJECT_ID por el ID del proyecto que contiene las tablas de BigQuery que modifica la CDC de BigQuery. Este proyecto está asignado a la reserva.

Ajusta el tamaño y supervisa las reservas de BACKGROUND

Las reservas determinan la cantidad de recursos de procesamiento disponibles para realizar operaciones de procesamiento de BigQuery. Reducir el tamaño de una reserva puede aumentar el tiempo de procesamiento de las operaciones de modificación de filas de la CDC. Para dimensionar con precisión una reserva, supervisa el consumo histórico de ranuras del proyecto que realiza las operaciones de CDC mediante la consulta de la vista 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;

Reemplaza REGION por el nombre de la región en la que se encuentra tu proyecto. Por ejemplo, region-us.

Consideraciones de datos borrados

  • Las operaciones de CDC de BigQuery aprovechan los recursos de procesamiento de BigQuery. Si las operaciones de CDC se configuran para usar la facturación on demand, las operaciones de CDC se realizan de forma periódica mediante recursos internos de BigQuery. Si las operaciones de CDC se configuran con una reserva BACKGROUND, las operaciones de CDC están sujetas a la disponibilidad de recursos de la reserva configurada. Si no hay suficientes recursos disponibles dentro de la reserva configurada, el procesamiento de las operaciones de CDC, incluida la eliminación, puede tardar más de lo previsto.
  • Se considera que una operación DELETE de CDC solo se aplica cuando la marca de tiempo upsert_stream_apply_watermark pasó la marca de tiempo en la que la API de Storage Write transmitió la operación. Una vez que se aplica la operación, comienza el proceso de eliminación de datos estándar de Google Cloud. Para obtener más información sobre la marca de tiempo upsert_stream_apply_watermark, consulta Supervisa el progreso de la operación de inserción y actualización de tablas.

Limitaciones

  • La CDC de BigQuery no realiza la aplicación de claves, por lo que es esencial que tus claves primarias sean únicas.
  • Las claves primarias no pueden superar las 16 columnas.
  • Las tablas habilitadas para CDC no admiten lo siguiente:
  • Las tablas habilitadas para la CDC que realizan trabajos de combinación en el entorno de ejecución porque el valor max_staleness de la tabla es demasiado bajo no pueden admitir lo siguiente:
  • Las operaciones de exportación de BigQuery en las tablas habilitadas para la CDC no exportan las modificaciones de filas transmitidas recientemente que aún no se aplicaron mediante un trabajo en segundo plano. Para exportar la tabla completa, usa una sentencia EXPORT DATA.
  • Si tu consulta activa una combinación de entorno de ejecución en una tabla particionada, toda la tabla se analiza sin importar si la consulta está restringida a un subconjunto de las particiones o no.
  • Si usas la Edición estándar, BACKGROUND las reservas no estarán disponibles, por lo que aplicar modificaciones de filas pendientes usa el modelo de precios según demanda. Sin embargo, puedes consultar tablas habilitadas para CDC, sin importar la edición.

Precios de la CDC de BigQuery

La CDC de BigQuery usa la API de Storage Write para la transferencia de datos, el almacenamiento de BigQuery para el almacenamiento de datos y el procesamiento de BigQuery para las operaciones de modificación de filas, todos los cuales generan costos. Para obtener información sobre los precios, consulta Precios de BigQuery.

Estima los costos de los CDC de BigQuery

Además de las prácticas recomendadas generales de estimación de costos de BigQuery, estimar los costos de los CDC de BigQuery puede ser importante para los flujos de trabajo que tienen grandes cantidades de datos, una configuración de max_staleness baja o datos que cambian con frecuencia.

Los precios de la transferencia de datos de BigQuery y los precios de almacenamiento de BigQuery se calculan directamente según la cantidad de datos que transfieres y almacenas. Sin embargo, los precios de procesamiento de BigQuery pueden ser más difíciles de estimar, ya que se relacionan con el consumo de recursos de procesamiento que se usan para ejecutar trabajos de CDC de BigQuery.

Los trabajos de CDC de BigQuery se dividen en tres categorías:

  • Trabajos de aplicación en segundo plano: trabajos que se ejecutan en segundo plano en intervalos regulares que se definen mediante el valor max_staleness de la tabla. Estos trabajos aplican modificaciones de filas transmitidas recientemente a la tabla habilitada para los CDC.
  • Trabajos de consulta: Consultas de Google SQL que se ejecutan dentro de la ventana max_staleness y solo leen desde la tabla de referencia de los CDC.
  • Trabajos de combinación en entorno de ejecución: trabajos que se activan mediante consultas Google SQL ad hoc que se ejecutan fuera de la ventana max_staleness. Estos trabajos deben realizar una combinación sobre la marcha de la tabla de referencia de CDC y las modificaciones de filas transmitidas recientemente en el entorno de ejecución de la consulta.

Los tres tipos de trabajos de CDC de BigQuery aprovechan el agrupamiento en clústeres de BigQuery, pero solo los trabajos de consulta aprovechan la partición de BigQuery. Los trabajos de aplicación en segundo plano y los trabajos de combinación del entorno de ejecución no se pueden particionar porque, cuando se aplican modificaciones de filas transmitidas recientemente, no hay garantía de a qué partición de tabla se aplican las inserciones y actualizaciones recientemente transmitidas. En otras palabras, la tabla de modelo de referencia completa se lee durante los trabajos de aplicación en segundo plano y los trabajos de combinación del entorno de ejecución. Comprender la cantidad de datos que se leen para realizar operaciones de los CDC es útil a la hora de estimar el costo total.

Si la cantidad de datos que se leen en el modelo de referencia de la tabla es alta, considera usar el modelo de precios de capacidad de BigQuery, que no se basa en la cantidad de datos procesados.

Prácticas recomendadas para los costos de los CDC de BigQuery

Además de las prácticas recomendadas generales sobre los costos de BigQuery, usa las siguientes técnicas para optimizar los costos de las operaciones de CDC de BigQuery:

  • A menos que sea necesario, evita configurar la opción max_staleness de una tabla con un valor muy bajo. El valor max_staleness puede aumentar la aparición de trabajos de aplicación en segundo plano y trabajos de combinación del entorno de ejecución, que son más costosos y lentos que los trabajos de consulta. Para obtener orientación detallada, consulta Valor recomendado de la tabla max_staleness.
  • Considera configurar una reserva de BigQuery para usarla con tablas de CDC. De lo contrario, los trabajos de aplicación en segundo plano y los trabajos de combinación del entorno de ejecución usan precios según demanda, lo que puede ser más costoso debido al procesamiento de datos. Para obtener más información, consulta las reservas de BigQuery y sigue las instrucciones sobre cómo dimensionar y supervisar una reserva BACKGROUND para usar con los CDC de BigQuery.

¿Qué sigue?