developerlover.com

Buscador con Sphinx y MySQL

Vamos a explicar cómo montar un buscador con Sphinx. Explicaremos qué es Sphinx, qué nos ofrece, cómo instalarlo y cómo usarlo con MySQL.

También hablaremos un poco de SphinxQL, de cómo integrarlo y usarlo a través de MySQL, y haremos un pequeño script en PHP desde el que lanzaremos las consultas.

¿Qué es Sphinx?

Sphinx es un motor de búsqueda full-text escrito en C++.

¿Y qué es esto de full-text? Significa que nos ofrece búsquedas sobre texto, rápidas, relevantes y ordenadas, además de múltiples opciones de configuración. Perfecto para implementarlo en nuestra web a la hora de buscar posts, usuarios, productos o cualquier cosa que se nos ocurra.

Sphinx está especialmente diseñado para utilizarse sobre bases de datos SQL y desde múltiples lenguajes de script como PHP, Ruby o Python.

¿Qué nos ofrece Sphinx?

  • Indexación de contenidos muy sencilla.
  • Resultados de calidad y relevantes, muy personalizables gracias a los diferentes tipos de búsqueda y opciones que ofrece.
  • Alto rendimiento. Sphinx es capaz de manejar millones de documentos y terabytes de datos, y a su vez, de realizar miles de consultas por segundo.
  • Escalabilidad.

¿Cómo funciona Sphinx?

El funcionamiento de Sphinx se divide en dos partes muy claras:

  • Indexación de contenidos: En la que Sphinx se va a encargar (mediante la ejecución de un comando) de extraer datos de alguna fuente, como bases de datos, archivos de texto, archivos HTML, buzones de correo, etc. Y posteriormente almacenar esos datos en ficheros indexados.
  • Consultas al contenido indexado: Esta parte se puede realizar tanto desde la línea de comandos como desde un script en PHP, Ruby o Python, y es la parte en la que enviaremos (a través de un daemon) una consulta a Sphinx y obtendremos resultados ordenados por relevancia.

Explicado en una frase; Sphinx se va a encargar de sacar datos de “algún sitio” que le indiquemos e indexar esos datos en ficheros, para posteriormente, desde PHP, Ruby, Python o la línea de comandos, lanzar consultas al daemon de Sphinx, que buscará los resultados más relevantes en los ficheros indexados.

¿Qué es SphinxQL?

Como hemos dicho en el punto anterior, podemos enviar consultas al daemon de Sphinx desde diferentes sitios (PHP, Ruby, Python, la línea de comandos, etc), pero también existen diferentes métodos de acceder a éste.

Uno de esos métodos, y además el más recomendado en el manual oficial de Sphinx, es SphinxQL, ya que, cómo dicen en su manual, aparte de que tienen pensado borrar los otros métodos (SphinxAPI y Sphinx SE) algún día, exprime al máximo las características del daemon ofreciendo funcionalidades que los otros métodos no tienen.

SphinxQL es un lenguaje prácticamente idéntico a SQL pero con algunos “añadidos”.

Vamos a ver un ejemplo para entenderlo mejor. Una consulta en SphinxQL podría ser:

SELECT * FROM posts WHERE MATCH('vagrant');

Donde posts es el nombre del índice donde buscar y vagrant lo que queremos buscar, sencillo ¿verdad?

¿Y dónde lanzamos estas consultas SphinxQL? Pues esto es lo mejor de todo, con una sencilla configuración Sphinx es capaz de integrarse con MySQL y podremos ejecutarlas a través de éste.

Es decir, podremos lanzar consultas SphinxQL desde la terminal de MySQL o desde un script de PHP mediante la función mysql_query(). La única diferencia es que nos conectaremos a MySQL a través de un puerto que previamente indicaremos en el fichero de configuración de Sphinx.

Podéis consultar el manual de referencia de SphinxQL para saber más acerca de su sintaxis.

Instalación de Sphinx y MySQL en Ubuntu

$ sudo apt-get update
$ sudo apt-get install mysql-server unixodbc libpq5 sphinxsearch

