How to synchronize MySQL Database with ElasticSearch And perform data querying in a Spring Boot Application
Tags: spring, mysql, elasticsearch, docker
Published: 2020-06-21 16:52:05

Sometimes the need for using an advanced searching mechanism such as ElasticSearch is not mandatory at the beginning of a project, but as it grows with time this need comes to the surface.

In order to satisfy this technical need and without altering the existing project’s architecture, we can implement a logic to synchronize the database data and index it in ElasticSearch and perform complex querying without impacting the performance of the database in production.

Let’s dive into it and explore how we can implement this …

> **Disclaimer**: I will share only the important code snippets in order to keep the reading quick, you can check the link to the dedicated repo for this article on GitHub at the end.

* * *

# Setting up the project

First of all, let's create a simple spring boot application with a MySQL database.

> Tip: you can use [Spring Initializr](https://start.spring.io) to set up the application's skeleton structure

## Project configuration

In order to interface with ElasticSearch we need to do the following :


1. Add the **_data-elasticsearch_** dependency to the _pom.xml_ 

{% gist https://gist.github.com/yassinesabri/f745630577f59705e197efa57df30196 %}

> Tip: the version of the engine depends on spring-jpa dependency used within the project (see the [compatibility matrix](https://docs.spring.io/spring-data/elasticsearch/docs/3.2.0.RC3/reference/html/#preface.versions))

2\. Add the following properties to the _application.properties_

{% gist https://gist.github.com/yassinesabri/dcc8a5313c6004fd2b40aa1475b38b6f %}

* _spring.data.elasticsearch.cluster-name: _the cluster name, CLUSTER_NAME is an environment variable that we use if we're running the application in a docker container
* _spring.data.elasticsearch.cluster-nodes:_ the cluster nodes
* _spring.data.elasticsearch.repositories.enabled:_ to enable repositories so we can use JPA interfaces with ElasticSearch.

3\. Define the package's path where the elastic's repositories reside

{% gist https://gist.github.com/yassinesabri/1bcae05770d35708e1d9eb94a6b2aecf %}

In our example it's "com.example.springmysqlelastic.repo.elastic".

## Project structure

For demonstration purposes, we're going to create a single table in our database and wrap it in its own index. In a real project, we will have a much complex schema and indexes to create.

* Create a User entity with first name and last name attributes

{% gist https://gist.github.com/yassinesabri/b431fc773822bed981f05a4d6ab0df0a %}

* Create a model class to represent our User index within ElasticSearch

{% gist https://gist.github.com/yassinesabri/429407db01d9fee4052b0f9b9a9ed014 %}

* Create a user repo and extend it from [_ElasticsearchRepository_](https://docs.spring.io/spring-data/elasticsearch/docs/current/api/org/springframework/data/elasticsearch/repository/ElasticsearchRepository.html)

{% gist https://gist.github.com/yassinesabri/c5c0c50f2219951bba1407fad3dbd157 %}

We're done settings up the main components for our app.

* * *

# Synchronize data with ElasticSearch

In order to synchronize our data from MySQL with the user index in ElasticSearch, we're gonna be using a Scheduler in which we will implement the synchronization logic.

First of all, we need to enable Spring's scheduled task execution capability by adding the [_EnableScheduling_](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/scheduling/annotation/EnableScheduling.html) annotation :

{% gist https://gist.github.com/yassinesabri/bd12de213da59c13fda9552a908618c7 %}

After that and since we can't sync all the records from the database each time the scheduler is running, we will add a modification date attribute to our user entity and use the [_UpdateTimestamp_](https://docs.jboss.org/hibernate/orm/4.3/javadocs/org/hibernate/annotations/UpdateTimestamp.html) annotation to delegate the updating process of its value to Hibernate each time the entity will be changed.

{% gist https://gist.github.com/yassinesabri/7af2fe272a27d2ced8084af95ddbe16e %}

Next, we need to create our scheduler service.

{% gist https://gist.github.com/yassinesabri/1e956601e42e7deb1a03389e06188497 %}

In this example, the scheduler will run every _3 minutes_, this period will change from a project to another depending on the importance of synched data.

Each time the main method of the scheduler is executed, we will sync our users.

{% gist https://gist.github.com/yassinesabri/8ff65b387b424752e139f64a8931da06 %}

{% gist https://gist.github.com/yassinesabri/fd48770f8ae1e994f56f22523feb00de %}

In our **_syncUsers()_** method, we have two scenarios :

* When the index is empty at the first launch of the application, we will send all the user records to ElasticSearch
* Otherwise, we retrieve only the records that have been changed within the last **_INTERVAL_IN_MILLISECONDE_** starting from the time of the execution of the scheduler (as explained in the _getModificationDatePredicate _method above_)._

We're done with the synchronization, let's move on to the next part.

* * *

# Querying data from ElasticSearch

We will start by adding a new rest Endpoint to take the search query as input and return the result as a [ResultQuery](https://github.com/yassinesabri/spring-mysql-elastic/blob/master/src/main/java/com/example/springmysqlelastic/utils/ResultQuery.java) object that will create later on.

{% gist https://gist.github.com/yassinesabri/6ae50e44d401cd5580351782edfd478f %}

After that, we will create a search service where we will build our custom request and retrieve data from ElasticSearch.

{% gist https://gist.github.com/yassinesabri/b778bced69178d5a694dc3ddfb2870fd %}

In our main function of the service _searchFromQuery() _we do the following :

* First, we build the body of the request, in our case, we will create a multi-index search using the [query_string](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-query-string-query.html) function and add the list of fields where we want the engine to look for the giving query.

The body of the request will look something like this:

{% gist https://gist.github.com/yassinesabri/dda4c961eb5db8b361ab811431ef69db %}

> The fields of the query-string are a static list in the code, but they can be parametrized in the application to have more control and flexibility.

* Second, we will execute the post request with the body that we created earlier and wrap the response from ElasticSearch in the [ResultQuery](https://github.com/yassinesabri/spring-mysql-elastic/blob/master/src/main/java/com/example/springmysqlelastic/utils/ResultQuery.java)

{% gist https://gist.github.com/yassinesabri/7a1075bd7e312962fa983e32b7df5a90 %}

Our **_ResultQuery_** class contains three main attributes :

{% gist https://gist.github.com/yassinesabri/4fc9378f9578de9bc8bcd2e4dde1a754 %}

* _timeTook: ElasticSearch response time in seconds_
* _numberOfResults: the number of total elements retrieved_
* _elements: a _stringified JSON that represents the total hits found.

# Testing the application

To run the application you can either do it locally or in a Docker container. (visit the [_README_](https://github.com/yassinesabri/spring-mysql-elastic/blob/master/README.md) file for more info).

I added some records to the database and synced them to the user index.


![User index’s records](https://miro.medium.com/max/1400/1*ctS71UWEdnb2bewB6ZSfAA.png)


> Tip: you can visualize the index content using the [**_ElasticSearch Head_**](https://chrome.google.com/webstore/detail/elasticsearch-head/ffmkiejjmecolpfloofpjologoblkegm) extension in Chrome/Mozilla browsers

As an example, let's look for the users that their first names or last names contain **_"est" _**:


![Query response](https://miro.medium.com/max/1400/1*srMZyYoXdHG80opN0SSJPg.png)


**_Voilà, _**we've found 2 records that satisfy our criteria in _0.017 ms_.


### **That's all folks!**

* * *

Learn more by:

* Visiting the Github [**repo**](https://github.com/yassinesabri/spring-mysql-elastic) to see the full code of the application.
* Visiting the ElasticSearch [**documentation**](https://www.elastic.co/guide/index.html) for more complex querying syntaxes.
* For Medium fans you can read the article [**there**](https://medium.com/@yassine.s.sabri/how-to-synchronize-mysql-database-with-elasticsearch-and-perform-data-querying-in-a-spring-boot-829ff7717380).