Mover datos históricos y borrar

Al consultar ciertas tablas de MySql notamos que tienen una cantidad excesiva de filas. Acá veremos una forma de hacer una copia de los datos en un dataset de BigQuery por un lado, y por el otro la posibilidad de borrar los datos de MySql.

Esto aplica a tablas que tengan alguna columna de tipo DateTime e idealmente que sea index.

Problemática detectada

• Disminución del rendimiento (incluso utilizando índices)
• Consultas complejas o reportes se vuelven muy costosos
• Dificultad en el manejo de grandes volúmenes de datos
• Análisis ineficiente al realizar consultas analíticas grandes
• Costo de almacenamiento

Ejemplos en producción

Para poner un ejemplo en producción de Keeper, podemos ver el tamaño de las tablas más grandes.

Al día de hoy, 03/03/2026, la cantidad de filas aproximadas de las tablas es:

• incidentes: ~1500M
• incidentesHistoricos: ~92M
• infracciones: ~63M

Para conocer más en detalle la antigüedad de los datos, basta con ver la fecha del primer evento de cada tabla:

• incidentes: 01/01/2022
• incindetesHistoricos: Del 01/01/2021 al 02/11/2021
• infracciones: 18/10/2019

Como se puede ver, hay datos que tienen más de 5 años.

Funciones intervinientes

Para realizar la copia y luego el borrado se usa un mecanismo de procesamiento por lotes, utilizando colas de GCP.

Las funciones que intervienen son:

• migration-mysql-to-store
• migration-task-processor

Migración

Ejecución

La migración se comienza llamando a la primera función vía Pub/Sub con un mensaje como:

{
  "years_to_migrate": 1,
  "months_to_migrate": 3,
  "source_tables": ["infracciones"],
  "destination_tables": ["infracciones_hist"],
  "filter_columns": ["fechaGPS"],
  "tasks_enqueued_limit": 365,
  "min_date_limit_iso": "2024-01-04T20:49:16"
}

• years_to_migrate: Número de años en el pasado desde la fecha actual que se desea migrar.
• months_to_migrate: Meses adicionales a restar para definir el límite de migración.
• source_tables: Lista de las tablas de origen en la base de datos MySQL.
• destination_tables: Lista de las tablas destino en BigQuery.
• filter_columns: Lista de columnas (una para cada tabla) que se utilizan para filtrar los registros en función de una fecha (estas columnas deben ser de tipo DATETIME e Index para optimizar la consulta).
• tasks_enqueued_limit (default: 20): Límite máximo de tareas a encolar para cada tabla, que permite controlar la carga del proceso.
• min_date_limit_iso (opcional): Si está fecha está presente, se migran las filas desde esta fecha en adelante.

Revisión

Una vez ejecutada la función, se deben chequear las tareas en la cola jobs-migration. Cuando esta cola reduzca el número de tareas a 0, quiere decir que la migración ha finalizado.

Para ver los días migrados se puede revisar el set de redis mig_<table> por ejemplo

curl -m 25 -X POST https://us-central1-sils-des.cloudfunctions.net/loadRedisData \
-H "Authorization:bearer $(gcloud auth print-identity-token)" \
-H "Content-Type:application/json" \
-d '{"method":"getSet","data":"mig_infracciones"}'

Así mismo hay que consultar la tabla creada en BigQuery para ver que todos los registros se hayan migrado, por ejemplo con la siguiente query:

SELECT count(*) FROM `sils-des.prueba.infracciones_hist`;

Hasta el momento no se han borrado filas de la tabla original de MySql. Veamos cómo hacerlo.

Limpieza

Una vez que se haya comprobado la correcta migración, se pueden borrar los datos originales para reducir las tablas de MySql.

Para ello se ejecuta la misma función pero con los siguientes parámetros:

{
  "clean_table": "infracciones",
  "filter_column": "fechaGPS",
  "dry_run": true
}

• clean_table: Tabla original en MySql a limpiar.
• filter_column: Columna usada para filtrar.
• dry_run (boolean, default: True): Sólo loguea pero no borra.

Cada día migrado previamente va a aparecer listado en el Set de Redis correspondiente. Este mismo set es el que se usa para saber qué filas borrar.

DEMO

Queremos migrar los datos de la tabla incidentes que tengan más de un año. Para ello vamos a invocar a la función vía Pub/Sub con los siguientes parámetros:

{
  "years_to_migrate": 1,
  "months_to_migrate": 0,
  "source_tables": ["incidentes"],
  "destination_tables": ["incidentes_hist"],
  "filter_columns": ["fechaGPS"],
  "tasks_enqueued_limit": 365
}

Como el lote se arma con filas del mismo día y acá estamos ejecutando con un límite de tareas de 365 días, esta ejecución sólo migrará el año más antiguo anterior al último año. Para migrar años posteriores, se puede volver a ejecutar con los mismos parámetros.

Una vez que se haya comprobado la migración, veremos en redis un listado con los días migrados. Por ejemplo

["2023-12-13","2024-04-16","2023-09-30","2024-09-06","2024-09-14","2024-04-21","2024-04-08","2024-01-02","2023-11-18","2024-08-15","2024-06-01","2023-12-23","2024-11-27","2024-04-28","2024-07-11","2024-10-08","2024-02-10","2024-04-14","2023-09-23","2024-02-21","2023-10-13","2024-01-03","2024-08-11","2024-01-04","2024-04-30","2024-11-29","2024-09-09","2023-12-30","2023-12-27","2024-05-04","2024-01-23","2024-07-12","2024-08-13","2023-11-24","2023-12-07","2024-10-12","2024-02-19","2024-05-30","2024-04-26","2024-01-27","2024-05-20","2023-10-03","2023-12-18","2024-01-17","2024-06-17","2024-02-29","2023-12-12","2023-11-12","2023-11-26","2024-11-13","2024-06-03","2023-08-13","2024-09-30","2023-11-09","2024-09-23","2024-06-05","2023-12-29","2024-07-03","2024-04-04","2024-08-04","2023-12-02","2024-11-15","2024-04-20","2023-08-19","2023-10-20","2023-11-01","2023-10-23"]

Si ahora ejecutamos el borrado

{
  "clean_table": "incidentes",
  "filter_column": "fechaGPS",
  "dry_run": true
}

Se borrarán (con dry_run en true sólo se loguearán) todos los incidentes generados en cualquiera de esos días.