How to Connect Elasticsearch to MySQL Integration in 3 Easy Methods

Every data operation uses ETL (extract, transform, and load) processes to load data in and out of storage. A wide range of ETL tools is available that can work easily with Elastic Search. 

Don’t worry, we have you covered. In this blog post, you will see what Elasticsearch does and why it is beneficial to move your data from Elasticsearch to MySQL using 3 easy methods.

Methods to Connect Elasticsearch to MySQL

• Method 1: Using Hevo Data to Connect Elasticsearch to MySQL
• Method 2: Using CRUD Queries to Connect Elasticsearch to MySQL
• Method 3: Using Logstash to Connect ElasticSearch to MySQL

Method 1: Using Hevo Data to Connect Elasticsearch to MySQL

• Hevo Data, a No-code Data Pipeline, helps to transfer data from 150+ data sources(40+ free sources) including Elasticsearch to MySQL and visualize it in a BI Tool.
• Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code.
• Its fault-tolerant architecture ensures that the data is handled securely and consistently with zero data loss.

Pro Tip:- Hevo supports data replication using MySQL Change Data Capture. Get up-to-date data in your warehouse in minutes without impacting your production database.

Hevo Data offers you seamless data migration from Elasticsearch to MySQL in 2 very simple steps:

• Authenticate Source: Authenticate and configure your Elasticsearch account as the data source. T

• Configure Destination: Connect the MySQL Database as the destination.

Check out what makes Hevo amazing:

• Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
• Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
• Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
• Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
• Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.

Method 2: Using CRUD Queries to Connect Elasticsearch to MySQL

You can easily set up your Elasticsearch to MySQL connection using CRUD Queries with the following steps:

• Step 1: Creating a Custom Cluster and Node
• Step 2: Creating CRUD Functions
• Step 3: Search the Elasticsearch Data

Step 1: Creating a Custom Cluster and Node

The first step is to create the custom cluster and node. Looking at things from a security perspective, having a unique name for each cluster and node is considered good practice. To do this, navigate to your Elasticsearch folder, open its config folder and then open the elasticsearch.yml file in any text editor of your choice. Name the cluster and node as:

Cluster.name = blog
Node.name = articles

Save this file. The next step is to create a class that will perform CRUD and search operations in the cluster.

Step 2: Creating CRUD Functions

To handle CRUD operations, you will have to create a separate class. Write the following code in a file, let’s call this class Searchelastic.

<?php

require 'vendor/autoload.php';

class SearchElastic

{

  private $elasticclient = null;

   public function __construct()

  {

      $this->elasticclient = ElasticsearchClientBuilder::create()->build();

  }

	}

First, you load the Elasticsearch API library. The next step is to create a private variable that handles the connection of Elasticsearch within the class. Next, you will create a constructor so that every time the class is called, a connection with Elasticsearch is created automatically. Now, we will move on to creating a function for Mapping data types to the fields in Elasticsearch:

public function Mapping(){
               $params = [
                   'index' => 'articles',
                   'body' => [
                       'mappings' => [
                           'article' => [
                               'properties' => [
                                   'id' => [
                                       'type' => 'integer'
                                   
                                   ],
                                   'article_name' => [
                                       'type' => 'string'
                                   
                                   ],
                                   'article_content' => [
                                       'type' => 'string'
                                   
                                   ],
                                   'article_url' => [
                                       'type' => 'string'
                                   
                                   ],
                                   'category_name' => [
                                       'type' => 'string'
                                   
                                   ],
                                   'username' => [
                                       'type' => 'string'
                                   
                                   ],
                                   'date' => [
                                       'type' => 'date',
                                       'format' => 'dd-MM-yyyy'
                                   ],
                                   'article_img' => [
                                       'type' => 'string'
                                   
                                   ],
                               ]
                           ]
                       ]
                   ]
               ];
      $this->elasticclient->indices()->create($params);
     
   }

Next, you will need a function that gathers all the data from the MySQL Database and saves it in the Elasticsearch Database. Use the following code to do this:

public function InsertData($conn)
 {
 $con = $conn;
 $client = $this->elasticclient;
$sql="SELECT articles.article_id,articles.article_name,articles.article_content,articles.img,articles.url,categories.category_name,CONCAT(users.u_fname,' ',users.u_lname) AS username,DATE_FORMAT(articles.date,'%d-%m-%Y') AS dates FROM article INNER JOIN users ON users.user_id = article.user_Id INNER JOIN articles ON articles.article_id = article.article_id INNER JOIN categories ON categories.category_id = articles.category_id ";
 $result = $con->query($stmt);
 $params = null;
 while ($row = $result->fetch_assoc())
   {
   $params['body'][] = array(
     'index' => array(
       '_index' => 'articles',
       '_type' => 'article',
       '_id' => $row['article_id'],
     ) ,
   );
   $params['body'][] = ['article_name' => $row['article_name'], 'article_content' => $row['article_content'], 'article_url' => $row['url'], 'category_name' => $row['category_name'], 'username' => $row['username'], 'date' => $row['dates'], 'article_img' => $row['img'], ];
   }

 $this->Mapping();
 $responses = $client->bulk($params);
 return true;
 }

In this function, a MySQL Database connection is called, followed by a query that gathers all the articles from the Database.

This data is saved in an array as $params[‘body’][].  It should be noted that the same ID as the MySQL Database is being used, which helps update or delete the data. After the entire data has been fetched, the data types are mapped by calling the Mapping() function. Next, the array is saved in Elasticsearch. This needs to be done only once when connecting the MySQL database to Elasticsearch so that all the data that is saved in MySQL will be saved in Elasticsearch.

The following functions will be repeated whenever a new post is added, updated, or deleted.

