Registros duplicados

Recientemente estuve trabajando en localizar y eliminar duplicados de registros en uno de los inventarios que manejamos con SQL Server 2005.

La premisa es muy sencilla: "Localiza y elimina los registros duplicados"

La tabla es sencilla sin muchas columnas, simplemente es un inventario de impresoras donde almacenamos el tipo de impresora, la marca y modelo, su dirección IP y dirección MAC, número de serie y el puerto por donde sale a la red, bien hasta aquí.

El problema es que los usuarios nos avisaron de que había registros donde se veían direcciones MAC duplicadas y también encontraron Numeros de Serie duplicados, me puedo imaginar al pobre usuario intentando actualizar su inventario para darse cuenta de que hay discrepancias y que ahora no esta seguro "cual registro es el bueno".

Sin duda eliminar registros duplicados puede ser un trabajo muy sencillo o sumamente complicado y digo esto por la razón de que si sencillamente quieres quitar duplicados sin revisar o calificar los registros para saber "cual es el bueno" entonces la solución esta a un 'tris' de tus dedos, pero si vas a revisar cuales son esos duplicados, analizar los valores de las columnas para decidir cual es registro con el que te quedas y finalmente borrar lo que decides que son registros basura entonces todo toma un sentido diferente y la tarea se vuelve complicada por no decir que consume mucho tiempo  (al menos hablando desde mi experiencia y asegurando que hice todo lo posible por rápidamente entender los mejores pasos a tomar para resolver este divertido reto).

Continuando, describo las opciones que tuve y cual tome así como los pasos que seguí para mejorar el inventario.

Debido a que yo trabajo con tecnología de Microsoft mis opciones se convirtieron en usar cualquiera de las siguiente herramientas:

• Excel 2007
• Access 2007
• Lenguaje Transact SQL
• Integration Services (SSIS)

Lo primero que pense fué "Voy a automatizar esto con SSIS y en un santiamén lo resuelvo" (ja! iluso) y encontre este excelente artículo de Todd McDermid titulado "Eliminating Duplicate Primary Keys in SSIS". 

No quise utilizar ni Excel ni Access porque sabía que habría mas trabajo manual del que pudiera soportar, sin embargo no descarto que se puedan utilizar y que puedan ayudar mucho en la tarea de encontrar duplicados y eliminarlos de la tabla. Tal vez si el trabajo es hecho por mas de una persona se pueda facilitar el proceso, en mi caso solo estaba yo para resolver el problema y por eso es que necesite ayuda de algo mas sofisticado como Integration Services.

Solo un comentario mas sobre Excel 2007, sus tablas dinámicas si me sivieron para filtrar los registros y con esto me pude dar una mejor idea de los pasos que tomaría para automatizar el proceso de elimado de duplicados, ya eso es todo ahora si a continuar...

Aviso importante: Me voy a permitir describir a continuación y en resumen lo que Todd ahí comparte con respecto al tema de "deduplicación de registros", cualquier buena idea aquí mostrada la tome a partir de su publicación.

Usando SSIS hay 3 formas para eliminar duplicados de una tabla: La super fácil, La moderadamente dificil y La muy dificil.

La super fácil consta de sencillamente poner en un "Data Flow" un componente de conexión a la tabla donde están los registros duplicados, luego conectarla a un componente "Sort" para ordenar y en este es donde debes utilizar la columna donde se sabe que estan los datos duplicados. En el editor del componente Sort se activa la casilla de elimnar duplicados y finalmente se conecta a un componente de destino.

La moderadamente difícil (que fue la finalmente yo tome como base para comenzar mi trabajo) utiliza una columna donde se califican los registros. El concepto a primera vista es sencillo de entender, digamos que se tiene un registro que esta repetido tres veces, a partir del valor o los valores de los campos de esos registros se crea una columna con la calificación y esta es la que servirá para tomar el mejor calificado y conservarlo. Este modo es solo bueno sí hay la forma de determinar esta "calificación", yo en realidad no hice mucho aqui y mi punto de partida fué tomando el campo de clave primaria (priamry key) viendo cual era el registro mas nuevo para borrar los mas viejos, asi de sencillo.

Bien, Todd describe por pasos como crear ese paquete así:

