Data showing wrong when searched

(vaibhav savala) #1

Hi! i have a dataset of more than 10 lac rows. I have integrated elasticsearch with Mysql using logstash.
When i type the following URL to fetch in postman,
i get the following,
"took": 169,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"failed": 0
"hits": {
"total": 1,
"max_score": 1,
"hits": [
"_index": "persondetails",
"_type": "Document",
"_id": "%{idDocument}",
"_score": 1,
"_source": {
"iddocument": 151170,
"@timestamp": "2017-08-31T05:03:43.968Z",
"author": "rishav",
"expiry_date": "2014-09-24T18:30:00.000Z",
"@version": "1",
"description": "d help ",
"creation_date": "2014-05-24T18:30:00.000Z",
"type": 10
it is wrong as the number of rows in my table are more than 10 lac and this shows that total is only 1. I am unable to find what is the mistake here.

(Mark Walkom) #2

What does _cat/indices?v show?

(vaibhav savala) #3

it shows this message

health |status| index | uuid | pri |rep |docs.count |docs.deleted |store.size |
yellow| open | persondetails |4FiGngZcQfS0Xvu6IeHIfg |5 | 1 | 2 | 1054 | 488.2kb | 488.2kb

(Mark Walkom) #4

Looks like you have 2 documents, and 1054 deleted ones.

What does your Logstash config look like?

(vaibhav savala) #5

This is my logstash.conf file
input {
jdbc {
jdbc_connection_string => "jdbc:mysql://"
jdbc_user => "root"
jdbc_password => ""
schedule => "* * * * *"
jdbc_validate_connection => true
jdbc_driver_library => "/usr/local/Cellar/logstash/5.5.2/mysql-connector-java-3.1.14/mysql-connector-java-3.1.14-bin.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
statement => "SELECT * FROM Document"
type => "persondetails"
output {
elasticsearch {
index =>"persondetails"
document_type => "Document"
document_id => "%{idDocument}"
hosts => ["http://localhost:9200"]
stdout{ codec => json_lines}

(Christian Dahlqvist) #6

It looks like the field you have assigned as document id is incorrectly spelled (it is case sensitive and you have a capital D in the output but not the document), and that the string %{idDocument} has been used as id, causing the same document to be updated over and over.

(vaibhav savala) #7

I have used idDocument in my mySql table as my row name. So, where should i change the name from iddocument to idDocument? I have written %{idDocument} as well in my conf file.

(Christian Dahlqvist) #8

Based on the sample document you posted, all field names are lowercased (might be done by the JDBC input?), so your config need to match that. While developing config, send data to a stdout output plugin with a rubydebug codec, as this makes it easy to see the actual structure of events and debug without having to go to Elasticsearch in the process.

(vaibhav savala) #9

Problem has been resolved. Thanks for helping me out! i changed it to iddocument and it worked.

(system) #10

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