# Sync Elasticsearch with MySQL Database **URL:** \ **Category:** Elasticsearch\ **Created:** [May 24, 2020, 8:08am UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032 "2020-05-24T08:08:33Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![Punj\_Shah](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/punj_shah/32/68873_2.png) [@Punj\_Shah](https://discuss.elastic.co/u/Punj_Shah)\ **Post date:** [May 24, 2020, 8:08am UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032/1 "2020-05-24T08:08:34Z")
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](https://us1.discourse-cdn.com/elastic/original/3X/a/d/ad35d2184175157dcadd7cbfc4713a8ef5afa0f7.png) Now we need to apply the below things 1. Elasticsearch will contain Index with Denormalised form, **something like below** ```auto { "shops": [ { "shop_id": 1001, "shop_name": "XYZ Pharmacy", "shop_type": "Pharmacy Store", "latlong": { "lat": 12.345678, "lon": 12.345678 }, "timing": "8:00 to 8:00", "working_days": "Mon - Sun", "products": [ { "product_id": 201, "product_name": "AAAAA 10 mg", "product_tag": [ "Drug", "Strip", "XXXX" ] }, { "product_id": 202, "product_name": "BBBBB 20 mg", "product_tag": [ "Syrup", "Cough", "XXXX" ] } ] }, { "shop_id": 1002, "shop_name": "ABC Fastfood", "shop_type": "Fastfood", "latlong": { "lat": 12.345678, "lon": 12.345678 }, "timing": "8:00 to 8:00", "working_days": "Mon - Sun", "products": [ { "product_id": 302, "product_name": "CCCC", "product_tag": [ "Sandwich", "Wrap", "XXXX" ] } ] } ] } ``` 1. If there's any change (Insert, Update) either in Shop or Product Table of Mysql it should sync into Elasticsearch. 2. We will follow timestamp with is\_updated, is\_deleted field for syncing. But the problem is, - How to denormalize the Index? - How to configure to detect any changes in Shop, Product\_Category, Category or Product table in MySQL and sync with ES. **Usecases** 1. User can search by Product Name, 2. Product Category 3. Shop Name 4. Shop Type We highly appreciate your best advice on this. Thanks in Advance! ---
**Author:** ![Rahul\_Kumar4](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rahul_kumar4/32/67369_2.png) [@Rahul\_Kumar4](https://discuss.elastic.co/u/Rahul_Kumar4)\ **Post date:** [May 24, 2020, 11:01am UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032/2 "2020-05-24T11:01:39Z")
> [@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](https://www.elastic.co/blog/how-to-keep-elasticsearch-synchronized-with-a-relational-database-using-logstash) and for denormalizing you can use a `JOIN` between those two tables in your `statement` option of the [JDBC input](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-statement) plugin. ---
**Author:** ![Punj\_Shah](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/punj_shah/32/68873_2.png) [@Punj\_Shah](https://discuss.elastic.co/u/Punj_Shah)\ **Post date:** [May 24, 2020, 11:07am UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032/3 "2020-05-24T11:07:39Z")
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 ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [May 24, 2020, 11:22am UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032/4 "2020-05-24T11:22:27Z")
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 be transformed into a number of distinct documents like this that consist of data from all tables: ```auto { "shop_id": 1001, "shop_name": "XYZ Pharmacy", "shop_type": "Pharmacy Store", "latlong": { "lat": 12.345678, "lon": 12.345678 }, "timing": "8:00 to 8:00", "working_days": "Mon - Sun", "product_id": 201, "product_name": "AAAAA 10 mg", "product_tag": [ "Drug", "Strip", "XXXX" ] } ``` You can store this with a document ID created from shop\_id, product\_id and potentially also category\_id. If you add a timestamp field to this query that is the maximum of the modified\_on fields from the tables you will be able to identify which documents that have changed and update just these. This means that some information is duplicated across documents, but that will provide you will simpler querying and maintenance, so is often a worthwhile tradeoff. This is an example of how you often need to step away from a relational thinking when working with Elasticsearch. ---
**Author:** ![Punj\_Shah](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/punj_shah/32/68873_2.png) [@Punj\_Shah](https://discuss.elastic.co/u/Punj_Shah)\ **Post date:** [May 24, 2020, 11:58am UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032/5 "2020-05-24T11:58:48Z")
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 change in join table, I think JDBC plugin will not listen to it. I think JDBC plugin can listen to only field for any changes? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [May 24, 2020, 12:07pm UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032/6 "2020-05-24T12:07:55Z")
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 accordingly. The Logstash JDBC plugin records the last time it ran and will find documents having a newer timestamp and update these every time it runs. ---
**Author:** ![Punj\_Shah](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/punj_shah/32/68873_2.png) [@Punj\_Shah](https://discuss.elastic.co/u/Punj_Shah)\ **Post date:** [May 24, 2020, 12:14pm UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032/7 "2020-05-24T12:14:58Z")
@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. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [May 24, 2020, 12:21pm UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032/8 "2020-05-24T12:21:10Z")
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 list: ```auto 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; ``` Updates get a bit more expensive as many documents need to be updated if details of shops and categories change, but I would assume this is a reasonably rare thing so it will just add load at times. These updates are likely to be done in bulk anyway which is quite fast and efficient. ---
**Author:** ![Punj\_Shah](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/punj_shah/32/68873_2.png) [@Punj\_Shah](https://discuss.elastic.co/u/Punj_Shah)\ **Post date:** [May 24, 2020, 12:23pm UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032/9 "2020-05-24T12:23:02Z")
> [@Christian\_Dahlqvist](#): > > ```auto > 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 you on how it works. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [May 24, 2020, 12:24pm UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032/10 "2020-05-24T12:24:22Z")
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. ---
**Author:** ![Punj\_Shah](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/punj_shah/32/68873_2.png) [@Punj\_Shah](https://discuss.elastic.co/u/Punj_Shah)\ **Post date:** [May 24, 2020, 12:35pm UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032/11 "2020-05-24T12:35:24Z")
> [@Christian\_Dahlqvist](#): > > 🙂 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 🕶. ---
**Author:** ![Punj\_Shah](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/punj_shah/32/68873_2.png) [@Punj\_Shah](https://discuss.elastic.co/u/Punj_Shah)\ **Post date:** [May 25, 2020, 1:53pm UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032/12 "2020-05-25T13:53:19Z")
@Christian_Dahlqvist , I implemented the logic exactly as we discussed. But the problem is, it is creating a new Document everytime it finds an updated value of last\_modified\_on instead of updating the document. Due to this, ie, even if the product name is changed or say product is deleted; even though it's deleted or updated the product with new name and old name or deleted it will return in the JSON list. Would you please guide me on this? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [May 25, 2020, 2:21pm UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032/13 "2020-05-25T14:21:04Z")
Select fields that uniquely identifies a document and use this as a document\_id in your Elasticsearch output. ---
**Author:** ![Punj\_Shah](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/punj_shah/32/68873_2.png) [@Punj\_Shah](https://discuss.elastic.co/u/Punj_Shah)\ **Post date:** [May 25, 2020, 3:09pm UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032/14 "2020-05-25T15:09:09Z")
Then I think I need to maintain Composite Key as relation is One-Many. Let me check. Thanks for immediate kind response. ---
**Author:** ![Punj\_Shah](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/punj_shah/32/68873_2.png) [@Punj\_Shah](https://discuss.elastic.co/u/Punj_Shah)\ **Post date:** [May 26, 2020, 7:54am UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032/15 "2020-05-26T07:54:33Z")
Hello @Christian_Dahlqvist, MySQL database has two double type fields latitude and longitude for location. But how to map it to elasticsearch in one field as a location with datatype geo\_point using JDBC Plugin. To achieve similar to below, ```auto "location" : { "lat" : 23.026313, "lon" : 72.553062 } ``` Thanks in Advance! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [June 23, 2020, 7:54am UTC](https://discuss.elastic.co/t/sync-elasticsearch-with-mysql-database/234032/16 "2020-06-23T07:54:35Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.