Elasticsearch SQL Server Integration: 2 Easy Methods

Are you trying to load data from Elasticsearch to SQL Server? Have you looked all over the internet for it? If yes, then you are in the right place. Loading data from Elasticsearch to SQL Server can provide a backup of your Elasticsearch data and help you to restore your data in case of failure.

A fast and efficient disaster recovery mechanism like this is crucial for real-time data migration. With real-time data migration, you can analyze your data in business analytics platforms and form better decisions. In this blog, you will learn about Elasticsearch, SQL Server, and two different approaches to connecting Elasticsearch with SQL Server.

You will also go through all the limitations of manual methods for Elasticsearch SQL Server integration. Let’s get started!

Introduction to Elasticsearch

Elasticsearch is a distributed, open-source analytics and search engine for all types of data like numerical, textual, structured, etc. It was developed on Apache Lucene in 2010. Elasticsearch is known for its REST APIs, scalability, speed, distributed nature, etc.

It is also considered as a central component of Elastic Stack, also known as ELK Stack (Elasticsearch, Logstask, Kibana). Elasticsearch allows you to search, store, and analyze huge volumes of data in real-time.

It can achieve fast responses because it uses indexes for searching. Elasticsearch is widely used in application search, website search, security analysis, business analytics, log analytics, enterprise search, application performance monitoring, etc. 

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 SQL Server

Microsoft SQL Server is an RDMS (Relational Database Management System). It is a database server that allows data storage and retrieval capabilities by other applications. It supports ANSI SQL and also comes with its own SQL language, T-SQL (Transact SQL).

SQL Server Management Studio (SSMS) is the interface tool for SQL Server. You can analyze your data using SQL Server Analysis Services (SSAS), generate reports using SQL Server Reporting Services (SSRS), and carry out any ETL operations using SQL Server Integration Services (SSIS). SQL Server also offers features like separate security privileges, reduces cost, maintains standby servers, etc. 

Learn insights and step-by-step instructions on how to export data from MySQL to CSV format, enabling you to effectively manage and analyze your data.

Prerequisites

This is what you need to connect Elasticsearch to SQL Server:

1. Download and install Elasticsearch. 
2. Download and install Microsoft SQL Server. You also need to download SQL Server Management Studio.
3. Node.js installed on your computer. 

Steps to Insert Data in Elasticsearch

In this section, you will learn how to create an index and insert values in Elasticsearch. You can use Postman for this process. Elasticsearch refers to the database as an index, table as a type, and row as a document.  The REST API format of Elasticsearch is as follows:

http://host:port/[index]/[type]/[_action/id]

Here, HTTP methods can be GET, PUT, POST, and DELETE. The hostname is ‘localhost’, and the port is ‘9200’ by default. Index refers to your database name, and the type refers to your table name. The action can be search, create, etc.

Let’s see the steps to insert data in Elasticsearch:  

1.  To check your Elasticsearch on the Postman, use the following URL in ‘GET’ and click ‘Send’.

http://localhost:9200

The following output will appear on your screen.

2. To create an index, use the following URL with ‘PUT’. Here, ‘company’ refers to the name of the index.

http://localhost:9200/company

If the request returns true in acknowledgment, then you have successfully created your index.

3. To post data for the index, you can use the following command in ‘POST’.

http://localhost:9200/company/employee/_create?

Here, ’employee’ is your type, and ‘_create’ refers to the action. You need to place your data in the following format in the ‘Body’ as ‘raw’.

{
Key: value
} 

An example of the following format is as follows:

You can repeat this step to add data in Elasticsearch using Postman. 

Elasticsearch SQL Server Method 1: Elasticsearch to SQL Server Using Manual Method

Migrating your data from Elasticsearch helps you to extract data for in-depth data analysis in other business analytics platforms. It also helps in data restoration and backup. You can use a variety of ETL (Extract, Transform, Load) processes to load data in and out of Elasticsearch storage. 

In this method, you will use ‘elasticdump’ to export the data from Elasticsearch as a JSON file and then import it into SQL Server. Follow these steps to migrate data from Elasticsearch to SQL Server:

• Step 1: Extract Data from Elasticsearch
• Step 2: Import Data into SQL Server

Step 1: Extract Data from Elasticsearch

In this step, you will download ‘elasticdump’ and then use it to export Elasticsearch data. It is assumed that you have Node.js installed on your computer.  

1. Use the following command to download ‘elasticdump’ globally.

npm install elasticdump -g

2. To extract data from your Elasticsearch, you can use the following command in CMD:

 elasticdump   --input=http://username:password@localhost:9200/company/employee/ 
--output=my_index_data.json   
--type=data

3. The output file will be created at your desired destination. The following image shows the JSON file created using ‘elasticdump’.

Step 2: Import Data into SQL Server

For this blog, I have used SQL Server Management Studio 18. To import the JSON file created in the last step, you need to perform the following queries:

Declare @JSON varchar(max)  //creates a variable for storing data

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'C:UsersOSHIelasticdata.json', SINGLE_CLOB) as j
// loads entire JSON file data in variable

SELECT *
FROM OPENJSON(@json)
 WITH (firstName nvarchar(50) '$._source.name',
       age int '$._source.age',
       experience int '$._source.experienceInYears')
//Puts data in the table

The following screenshot shows the loaded data in SQL Server.

Voila! You have successfully loaded data from Elasticsearch to SQL Server. Now you can use SSAS for the analysis of your data and get better insights from it.

Limitations of Using Manual Method

When you are migrating your data from Elasticsearch to SQL Server using a manual method, there is no real-time data migration. As you are exporting your data by creating a JSON file on your computer and then loading it in SQL Server, the need for real-time data doesn’t get fulfilled. 

When you are dealing with a huge volume of data that is increasing exponentially, then this method will fail to provide insights into the latest data. 

Elastic Search SQL Server Method 2: Elasticsearch to SQL Server Using Hevo Data

Hevo is a No-code Data Pipeline. It supports pre-built data integrations from 150+ data sources, including Elasticsearch. Hevo offers a fully managed solution for your data migration process. It will automate your data flow in minutes without writing any line of code.

Its fault-tolerant architecture makes sure that your data is secure and consistent. Hevo provides a truly efficient and fully-automated solution to manage data in real-time and always have analysis-ready data at SQL Server.

The steps to load data from Elasticsearch to SQL Server using Hevo Data are as follows:

1. Authenticate and connect Elasticsearch as your data source.
2. Connect the Microsoft SQL Server as a destination to load your data. 

GET STARTED WITH HEVO FOR FREE

Let’s look at some salient features of Hevo:

• Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
• Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
• Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
• Schema Management: Hevo can automatically detect the schema of the incoming data and maps it to the destination schema.
• Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within pipelines.
• Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

visit our website to explore hevo

Conclusion

In this blog, you have learned about Elasticsearch, SQL Server, and two different approaches to loading data from Elasticsearch to SQL Server. You also came across various limitations of the manual method. So, if you want to automatically load your data from Elasticsearch to SQL Server in real time, try Hevo Data.

Hevo Data is a No-code Data Pipeline. It supports pre-built integrations from 150+ data sources at a reasonable price. Hevo will fully automate your data flow from Elasticsearch to SQL Server in real-time.

SIGN UP HERE FOR A 14-DAY FREE TRIAL

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. You can also look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of loading data from Elasticsearch to SQL Server in the comment section below.