Skip to main content

Ingest data from Oracle DataBase to ElasticSearch

One of my few blog posts i have mentioned how to use and the use cases of using Oracle DataBase changed notification. When you have to need context search or count facts over your datas, you certainly need any modern Lucene based search engine. Elastic search is one of that search engine that can provide those above functionalities. However, from the previous versions of ES, elastic search river is deprecated to ingesting data to ES. Here you can get the whole history about deprecating river in ES. Any way, for now we have two option to ingest or import data from any sources to ES:
1) Implements or modify your DAO services, that can update data in ES and DataBase same time.
2) Polling, implements such a job, which will polling data in some period of time to update data in ES.

First approach is the very best option to implements, however if you have any legacy DAO services or 3rd party application that you couldn't make any changes is not for you. Polling to data base frequently with huge data can hard the performance of the DataBase.
In this blog post i am going to describe an alternative way to ingest data from data base to ES. One of the Prerequisites is that, you have Oracle Database with version higher than 9.0. Also i added the whole code base in github.com to explorer the capability.
Here is the data flow from Oracle to ES:
Data Flow is very simple:
1) Registered listener getting changes (RowID, ObjectId[~tableID]) for every commit in DB.
2) Listener send the changes (RowId, ObjectId) in xml to any MQ, we are using Apache Apollo.
3) Consumer of the queue collects the messages and query to the DataBase for Table meta data and result set by rowId.
4) Consumer build the result set in JSON format and index the result set in ES.

Git hub repository contains the three module to implements the data flow.
[qrcn] - collect notification from Oracle and send the notifications to any existing queue [apollo].
[es] - consumer, collects the message from the queue and index in Elastic search
[es-dto] - common dto
You can change your Query notification on QRCN module (file connection.properties).
querystring=select * from temp t where t.a = 'a1';select * from ATM_STATE
Also i add zookeeper to QRCN module to be fault tolerate.
Any way, at these moment project contains the following prerequisites:
1) racle JDBC 11g driver needs to compile the project.
2) apache zookeeper
3) apache Apollo
4) elastic search
Any way, you can always make any changes by your requirements. Happy weekend.

Comments

Popular posts from this blog

Send e-mail with attachment through OSB

Oracle Service Bus (OSB) contains a good collection of adapter to integrate with any legacy application, including ftp, email, MQ, tuxedo. However e-mail still recognize as a stable protocol to integrate with any application asynchronously. Send e-mail with attachment is a common task of any business process. Inbound e-mail adapter which, integrated with OSB support attachment but outbound adapter doesn't. This post is all about sending attachment though JavaCallout action. There are two ways to handle attachment in OSB: 1) Use JavaCallout action to pass the binary data for further manipulation. It means write down a small java library which will get the attachment and send the e-mail. 2) Use integrated outbound e-mail adapter to send attachment, here you have to add a custom variable named attachment and assign the binary data to the body of the attachment variable. First option is very common and easy to implement through javax.mail api, however a much more developer manage t

Tip: SQL client for Apache Ignite cache

A new SQL client configuration described in  The Apache Ignite book . If it got you interested, check out the rest of the book for more helpful information. Apache Ignite provides SQL queries execution on the caches, SQL syntax is an ANSI-99 compliant. Therefore, you can execute SQL queries against any caches from any SQL client which supports JDBC thin client. This section is for those, who feels comfortable with SQL rather than execute a bunch of code to retrieve data from the cache. Apache Ignite out of the box shipped with JDBC driver that allows you to connect to Ignite caches and retrieve distributed data from the cache using standard SQL queries. Rest of the section of this chapter will describe how to connect SQL IDE (Integrated Development Environment) to Ignite cache and executes some SQL queries to play with the data. SQL IDE or SQL editor can simplify the development process and allow you to get productive much quicker. Most database vendors have their own front-en

Load balancing and fail over with scheduler

Every programmer at least develop one Scheduler or Job in their life time of programming. Nowadays writing or developing scheduler to get you job done is very simple, but when you are thinking about high availability or load balancing your scheduler or job it getting some tricky. Even more when you have a few instance of your scheduler but only one can be run at a time also need some tricks to done. A long time ago i used some data base table lock to achieved such a functionality as leader election. Around 2010 when Zookeeper comes into play, i always preferred to use Zookeeper to bring high availability and scalability. For using Zookeeper you have to need Zookeeper cluster with minimum 3 nodes and maintain the cluster. Our new customer denied to use such a open source product in their environment and i was definitely need to find something alternative. Definitely Quartz was the next choose. Quartz makes developing scheduler easy and simple. Quartz clustering feature brings the HA and