Construcción de un pipeline de canonicalización de URLs de video
Ejecuté una consulta en mi base de datos SQLite de producción y encontré un problema masivo.
La tabla de videos tenía 41.283 filas, pero solo 29.000 títulos únicos. Estábamos almacenando los mismos videos varias veces.
¿Por qué sucedió esto? El mismo video de YouTube llegaba en diferentes formatos de URL:
• Enlaces cortos: youtu.be/ID • Enlaces de escritorio: youtube.com/watch?v=ID • Enlaces móviles: m.youtube.com/watch?v=ID • Shorts: youtube.com/shorts/ID
Un solo video significaba cuatro filas en la base de datos y cuatro páginas web casi idénticas. Esto desperdició mi presupuesto de rastreo y generó errores en Google Search Console.
Dirijo TrendVidStream. Extraemos datos de tendencias de ocho regiones diferentes. Cada región puede mostrar el mismo video viral utilizando diferentes estructuras de URL.
Construí un pipeline para solucionar esto usando PHP 8.4 y SQLite.
Así es como funciona el pipeline:
- Extraer: Convertir cualquier variante de URL en un ID de video estable de 11 caracteres.
- Normalizar: Crear una única URL canónica a partir de ese ID.
- Validar: Realizar comprobaciones rápidas antes de tocar la base de datos.
- Upsert: Usar SQLite UPSERT para fusionar datos en lugar de añadir nuevas filas.
- Emitir: Añadir etiquetas canónicas y redirecciones 301 para que los motores de búsqueda encuentren una sola URL.
Decisiones técnicas clave:
• Usar una lista de permitidos (allowlist) de hosts exactos. No utilizar la coincidencia de sufijos. Esto evita riesgos de seguridad.
• Nunca convertir los IDs a minúsculas. Los IDs de YouTube distinguen entre mayúsculas y minúsculas. Convertirlos a minúsculas fusionaría videos diferentes.
• Usar una restricción UNIQUE en video_id. Esta es su defensa más sólida contra los duplicados.
• Usar el modo WAL de SQLite. Esto hace que las escrituras sean rápidas y fiables.
El resultado:
Nuestra tabla de videos pasó de 41.283 filas a 28.094 filas. No perdimos ningún video. Los errores de Google Search Console bajaron de 412 a 9. Los resultados de búsqueda se volvieron más limpios porque dejamos de mostrar el mismo video cuatro veces.
La lección es sencilla: cuando agregues datos de muchas fuentes, construye primero la identidad en tu sistema. Extrae un ID estable, aplícalo mediante restricciones de base de datos y haz que cada escritura sea idempotente.
No necesitas herramientas complejas. PHP y SQLite son suficientes.