Migrating Data from Oracle to ElasticSearch

nigilan — Fri, 08 Nov 2019 05:35:17 +0000

Environment : Windows

Prerequisites

Installing elasticsearch is very straight forward in Windows. Download the latest version of it and extract it in a folder. link to download and run Elasticsearch as a service
In environment variables section, set the JAVA_HOME path.
Now, if you go to [http://localhost:9200/] in the browser, you can see that elasticsearch is running.
Install Logstash for migrating data from Oracle to Elasticsearch.
To extract the data, make sure your Oracle server is up and running.

Steps for Migration

Edit the file logstash-ora.conf under config folder in Logstash as below

input {
 jdbc {
       jdbc_validate_connection => true
       jdbc_connection_string => "jdbc:oracle:thin:@192.168.1.2:1521/xe"
       jdbc_user => "user_details"
       jdbc_password => "tiger"
       jdbc_driver_library => "D:\elk\OJDBC-Full\ojdbc7.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

'jdbc_connection_string' = connection string for Oracle

'jdbc_user' = schema_name

'jdbc_driver_library' = path to ojdbc7.jar (OJDBC is freely available for download)

Output

index = Index where the data is going to get stored

Save the file and run the command logstash.bat -f ../config/logstash-ora.conf
Once this is done, data will be loaded in the rawsearchdata index

Check the data

First of all, lets check the index creation by running this in the browser [http://localhost:9200/_cat/indices?v]
We should see the rawsearchdata in the list of indices.
Now, we can query the elasticsearch using CURL.

curl -X GET "localhost:9200/rawsearchdata/_search" -H 'Content-Type: application/json' -d'
{
   "query": {
       "query_string": {
           "fields": [
               "search_column"
           ],
           "query": "customer data"
       }
   }
}'

Pass the column name under the fields

Thanks for reading...

DEV Community: nigilan

Migrating Data from Oracle to ElasticSearch

Prerequisites

Steps for Migration

Input

Output

Check the data