Understanding ElasticSearch with MySQL

Hello,

I am trying to add elastic search to a eCommerce website based on php and
MySQL platform and spend today trying to research on elastic search. I
learnt alot and have a fairly good idea on what to do. I just wanted some
guidance if my understanding of elastic search with MySQL is correct. From
what i gathered:

  1. Get elasticsearch, jdbc river, MySQL driver for jdbc river
  2. Run elasticsearch server on terminal
  3. Create jdbc riverusing curl command on a new terminal
  4. Feed the data from mysql to elasticsearch. This will ensure that all
    data will also be available on elasticsearch from our database and
    hopefully by setting parameter autocommit to true, everytime MySQL is
    updated, elasticsearch server will also be updated

Assuming this is also hopefully correct, my question is how can i use PHP
to get the search query from front-end and get results from elasticsearch
server? I still have issues understanding this concept. Most of the
examples that i saw were either using PHP with documents-based storage or
using curl commands for searching. I want to know how i can use PHP to get
results of our search using elasticsearch.

I went through
https://github.com/jprante/elasticsearch-river-jdbc/wiki/_pages and many
other resources online and have a pretty good idea about elasticsearch. I
just need a few more things to figure out.

This is the very first time that I have read on elasticsearch and i really
like the search engine and would like to add this to the website. Would
really really appreciate some guidance on this.

Usman Ehtesham Gul

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/89988ecc-5a1e-4d3b-8ff9-26835764b33b%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Congrats for deciding to start indexing RDBMS data with Elasticsearch, I
hope I can be helpful.

To add to 4) - you can craft a select query (or queries) to either
overwrite old docs in ES or add timeframe based incremental updates to ES.

Autocommit is not related to updates in ES. In fact there are no automatic
update. With a crontab notation, the river can start periodically and fire
a series of SQL statements, in the hope to select all the data for indexing.

JDBC river can generate nested JSON docs out of SQL result rows by column
name notation, so there is not necessarily a 1:1 relationship between rows
in the DB and docs in ES.

Quite a few instructional docs in the JDBC river wiki are missing, I'm
sorry for that, they will be added soon. If you like to write your
experiences or about how to use the JDBC river, you can drop me a note or
add it to the JDBC river wiki.

You can use PHP standard ES client to search and to administrate the
cluster, from then on, there is no difference to regular ES applications.

Jörg

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGhN%2B9bkE%3D8D3pX%2BD1avXKH4BuJi5JUU6PZSkHqYPY%2B0A%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.

FIrst of all thank you so much for the detailed reply. A few questions just
so that i am on the right track about the suggestions you gave:

  1. When you suggested we can craft a select query to add time based
    incremental updates to ES, would that add new records from the DB to ES?
    Would this me done using the river at a given poll or using a cronjob?

  2. the river runs only once? so if we have to run the curl command to run
    the river periodically, we have to use a cronjob? will the ES server always
    be runnig during this time? we dont have to disconnect and connect again,
    right?

  3. For using PHP ES client, whenever a user searches for something, we will
    connect to the ES client and pass the search parameters into PHP ES api
    functions? if yes, what is ur opinion on using Elastica for this?

Thanks again :slight_smile: Hopefully after getting more info from these questions, i
can start with adding Elasticsearch to the eCommerce website :smiley:

On Tuesday, February 4, 2014 7:59:27 PM UTC-5, Jörg Prante wrote:

Congrats for deciding to start indexing RDBMS data with Elasticsearch, I
hope I can be helpful.

To add to 4) - you can craft a select query (or queries) to either
overwrite old docs in ES or add timeframe based incremental updates to ES.

Autocommit is not related to updates in ES. In fact there are no automatic
update. With a crontab notation, the river can start periodically and fire
a series of SQL statements, in the hope to select all the data for indexing.

JDBC river can generate nested JSON docs out of SQL result rows by column
name notation, so there is not necessarily a 1:1 relationship between rows
in the DB and docs in ES.

Quite a few instructional docs in the JDBC river wiki are missing, I'm
sorry for that, they will be added soon. If you like to write your
experiences or about how to use the JDBC river, you can drop me a note or
add it to the JDBC river wiki.

You can use PHP standard ES client to search and to administrate the
cluster, from then on, there is no difference to regular ES applications.

Jörg

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/72afeb41-f2fd-482c-a2f8-d8d6c727a607%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.