what is the better way to index data from Oracle/relational tables into elastic search?

java oracle elasticsearch relational-database elasticsearch-plugin
Rakesh Shiriyara picture Rakesh Shiriyara · Dec 15, 2014 · Viewed 10.5k times · Source

What are the options to index large data from Oracle DB to elastic search cluster? Requirement is to index 300Million records one time into multiple indexes and also incremental updates having around approximate 1 Million changes every day.

I have tried JDBC plugin for elasticsearch river/feeder, both seems to be running inside or require locally running elastic search instance. Please let me know if there is any better option for running elastic search indexer as a standalone job (probably java based). Any suggestions will be very helpful. Thanks.

Answer

jhilden picture jhilden · Dec 16, 2014

We use ES as a reporting db and when new records are written to SQL we take the following action to get them into ES:

  1. Write the primary key into a queue (we use rabbitMQ)
  2. Rabbit picks up the primary key (when it has time) and queries the relation DB to get the info it needs and then writes the data into ES

This process works great because it handles both new data and old data. For old data just write a quick script to write 300M primary keys into rabbit and you're done!

Related questions

Java JDBC - How to connect to Oracle using Service Name instead of SID

I have a Java application that uses JDBC (via JPA) that was connecting to a development database using hostname, port and Oracle SID, like this: jdbc:oracle:thin:@oracle.hostserver1.mydomain.ca:1521:XYZ XYZ was the Oracle SID. Now I …

Read More
java.sql.SQLException: - ORA-01000: maximum open cursors exceeded

I am getting an ORA-01000 SQL exception. So I have some queries related to it. Are maximum open cursors exactly related to number of JDBC connections, or are they also related to the statement and resultset objects we have created …

Read More
Using setDate in PreparedStatement

In order to make our code more standard, we were asked to change all the places where we hardcoded our SQL variables to prepared statements and bind the variables instead. I am however facing a problem with the setDate(). Here …

Read More