Easy Elasticsearch and Oracle Data Connection (3 Steps Explained)

Are you tired of sifting through mountains of tables in your Oracle database, struggling to find the information you need? If so, you’re not alone. Many businesses today are grappling with the challenge of managing and making sense of vast amounts of database tables. But fear not – there is a solution!

By leveraging the power of Elasticsearch and Oracle data connection, you can unlock powerful searching and indexing capabilities that can enhance the functionality of your Oracle database. By running queries through Elasticsearch, your users can easily search through large volumes of data and find relevant information quickly.

In this blog, we’ll guide you through three easy steps to establish a seamless connection between Elasticsearch and Oracle data, empowering you to supercharge your search capabilities in the Oracle database and take your data analysis to the next level.

What is Elasticsearch?

• Elasticsearch is a distributed, open-source search and analytics engine built on Apache Lucene and developed in Java.

• At its core, Elasticsearch is a server that can process JSON requests & returns JSON data. Elasticsearch allows you to store, search and analyze huge volumes of data quickly, in real-time, and also returns answers in milliseconds.
• It uses a structure based on documents instead of tables & schemas and comes with extensive REST APIs for storing & searching data.
• Its backend components include Clusters, Nodes, Shards & Replicas. One of the most famous Tech stacks is the ELK Stack where E stands for Elastic, Logstash, Beats & Kibana. 

Primary Use Cases of Elasticsearch

• Application Search 
• Website Search 
• Enterprise Search 
• Logging & Log Analytics
• Security Analytics
• Business Analytics 
• Infrastructure metrics & container monitoring

Related: Demystify the intricacies of ingest pipelines in Elasticsearch. Discover how Elasticsearch’s powerful tools can transform your data ingestion process, allowing you to preprocess and enrich your data seamlessly before indexing it into Elasticsearch in our comprehensive guide on how to ingest data to Elasticsearch.

What is Oracle Database?

• Oracle Autonomous Database is an all-in-one cloud database solution for data marts, data lakes, operational reporting, and batch data processing.
• Oracle is a database commonly used for running Online Transaction Processing (OLTP), Data Warehousing & mixed database workloads.
• Usually, Oracle database products compete with open-source relational & non-relational databases like PostgreSQL, Couchbase, MongoDB, Neo4j & many more.

Prerequisites

