Gerador de upsert

Upsert no Google BigQuery: MERGE with partition-pruned target

Nomeie tabela, chave e colunas e receba a instrução que o Google BigQuery 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 Google BigQuery de forma incremental.

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

Gere a instrução

Instrução · MERGE with partition-pruned target

MERGE `deals` AS t
USING (
  SELECT id, name, stage, amount, owner_id, updated_at
  FROM `deals_staging`
  QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1
) AS s
ON t.id = s.id
  -- AND t._PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
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 Google BigQuery

BigQuery has no upsert keyword; MERGE is the mechanism, and the pricing makes one detail non-negotiable. A MERGE bills the bytes it scans in the target, and without a constant partition filter in the ON clause it scans everything. The generated statement carries that filter as a comment for you to set, dedupes the staging rows with QUALIFY, and skips stale rows via the watermark predicate on the MATCHED branch.

Partition the target by ingestion or event date and cluster it on the key columns, so the matched rows sit in a handful of blocks. Load the staging table with a batch load job rather than streaming, because rows still in the streaming buffer cannot be modified by DML and the MERGE will skip or fail on them.

Antes de executar

A MERGE scans the whole target unless the ON clause carries a constant partition filter (a literal or query parameter, not a subquery). Uncomment the partition line and set the window your late data actually needs.
Rows still in the streaming buffer (recently streamed via the legacy insertAll API) cannot be modified by DML; batch load the staging table or wait for the buffer to flush.
Cluster the target on the key columns so the matched rows sit in few blocks; BigQuery bills the bytes it reads to find them.

Perguntas frequentes

Does a MERGE scan the whole table?

Yes, unless the ON clause has a partition filter with a literal or query parameter. A subquery does not prune. Set the window to how late your source data can arrive.

Why did rows in the staging table not update?

They were streamed and still sit in the streaming buffer, which DML cannot touch for up to 90 minutes. Use load jobs for staging data.

Is there a DML quota to worry about?

The old 1,000 DML statements per table per day limit was removed in 2020. Concurrent mutating statements on one table are queued rather than rejected, so schedule merges per entity, not per row.

O mesmo gerador para outros destinos

Pule a escrita do merge

A Datrise entrega entidades de CRM e SaaS no Google BigQuery 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