JDBC input: connection OK, request OK, but no result

[AurelienG]

Hello,
I think the title says a lot already but here are some details. I have a PostgreSQL database, which I try to get data from with the JDBC input plugin. There doesn't seem to be a problem in my Logstash config file, since the connection to the database works. When I query the database directly I get results so the query is correct too.

Here is the file:

input {
	jdbc {
		jdbc_connection_string => "jdbc:postgresql://database:5432/dbname"
		jdbc_user => "user"
		jdbc_password => "password"
		jdbc_validate_connection => "true"
		jdbc_driver_class => "org.postgresql.Driver"
		jdbc_driver_library => "/path/to/postgresql-42.2.2.jar"
		statement => "select id from ci_builds limit 10;"
		schedule => "* * * * *"
	}
}


output {
	elasticsearch {
		hosts => "{{ elasticsearch_ip_server }}:9200"
		index => "my-db"
	}
	stdout { codec => rubydebug }
}

The output in the logs is just this, every minute:

[2018-06-12T14:50:00,898][INFO ][logstash.inputs.jdbc     ] (0.007369s) select * from ci_builds limit 10;

And the mapping is well detected in Elasticsearch.

Does anyone have an idea what could cause that?

[kartheek91 (Kartheek Gummaluri)]

You need to specify index type and id for the above record

[AurelienG]

According to the documentation the value "doc" is used if the index type isn't specified, and that's what I see in my mapping.
And the id is only required if I want to overwrite the previous data I think. And since I don't have any to begin with, I'm not sure it's useful.

I still tried but it didn't change anything. I would have loved if it had been that simple!

Thanks though, any help is appreciated

[kartheek91 (Kartheek Gummaluri)]

Actually from past few days I'm working on same functionality like migrating data from sql to es.Still working on it.

[AurelienG]

Ok I activated trace logs, it told me that there shouldn't be a semi-colon in the end of the query. I removed it but it didn't help.
Then I changed my index pattern: I used one my database date fields instead of @timestamp and apparently that's what was creating the issue.

Now I have a problem because I want to calculate the difference between two dates, but that's for another thread (if someone knows the solution, an answer by PM is welcome )

Good luck with your problem @kartheek91!