Any company that employs Elasticsearch as a data source will often find the need to extract this data for analysis in other Business Analytics platforms. Moreover, if Elasticsearch is used for backend storage, they will require a way to put data extracted from other sources into their Elasticsearch data warehouse.

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. 

Are you looking to move your data from Elasticsearch to MySQL? 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. Most importantly, you will learn how to perform Elasticsearch to MySQL ETL. Read along to decide which method suits you the most!

Methods to Connect Elasticsearch to MySQL

Following are the 2 methods using which you can set up your Elasticsearch to MySQL integration:

Method 1: Using Hevo Data to Connect Elasticsearch to MySQL

Hevo Logo
Image Source: Hevo Data

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.

Sign up here for a 14-Day Free Trial!

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. To learn more bout this step visit here.
Configuring Elasticsearch as Source: Elasticsearch to MySQL
Image Source: Hevo Docs
  • Configure Destination: Connect the MySQL Database as the destination. To learn more about this step, visit here.
Configuring MySQL as Destination: : Elasticsearch to MySQL
Image Source: Hevo Docs

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.
Solve your data integration problems with Hevo’s reliable, no-code, automated pipelines with 150+ connectors.
Get your free trial right away!

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

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

Logstash may be used to establish a data pipe that connects Elasticsearch to MySQL, allowing you to create an index from scratch and replicate any changes made to database records into Elasticsearch. Here are the steps.

  • Create a host directory for the project.
  • Create a MySQL database.
  • Set up ElasticSearch and Kibana.
  • 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.

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.

This method requires the users to have technical knowledge and involves several steps. With the data growing at an exponential rate in today’s world, integrating and managing such large volumes of data is no small feat, especially for a beginner & this is where an automated tool like Hevo saves the day.

Visit our Website to Explore Hevo

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. Check out our unbeatable pricing to choose the best plan for your organization.

Have any further queries about moving data from Elasticsearch to MySQL? Get in touch with us in the comments section below.

Shruti Garg
Freelance Technical Content Writer, Hevo Data

With an analytical perspective and a problem-solving approach, Shruti has extensive experience in solving business problems of data teams by delivering thoroughly researched content pivotal for data practitioners.

No-Code Data Pipeline for MySQL

Get Started with Hevo