1. There must be an Elasticsearch Setup on your machine. Installing Elasticsearch is very straightforward in Windows. Download the latest version of it and extract it in a folder. 
2. In the environment variables section, set the JAVA_HOME path.
3. Now, if you go to [http://localhost:9200/] in the browser, you can see that Elasticsearch is running.
4. Install Logstash for migrating data from Oracle to Elasticsearch.
5. To extract the data, make sure your Oracle server is up and running.

Related: In the ever-evolving landscape of database design, it’s crucial to stay ahead of the game with the best Oracle database design tools. In our blog, the best Oracle database design tools, we take you on a journey through the top tools that will empower you to create efficient, scalable, and robust database designs.

Role of Logstash in Integrating Elasticsearch and Oracle Data

• Logstash is an open-source server-side data processing pipeline that ingests data from many data sources, transforms it & then sends it to the destination.
• It’s a powerful tool for integrating Elasticsearch and Oracle databases while supporting a variety of inputs that pull in events from a multitude of common sources, all at the same time.
• It allows you to ingest easily from your logs, metrics, webs, and various AWS services in a continuous & streaming manner. Here is a detailed guide on how Logstash can be installed & run on a Windows machine. 
• As Logstash is a primary component for ingesting the data, it is very important to make suitable changes to logstash-ora.conf file as described later in detail for a seamless Elasticsearch and Oracle data connection.

JDBC Connection

• Java Database Connectivity (JDBC) is an API for Java (a programming language) that defines how a client will be able to access a database. It is a Java-based data access technology used for Java database connectivity.
• JDBC is a part of the Java Standard Edition platform, from Oracle Corporation. Its basic purpose is to provide methods for querying & updating data in the database & is oriented toward relational databases. 
• JDBC plays a vital role in bridging the gap between your Elasticsearch and Oracle databases.
• By utilizing the JDBC API, as we’ll see in the upcoming sections, your developers can harness the full potential of Elasticsearch and Oracle data, ensuring a robust and scalable solution for their Java-based data access needs, for efficient data querying, updating, and analysis.

JDBC Input Connection

• JDBC Input Connection is a component of the JDBC integration plugin. The plugin is a way to ingest data in any database with a JDBC interface into Logstash.
• Data ingestions can also be scheduled using Cron Jobs. To be able to use this input plugin, the JDBC driver library must be passed explicitly into the plugin using jdbc_driver_library configuration option.
• The option of scheduling is also present in this plugin. This is based on rufus-scheduler. 
• Take a look at the detailed information about JDBC Connector configuration. 
• The basic JDBC input parameters setting looks like this:

input {
  jdbc {
    jdbc_driver_library => ""
    jdbc_driver_class => ""
    jdbc_connection_string => ""
    jdbc_user => ""
    parameters => {}
    schedule => ""
    statement => ""
  }
}

Setting Up Elasticsearch Oracle Database Connection & Migrating Data

• Edit the file logstash-ora.conf under config folder in Logstash as below. logstash-ora is an Oracle configuration file. 

input {
 			jdbc {
   				    jdbc_validate_connection => true
  			     	    jdbc_connection_string => "jdbc:oracle:thin:@192.168.1.2:1881/xe"
      				    jdbc_user => "user_details"
      				    jdbc_password => "user_password"
      				    jdbc_driver_library => "C:elk-2OJDBC-Fullojdbc7.jar"
      				    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
                                                        statement => "select * from search"
  			       }
        }
 
output {
 elasticsearch {
   					hosts => ["http://localhost:9200"]
   					index => "rawsearchdata"
   					#user => "elastic"
  					#password => "changeme"
                }
         }

• Input is:

'jdbc_connection_string' = connection string for Oracle
'jdbc_user' = schema_name
'jdbc_driver_library' = path to ojdbc7.jar (OJDBC is freely available for download for all OS)

• Output is:

index = Index where the data is going to get stored

1. Save the file and run the command logstash.bat -f ../config/logstash-ora.conf
2. Once this is done, data will be loaded in the raw search data index
3. Pass the column name under the fields.

Common errors you might get in this process

1. “Error: java::oracle.jdbc.driver.OracleDriver not loaded. Are you sure you’ve included the correct JDBC driver in :jdbc_driver_library? You may be interested in the ‘–configtest’ flag which you can use to validate logstash’s configuration before you choose to restart a running system.”
   
   This error could arise because of any Typo error e.g. instead of writing “Java::oracle.jdbc.driver.OracleDriver”, one may type “java::oracle.jdbc.driver.OracleDriver”. Therefore you will have to check carefully about these typo errors. They might be very annoying. 

2. “Java::JavaSql::SQLRecoverableException: IO Error: Invalid connection string format, a valid format is: “host:port:sid”.” 
   
   This error could rise because of connection string format i.e., “jdbc:oracle:thin:@192.168.1.2:1881/xe”. 
3. “Java::JavaSql::SQLRecoverableException: IO Error: The Network Adapter could not establish the connection”.
   
   When this error occurs, check the following cases if they are running fine:
   • The database and the listener are running.
   • The specified port in the code is the same port that the database is listening to.
   • There is no firewall blocking.

Summary

• This blog helps you set up the dynamic discovery for data exploration and invaluable insights with Elasticsearch and Oracle data.
• The manual process of connecting ElasticSearch and Oracle databases can be intimidating & can usually result in errors that need to be resolved in order to get the connection working.
• One simple way could be by using Hevo Data which automates the whole connection process by building automated data pipelines.

Sign up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.

Frequently Asked Questions