Para comprobar la instalación ejecutamos:

$ search

Y si todo ha ido bien, nos devolverá información del paquete, su versión y diferentes opciones aceptadas por el comando.

Para obtener más información sobre la instalación o instalar Sphinx en otros sistemas operativos, consultar el manual de instalación oficial de Sphinx.

Crear base de datos de prueba

Como hemos dicho antes, Sphinx necesita de alguna fuente de donde sacar los datos. Para nuestro ejemplo vamos a utilizar la más común, una base de datos.

Y ahora vamos a imaginar que queremos montar un buscador en nuestro blog que devuelva los posts más relevantes. Para ello, necesitamos crear una base de datos llamada blog que contenga una tabla posts:

CREATE DATABASE `blog`;

USE `blog`;

CREATE TABLE `posts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `posts` (`id`, `title`) VALUES
(1,'Añadir clave privada SSH de forma permanente en Mac OS X'),
(2,'Cómo crear y utilizar los módulos de Puppet con Vagrant'),
(3,'Crear un entorno de desarrollo con Vagrant y Puppet'),
(4,'Habilitar enlaces simbólicos en las carpetas compartidas de VirtualBox'),
(5,'Guardar un fichero en VI cuando lo hemos abierto sin permisos');

Configuración de Sphinx

Ahora que tenemos Sphinx instalado y una base de datos, vamos a configurarlo.

Sphinx necesita un fichero de configuración en el que le indiquemos qué tipo de base de datos vamos a usar, como se puede conectar a ella, qué información extraer, etc…

Este fichero lo podemos crear donde queramos, en mi caso (Ubuntu) lo voy a crear en /etc/sphinxsearch/sphinx.conf con el siguiente contenido:

source posts
{
    type                = mysql
    
    sql_host            = localhost
    sql_user            = root
    sql_pass            = root
    sql_db              = blog
    
    sql_query           = \
                            SELECT id, title \
                            FROM posts
                        
    sql_field_string    = title
}

index posts
{
    source              = posts
    path                = /etc/sphinxsearch/data/blog/posts
}

indexer
{
    mem_limit           = 32M
}

searchd
{
    listen              = 9306:mysql41
    log                 = /etc/sphinxsearch/searchd.log
    query_log           = /etc/sphinxsearch/query.log
    pid_file            = /etc/sphinxsearch/searchd.pid
}

IMPORTANTE: Este fichero contiene una configuración con las opciones mínimas pero completamente válido (como veremos más adelante).

Ahora vamos a explicar un poco el contenido del fichero de configuración. Una configuración básica como la nuestra consta de cuatro tipos de bloques.

source nombre

Aquí indicamos a Sphinx cómo puede conectarse a la base de datos (recordad cambiar los datos de conexión a la base de datos por los vuestros) y qué información extraer a través de una consulta SQL. Podemos tener tantos bloques source como necesitemos, uno para posts, otro para usuarios, otro para productos…

IMPORTANTE: La barra invertida \ en la query es una forma de concatenar líneas y es obligatoria menos en la última línea. Si no la ponemos nos dará error al indexar.

MUY IMPORTANTE: Cuando enviemos una consulta a Sphinx, éste va a buscar en la lista de campos indicados en la SELECT de la query SQL.

sql_field_string es una forma de traernos un campo en los resultados ya que por defecto Sphinx solo devuelve los ID´s de los registros encontrados.

Más opciones de configuración del bloque source.

index nombre

En este bloque indicamos cómo indexar la información del bloque source que queramos, y de la misma manera, tendremos tantos como necesitemos.

Por ejemplo, le podremos indicar opciones como:

  • source: La fuente de datos que queremos usar, en este caso posts.
  • path: En qué directorio guardar los documentos indexados, puede ser el que nosotros queramos.
  • min_word_len: Número de caracteres mínimos que debe contener una palabra para ser indexada.
  • ignore_chars: Qué caracteres ignorar.
  • stopwords: Fichero de texto compuesto de palabras (preposiciones o palabras malsonantes) para que no las indexe.
  • etc.

IMPORTANTE: Crear la estructura de directorios indicada en el path.

Más opciones de configuración del bloque index.

indexer

Configuración del indexador de contenidos. Como veremos más abajo, este es el comando que se va a encargar de conectarse a la base de datos, ejecutar la query e indexar los contenidos.

Más opciones de configuración del bloque indexer.

searchd

Esta es la configuración del proceso/daemon que va a estar corriendo en el servidor en el puerto/s indicado/s y al que vamos a enviar las consultas para que busque en los ficheros indexados y nos devuelva los resultados más relevantes.

MUY IMPORTANTE: En la línea listen = 9306:mysql41 estamos indicando el puerto por el cuál nos podremos conectar a Sphinx usando el protocolo de MySQL para que posteriormente podamos usar SphinxQL.

Más opciones de configuración del bloque searchd.

Indexar contenidos y arrancar daemon

Con Sphinx instalado, y la base de datos y el fichero de configuración creados, ya tenemos todo. Ahora solo necesitamos:

  • Indexar los contenidos: Lo haremos (indicando nuestro fichero de configuración creado anteriormente) con el siguiente comando:
    $ sudo indexer -c /etc/sphinxsearch/sphinx.conf --all
  • Arrancar el daemon: Lo siguiente es arrancar el daemon para que podamos empezar a enviarle consultas. Necesitamos ejecutar el comando:
    $ sudo searchd -c /etc/sphinxsearch/sphinx.conf

Y ya tenemos todo listo para poder enviar consultas a Sphinx. A continuación veremos diferentes formas de hacerlo.

Enviar una consulta a Sphinx desde la línea de comandos

$ sudo search -c /etc/sphinxsearch/sphinx.conf "vagrant"

Este comando busca la palabra vagrant en todos los índices del fichero de configuración indicado.

Aquí no estamos usando SphinxQL ya que no nos estamos conectado a través de MySQL.

Enviar una consulta a Sphinx desde la línea de comandos usando SphinxQL

$ mysql -u root -proot -P9306 --protocol=tcp --prompt='sphinxQL> '
sphinxQL> SELECT * FROM posts WHERE MATCH('vagrant');

En este caso, para poder usar SphinxQL lo primero es conectarnos a MySQL a través del puerto indicado en el fichero de configuración de Sphinx. Una vez dentro es como cualquier consola de MySQL, podremos lanzar todas las consultas que queramos.

IMPORTANTE: Indicar el parámetro --protocol=tcp ya que si MySQL detecta una conexión abierta (que sería lo más normal) ignorará el parámetro donde indicamos el puerto.

Enviar una consulta a Sphinx desde PHP usando SphinxQL

<?php

$link = mysql_connect('127.0.0.1:9306', 'root', 'root');
if (!$link) {
    die('No se pudo conectar : ' . mysql_error());
}

$matches = mysql_query("SELECT * FROM posts WHERE MATCH('vagrant')");
if (!$matches) {
    die('Consulta no válida: ' . mysql_error());
}

echo '<pre>';
while ($match = mysql_fetch_array($matches, MYSQL_ASSOC)) {
    print_r($match);
}

?>

Como hemos dicho antes, donde posts es el índice que queremos consultar y vagrant la palabra o frase qué queremos buscar.

IMPORTANTE: En la conexión a MySQL tenemos que indicar el puerto que hemos configurado previamente en el fichero de configuración de Sphinx para poder usar SphinxQL.

Cheat sheet de comandos de Sphinx

Estos son algunos de los comandos más típicos que vamos a necesitar:

Indexar todos los índices (con el daemon searchd apagado)

$ sudo indexer -c /etc/sphinxsearch/sphinx.conf --all

Indexar todos los índices (con el daemon searchd corriendo)

$ sudo indexer -c /etc/sphinxsearch/sphinx.conf --all --rotate

Indexar un solo índice (con el daemon searchd apagado)

$ sudo indexer -c /etc/sphinxsearch/sphinx.conf nombre_del_indice

Indexar un solo índice (con el daemon searchd corriendo)

$ sudo indexer -c /etc/sphinxsearch/sphinx.conf nombre_del_indice --rotate

Arrancar el daemon searchd

$ sudo searchd -c /etc/sphinxsearch/sphinx.conf

Parar el daemon searchd

$ sudo searchd -c /etc/sphinxsearch/sphinx.conf --stop

Conectarse a SphinxQL

$ mysql -u root -proot -P9306 --protocol=tcp --prompt='sphinxQL> '

MUY IMPORTANTE: A la hora de indexar, no hay que olvidarse de indicar en el comando el parámetro --rotate si el daemon searchd está corriendo.

Categorías: MySQL, Sphinx

Monitorización de logs con el stack ELK (Elasticsearch, Logstash y Kibana) » « Editar archivo hosts desde Vagrant

8 Comentarios

  1. Hola, muchas gracias por este tuto que me aclara muchas cosas pero que se queda corto con los propósitos que quiero obtener de MySQL y Sphinx.
    Soy periodista y usuario al 100% de Linux desde hace más de 12 años y quisiera consultarte lo siguiente:
    He descargado miles de archivos .pdf -con wget- de varios sitios y los he alojado en dos o tres carpetas en mi sistema Linux Mint Debian 2 y convertido también a .txt. Tengo también archivos .html, .jpg, .png, etc. ( /home/Informacion/1 / 2 / 3 / … / mas carpetas ) Quisiera utilizar Sphinx como buscador de estos documentos, y más información que iré reuniendo e indexando desde wget para luego procesarla con propósitos meramente informativos. Mi pregunta concreta es:

    ¿Ojalá puedas explicarme más a detalle cómo debo configurar sphinx.conf o cómo se indexan o se meten los documentos alojados en las carpetas a la base o bases de MySQL para consultar desde sphinx? Es decir, utilizar sphinx como buscador de estos miles de documentos.

    Actualmente lo hago desde línea de comandos con grep, pero me gustaría configurar sphinx como buscador semántico, con Freeling y otras herramientas. Ando liado con todo esto del periodismo de datos, buscadores semánticos y este tuto me ha sido de muchísima ayuda.
    Abrazo y muchas gracias, de antemano, por tu respuesta y paciencia.

    • developerlover

      2 julio, 2015 — 10:49

      Hola Bakará!

      lo primero, gracias por tu comentario.

      Como dice el post, Sphinx puede leer de diferentes fuentes. Una de ellas es xmlpipe2, en la que Sphinx va a capturar la salida que produzca la ejecución de un comando.

      Ese comando puede ser cualquier cosa que se te ocurra y que genere una salida. El comando cat, un script de PHP, etc.

      Y la salida debe de ser un XML ya que Sphinx necesita tener una estructura, aunque sea mínima. En tu caso podría contener un único campo que contenga todo el contenido del fichero, ya que imagino que esos ficheros serán de texto sin ningún tipo de formato, ¿no?

      Pero cuéntame un poco más de lo que necesitas a nivel práctico, ¿quieres buscar dentro de los ficheros y que Sphinx te devuelva en qué ficheros ha encontrado tu búsqueda? ¿quieres buscar también en el nombre del fichero? ¿esos ficheros tienen algún tipo de formato?

      Lo más probable es que escriba una entrada hablando sobre este tema ya que me parece que puede ser muy interesante.

      Un saludo!
      Pablo.

  2. Hola Pablo, muchas gracias por tu respuesta y disposición. Estas preguntas que haces me ayudan a construir el proyecto que me trae de cabeza:

    >> Pero cuéntame un poco más de lo que necesitas a nivel práctico, ¿quieres buscar dentro de los ficheros y que Sphinx te devuelva en qué ficheros ha encontrado tu búsqueda? ¿quieres buscar también en el nombre del fichero? ¿esos ficheros tienen algún tipo de formato?

    Te platico, entonces.

    En mi sumersión en este mundo de los buscadores semánticos, las bases de datos, la minería de datos, MySQL, etcétera localicé ayer Solr-Lucene. Lo instalé en mi cacharro (Core i5 -2.30Ghz x 2), 4Gb ram, 600 gb HD) y conseguí indexar 24 mil documentos (pdf, doc, html, jpg y txt) en un proceso que realizó en casi dos horas. Estos documentos previamente los he bajado con wget de distintos sitios y guardado en una carpeta local de mi laptop. Documentos en los que trabajaré para realizar búsquedas en ellos durante investigaciones y trabajos periodísticos en lo posterior. Como tener el viejo archivo que usábamos los periodistas de recortes de periódicos, documentos, estudios y demás cosas que consultábamos continuamente cuando nos poníamos a escribir artículos, reportajes, notas y demás. Es decir, me estoy haciendo de mi archivo personal y necesito consultarlo.

    Bien, mis dudas son muchas. Porque no sé si Solr-Lucene sea la herramienta adecuada para ir desarrollando y que pueda perfeccionar para mejorar mis búsquedas en miles de documentos. Sphinx siento que es más robusto y tiene un mejor desarrollo y más comunidad, pero es más complicado para alguien que tiene conocimientos básicos de programación como yo. Ojalá me des tus puntos de vista sobre esto y una vez que explico paso a responder a las preguntas que me haces para utilizar Sphinx y en una de esas mostramos aquí mis experiencias con Solr-Lucene y Sphinx para que otros puedan consultar y les sirva.

    Quisiera que Sphinx me sirviera en este proyecto concreto:

    Me he descargado todas las recomendaciones de la Comisión Nacional de Derechos Humanos (CNDH, México) desde 1991 a la fecha. Esos documentos (recomendaciones) detallan los casos de abusos, secuestros, homicidios, violaciones y demás lindezas del sótano social de México. Los documentos están en .pdf. Los tengo en una carpeta que tiene la siguiente ruta en mi carpeta local /home/debian/senado/cndh.org.mx

    Para realizar mis búsquedas los documentos .pdf los he convertido a .txt con el comando:
    ls | parallel -I {} pdftotext {}
    Luego realizo búsquedas por palabras con grep, como por ejemplo, escribo homicidios y la shell me muestra diez líneas arriba y diez líneas abajo de donde está “homicidios”. Uno por uno.:
    grep -n -A 10 -B 10 “homicidios” *.txt
    Como verás es muy rudimentario mi método, pero me ha servido. El asunto es que quiero mejorarlo y crear una base de datos, hacer consultas más directas con un buscador como Sphinx o Solr, llamémosle inteligentes y búsquedas semánticas, que me agilice el proceso y entregue resultados más fáciles. Así que vamos a las preguntas de lo qué quiero hacer con esta documentación, en específico:

    Quisiera saber por ejemplo con una pregunta simple tener los resultados apropiados, o bien con varias preguntas obtener los resultados:

    -¿Qué estados de México han tenido más recomendaciones de la CNDH desde 1991?

    -¿Cuáles han sido el tipo de recomendaciones (homicidios, violaciones, secuestros, etc) que más ha habido?

    -¿Quiénes son los afectados directos y de qué estados son?

    -¿Cuántos homicidios hubo en 1991, en 1992, en 1993, etc. cuántos secuestros, cuántas violaciones de derechos humanos, sexuales, casos de corrupción y demás, por año?

    Es decir, necesito construir o instalar un buscador semántico para una base de datos alojada en una carpeta local que me permita hacer todo esto.

    Los documentos (tengo más de otras fuentes) pero de la CNDH mexicana los descargué con el siguiente comando y utilizando wget para ello:

    wget -c -r -l3 -H -t1 -nd -N -np -A.pdf -erobots=off -i cndh.txt

    y en el documento cndh.txt he puesto las siguientes rutas:

    http://www.cndh.org.mx/Recomendaciones
    http://www.cndh.org.mx/Recomendaciones_Generales
    http://www.cndh.org.mx/Recomendaciones_Violaciones_Graves
    http://www.cndh.org.mx/Informes_Actividades
    http://www.cndh.org.mx/Informes_Especiales
    http://www.cndh.org.mx/Agenda_Nacional
    http://www.cndh.org.mx/Acciones_Inconstitucionalidad
    http://www.cndh.org.mx/Casos_Especiales

    Creo que ya quedó un libro de todo esto que te cuento. Obviamente será un caso de periodismo de datos o data journalism que describiré en el manual que desarrollo y sirva para más periodistas y que puedes consultar en periodismolinux.com

    Muchas gracias, Pablo, por tus atenciones, recomendaciones (mientras no sean de la CNDH) y orientación. Saludos.

    • developerlover

      3 julio, 2015 — 15:50

      Por un lado, la verdad es que no he trabajado con Lucene y no puedo valorar si es mejor o peor, pero lo que si tengo entendido es que es un buscador full-text al igual que Sphinx.

      Respecto a lo que necesitas para tu proyecto lamento darte malas noticias.

      No, Sphinx no es un buscador inteligente (tampoco lo son Lucene, Elasticsearch, etc.). Sphinx es un buscador e indexador de texto, pero no es capaz de resolver preguntas como las que propones.

      Al igual que un buscador web, por ejemplo, Sphinx no puede responderte “¿Qué estados de México han tenido más recomendaciones de la CNDH desde 1991?”, pero sí puede localizarte el post, noticia, documento, etc, que contiene información relacionada y ordenarla por relevancia.

      Por otro lado, he estado trabajando en algo parecido a lo que necesitas y publicaré una entrada lo más pronto posible explicándolo.

      En resumen, contaré cómo indexar todos los ficheros de un directorio (recursivamente) en Sphinx con la ayuda de un script en Node.js, de tal forma que cuando se lancen consultas a Sphinx, éste devuelva los ficheros más relevantes.

      Te aviso cuando esté publicado si quieres.

      Un saludo!
      Pablo.

  3. Muchas gracias Pablo por la explicación. Estaré muy al pendiente de tu publicación que comentas. Ya te contaré como van mis avances con Sphinx y Solr-Lucene.

    Siempre he dicho que el mundo de los informáticos y los periodistas es muy distinto. Recuerdo a más de 10 de mis compañeros muy compungidos después de hablar con un informático para “hacer su blog” y a más de 20 amigos informáticos cabreados por el lenguaje raro de los periodistas. Sobre todo ese pedir y pedir. Son idiomas distintos y así lo veo en foros, libros y no se diga en explicaciones técnicas. Me dio mucha risa leer eso del “buscador inteligente” porque cuando utilicé el término pensé que causaría ruido porque sé qué significa para un programador, muy distinto para nosotros, los periodistas. He ahí el dilema. Lo que quise decir es un buscador semántico, así como se explica en el lenguaje estrictamente informático. Olvida, por favor, eso del “buscador inteligente” porque si tuviéramos la fórmula para tener un buscador que responda a las preguntas que planteo, así en una entrega de respuestas del buscador, Google ya hubiera muerto. Y eso no es poca cosa. Lo que quiero es un buscador semántico, que a fuerza de preguntas lógicas y no tan lógicas nos pueda entregar, al menos, respuestas más cercanas a lo que queremos, hasta encontrarlas. Un buscador semántico en el que podamos estar trabajando para mejorarlo y adecuarlo a nuestras necesidades. Eso.

    Un abrazo Pablo y de verdad muchas gracias por tus respuestas.
    Quedo muy pendiente de la publicación que comentas.

  4. Estimado Pablo, soy el CEO de VCoins.com. Veo que controlas Sphinx.
    Nosotros utilizamos Sphinx y estamos muy contentos.
    En estos momentos nos encontramos en un proceso de fine tunning.
    Tengo la sensación que tenemos el Sphinx configurado por defecto y no le estamos sacando todo el jugo.
    La persona que lo controlaba e instaló ya no trabaja con nosotros.
    Estoy buscando a alguien que pueda interesarle hacernos una consultoría o ayuda puntual con este tema.
    Puedes contactarme? Muchas gracias, Ernesto

Deja un comentario

Your email address will not be published.

Copyright © 2017 developerlover.com

Up ↑