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 2 easy methods. Most importantly, you will learn how to perform Elasticsearch to MySQL ETL. Read along to decide which method suits you the most!
Prerequisites
- An Elasticsearch account.
- A MySQL account.
- Working knowledge of CRUD Queries.
Introduction to Elasticsearch
Image Source: Logotype.us
Elasticsearch is a search and analytics engine that supports a wide range of products provided by Elastic, known as the Elastic Stack. It helps you search apps enterprises, websites, maps, logs, IoT data sources, and more, and also lets you create a distributed document store for your collected data.
Elasticsearch can also process a large number of events in a matter of seconds and perform complex analytics functions including mistake handling and Machine Learning that helps you decide on ranks and patterns. Extensions such as Elasticsearch SQL let you get fast results to your Elasticsearch queries using SQL.
Related: Looking to replicate data from Elasticsearch to Databricks? Our blog on Elasticsearch to Databricks provides you with two simple and effective methods to achieve this seamless integration. If you’re new to Elasticsearch and want to learn how to ingest data effortlessly, check out our blog on how to ingest data to Elasticsearch.
Introduction to MySQL
Image Source: Free PNG Logos
MySQL is a Relational Database Management System based on SQL (a structured query language). The application is used for multiple purposes, including data storage, e-commerce, and registration applications. However, most commonly, MySQL is used for Web Databases. It can be used to store everything from a single record to a complete list of products available in an online store.
Combined with a scripting language, such as PHP or Perl (both available on our hosting account), you can create a website that interacts with the MySQL Database in real-time to quickly display categorized and searchable information to Web Users
To gain a deeper understanding of MySQL’s high availability, we recommend consulting our comprehensive guide, MySQL High Availability, and Replication. Additionally, for insights into the simplified MySQL data load process, we have a helpful guide dedicated to explaining and streamlining the MySQL load data procedure.
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 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);
}
The next thing you will need is a function that gathers all the data from 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 being performed which 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 in updating or deleting 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 MySQL database to Elasticsearch so that all the data that is saved in MySQL will be saved in Elasticsearch.
The functions to follow will be repeated every time 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. Now, this class is called whenever update or delete operations are carried out for any post. Next, you will look at how to create 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 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!
Method 2: Using Hevo Data to Connect Elasticsearch to MySQL
Image Source: Hevo Data
Hevo Data, a No-code Data Pipeline, helps to transfer data from 150+ 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 in a secure, consistent manner with zero data loss.
It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on key business needs and perform insightful analysis using various BI tools such as Power BI, Tableau, etc.
Sign up here for a 14-Day Free Trial!
Hevo Data, an official Elasticsearch partner, 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.
Image Source: Hevo Docs
- Configure Destination: Connect the MySQL Database as the destination. To learn more about this step, visit here.
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.
- Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- 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 Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Conclusion
In this blog post, you got a step-by-step guide on connecting Elasticsearch to MySQL using 2 easy methods. This first 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
Hevo Data, a No-code Data Pipeline, helps you transfer data from a source of your choice in a fully automated and secure manner without having to write the code repeatedly. Hevo, with its strong integration with 150+ sources & BI tools, allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiff.
Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.
Have any further queries about moving data from Elasticsearch to MySQL? Get in touch with us in the comments section below.