optimizacion para quita de duplicados

yoguille · 03-07-2024 11:02 AM

Tengo una tabla grande que puede tener duplicados, lo que hago es una vista con un row_number(), el tema que al hacerlo lo hace sobre todo el universo de la tabla genera mucho gasto.
Para no traer y borrar siempre el histórico particione la tabla por fecha de actualización y traigo los registros de ayer y borro los registros de ayer sin tocar el histórico, el tema aquí que por el rango tan chico puedo tener duplicados ya que si ese registro metió un id hace 5 días yo al buscar y borrar por un día si hubo una actualización ayer me lo va a meter generando duplicidad.
Lo que intente es traer los id de ayer y borrar los IDs que traigo de la tabla histórica. Pero tabien genera gran gasto por que el delete se fija en el universo entero.(incluso particione la tabla por ID)
Intente luego un merge, me genera más gasto que lo anterior.
Intente vista materializada, no soporta el row_numbre, intente cambiar el row_numbre por un group by haciendo el inner join con la misma tabla para quedarme con el máximo ID, las vistas materializadas tampoco soporta que instancies mas de una vez a una tabla.
Se me están quemando los libros, alguna sugerencia????

ms4446

Entendiendo Tu Desafío

Problema de Registros Duplicados: Tienes una gran tabla en Google Cloud BigQuery que acumula registros duplicados con el tiempo.
Preocupaciones de Costo con ROW_NUMBER(): Usar ROW_NUMBER() en todo el conjunto de datos para identificar y eliminar duplicados es costoso.
Particionamiento por Fecha de Actualización: Has particionado la tabla por la fecha de actualización para gestionar los datos de manera más eficiente, centrándote en los cambios recientes (por ejemplo, los datos de ayer). Sin embargo, este enfoque no resuelve completamente el problema de duplicación porque un registro actualizado ayer podría haber sido ingresado antes, llevando a duplicados a través de particiones.
Estrategia de Eliminación: Intentar eliminar duplicados por sus ID basándose en los datos de ayer también resultó costoso, ya que implica escanear y modificar una gran parte del conjunto de datos.
Merge y Vistas Materializadas: Has intentado usar declaraciones MERGE y considerado vistas materializadas para gestionar duplicados, pero estos enfoques aumentaron los costos o no fueron compatibles debido a limitaciones con ROW_NUMBER() y auto-joins en vistas materializadas.

Solución Propuesta

Dadas las limitaciones y desafíos que has descrito, aquí tienes un enfoque revisado para gestionar eficientemente los duplicados sin incurrir en costos excesivos:

Paso 1: Deduplicación Incremental con Enfoque en Cambios Recientes

Reducir el Alcance: En lugar de procesar toda la tabla, concéntrate en un subconjunto más pequeño de datos que es más probable que contenga duplicados, como registros actualizados o agregados en los últimos días. Esto reduce el volumen de datos a procesar.

Paso 2: Usar una Tabla Temporal para Cambios Recientes

Crear una Tabla Temporal: Extrae registros de los últimos días (incluyendo ayer) en una tabla temporal. Esta tabla debe incluir todas las columnas necesarias para identificar duplicados, como ID y fechas de actualización.
Aplicar Lógica de Deduplicación: En esta tabla temporal, usa la función ROW_NUMBER() particionada por tu identificador único (por ejemplo, ID) y ordenada por update_date DESC para asignar números de fila a cada registro.

SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY update_date DESC) AS rn

FROM tabla_temporal
Filtrar Duplicados: Selecciona registros donde rn = 1 para obtener la última entrada por ID, eliminando efectivamente duplicados dentro de este subconjunto.

Paso 3: Fusionar los Datos Deduplicados de Vuelta

Usar la Declaración MERGE: Con tu subconjunto deduplicado listo, usa una declaración MERGE para actualizar tu tabla principal. Esto te permite insertar nuevos registros y actualizar los existentes basándose en el ID único, asegurando que tu tabla principal permanezca actualizada sin duplicados.
- Este paso es crucial ya que asegura que solo las filas necesarias sean tocadas, minimizando los costos asociados con eliminaciones o actualizaciones a gran escala.

Paso 4: Mantenimiento Regular

Programar Limpiezas Regulares: Automatiza este proceso para que se ejecute a intervalos regulares, enfocándote en datos recientes para prevenir la acumulación de duplicados con el tiempo.

