Lograr inserciones atómicas y consistencia entre múltiples tablas en ClickHouse Cloud

Cómo cargar datos de forma atómica y mantener la consistencia entre múltiples tablas en ClickHouse Cloud sin transacciones multisentencia, mediante tablas de staging y operaciones a nivel de partición.

​

Problema

ClickHouse Cloud no admite transacciones de múltiples sentencias en el sentido tradicional de los RDBMS. Esto plantea dos desafíos comunes:

1. Atomicidad de una sola tabla para cargas masivas: Un enfoque habitual consiste en insertar primero en claves parciales temporales, luego copiar los registros a la clave real y eliminar los registros temporales. Este enfoque tiene un rendimiento deficiente, especialmente en el paso de eliminación, que puede consumir más del 90 % del tiempo total de la operación.
2. Consistencia entre varias tablas: Cuando un pipeline carga la Tabla A correctamente pero falla en la Tabla B, la Tabla A ya se confirmó y no puede revertirse. Los analistas que consultan ambas tablas ven datos desincronizados.

​

Antecedentes

ClickHouse garantiza la atomicidad a nivel de una sola inserción y una sola partición: si un INSERT se completa correctamente, todas las filas de ese bloque son visibles; si falla, no se ve ninguna. Sin embargo, no existe ningún mecanismo integrado para hacer commit de los datos de forma atómica entre múltiples inserciones o varias tablas.

Los comandos de manipulación de particiones (MOVE PARTITION TO TABLE, REPLACE PARTITION, ATTACH PARTITION FROM) actúan a nivel de metadatos cuando las tablas de origen y destino comparten la misma política de almacenamiento.

Esto significa que se ejecutan casi instantáneamente, independientemente del tamaño de los datos, lo que los convierte en bloques de construcción ideales para patrones de intercambio atómico.

​

Solución recomendada

En lugar de insertar directamente en las tablas de producción e intentar corregirlo en caso de error, use tablas de staging dedicadas como zona de aterrizaje. Después de validar los datos, use operaciones a nivel de partición para promoverlos de forma atómica a las tablas de producción.

​

Paso a paso para la atomicidad en una sola tabla

1. ​

   Crear una tabla de staging

   Usa el mismo esquema, clave de partición, ORDER BY y política de almacenamiento que la tabla de producción.

   CREATE TABLE my_table_staging AS my_table_prod;
   
2. ​

   Insertar datos en la tabla de staging

   Ejecuta la inserción en la tabla de staging en lugar de hacerlo en la tabla de producción.

   INSERT INTO my_table_staging SELECT ... FROM source;
   
3. ​

   Si la inserción falla, trunca y reintenta

   Trunca la tabla de staging y vuelve a ejecutar la carga. No se ha afectado ningún dato de producción.

   TRUNCATE TABLE my_table_staging;
   
4. ​

   Si la inserción se realiza correctamente, mueve las particiones a producción

   Para mover los datos a producción y eliminarlos de la tabla de staging, usa MOVE PARTITION.

   ALTER TABLE my_table_staging MOVE PARTITION <partition_expr> TO TABLE my_table_prod;
   

   Como alternativa, copia los datos a una partición existente en producción con ATTACH PARTITION.

   ALTER TABLE my_table_prod ATTACH PARTITION tuple() FROM my_table_staging;
   

   Ambas operaciones son cambios de metadatos sobre la misma política de almacenamiento y se completan casi instantáneamente.

5. ​

   Limpiar la tabla de staging

   Una vez que se hayan movido todas las particiones, trunca la tabla de staging para dejarla vacía para la siguiente carga.

   TRUNCATE TABLE my_table_staging;
   

​

Consistencia entre varias tablas

El mismo patrón resuelve canalizaciones en las que dos o más tablas deben estar completamente cargadas antes de que cualquiera de ellas sea visible para los analistas. Cargue los datos de cada tabla en su propia tabla de staging y valídelas todas; a continuación, ejecute a la vez los movimientos de particiones. Como cada movimiento es una operación de metadatos casi instantánea, el intervalo en el que las tablas no coinciden se reduce de la duración de la carga completa al tiempo necesario para intercambiar particiones.

​

Requisitos y restricciones

Para MOVE PARTITION TO TABLE, REPLACE PARTITION y ATTACH PARTITION FROM, las tablas de origen y de destino deben tener:

• La misma estructura de columnas
• La misma clave de partición, la misma clave de ORDER BY y la misma clave primaria
• La misma política de almacenamiento
• La tabla de destino debe incluir todos los índices y las proyecciones de la tabla de origen

​

Ejemplo

Puede ejecutar el siguiente ejemplo de forma interactiva en fiddle:

CREATE TABLE prod
(
  uid Int16,
  name String,
  age Int16
)
ENGINE=MergeTree
ORDER BY ();

CREATE TABLE staging
(
  uid Int16,
  name String,
  age Int16
)
ENGINE=MergeTree
ORDER BY ();

-- Datos iniciales
INSERT INTO prod VALUES (123, 'John', 33);
INSERT INTO prod VALUES (456, 'Ksenia', 48);
-- Cargar datos
INSERT INTO staging VALUES (8811, 'Alice', 50);
INSERT INTO staging VALUES (8812, 'Bob', 23);

-- Validar importación
SELECT 'Staging count:', COUNT() FROM staging;
-- Mover partición
ALTER TABLE staging MOVE PARTITION tuple() TO TABLE prod; -- operación atómica

-- Verificar datos
SELECT 'Prod count:', COUNT() FROM prod;
SELECT * FROM prod;

​

Referencias

• Manipular particiones y partes
• Para obtener más información sobre esta estrategia, consulta la entrada del blog Potencia tus grandes cargas de datos en ClickHouse - Parte 3: cómo hacer resiliente una gran carga de datos.