1. Después de crear el componente de origen de datos, se envían esos datos aun componente de "Derived Column" en donde se crea esta columna de calificación la cual se utilizará mas adelante.
2. Luego se conecta a un componente "Multicast" para hacer una copia de estos datos. Se utilizarán dos salidas que para efectos de simplicidad se describen como Salida A y Salida B.
3. La Salida A se conecta con un componente "Aggregate" para realizar la operación de agregación "Group By"  UNICAMENTE a la(s) columna(s) donde estan los datos repetidos. En mi caso fué la dirección MAC de la impresora. Ojo!, al conectar la Salida A al componente Aggregate las demás columnas y sus datos se perderán, no hay problema y por eso reitero unicamente agrupar la(s) columnna(s) que tiene los datos repetidos.
4. A continuación se envía la Salida A a un componente "Sort" y se ordena primero por la columna de clave principal (primary key) y después por la columna de calificación. Es necesario hacer esto pues el componente de Merge Join requiere que haya al menos una columna ordenada.
5. Conectar la Salida A a un componenete Merge Joing. Ponerlo en LEFT Side.
6. Enviar la Salida B a un componente "Sort" y ordenar por la columna donde estan los datos repetidos. NO seleccionar la casilla para remover duplicados.
7. Conectar la Salida B al componente Merge Join, configurarlo en RIGHT Side.
8. Editar el componente "Merge Join", hay que dejar la configuración predeterminada de "Inner Join" esto hace que el resultado sean solo aquellos registros donde haya coincidencia en la columna que esta ordenada. Finalmente, se activa la casilla en los campos de la Salida B o lo que entro por RIGHT Side del componente.

La teoría y los pasos ahi estan, la siguiente imagen muestra lo que yo hice, puse tres secciones enmarcadas en rojo para describir los pasos principales del proceso, primero se obtienen los datos duplicados, se califican en el componente "Derived Colunn" y se pasan al componente "Multicast". Despues se trabaja con cada una de las salidas donde la Salida A se agrupa por las columnas donde haya datos repetidos y se ordenan. La Salida B unicamente se ordena por la columna donde haya datos duplicados.

Ahora bien, en mi caso decidi primero atacar aquellos registros donde hubiera unicamente dos registros duplicados. Lo decidi asi porque al analizar los datos en la tabla dinámica de Excel me di cuenta de que en esas dos repeticiones podía tomar el registro donde la clave principal fuera mayor y eliminar el otro registro. Por esta razon agregue el componente "Conditional Splilt" para tomar unicamente los registros donde hubiera dos repeticiones.

Despues de hacer la selección de los registros con solo dos duplicados, se pasan los datos por el componente Merge Join para efectuar la operación Inner Join, el resultado es entonces enviado al tercer paso del proceso. En este paso vuelo a pasar los datos por un componente "Aggregate" porque es ahi donde seleccione con la función MIN el registro que tenía que eliminar. 

El componente "Multicast" que viene despues tiene como objetivo crear una copia de esos registros que iba a borrar. Una copia fué enviado a un archivo plano para tenerlo como referencia en caso de que me hubiera equivocado y necesitara restaurarlos y la otra copia se fué a un componente "OLE DB Command" donde escribi un "query" bastante sencillo:

DELETE FROM Tabla_Impresoras WHERE ID = ?

Aqui la imagen de como se ve mi paquete:

Algo que no comente fué que en el componente "Aggregate" en el cuadro 2, hay que agrupar por la columna donde estan los datos duplicados con la función de agregación "Group By" y luego hay que incluir la columna de la clave principarl con la función de conteo, de otra forma el componente de agregación del cuadro 3 no va a funcionar.

Aquí la imagen de como quedo el mío:

La muy difil no la comento mucho pues no use este metodo sin embargo la propuesta de Todd es interesante. Para esta opearción se utiliza un componente "Script" que funciona de manera asíncrona.
Se agregan las columnas que se desea pasar al componente y luego configura las columnas de salida. Finalmente hay que tirar codigo en el "Script" que contenga la logica necesaria para la seleccionar los registros que estan duplicados.

Espero esta entrada haya sido provechosa, termino agradeciendote por haber leido hasta aqui y comentando que además de estas opciones, hay personas que estan haciendo deduplicación con las fucniones RANK() y ROW_NUMBER() de SQL.

Yo hice algunas pruebas, aqui mi script


SELECT ROW_NUMBER() OVER (PARTITION BY MACAddress ORDER BY SerialNumber ASC) AS RowNumber, MACAddress, SerialNumber, IPAddress
FROM Printer_Manual_Data
WHERE MACAddress IS NOT NULL


Saludos y suerte!