Denormalize and Index MySQL database with ElasticSearch and Kafka

Overview

• This example demonstrates how we can create a real time search engine for entities in an SQL database using Change Data Capture.
• The same mechanism can be applied to a NonSQL database like MongoDb or to manage a cache database.
• This example relies on Debezium Connector for MySQL and Kafka Connect.
• The microservices was developed using Spring boot 2.

Donations

If you find my work useful, consider donating to support it :)

Ethereum

You can simply scan this QR code to get my Ethereum address

Architecture

Running the example

Requirements

This example requires:

• JDK 1.8
• Docker >= 17
• Docker-compose >= 3.6

Steps

Start by building the Spring projects using

sh build.sh

Next, run the containers Command, MySQL, Kafka, Zookeeper, Kafka Connect using

docker-compose up --build -d mysql user-command kafka-connect

Note: You can start all the project containers but this costs a lots of memory. You can simply use

docker-compose up --build -d

After the successful start of the containers, activate the Kafka connector by running the command

sh activate_connector.sh

If there are entities in the database Debezium will create a Snapshot.

When the connector is successfully added, you can add an entity by running the command

sh test_command.sh

Add, update and remove endpoints was implemented in the Command Microservice but this will not be documented in this tutorial you can try them by yourself. After running the command it's possible to stop this Microservice and MySQL container. User Command will insert an entity into MySQL. Debezium will capture the changes and send events to kafka.

When the new entity is added, start the Denormalizer Microservice and ElasticSearch. The Denormalizer will process the events received from Kafka and apply changes in ElasticSearch.

docker-compose up --build -d user-denormalizer elasticsearch

Note: It's possible also to stop MySQL and User Command if you don't plan to test some other operations

docker-compose stop user-command mysql

Check if the events were successfully processed by reading the logs of the Denormalizer. You will find the message EventDispatcher.handleEvents took : x milliseconds

To verify that everything is working start the Query Microservice and send a request to retrieve the entities. Use the commands

docker-compose up --build -d user-query
sh test_query.sh

Note: It's possible also to stop Denormalizer

docker-compose stop user-denormalizer

You can acheive a realtime indexing and denormalizing by running all the containers in the same time and launching the same test commands in the tutorial for example

sh test_command.sh
sleep 3 # the process takes usually 2-3 seconds
sh test_query.sh