Generador de upsert

Upsert en Amazon Athena: MERGE INTO (Iceberg table, engine v3)

Nombra tu tabla, clave y columnas y obtén la sentencia que Amazon Athena 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 Amazon Athena de forma incremental.

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

Genera la sentencia

Sentencia · MERGE INTO (Iceberg table, engine v3)

-- Target must be an Iceberg table (TBLPROPERTIES ('table_type'='ICEBERG')).
MERGE INTO deals AS t
USING (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rn
    FROM deals_staging
  ) WHERE rn = 1
) AS s
ON t.id = s.id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET
  name = s.name,
  stage = s.stage,
  amount = s.amount,
  owner_id = s.owner_id,
  updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (id, name, stage, amount, owner_id, updated_at)
  VALUES (s.id, s.name, s.stage, s.amount, s.owner_id, s.updated_at)

Cómo funciona el upsert en Amazon Athena

Athena can MERGE, but only into Apache Iceberg tables on engine version 3. Classic Hive-style tables over Parquet are append-only: INSERT INTO adds files and nothing edits a row. So the first decision is the table format. With Iceberg, the generated MERGE INTO joins a deduped staging select to the target and updates or inserts per key; with Hive tables, append a partition and dedupe at read time instead.

Athena bills per byte scanned, and a MERGE scans the target to find matches, so a partition predicate in the ON clause matters as much as it does in BigQuery. Every merge writes new small files; schedule OPTIMIZE … REWRITE DATA USING BIN_PACK to compact them and VACUUM to expire old snapshots, or scan cost creeps up batch by batch.

Antes de ejecutarla

Plain Hive/Parquet tables in Athena are append-only: MERGE, UPDATE and DELETE exist only for Iceberg tables on engine version 3. Convert with CREATE TABLE … TBLPROPERTIES ('table_type'='ICEBERG') AS SELECT.
Athena bills per byte scanned, so put a partition predicate on the target inside the ON clause; otherwise every MERGE reads the whole table.
Merges create small files. Schedule OPTIMIZE table REWRITE DATA USING BIN_PACK and VACUUM to compact and expire old snapshots.

Preguntas frecuentes

Why does Athena reject my MERGE?

The target is not an Iceberg table, or the workgroup runs engine version 2. Create the table with TBLPROPERTIES ('table_type' = 'ICEBERG') and switch the workgroup to engine version 3.

Can I convert an existing Parquet table to Iceberg?

Yes: CREATE TABLE new_table WITH (table_type = 'ICEBERG', location = '…') AS SELECT * FROM old_table, then point consumers at the new name.

How often should I compact?

After every few merges on a busy table. OPTIMIZE rewrites small files into larger ones, VACUUM removes snapshots older than the retention you set; both are Athena SQL statements you can schedule.

El mismo generador para otros destinos

Sáltate escribir el merge

Datrise aterriza entidades de CRM y SaaS en Amazon Athena 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