Generador de upsert

Upsert en ClickHouse: INSERT into ReplacingMergeTree + FINAL

Nombra tu tabla, clave y columnas y obtén la sentencia que ClickHouse acepta de verdad, con una guarda para que una fila antigua nunca sobrescriba una nueva. Debajo: cómo funciona la mecánica, qué se rompe y cómo Datrise carga ClickHouse de forma incremental.

Las notas técnicas de esta página están en inglés.

Genera la sentencia

Sentencia · INSERT into ReplacingMergeTree + FINAL

-- Declare the target once; the ORDER BY key is the dedupe key and
-- the version column decides which duplicate survives a merge:
--   ENGINE = ReplacingMergeTree(updated_at) ORDER BY (id)

INSERT INTO deals (id, name, stage, amount, owner_id, updated_at)
SELECT id, name, stage, amount, owner_id, updated_at
FROM deals_staging;

-- Reads must ask for the collapsed view; merges are asynchronous.
SELECT id, name, stage, amount, owner_id, updated_at
FROM deals FINAL;

Cómo funciona el upsert en ClickHouse

ClickHouse does not upsert on write. The idiom is a ReplacingMergeTree table: you INSERT every version of a row, and background merges later collapse duplicates that share the ORDER BY key, keeping the one with the highest version column (updated_at in the generated statement). Until a merge runs, both versions exist, so reads use FINAL or argMax to see the current one.

That trade is what makes ClickHouse fast at ingest: inserts are append-only and cheap, and deduplication is deferred. Insert in large batches, thousands of rows at a time or with async_insert enabled, because each INSERT creates a data part and a table with too many parts throttles writes. Partition by month and order by (key, updated_at) so range scans and merges stay local.

Antes de ejecutarla

There is no UPDATE-on-insert: ReplacingMergeTree keeps every version until a background merge collapses duplicates by ORDER BY key, keeping the row with the highest version column.
Query with FINAL (or GROUP BY key with argMax) to see one row per key before merges catch up. OPTIMIZE TABLE … FINAL forces a merge but rewrites the whole part; do not run it per batch.
Insert in large batches (thousands of rows, or async_insert=1). Each small INSERT creates a part, and too many parts throttles the table.

Preguntas frecuentes

Why do I still see two rows for one key?

Merges are asynchronous. Query with FINAL, or GROUP BY key and take argMax(column, updated_at), until the background merge collapses them.

Should I run OPTIMIZE TABLE … FINAL after each load?

No. It forces a full merge of every part and is expensive on large tables. Let merges run in the background and read with FINAL.

How do deletes work?

ReplacingMergeTree accepts an is_deleted column (ClickHouse 23.2+): insert the row with is_deleted = 1 and a newer version, and merges drop it. Lightweight DELETE also works but marks rows rather than removing them immediately.

El mismo generador para otros destinos

Sáltate escribir el merge

Datrise aterriza entidades de CRM y SaaS en ClickHouse con esta misma mecánica, una marca de agua sobre updated-at y columnas tipadas, así que la sentencia de arriba es la que corre por ti. Únete a la lista de espera para acceso anticipado.

Explorar el catálogo de integraciones