How to use Elastic Search on top of a pre-existing SQL Database?

Question

I've been reading through a lot of good documentation about how to implement Elastic Search on a website with javascript or PHP.

Very good introduction to ES.

Very complete documentation here and here.

A whole CRUD.

Elastic search with PHP: here, here, and here.

So the reason why I'm giving you those URLs is to understand how to use one or many of those great documentations when having a pre-existing SQL DB.

I'm missing the point somewhere: As they said Elasticsearch will create its own indexes and DB with MongoDB, I don't understand how can I use my (gigantic) database using SQL? Let say I have a MySQL DB, and I would like to use Elasticsearch to make my research faster and to propose the user pre-made queries, how do I do that? How does ES works over/along MySQL? How to transfer this gigantic set of Datas (over 8GB) into ES DB in order to be fully efficient at the beginning?

Many Thanks

Answer 1

I am using jdbc-river w/ mysql. It is very fast. You can configure them to continually poll data, or use one-time (one-shot strategy) imports.

e.g.

curl -xPUT http://es-server:9200/_river/my_river/_meta -d ' {     "type" : "jdbc",     "jdbc" : {         "strategy" : "simple",         "poll" : "5s",         "scale" : 0,         "autocommit" : false,         "fetchsize" : 10,         "max_rows" : 0,         "max_retries" : 3,         "max_retries_wait" : "10s",         "driver" : "com.mysql.jdbc.Driver",         "url" : "jdbc:mysql://mysql-server:3306/mydb",         "user" : "root",         "password" : "password*",         "sql" : "select c.id, c.brandCode, c.companyCode from category c"     },     "index" : {         "index" : "mainIndex",         "type" : "category",         "bulk_size" : 30,         "max_bulk_requests" : 100,         "index_settings" : null,         "type_mapping" : null,         "versioning" : false,         "acknowledge" : false     } }'