Amazon Redshift es un potente servicio de almacenamiento de datos analíticos basado en la nube que se ha vuelto esencial para empresas de todos los tamaños. Sin embargo, una limitación de Redshift es su falta de soporte nativo para restricciones, lo que puede dificultar la prevención de duplicados en las tablas. En este artículo, te guiaremos a través de formas de evitar duplicados en tus tablas de Redshift.

Imitando el UPSERT en Amazon Redshift

Realizar una operación UPDATE+INSERT en caso de conflicto es una operación UPSERT. Sin embargo, debido a la falta de soporte de restricciones en Redshift, las operaciones UPSERT pueden ser desafiantes. AWS ha proporcionado una solución para superar este problema utilizando una operación MERGE con tablas temporales o de preparación. Hay dos estrategias para imitar la operación UPSERT en Redshift: Merge por filas y Merge por columnas.

Estrategia 1 – Merge por filas

Esta estrategia es adecuada cuando necesitas actualizar todas las columnas de los registros coincidentes. Los pasos involucrados en esta estrategia son:

1. Crea una tabla de preparación que contenga los registros entrantes.
2. Elimina las filas en la tabla objetivo que necesitan ser actualizadas escaneando la tabla objetivo una vez (todas las filas coincidentes).
3. Fusiona todo el contenido de la tabla de preparación en la tabla objetivo.
4. Elimina la tabla de preparación para marcar la finalización.

Sin embargo, esta estrategia tiene algunas desventajas. No se puede utilizar para tablas grandes, ya que implica purgar y reemplazar todas las columnas de las filas coincidentes. Además, se produce una operación de eliminación masiva como parte de la operación Merge, y se debe realizar una operación de Vacuum obligatoria al finalizar.

Estrategia 2 – Merge por columnas

Esta estrategia es adecuada cuando solo necesitas actualizar ciertas columnas de los registros coincidentes. Los pasos involucrados en esta estrategia son:

1. Crea una tabla de preparación que contenga los registros entrantes.
2. Actualiza las columnas de los registros coincidentes en la tabla objetivo.
3. Elimina los registros actualizados de la tabla de preparación.
4. Fusiona todo el contenido de los registros restantes en la tabla de preparación en la tabla objetivo.
5. Elimina la tabla de preparación para marcar la finalización.

Sin embargo, esta estrategia tarda más en completarse en comparación con la estrategia Merge por filas.

Ejemplos

Profundicemos en los detalles trabajando en algunos ejemplos. Considera la siguiente tabla:

CREATE TABLE usuarios (
    id_usuario bigint,
    nombre varchar(100),
    apellido varchar(100),
    direccion varchar(150),
    ciudad varchar(100),
    creado_en timestamp
)
DISTSTYLE AUTO
SORTKEY AUTO;

Ahora, veamos cómo se pueden implementar las dos estrategias:

Merge por filas

Como primer paso, se crea una tabla temporal (usuarios_preparacion) similar a la tabla objetivo (usuarios).

CREATE TEMP TABLE usuarios_preparacion (LIKE usuarios);

Con la tabla temporal en su lugar, realizamos todas las operaciones requeridas dentro de una única transacción de consulta. Esto incluye eliminar los registros en la tabla usuarios que coinciden con la tabla usuarios_preparacion e insertar todos los registros de la tabla usuarios_preparacion en la tabla usuarios.

BEGIN TRANSACTION;
DELETE FROM usuarios
USING usuarios_preparacion
WHERE usuarios.id_usuario = usuarios_preparacion.id_usuario;
INSERT INTO usuarios
SELECT * FROM usuarios_preparacion;
END TRANSACTION;

Al completar con éxito la transacción, se puede eliminar la tabla usuarios_preparacion y se debe realizar una operación de Vacuum en la tabla usuarios para volver a ordenar las filas y recuperar espacio.

DROP TABLE usuarios_preparacion;
VACUUM usuarios;

Merge por columnas

Para validar la estrategia de merge por columnas, creamos una vez más una tabla temporal llamada usuarios_preparacion. Sin embargo, esta vez, la tabla se crea con la columna a comparar y solo incluye aquellas columnas que han sido modificadas.

CREATE TEMP TABLE usuarios_preparacion (
    id_usuario bigint,
    direccion varchar(150),
    ciudad varchar(100),
    creado_en timestamp
);

En esta tabla, observa que el id_usuario 5 tiene columnas que requieren actualización, mientras que el id_usuario 7 es un registro completamente nuevo. Crearemos una transacción de consulta separada con los siguientes pasos:

1. Acomodar las actualizaciones en la tabla usuarios basadas en los cambios en la tabla usuarios_preparacion.
2. Eliminar los registros actualizados de la tabla usuarios_preparacion.
3. Insertar los nuevos registros de la tabla usuarios_preparacion en la tabla usuarios.
4. Completar la transacción.

BEGIN TRANSACTION;
UPDATE usuarios
  SET
    direccion = usuarios_preparacion.direccion,
    ciudad    = usuarios_preparacion.ciudad,
    creado_en = usuarios_preparacion.creado_en
FROM usuarios_preparacion
WHERE usuarios.id_usuario = usuarios_preparacion.id_usuario;
DELETE FROM usuarios_preparacion
USING usuarios
WHERE usuarios_preparacion.id_usuario = usuarios.id_usuario;
INSERT INTO usuarios (id_usuario, direccion, ciudad, creado_en)
(SELECT id_usuario, direccion, ciudad, creado_en FROM usuarios_preparacion);
END TRANSACTION;

Una vez que se completan con éxito las transacciones anteriores, se elimina la tabla usuarios_preparacion y se realiza una operación de Vacuum en la tabla usuarios para recuperar el espacio.

DROP TABLE usuarios_preparacion;
VACUUM usuarios;

Aunque Redshift no admite UPSERT de forma nativa, según los ejemplos proporcionados anteriormente, las operaciones UPSERT se pueden recrear utilizando las estrategias Merge por filas o Merge por columnas. Estas estrategias te permiten evitar duplicados en tus tablas de Redshift y mantener la integridad de los datos.