Hello! As per our application requirement, we need to sync Elasticsearch with MySQL. We will apply the best optimal way according to your advice. Below is the DB EER Diagram just for the reference [DB_1] Now we need to apply the below things Elasticsearch will contain Index with Denormalised …

[image] Punj_Shah: How to denormalize the Index? How to configure to detect any changes in Shop or Product table in MySQL to sync with ES. There is a good blog post about that here and for denormalizing you can use a JOIN between those two tables in your statement option of the JDBC input pl…

Dear Rahul Kumar, Thank you very much for your prompt response. Sorry I was just editing my earlier post and before I finish editing, I got your response Could you please throw your sight once again on my edited query

How you structure this will depend on how you expect to search the data and how you want the results back. If you primarily are searching for products and want to filter based on shop details and categories I would recommend looking into fully flattening when you denormalize. Your example would then…

Hello @Christian_Dahlqvist , Thanks for the reply. How is it possible to have one-many without relating to each other? If theres any way pls suggest me. I can managed to do One-One by putting the value of itself in the table rather then referencing it. If I use, join query and if there is any cha…

Perform a full join across all tables and store each record as a single document in Elasticsearch. If you include a single field that is the maximum of the modified timestamp for all 4 included tables the JDBC query should identify any document affected by an update and update Elasticsearch accordin…

@Christian_Dahlqvist I was also thinking of a similar solution as you suggested. But I hope this will not create performance issues and there will be a repetition of fields.

Yes, there will be a repetition of fields, but Elasticsearch is quite good at compressing that. It does make handling updates quite easy and is ideally suited for efficiently searching for products. The query would be structured something like this, although you may want to add the categories as a l…

[image] Christian_Dahlqvist: SELECT t.type_name, s.shop_id, s.shop_name, p.product_id, p.name, GREATEST(t.modified_on, s.modified_on, p.mofified_on) as last_modified_on WHERE s.shop_id = p.shop_id AND s.shop_id = t.shop_id; Thanks again, you are super quick. Let me give a try and will update…

I stripped it down to the base minimum as I have not written a lot of SQL lately. Hopefully it is a useful starting point though.

[image] Christian_Dahlqvist: :slightly_smiling_face: I executed the query and at first glance it seems all okay. Now, let me try with logstash, for me logstash is completely new though. If I need any help will bother you again :sunglasses:.

Sync Elasticsearch with MySQL Database

Elastic Stack Elasticsearch

Rahul_Kumar4 (Rahul Kumar) May 24, 2020, 11:01am 2

There is a good blog post about that here and for denormalizing you can use a JOIN between those two tables in your statement option of the JDBC input plugin.

Topic		Replies	Views
Strategy for keeping Elasticsearch updated with MySQL Elasticsearch	5	5445	July 6, 2017
Integrate elastic search with MySQL Elasticsearch	3	484	January 16, 2019
How to make the Elasticsearch crud operation to be reflected in mysql database Elasticsearch	7	1981	July 24, 2018
Mysql to Elasticsearch Sync with Logstash & input JDBC plugin Logstash	5	2033	January 10, 2019
Realtime Sync MySQL Database to elasticsearch Elasticsearch	3	4131	August 14, 2019

Sync Elasticsearch with MySQL Database

Related topics