In this blog, you will see two different methods for an Elasticsearch Postgres integration. The first method involves the use of Python Libraries to complete the data transfer process. The second method uses Hevo’s automated Data Pipeline to set up your Elasticsearch with PostgreSQL connection. Read along to decide which method suits you the best!
Methods to Connect Elasticsearch to PostgreSQL
The following 2 methods can easily set up your Elasticsearch to PostgreSQL integration:
Method 1: Using Hevo Data to Connect Elasticsearch to PostgreSQL
Image Source
Hevo Data, a No-code Data Pipeline, helps you directly establish a PostgreSQL Elasticsearch connection in a completely hassle-free & automated manner. Hevo connects to your Elasticsearch cluster using the Elasticsearch transport client and synchronizes the data available in the cluster to your destination.
With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready 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.
Get Started with Hevo for Free
Hevo Data offers you seamless Postgres Elasticsearch data migration in two very simple steps:
- Authenticate Source: Authenticate and configure your Elasticsearch account as the data source.
Image Source
- Configure Destination: Connect the PostgreSQL Database as the destination.
Image Source
Here are more reasons to love Hevo:
- Auto Schema Mapping: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- 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 call
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular time.
Get Started with Hevo for Free
Method 2: Using Python Libraries to Connect Elasticsearch to PostgreSQL
You can easily set up your Elasticsearch Postgres connection using Python Libraries with the following steps:
Step 1: Import Python Libraries
First, import the required Python libraries such as elasticsearch, elasticsearch-dsl, and psycopg2. Elasticsearch is a library that provides a common ground for all Elasticsearch related code written in Python. Elasticsearch DSL is used to write and run queries against Elasticsearch. Psycopg2 is used for connecting to the PostgreSQL database.
import elasticsearch
from elasticsearch import Elasticsearch
import elasticsearch_dsl
from elasticsearch_dsl import Search
import psycopg2
Step 2: Create an Elasticsearch Object
Next, create an Elasticsearch object to send a data request to Elasticsearch. You need your Elasticsearch database login credentials to set up a connection.
es= Elasticsearch(hosts= “http://user_name:your_password@localhost:9200/”)
Step 3: Connect to PostgreSQL Database
Similarly, establish a connection to your PostgreSQL database and create a cursor object to execute your INSERT command.
conn = psycopg2.connect(host=”localhost”, database=”postgres_db_name”, user="postgres”, password= “postgres_password”)
cur = conn.cursor()
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.
Step 4: Get Records from Elasticsearch
Now we will use the Elasticsearch object to get the subscriber’s names.
s= Search(index= “es_db_name”).using(es).query(“match”, status=”subscriber”)
es_result= s.execute()
Step 5: Read Elasticsearch Data into a List
Read es_result into a list which can then be moved to PostgreSQL.
subscriber_list = []
for x in es_result:
subscriber_list.append(x.name)
All the names in the records will be appended to the empty subsciber_list list.
Step 6: Load Data into PostgreSQL
Now we just have to load this list into the appropriate solemn in PostgreSQL.
q= “INSERT INTO the_table VALUES (%s)”
cur.execute(q, subsciber_list)
conn.commit()
conn.close()
In the above section, we wrote a query that inserts values into a PostgreSQL table called ‘the_table’. Then we used the cursor we created earlier to load the values in the list subscriber_list into PostgreSQL using the query. Finally, the changes are committed and the PostgreSQL connection is closed.
Step 7: Elasticsearch to PostgreSQL: Final Code
Here is the complete code to load data from Elasticsearch to PostgreSQL:
import elasticsearch
from elasticsearch import Elasticsearch
import elasticsearch_dsl
from elasticsearch_dsl import Search
import psycopg2
# create an elasticsearch object.
es= Elasticsearch(hosts= “http://user_name:your_password@localhost:9200/”)
# connect to PostgreSQL and create a cursor object.
conn = psycopg2.connect(host=”localhost”, database=”postgres_db_name”, user=”postgres”, password= “postgres_password”)
cur = conn.cursor()
# Get the required data from the documents.
s= Search(index= “es_db_name”).using(es).query(“match”, status=”subscriber”)
es_result= s.execute()
# Load the data into a list.
subscriber_list = []
for x in es_result:
subscriber_list.append(x.name)
# Load the list into a PostgreSQL table using the INSERT statement and close the connection.
q= “INSERT INTO the_table VALUES (%s)”
cur.execute(q, subsciber_list)
conn.commit()
conn.close()
If you are looking to streamline your PostgreSQL workflow, do read our blog on PostgreSQL import CSV, saving you time and effort. And if you’re interested in optimizing performance and achieving high availability, don’t miss our guide on setting up PostgreSQL clusters [+clustering options].
Conclusion
The article discussed 2 methods of connecting Elasticsearch to PostgreSQL. The first method is to load data from Elasticsearch to PostgreSQL using Python Libraries. But often, in reality, NoSQL data does not have a consistent structure. The data is usually semi-structured and data needs to be transformed. There are several other complexities involved all of which require heavy engineering bandwidth to deal with.
Instead, if you need an ETL tool that handles moving data from Elasticsearch to PostgreSQL automatically, give Hevo, a no-code data pipeline a try.
Learn more about Hevo
Want to take Hevo for a spin? Signup for a 14-day free trial and experience the feature-rich Hevo suite firsthand. Checkout our unbeatable pricing to select the best plan for your organization.
Share your thoughts on loading data from Elasticsearch to PostgreSQL in the comments below.
Nikhil specializes in freelance writing within the data industry, delivering informative and engaging content related to data science by blending his problem solving ability.