Auto importing of data from mysql to solr

Question

I want to import values from mysql to solr.. I did automatic import by calling a php script

using mysql trigger. But i read that its not a good method.. Is there any other solution for

importing data automatically?

Can someone help me plzz...

Answer 1

Even though there is a built in mechanism for this very thing, Data Import Handler (DIH), as mentioned in the other responses, I found this tool not very flexible. What I mean by this is, if I wanted to do any data massaging before indexing I could only depend on MySQL functions, when I could have used PHP functions.

I ended up writing my own Data Import Handler as a PHP script, where it does the initial query, then steps through the results and massages (and caches) data upon insert into the SOLR index. It wasn't too complicated, and would look something like (demonstrative only):

SELECT 
  book.id AS book_id,
  book.name AS book_name,
  GROUP_CONCAT(DISTINCT author.name) AS authors
FROM
  book
INNER JOIN
  link_book_author AS alink ON alink.book_id = book.id
INNER JOIN
  author ON author.id = alink.author_id
GROUP BY
  book.id;

$stmt = $dbo->prepare($sql);

$stmt->execute();

while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {

    try {

        $document = new Apache_Solr_Document();

        $document->Id = $row->book_id;
        $document->BookName = $row->book_name;

        $document->Author = explode(',' $row->author);

        $this->getSearchEngineInstance()->addDocument($document);

    } catch (Exception $e) {

        error_log(sprintf('Unable to add document to index: (%s)', $e->getMessage());
    }
}

This is just an example of what you can do, In my situation I also involve caching to increase performance when I do a full import. Something you cannot do using the native DIH.

The API I use to access SOLR through PHP is solr-php-client, there may be others out there, so google around.

Answer 2

Solr DataImportHandler would help you the import from the data from mysql and get it indexed.
It provides ability to Full index and well as Incrementally index the data.
However, it would not be automatic and you need to fire the data imports through a scheduler, cron. There are some options being worked upon for Scheduling it.

Answer 3

You could also use the Solr Data Import Handler