Trying to send postgresql query from Logstash to ElasticSearch

Hi I'm following this tutorial (https://www.elastic.co/blog/logstash-jdbc-input-plugin) and I managed to recreate example 1 using my own table. But example 2 give me the following error and stops.

./bin/logstash -f logstash-simple.conf

Sending Logstash's logs to ///logstash-5.6.3/logs which is now configured via log4j2.properties
[2017-11-01T19:48:29,266][INFO ][logstash.modules.scaffold] Initializing module {:module_name=>"fb_apache", :directory=>"///logstash-5.6.3/modules/fb_apache/configuration"}
[2017-11-01T19:48:29,276][INFO ][logstash.modules.scaffold] Initializing module {:module_name=>"netflow", :directory=>"///logstash-5.6.3/modules/netflow/configuration"}
[2017-11-01T19:48:29,930][INFO ][logstash.outputs.elasticsearch] Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=>[http://localhost:9200/]}}
[2017-11-01T19:48:29,932][INFO ][logstash.outputs.elasticsearch] Running health check to see if an Elasticsearch connection is working {:healthcheck_url=>http://localhost:9200/, :path=>"/"}
[2017-11-01T19:48:30,106][WARN ][logstash.outputs.elasticsearch] Restored connection to ES instance {:url=>"http://localhost:9200/"}
[2017-11-01T19:48:30,168][INFO ][logstash.outputs.elasticsearch] Using mapping template from {:path=>nil}
[2017-11-01T19:48:30,172][INFO ][logstash.outputs.elasticsearch] Attempting to install template {:manage_template=>{"template"=>"logstash-", "version"=>50001, "settings"=>{"index.refresh_interval"=>"5s"}, "mappings"=>{"default"=>{"_all"=>{"enabled"=>true, "norms"=>false}, "dynamic_templates"=>[{"message_field"=>{"path_match"=>"message", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false}}}, {"string_fields"=>{"match"=>"", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false, "fields"=>{"keyword"=>{"type"=>"keyword", "ignore_above"=>256}}}}}], "properties"=>{"@timestamp"=>{"type"=>"date", "include_in_all"=>false}, "@version"=>{"type"=>"keyword", "include_in_all"=>false}, "geoip"=>{"dynamic"=>true, "properties"=>{"ip"=>{"type"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"}, "longitude"=>{"type"=>"half_float"}}}}}}}}
[2017-11-01T19:48:30,191][INFO ][logstash.outputs.elasticsearch] New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=>["//localhost:9200"]}
[2017-11-01T19:48:30,195][INFO ][logstash.pipeline ] Starting pipeline {"id"=>"main", "pipeline.workers"=>4, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>5, "pipeline.max_inflight"=>500}
[2017-11-01T19:48:30,318][INFO ][logstash.pipeline ] Pipeline main started
[2017-11-01T19:48:30,395][INFO ][logstash.agent ] Successfully started Logstash API endpoint {:port=>9600}
[2017-11-01T19:48:32,283][INFO ][logstash.inputs.jdbc ] (0.572000s) SELECT * from contact where money < 80000
[2017-11-01T19:48:33,354][WARN ][logstash.agent ] stopping pipeline {:id=>"main"}

My config file is the following:

file: simple-out.conf

input {
jdbc {
# Postgres jdbc connection string to our database, mydb
jdbc_connection_string => "jdbc:postgresql://:5432/"
# The user we wish to execute our statement as
jdbc_user => "
"
jdbc_password => "
"
# The path to our downloaded jdbc driver
jdbc_driver_library => "/
//postgresql-42.1.4.jar"
# The name of the driver class for Postgresql
jdbc_driver_class => "org.postgresql.Driver"
# our query
statement => "SELECT * from contact where money < 80000"
}
}
output {
elasticsearch {
index => "contact"
document_type => "appr"
document_id => "%{app_id}"
hosts => ["localhost:9200"]
}
}

I'm very new to Elastic stack, could you please help me?
Thank you

What error? Logstash ran the query and then shut down gracefully.

My output when using the stdout is the following:

$ ./bin/logstash -f logstash-simple.conf
Sending Logstash's logs to /Users//logstash-5.6.3/logs which is now configured via log4j2.properties
#output {
[2017-11-02T10:30:42,925][INFO ][logstash.modules.scaffold] Initializing module {:module_name=>"fb_apache", :directory=>"/Users/
/logstash-5.6.3/modules/fb_apache/configuration"}
[2017-11-02T10:30:42,931][INFO ][logstash.modules.scaffold] Initializing module {:module_name=>"netflow", :directory=>"/Users/**/logstash-5.6.3/modules/netflow/configuration"}
[2017-11-02T10:30:43,163][INFO ][logstash.pipeline ] Starting pipeline {"id"=>"main", "pipeline.workers"=>4, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>5, "pipeline.max_inflight"=>500}
[2017-11-02T10:30:43,294][INFO ][logstash.pipeline ] Pipeline main started
[2017-11-02T10:30:43,377][INFO ][logstash.agent ] Successfully started Logstash API endpoint {:port=>9600}
[2017-11-02T10:30:45,144][INFO ][logstash.inputs.jdbc ] (0.116000s) SELECT * from contact where money < 80000 limit 10
{"@version":"1","index":3216,"@timestamp":"2017-11-02T17:30:45.170Z","money":68033.4310594501,"number":5460026}
{"@version":"1","index":4835,"@timestamp":"2017-11-02T17:30:45.171Z","money":53538.41957209386,"number":7143299}
{"@version":"1","index":6052,"@timestamp":"2017-11-02T17:30:45.172Z","money":44350.55210621592,"number":2932773}
{"@version":"1","index":22989,"@timestamp":"2017-11-02T17:30:45.172Z","money":79892.37130223823,"number":588222}
{"@version":"1","index":33264,"@timestamp":"2017-11-02T17:30:45.173Z","money":73328.9295691638,"number":6032738}
{"@version":"1","index":33418,"@timestamp":"2017-11-02T17:30:45.173Z","money":49627.51134069792,"number":5421704}
{"@version":"1","index":37885,"@timestamp":"2017-11-02T17:30:45.174Z","money":70076.97784429585,"number":636201}
{"@version":"1","index":38237,"@timestamp":"2017-11-02T17:30:45.175Z","money":79949.64912025326,"number":716658}
{"@version":"1","index":49968,"@timestamp":"2017-11-02T17:30:45.175Z","money":75552.05490521724,"number":397120}
{"@version":"1","index":50016,"@timestamp":"2017-11-02T17:30:45.176Z","money":63148.863764502734,"number":417760}
[2017-11-02T10:30:46,313][WARN ][logstash.agent ] stopping pipeline {:id=>"main"}

But when sending it to ElasticSearch and try:

GET contact/appr/1?

I get the following response:

{
"_index": "contact",
"_type": "appr",
"_id": "1",
"found": false
}

You're setting the document id to the contents of the app_id field, but the log you posted above indicates that there is no app_id field in the documents.

Instead of getting a particular document, what if you search for all documents in the index?

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.