script para identificar registros duplicados en sqlite3

En uno de mis proyectos estamos ya en la recta final a punto de poner en producción el nuevo programa. A último momento siempre te encuentras con aquellas puñetitas que hacen que la migración no salga como es lo esperado, es por esto que es muy importante practicar la migración al igual que si de una audición se tratase.

La puñetita con la que me he encontrado trata precisamente de la importación de datos mas grande, la de una tabla de 4Gb de datos, que contiene las lecturas de unos sensores, he decidido separarlas en alrededor de 4.000 ficheros de base de datos sqlite3, uno para cada uno de los sensores. Al realizar la importación de cada uno de los sensores de forma individual, de maravilla, pero a la que se hace el proceso de migración de todos los sensores, algunas de las lecturas se duplican y no he conseguido hallar el porqué ocurre.

Ya hace unos días puse un par de controles que si el fichero ya existía o la base de datos estaba siendo usada, pasase al siguiente sensor, aún así seguían duplicandose los datos. La solución era añadir un tercer control que comprobase que el registro ya estaba guardado, pero que ralentizaría la migración de los datos de forma significativa y la primera migración el interés es que sea lo más ágil posible. Así que dándole un par de vueltas y muy importante, tras más de 24h de no tocar el código (se llama descansar), he encontrado la solución, realizar un postprocesamiento de los datos una vez importados.

Una de las problemáticas que tenía es que el id del registro era la fecha en formato unix y milisegundos para procesar los datos en highcharts (que lo pide así).

La solución estos dos scripts de bash:

script1
Este lo uso en modo de control para comprobar si hay ficheros sin registros o con registros duplicados

#!/bin/bash

echo "This scripts checks duplicated rows"
echo

cd ../sqlite/

for p in *.db
do
    NUMBER_ROWS=`sqlite3 "$p" 'select time_ms from sensor_data' |wc -l`
    DUPLICATED_ROWS=`sqlite3 "$p" 'select time_ms from sensor_data group by time_ms having count(*) != 1' |wc -l`

    if [ ${NUMBER_ROWS} == "0" ] || [ ${DUPLICATED_ROWS} -gt 2 ]; then
        echo "### File ${p} must be attended ##########"
        echo "Number of rows: ${NUMBER_ROWS}"       

        if [ ${NUMBER_ROWS} == "0" ]; then
            echo "No data imported"
        fi

        if [ ${DUPLICATED_ROWS} != "0" ]; then
            echo "Duplicated rows: ${DUPLICATED_ROWS}/${NUMBER_ROWS}"
        fi

        echo ""
    fi
done

script2
Este otro, elimina los registros duplicados y guarda solo uno de ellos. Además, para aprovechar mejor el servidor, aquellos ficheros con más de 5000 registros, los mando a Background para usar mas CPU y agilizar el procesamiento

#!/bin/bash

echo "This script searches and deletes duplicated data"

# choose number CPU to use
NUMBER_CPU=3

cd ../sqlite/

for p in *.db
do
    NUMBER_ROWS=`sqlite3 "$p" 'select time_ms from sensor_data' |wc -l`
    DUPLICATED_ROWS=`sqlite3 "$p" 'select time_ms from sensor_data group by time_ms having count(*) != 1' |wc -l`

    if [ ${NUMBER_ROWS} == "0" ] || [ ${DUPLICATED_ROWS} -gt 2 ]; then
        echo "### File ${p} must be attended ##########"

        if [ ${NUMBER_ROWS} == "0" ]; then
            echo "No data imported"
        fi

if [ ${DUPLICATED_ROWS} != "0" ]; then
            echo "Duplicated rows: ${DUPLICATED_ROWS}/${NUMBER_ROWS} - processing delete duplicated"
            NUMBER_BACKGROUND_PROCESSES=`ps aux |grep sqlite |grep DELETE |wc -l`
            if [[ ${NUMBER_ROWS} -gt 5000 ]] && [[ ${NUMBER_BACKGROUND_PROCESSES} -le ${NUMBER_CPU} ]]; then
                echo "More than 5000 rows to manage, sending to background and continue with process"
                sqlite3 "$p" 'DELETE FROM sensor_data WHERE EXISTS (SELECT 1 FROM sensor_data p2 WHERE sensor_data.time_ms = p2.time_ms AND sensor_data.rowid > p2.rowid)' && sqlite3 "$p" 'vacuum' &
            else
                sqlite3 "$p" 'DELETE FROM sensor_data WHERE EXISTS (SELECT 1 FROM sensor_data p2 WHERE sensor_data.time_ms = p2.time_ms AND sensor_data.rowid > p2.rowid)'
                sqlite3 "$p" 'vacuum'
                NEW_NUMBER_ROWS=`sqlite3 "$p" 'select time_ms from sensor_data' |wc -l`
                CONTROL_DUPLICATED_ROWS=`sqlite3 "$p" 'select time_ms from sensor_data group by time_ms having count(*) != 1' |wc -l`
                echo "Process executed, result: ${CONTROL_DUPLICATED_ROWS}/${NEW_NUMBER_ROWS} - done!"
            fi
        fi

        echo ""
    fi
done

El vacuum es para reducir el tamaño de los ficheros.

Y hasta aquí esta pequeña receta a para dar solución a otro de los problemas cotidianos de la vida de un informático.

He optado para usar bash en lugar de python para agilizar el proceso de ejecución y también porqué había la posibilidad de ejecutar comandos sqlite3 de la forma mostrada.