9

I want to use elasticsearch-river-mysql in order to continuously transfer data from MySQL database to ElasticSearch. I'm beginner with ES and rivers so I hope you can help me out with my questions.

  1. From what I know, the data will be streamed from the MySQL database to the ES cluster which will index it automatically. Is that correct? Are there any timeouts or limits I have to be aware of?
  2. How the foreign key relations between the relational database tables will be translated into ES? Will the table row containing the foreign key become an inner object for an ES document or some other relation between the ES documents will be used?
  3. Are there any disadvantages in using this river for the mentioned above purpose?
  4. What will be the load on ES by implementing this? I assume that ES is powerful enough, but still I was wondering. Will the searches made on the ES cluster be affected anyhow in this scenario?
Improve this question
8
  • How many entries do you have in your MySQL database? Commented May 14, 2014 at 21:07
  • My advice already is to try to use the elasticsearch-jdbc-river for many reasons. One of them is that the elasticsearch-jbdc-river is more generic in case you decide to switch RDBMS and another is that the jbdc-river is still maintained when the other one hasn't been since 2 years, and Elasticsearch evolved a lot ever since Commented May 15, 2014 at 7:49
  • Here are some statistics about our database: total number of entries is 919161598, total number of tables is 314, total size 258,5GB, the biggest table contains 121942658 entries and is of size 25GB. So one really huge database. We have a lot of pick sales addind continuously new data to the database (not conting the new added features to the system). The growth of the database is ~10GB per month. You can see why we start to experience some performance issues when executing heavy and complex queries (especially the one done for reporting). Commented May 15, 2014 at 7:51
  • @eliasah thanks for the advice. Does the elasticsearch-jdbc-river support MySQL replication stream? Commented May 15, 2014 at 7:54
  • I'm not sure about what you mean by MySQL replication stream. Commented May 15, 2014 at 8:03

1 Answer 1

Reset to default
7

My advice already is to try to use the elasticsearch-jdbc-river for many reasons.

One of them is that the elasticsearch-jbdc-river is more generic in case you decide to switch RDBMS.

Another is that the jbdc-river is still maintained when the other one hasn't been since 2 years, and Elasticsearch evolved a lot ever since.

1. From what I know, the data will be streamed from the MySQL database to the ES cluster which will index it automatically. Is that correct? Are there any timeouts or limits I have to be aware of?

The data from MySQL should be streamed automatically from MySQL to the Elasticsearch cluster without a timeout limitation but the bottleneck will be your JVM Heap Size. I'm not sure how much do you need to process the amount of data you have. You need to test it.

2. How the foreign key relations between the relational database tables will be translated into ES? Will the table row containing the foreign key become an inner object for an ES document or some other relation between the ES documents will be used?

Elasticsearch is schemaless so you need to manage to the inside Elasticsearch. The river just streams the data into your cluster. You can define your mapping when you create your index and then use the river to stream it into the ES cluster.

3. Are there any disadvantages in using this river for the mentioned above purpose?

The river will be replaced with another cleaner way to stream these data but this is the best solution you have for now.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Which alternatives/cleaner ways are there in development? Seems like this river is still actively maintained and here to stay, right?
I think about Logstash maybe. I'm not sure. I haven't tried that approach yet. But I think that you might be able to use Logstash to forward data flow from your database toward a elasticsearch cluster

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.