Migrating Data from Oracle to ElasticSearch

Environment : Windows

Prerequisites

1. 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

2. In 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.

Steps for Migration

1. 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

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 rawsearchdata index

Check the data

1. First of all, lets check the index creation by running this in the browser [http://localhost:9200/_cat/indices?v]

2. We should see the rawsearchdata in the list of indices.

3. 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...