Gerador de upsert

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

Nomeie tabela, chave e colunas e receba a instrução que o Amazon Athena realmente aceita, com uma guarda para que uma linha antiga nunca sobrescreva uma nova. Abaixo: como a mecânica funciona, o que quebra e como a Datrise carrega o Amazon Athena de forma incremental.

As notas técnicas desta página estão em inglês.

Gere a instrução

Instrução · 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)

Como o upsert funciona no 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 executar

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.

Perguntas frequentes

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.

O mesmo gerador para outros destinos

Pule a escrita do merge

A Datrise entrega entidades de CRM e SaaS no Amazon Athena com exatamente esta mecânica, uma marca d'água sobre updated-at e colunas tipadas, então a instrução acima é a que roda por você. Entre na lista de espera para acesso antecipado.

Explorar o catálogo de integrações