Connect Elasticsearch to SQL Server: 2 Easy Methods

on ETL, Tutorials • November 18th, 2020 • Write for Hevo

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. With real-time data migration, you can analyse 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 load data from Elasticsearch to SQL Server. You will also go through the limitations of manual methods.  

Let’s see how this blog is structured for you:

  1. Introduction to Elasticsearch
  2. Introduction to SQL Server
  3. Prerequisites
  4. Steps to Insert Data in Elasticsearch
  5. Method 1: Elasticsearch to SQL Server Using Manual Method
  6. Limitations of Using Manual Method
  7. Method 2: Elasticsearch to SQL Server Using Hevo Data
  8. Conclusion

Introduction to Elasticsearch

Elasticsearch to SQL Server: 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 analyse 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, a website search, security analysis, business analytics, log analytics, enterprise search, application performance monitoring, etc. 

Introduction to SQL Server

Elasticsearch to SQL Server:: 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 analyse 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. 

Prerequisites

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

  1. Download and install Elasticsearch from here. You can refer to the guide here for the installation. 
  2. Download and install Microsoft SQL Server from here.  You can download SQL Server Management Studio from here
  3. Node.js installed on your computer. 

2 Methods to Connect Elasticsearch to SQL Server

You can use any of the following methods to migrate your data from Elasticsearch to SQL Server according to your requirements:

Let’s learn about these methods in detail.

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 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.

Elasticsearch to SQL Server: Start Elasticsearch
  1. 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 acknowledgement, then you have successfully created your index.

Elasticsearch to SQL Server: Create Index
  1. 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:

Elasticsearch to SQL Server: Add Dataa

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

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

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
  1. 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
Elasticsearch to SQL Server: Elasticdump
  1. The output file will be created at your desired destination. The following image shows the JSON file created using ‘elasticdump’.
Elasticsearch to SQL Server: JSON Data

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:\Users\OSHI\elasticdata.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
Elasticsearch to SQL Server: SQL Query

The following screenshot shows the loaded data in SQL Server.

Elasticsearch to SQL Server: SQL Server Table

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 fulfil. 

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

Method 2: Elasticsearch to SQL Server Using Hevo Data

Hevo is a No-code Data Pipeline. It supports pre-built data integrations from 100+ 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 you with 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 follow:

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

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 call.

Explore more about Hevo by signing up for a 14-day free trial today

Conclusion

In this blog, you have learned about Elasticsearch, SQL Server, and two different approaches to load 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, then try Hevo Data.

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

Explore more about Hevo Data by signing up for a 14-day free trial today.

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

No-code Data Pipeline for SQL Server