Sync data from two mysql tables with single Elasticsearch Index using Logstash

user_2577302 0
Viewed 246

I have two mysql tables that I want to save into a single Elastic index.

Table 1: Product has id, name ... etc.

Table 2: ProductWarehouse has productId, warehouseId, qtyAvailable ... etc

Product one-to-many ProductWarehouse

My question is, how can I save ProductWarehouse records for each product as an array?

If ProductWarehouse was modified, how to reflect that on the Elastic index?

What is the best practice?

the Product index in Elastic should look like:

{
 "id",
 "name": ...,
 "warehouses": [
   {"warehouseId": 1, "qtyAvailable": 3},
   {"warehouseId": 10, "qtyAvailable": 30}
 ]
}
logstash elasticsearch mysql
1 Answers

My suggestion is to create a MySQL view of the structure you need: Product joins with ProductWarehouse. This view just to use in Sync and then make the Sync from this view to ElasticSearch using Logstash.

Then you can use the ElasticSearsh Index as this structure:

{
 "id",
 "name": ...,
 "warehouses": 1
}
{
 "id",
 "name": ...,
 "warehouses": 3
}
user_27065660
Related