Take snapshot every day of Mysql datatbase in ElasticSearch

Elastic Stack Elasticsearch
1

Hello.

I have a Mysql application. But we want to do some analysis on the data that is being stored. In the Mysql database, the data is being overriden everyday. But we want to store everyday's dump in ElasticSearch so that we can use that data for plotting graphs or do anamolies detection.

Right now, I am not sure about which data model technique I should use to migrate the data.
There are two approaches so far:

  • application joins:
    Vehicle
    a
    b
    c
    ownershipTYpeId : 1
    vehicleTypeId: 2
    companyId: 1

VehicleTYe
id
name

ownershipTYpe
id
name

company
id
name

Which means I will create an index Vehicle, and store ids in it just like the RDBMS structure.
But here if I want to search instead of id field, I have to run separate queries for fetch id for each relation.

Another approach is:

Denormalization:
Vehicle
a
b
c
ownershipTYpe : {
id: 1
name: abc
}

I will store the data of the relational entities in the form of an object in the Vehicle index.
I am not sure which approach will be better for graph plotting and other analysis in the future.

Does anyone has any experiences in this topic?

Thanks a lot!

2

Hi @Anujeet,

My first attempt would be to Denormalize the data, then you can do a single query.

If all of your entries are updated every day, you might consider creating a daily index

Hope this helps!

3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.