Push MongoDB data to ElasticSearch through Logstash

Perform below Steps:



1. Download UnityJDBC_Trial_Install.jar from

http://unityjdbc.com/download.php

Note: This is a 30-day trial jar file.

2. Run this jar file with the below command

java -jar UnityJDBC_Trial_Install.jar

This will create a folder in the selected directory which was selected while installing. This folder will have mongodb_unityjdbc_full.jar in {installation directory} folder. Use this jar file's path in the logstash.yml configuration file.


3. Configure logstash.yml file.

Check the sample logstash.yml configuration file at the end of the blog.


Troubleshooting

1 ) Error: mongodb.jdbc.MongoDriver is not loaded. Are you sure you've included the correct jdbc driver in :jdbc_driver_library?
Exception: LogStash::ConfigurationError

Resolution:
If you have followed the steps then the problem can be in the input block of logstash.yml

jdbc_driver_class => "Java::mongodb.jdbc.MongoDriver"

The solution as to add Java:: with J capital

2)  could not index event to elasticsearch {: status => 400 mongo
"type" => "mapper_parsing_exception" "reason" => "Field [_id] is a metadata field and cannot be added inside a document. Use the index API request parameters."

Resolution:

Rename _id field  in filter block of logstash.yml

mutate
{
     rename => { "_id" => "mongo_id" }
}
and in the output block

elasticsearch
{
hosts => "elasticsearch:9200"
index => "testdata"
doc_as_upsert => true
document_id => "%{mongo_id}"
}


3)Java::JavaSql::SQLException: ERROR: Only SELECT statements allowed with executeQuery(): db..find()
OR
Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: ERROR: Only SELECT statements allowed with executeQuery(): mongo

Resolution:
Despite being a mongo no SQL DB we have to write a query in input block of logstash.yml in SQL query

e.g. statement => select * from mongo_test

instead of db.mongo_test.find()

4) No data pushed in elasticsearch through Logstash JDBC from MongoDB after performing all steps.

Resolution:
a. Add Index in kibana manually. (Got to Kibana - > Click on Management-> Kibana Index Patterns ->Create Index Pattern)
For e.g. here we have used "testdata" as an index in the logstash.yml in the output section, So add the index for testdata and then check the discover tab for data.

Final input block

input
{
jdbc
{
jdbc_driver_library => "/usr/share/lib/mongodb_unityjdbc_full.jar"
jdbc_driver_class => "Java::mongodb.jdbc.MongoDriver"
jdbc_connection_string => "jdbc:mongodb://localhost:27017/botengine"
jdbc_user => ""
schedule => "* * * * *"
statement => "select * from mongo_test"
type => "mongodb"
}
}


Final configuration

input

{

    jdbc

   {

jdbc_driver_library => "/usr/share/lib/mongodb_unityjdbc_full.jar"

jdbc_driver_class => "Java::mongodb.jdbc.MongoDriver"

jdbc_connection_string => "jdbc:mongodb://localhost:27017/botengine"

jdbc_user => ""

schedule => "* * * * *"

statement => "select * from mongo_test"

type => "mongodb"

  }

}

filter

{

  if [type] == "mongodb"

  {

mutate

{

rename => { "_id" => "mongo_id" }

}

  }

}

output

{

    if [type] == "mongodb"

{

     elasticsearch

    {

hosts => "elasticsearch:9200"

index => "testdata"

doc_as_upsert => true

document_id => "%{mongo_id}"

      }

    }

}

Handling date field of MongoDB, tracking on Mongodb date and push the same in Elasticsearch
input
{
    jdbc
   {
        jdbc_driver_library => "/usr/share/lib/mongodb_unityjdbc_full.jar"
        jdbc_driver_class => "Java::mongodb.jdbc.MongoDriver"
        jdbc_connection_string => "jdbc:mongodb://localhost:27017/botengine?rebuildschema=true"
        jdbc_user => ""
        schedule => "0 */1 * * *"
        statement => "select TO_DATE(starttime,'yyyy-MM-dd HH:mm:ss.SSS') as starttime ,test1,test2,test3,test4,_id  from $
        tracking_column => "starttime"
        tracking_column_type => "timestamp"
        use_column_value => true
        last_run_metadata_path => "/usr/share/logstash/lib/sahi_last_run_metadata.log"
        type => "mongodb"
    }
}

filter

{

    if [type] == "mongodb"

   {

mutate

{

rename => { "_id" => "mongo_id" }

}

  }

}

output

{

    if [type] == "mongodb"

{

      elasticsearch

       {

hosts => "elasticsearch:9200"

index => "testdata"

       }

    }

}