sql - unam - No se aplican las claves de desplazamiento al rojo de Amazon: ¿cómo evitar la duplicación de datos?

teoria del corrimiento rojo

Question

Solo probando AWS Redshift , y habiendo descubierto algunos datos duplicados en un inserto que esperaba que simplemente fallara en la duplicación en la columna clave, al leer los documentos se revela que las restricciones de la clave principal no son "impuestas".

Cualquiera descubrió cómo evitar la duplicación en la clave principal (según la expectativa "tradicional").

Gracias a todos los pioneros de Redshift!

Answer 1

Asigno UUIDs cuando se crean los registros. Si el registro es intrínsecamente único, uso UUID tipo 4 (aleatorios), y cuando no lo son, uso tipo 5 (hash SHA-1) utilizando las teclas naturales como entrada.
Entonces puede seguir estas instrucciones de AWS muy fácilmente para realizar UPSERT. Si su entrada tiene duplicados, debería poder limpiar emitiendo un SQL que se parece a esto en su tabla de preparación:

CREATE TABLE cleaned AS
SELECT
  pk_field,
  field_1,
  field_2,
  ...  
FROM (
       SELECT
         ROW_NUMBER() OVER (PARTITION BY pk_field order by pk_field) AS r,
       t.*
       from table1 t
     ) x
where x.r = 1

Answer 2

Confirmado, no lo hacen cumplir:

Las restricciones de singularidad, clave principal y clave externa son solo informativas; No se aplican por Amazon Redshift. No obstante, las claves primarias y externas se utilizan como sugerencias de planificación y deben declararse si su proceso ETL o algún otro proceso en su aplicación hace cumplir su integridad.

Por ejemplo, el planificador de consultas usa claves primarias y externas en ciertos cálculos estadísticos, para inferir la singularidad y las relaciones referenciales que afectan las técnicas de descorrelación de la subconsulta, para ordenar grandes cantidades de uniones y para eliminar uniones redundantes.

El planificador aprovecha estas relaciones clave, pero asume que todas las claves en las tablas de Amazon Redshift son válidas como cargadas. Si su aplicación permite claves foráneas o claves primarias no válidas, algunas consultas podrían generar resultados incorrectos. Por ejemplo, una consulta SELECT DISTINCT puede devolver filas duplicadas si la clave principal no es única. No defina restricciones clave para sus tablas si duda de su validez. Por otro lado, siempre debe declarar las claves primarias y externas y las restricciones de unicidad cuando sepa que son válidas.

Amazon Redshift impone restricciones de columna NOT NULL.

http://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html

Answer 3

Estoy usando IDENTIDAD para incrementar automáticamente mi clave principal.

Aquí hay una pregunta que hice en los foros de AWS:

https://forums.aws.amazon.com/message.jspa?messageID=450157#450157

Answer 4

Sí, no puedes hacer eso. Por el momento, creo que solo debes insertar datos duplicados (básicamente claves duplicadas) con una columna adicional de marca de tiempo. Por lo tanto, tendrá todas las versiones de esa fila en particular, ya que la actualización también es una inserción y mientras consulta Redshift, asegúrese de elegir la más reciente.

Answer 5

Si es demasiado tarde para agregar una columna de identidad para usar como rowid ( ALTER no le permitirá agregar una columna de IDENTITY en Redshift) puede hacer esto:

• Obtenga todas las filas de duplicados en una tabla temporal (use DISTINCT para deshacerse de duplicados)
• Eliminar estas filas de la tabla principal
• Reinsertar filas en la tabla principal

Aquí hay una muestra: (supongamos que id es su clave para verificar los duplicados, y data_table es su tabla)

CREATE TEMP TABLE delete_dupe_row_list AS
    SELECT t.id FROM data_table t WHERE t.id IS NOT NULL GROUP BY t.id HAVING COUNT(t.id)>1;
CREATE TEMP TABLE delete_dupe_rows AS
    SELECT DISTINCT d.* FROM data_table d JOIN delete_dupe_row_list l ON l.id=d.id;
START TRANSACTION;
DELETE FROM data_table USING delete_dupe_row_list l WHERE l.id=data_table.id;
INSERT INTO data_table SELECT * FROM delete_dupe_rows;
COMMIT;
DROP TABLE delete_dupe_rows;
DROP TABLE delete_dupe_row_list;

Answer 6

Una forma rápida y sucia es usar group by

select max(<column_a>), max(<column_a>), <pk_column1>, <pk_column2>
from <table_name>
group by <pk_column1>, <pk_column2>