Tendencias («trends») históricas del uso de palabras con Sphinx

Etiquetas

menéame trends, sphinx

Hace unos días quise saber desde cuándo se empezó a hablar de desahucios y suicidios en la prensa en España. Fui a Google Trends y el gráfico mostraba una evolución demasiada plana, que no se correspondía con tantas noticias que leímos en la prensa española. Me pregunté si, y cómo, podía obtener esas estadísticas en Menéame. Se me ocurrió que debería haber un truco relativamente sencillo usando los índices de de búsqueda de Sphinx (lo usamos para el buscador de Menéame). Así fue que en pocas horas pude implementar un sistema similar a Google Trends en Menéame.

Esto es lo que salió con las tendencias de esas dos palabras por su frecuencia de aparición por meses:

No sólo es posible analizar la frecuencia en las noticias, también por comentarios (o notas), por ejemplo:

También es posible conocer rápidamente cuál fue la noticia (o comentario o nota) más votada en cada uno de los puntos dibujados:

(El total «747» indica que apareció 747 veces en el mes 11 de 2012, la noticia con esa palabra es la que tuvo más votos en ese período, si se hace clic abre una ventana del buscador que estará «centrado» alrededor de esa fecha)

Implementación

A continuación explicaré brevemente (el código está publicado para los interesados, pondré los enlaces) cómo lo he implementado usando al máximo las posibilidades del Sphinx para lograr que se generen los datos en menos de un segundo (incluso para analizar los 15 millones de comentarios).

El sistema tiene tres partes principales:

1. El servidor de búsquedas Sphinx. Lo usamos desde hace años para el buscador de Menéame, es un sistema que periódicamente consulta a la base de datos MySQL y crea su propia base de datos para mejorar y optimizar las búsquedas por palabras. Tenemos tres índices diferentes, el de envíos (links), comentarios (comments) y notas (posts). Esta es la configuración que usamos para la indexación (se hace siempre sobre un servidor esclavo del principal del MySQL, si no sobrecargaría el maestro que se usa para web).
2. El dibujo de los datos, está hecho con Flotcharts para jQuery. Es la parte más sencilla y podéis ver el código y configuración que se empotra en la página HTML. Los datos que se diujan se obtienen mediante una llamada AJAX que hace las consultas a Sphinx y retorna los datos en formato JSON, el siguiente punto. (Veréis que hay una función addPostCode(), ésta sólo se usa para ejecutar el Javascript una vez que las librerías JS están cargadas (es parte de la implementación de Menéame para reducir el tiempo de visualización). La función que se llama con el evento «plothover» es sólo hacer la consulta y dibujar cuál es la noticia o comentario más relevante en cada mes.)
3. La obtención de los datos vía consultas a Sphinx y generación JSON. Esto es la parte fundamental que explico a continuación, veréis que en realidad es bastante sencillo.

Obtención de los datos

Para obtener los datos de los gráficos de tendencias de frecuencias mensuales hace falta:

1. Obtener el total noticias, comentarios o notas por mes, para poder hacer una gráfica de evolución relativa.
2. Obtener el número de noticias, comentarios o notas en los que aparece cada palabra o frase, en cada mes.
3. Ordenarlos y generar el JSON con una estructura compatible con Flot.

En ambos casos usamos características similares del Sphinx. En el segundo punto (total mensual) se podría hacer directamente desde el MySQL y cachear los datos, pero en las pruebas que hice el Sphinx lo hace mucho más rápidamente (tres segundos y medio para los 15 millones de comentarios, un segundo y medio para casi las dos millones de noticias) por lo que es más sencillo, no tenía que agregar tablas, y se guardan los totales completos en una cache válida durante 24 horas.

El código completo que hace esas tres cosas está en backend/trends.php. El código hace esas tres fases en el mismo orden (está razonablemente comentado). El trabajo pesado de los dos primeros pasos lo hace Sphinx. Las consultas se hacen vía SphinxSQL, un subconjunto de SQL que usa el mismo protocolo que MySQL, por lo que desde Menéame se crea la conexión con la misma clase que usamos para la base de datos principal:

$sp = new RGDB('', '', '', $globals['sphinx_server']);
$sp->port = 9306;
$sp->connect();

En la primera fase se obtienen los totales de artículos/comentarios/notas por mes y se almacenan en las «anotaciones» de Menéame con un tiempo máximo de 24 horas. La consulta SQL a Sphinx es la siguiente:

select yearmonth(date) as yymm, @count
      from $indices group by yymm
      limit 2000 option ranker = none

Cada fila de esta consulta nos devolverá los totales (@count) de enlaces/comentarios/notas por año-mes (yearmonth) de la fecha de lo que se busca (links, comments o posts, especificado por la variable $indices). Esos datos se almacenan en el array $totals.

La siguiente consulta es un multiquery, hay una consulta por cada palabra o frase que se busca, cada una de ellas define una serie. Se construye el SQL completo para todas las series (separados por «;»). El SQL para cada serie tiene la siguiente forma:

select yearmonth(date) as yymm, $sort, date, @count
    from $indices where match('\"$words\"')
    group by yymm within group order by $sort desc
    limit 1000;

Esta consulta es similar a la anterior, con un par diferencias:

• La función match() que es la que busca por palabras, en este caso $words tiene la palabra o frase que define la serie (usamos las \» para indicar que busque por la frase completa en caso que sean dos o más palabras.
• within group order by $sort desc especifica que para cada grupo año-mes, el id del artículo que devolverá (siempre devuelve un id) será el que tenga más votos o karma, dependiendo del valor de la variable $sort (en caso de enlaces se ordena por votos, en caso de comentarios y notas, por karma).

Esto es lo fundamental, la siguiente fase es simplemente ordenador los objetos y generar información adicional que es necesaria para el gráfico como para mostrar el tooltip con la noticia o comentario más relevante, y los timestamps correspondientes. Abajo del gráfico aparece el enlace a este script con los argumentos correspondientes (por ejemplo, donde podéis ver los datos que genera en formato JSON).

Eso es todo, sólo quería mostrar cómo es relativamente sencillo generar tendencias si se tiene un Sphinx como buscador para el sitio. Y con estas explicaciones, ya es trivial que puedas hacer las tuyas 😉