Consejos Adicionales

Monitorear Costos de Consultas: Mantén un ojo en los costos asociados con estas operaciones. BigQuery proporciona estimaciones de costos para las consultas, lo que puede ayudarte a ajustar tu estrategia según sea necesario.
Optimizar la Estructura de la Tabla: Si es posible, revisa y optimiza la configuración de particionamiento y agrupamiento de tu tabla para mejorar el rendimiento de las consultas y reducir costos.

Este enfoque busca equilibrar la eficiencia y la rentabilidad al enfocar los esfuerzos de deduplicación en datos recientes y minimizar el volumen de datos procesados en cualquier momento dado. Al mantener regularmente tu conjunto de datos con esta estrategia, puedes gestionar los duplicados de manera más efectiva sin incurrir en costos excesivos.

yoguille

Buenas tardes y muchas gracias por tu respuesta.
Yo estaba haciendo algo similar, creaba una tabla temporal, la llenaba con una query que tenia un rango de fechas por fecha de actualizacion y le aplicaba el row_number para quedarme con el ultimo registro, a este lo usaba como origen de datos y luego hacia el merge.
El tema aqui es que al hacer el merge por ID contra la tabla historica consume mucho.
Creo si es que no entendi mal es lo que me estas proponiendo hacer....
creo que no hay muchas opciones mas o no las conozco.....

ms4446

Buenas tardes! Entiendo tu situación y el desafío que enfrentas con el proceso de MERGE y su impacto en los costos. Si el MERGE por ID contra la tabla histórica está resultando ser muy costoso, podríamos considerar algunas alternativas o ajustes para optimizar el proceso y potencialmente reducir los costos. Aquí te dejo algunas ideas adicionales que podrían ayudarte:

1. Optimización del Proceso de MERGE

Reducir el Conjunto de Datos: Antes de realizar el MERGE, podrías intentar reducir aún más el conjunto de datos temporal. Por ejemplo, si solo estás interesado en los registros que han cambiado, podrías intentar identificar solo aquellos registros que son diferentes de los que ya están en la tabla histórica, ya sea por medio de hashes o comparando campos específicos que indicarían una actualización.
Clustering en la Tabla Histórica: Si aún no lo has hecho, considera usar el clustering en tu tabla histórica basado en los ID o en las columnas que más frecuentemente utilizas para las consultas. Esto puede ayudar a reducir el costo de las operaciones de MERGE al limitar la cantidad de datos que BigQuery necesita escanear.

2. Estrategias Alternativas al MERGE

Insertar y Sobrescribir Particiones: Dependiendo de cómo estén estructurados tus datos y tus necesidades de consulta, una estrategia que podría ser más eficiente desde el punto de vista de los costos es insertar nuevos registros en una tabla o partición temporal y luego sobrescribir la partición correspondiente en la tabla histórica. Esto es particularmente útil si tus datos están particionados por fecha y puedes identificar claramente las particiones que necesitan ser actualizadas.
Uso de Tablas de Intercambio: Otra estrategia podría ser el uso de una tabla de intercambio. Primero, realiza el MERGE en una tabla de intercambio que es una copia de tu tabla histórica pero sin los datos que estás actualizando. Luego, intercambia las tablas. Esta estrategia puede ser útil si estás actualizando una gran parte de tu tabla histórica a la vez.

3. Revisión del Diseño de Datos

Revisar la Necesidad de Historial Completo: Evalúa si realmente necesitas mantener un historial completo de todos los cambios en tu tabla histórica. En algunos casos, mantener solo los registros más recientes o resúmenes de los cambios puede ser suficiente para tus necesidades analíticas, lo que podría simplificar tu proceso de actualización.

4. Análisis de Costos Detallado

Análisis Detallado de los Costos: Realiza un análisis detallado de dónde se están generando los mayores costos en tu proceso actual. BigQuery proporciona información detallada sobre el uso de recursos para cada consulta, lo que puede ayudarte a identificar oportunidades específicas para la optimización.

Entiendo que cada situación es única y lo que funciona para un conjunto de datos o caso de uso puede no ser aplicable para otro. La clave está en encontrar un equilibrio entre mantener la integridad y la actualidad de tus datos con el costo de mantener esos datos. Espero que estas sugerencias te ofrezcan algunas vías adicionales para explorar y optimizar tu proceso de gestión de datos en BigQuery.