The following function will add the newly created article to the Elasticsearch Database now:

 public function InsertNode($articleid, $con)

  {

      $conn = $con;

      $client = $this->elasticclient;

      $stmt = "SELECT articles.article_id,articles.article_name,articles.article_content,articles.img,articles.url,categories.category_name,CONCAT(users.u_fname,' ',users.u_lname) AS username,DATE_FORMAT(articles.date,'%d-%m-%Y') AS dates FROM article INNER JOIN users ON users.user_id = article.user_Id INNER JOIN articles ON articles.article_id = article.article_id INNER JOIN categories ON categories.category_id = articles.category_id WHERE articles.article_id = $articleid";

      $result = $con->query($stmt);

      $params = null;

      while ($row = $result->fetch_assoc()) {

          $params = ['index' => 'articles', 'type' => 'article', 'id' => $row['article_id'], 'body' => ['article_name' => $row['article_name'], 'article_content' => $row['article_content'], 'article_url' => $row['url'], 'category_name' => $row['category_name'], 'username' => $row['username'], 'date' => $row['dates'], 'article_img' => $row['img'], ]];

      }

      $responses = $client->index($params);

      return true;

  }

The following function is used for Update and Delete as well. This function is similar to the previous one except for an Elasticsearch API function that will be changed.

  public function UpdateNode($articleid, $con)

  {

      $conn = $con;

      $client = $this->elasticclient;

      $stmt = "SELECT articles.article_id,articles.article_name,articles.article_content,articles.img,articles.url,categories.category_name,CONCAT(users.u_fname,' ',users.u_lname) AS username,DATE_FORMAT(articles.date,'%d-%m-%Y') AS dates FROM article INNER JOIN users ON users.user_id = article.user_Id INNER JOIN articles ON articles.article_id = article.article_id INNER JOIN categories ON categories.category_id = articles.category_id WHERE articles.article_id = $articleid";

      $result = $con->query($stmt);

      $params = null;

      while ($row = $result->fetch_assoc()) {

          $params = ['index' => 'articles', 'type' => 'article', 'id' => $row['article_id'], 'body' => ['article_name' => $row['article_name'], 'article_content' => $row['article_content'], 'article_url' => $row['article_id'], 'category_name' => $row['category_name'], 'username' => $row['username'], 'date' => $row['dates'], 'article_img' => $row['img'], ]];

      }

      $responses = $client->update($params);

      return true;

  }

  public function DeleteNode($id)

  {

      $client = $this->elasticclient;

      $params = ['index' => 'articles', 'type' => 'article', 'id' => $id, ];

      $responses = $client->delete($params);

      return true;

  }

The above steps have successfully created the CRUD functionality for Elasticsearch. This class is called whenever update or delete operations are carried out for any post. Next, you will look at creating a function that will search the user query in Elasticsearch data.

Step 3: Search the Elasticsearch Data

This process is quite simple. It involves sending a user query to Elasticsearch, which then returns the result for that query. The following code lets you create a function for search in the Elasticsearch class:

public function Search($query)

  {

      $client = $this->elasticclient;

      $result = array();

      $i = 0;

      $params = ['index' => 'articles', 'type' => 'article', 'body' => ['query' => ['match' => ['article_content' => $query], ], ], ];

      $query = $client->search($params);

      $hits = sizeof($query['hits']['hits']);

      $hit = $query['hits']['hits'];

      $result['searchfound'] = $hits;

      while ($i < $hits) {

          $result['result'][$i] = $query['hits']['hits'][$i]['_source'];

          $i++;

      }

      return $result;

  }

Elasticsearch search() takes an array to which the index and query are submitted. After the above step, your Elasticsearch to MySQL integration is complete!

Limitations and Challenges of Using CRUD Queries Methods to Integrate ElasticSearch with MySQL

• Writing CRUD queries is ideal when you just need to move data once. When trying to update data on a regular basis, the manual method becomes onerous and unwieldy.
• The data set’s location value must be in the same area or inside the same multi-region.

Method 3: Using Logstash to build an ElasticSearch MySQL Integration

1. Create a host directory for the project.
2. Create a MySQL database.
3. Set up ElasticSearch and Kibana.
4. Set up Logstash to route data from MySQL to Elasticsearch.
   • First scenario: Establishing an Elasticsearch index from scratch.
   • Second Scenario: Copying changes on database records to Elasticsearch.

The connection of ElasticSearch with MySQL database is now successfully established.

Benefits of using My SQL Over ElasticSearch?

Here are a few advantages of using MySQL over ElasticSearch: 

• Security: MySQL is one of the most secure database management systems. The most recent version of MySQL provides data security and transactional processing functionality, which can significantly assist any organization, particularly e-commerce enterprises that do regular monetary transactions.
• On-Demand Scalability: On-demand Scalability is a hallmark feature of My SQL. It can manage deeply embedded apps using a small footprint, even in databases with terabytes of data. Additionally, MySQL provides tailored solutions to e-commerce businesses with specialized database needs.
• Higher Efficiency: MySQL has various distinguishing features, including proprietary storage engine software. It enables system administrators to set up the MySQL database server for optimal performance. Whether it is an e-commerce web application receiving a million daily queries or a high-speed transactional processing system, it doesn’t matter.
• Open-Source: MySQL is a common alternative for organizations or enterprises seeking software as a service. It has a free community edition to use and modify, and it provides faster, more scalable, and more reliable performance. This can be incredibly advantageous, particularly for organizations looking to avoid paying licensing fees.

FAQ on Elasticsearch to MySQL Integration

Conclusion

• In this blog post, you got a step-by-step guide on connecting Elasticsearch to MySQL using 3 easy methods.
• This second method uses CRUD Queries to set up the Elasticsearch to MySQL integration manually.