# Data mismatch in elasticsearch when loading data from database using logstash **URL:** \ **Category:** Logstash\ **Created:** [December 19, 2018, 12:46pm UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513 "2018-12-19T12:46:49Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [December 19, 2018, 12:46pm UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/1 "2018-12-19T12:46:49Z")
Hello, I have loaded 6lakhs+ of data from database into elasticsearch using logstash. Observed the count of the number of rows in database is equal to the number of records in elasticsearch(as highlighted in yellow color below ). But observes some data is missing when validating group by query within database and with elasticsearch query(as observed from DB Count & ES(field count)). Is there is any chance of mismatch occuring with the data when loading data from database into elasticsearch using logstash. Ex:- DBQuery: select customer\_group\_name,count(_) FROM TEST group by customer\_group\_name order by count(_) desc ``` Elasticsearch Query: GET index_name/_search { "size":0, "aggs":{ "group_by_customer_group_name":{ "terms":{ "size":1000, "field":"customer_group_name.keyword" } } } } ``` Please find the difference that occured as shown below, ![MisMatchedDataCount](https://us1.discourse-cdn.com/elastic/original/3X/1/5/156b2ac09d33dfea6f5f7e1b086b61ec880c849c.png) Please help me. Waiting for your response..., Thanks inadvance. ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [December 20, 2018, 11:18am UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/2 "2018-12-20T11:18:21Z")
The input code used is as follows, ``` input { jdbc { jdbc_driver_library => "XXXXXX/Downloads/sqljdbc4-2.0.jar" jdbc_driver_class => "XXXXXX" jdbc_connection_string => "jdbc:sqlserver://XXXXXX;user=XXXXXX;password=yyyyyy;database=ZZZZZZZZZ" jdbc_user => "XXXXXX" jdbc_password => "yyyyyy" statement => "SELECT company ,name , customer_group_name from TEST" jdbc_paging_enabled => "true" jdbc_page_size => "50000" } } output{ elasticsearch { codec => json hosts => ["localhost:9200"] index => "index_name" document_type => "_doc" } stdout { codec => rubydebug } } ``` Waiting for your response, thanks inadvance. ---
**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\ **Post date:** [December 20, 2018, 2:40pm UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/3 "2018-12-20T14:40:39Z")
What do you get when you do match query on customer\_group\_name.keyword with one of the terms? Can you identify 8 extra records in X5 and and post them here together with mapping and the original record in SQL? ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [December 20, 2018, 2:45pm UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/4 "2018-12-20T14:45:01Z")
Thanks a lot for the reply, From the above image, 3rd and 4th columns represents the data when we execute the query. 1st and 2nd columns represents the data when we exeute the DBQuery as shown above. Waiting for your response. Thanks in advance.., ---
**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\ **Post date:** [December 20, 2018, 2:55pm UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/5 "2018-12-20T14:55:48Z")
What do you get when you execute this query? ```auto GET index_name/_search { "size": 0, "query": { "match": { "customer_group_name.keyword": "X5" } } } ``` If you get 1048, can you identify 8 extra records and and post one of them here together with mapping and the corresponding original record in SQL? ---
**Author:** ![yaauie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yaauie/32/23363_2.png) [@yaauie](https://discuss.elastic.co/u/yaauie)\ **Post date:** [December 20, 2018, 4:01pm UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/6 "2018-12-20T16:01:49Z")
Without an `ORDER BY` clause in your SQL, it is possible that your database is returning non-deterministic windows during pagination (returning some rows in multiple windows, while omitting other rows entirely). ---
**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\ **Post date:** [December 20, 2018, 5:00pm UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/7 "2018-12-20T17:00:33Z")
Yes that makes sense, and with auto id generation, the same record will appear as 2 different records in elasticsearch. Good to know. So the solution for that would be to add `ORDER BY something` to ```auto statement => "SELECT company ,name , customer_group_name from TEST" ``` ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [December 21, 2018, 12:56am UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/8 "2018-12-21T00:56:24Z")
Thanks a lot @Igor_Motov , @yaauie for your response, So if I add order by clause in my SQL statement(used in logstash file) for any particular column, will it eliminate my issue. Waiting for your response Thanks in advance ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [December 21, 2018, 7:34am UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/9 "2018-12-21T07:34:01Z")
Hello @Igor_Motov, @yaauie, thanks a lot for your support. I want to inform that none of the columns data present in the database is unique. As per your suggestion, I have modified the query as shown below, ```auto statement => "SELECT company ,name , customer_group_name from TEST ORDER BY customer_group_name desc" ``` i am getting the error as shown below, > Exception when executing JDBC query {:exception=\>#\ Java::ComMicrosoftSqlserverJdbc::SQLServerException: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.\>} So, i have modified the query as, ```auto statement => "SELECT TOP 638858 company ,name , customer_group_name from TEST ORDER BY customer_group_name desc" ``` the data has been loaded into ES correctly. I request you to please check the query and give me if i am moving in the wrong path. waiting for your response. Thanks inadvance. ---
**Author:** ![yaauie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yaauie/32/23363_2.png) [@yaauie](https://discuss.elastic.co/u/yaauie)\ **Post date:** [December 21, 2018, 12:24pm UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/10 "2018-12-21T12:24:11Z")
If you `ORDER BY` one field, and that field is not unique, you are not guaranteeing order; you need to define an `ORDER BY` clause that guarantees order of the entire dataset. ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [December 21, 2018, 12:38pm UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/11 "2018-12-21T12:38:14Z")
thank you @yaauie for your feedback, Right now,we have the same scenario like suppose we have table used in production which doesn't have any field unique, then how can we accomplish this issue to load into elasticsearch. is it possible or not. waiting for your response. Thanks inadvance. ---
**Author:** ![yaauie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yaauie/32/23363_2.png) [@yaauie](https://discuss.elastic.co/u/yaauie)\ **Post date:** [December 21, 2018, 4:07pm UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/12 "2018-12-21T16:07:03Z")
If no field is guaranteed to be unique, then the only way to effectively guarantee ordering is to specify _all_ fields in the `ORDER BY` clause. ```auto SELECT TOP 638858 company, name, customer_group_name from TEST ORDER BY company, name, customer_group_name DESC ``` ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [December 22, 2018, 2:36am UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/13 "2018-12-22T02:36:26Z")
Thank you @yaauie for your guidance in resolving this issue. Cheers!! I have learned that Logstash seems to use the last received record to determine the value of the tracking column. As Oracle does not guarantee the sort order of the query, the last record received is not necessarily the one with the latest ID. Thus records could be selected multiple times. We solved this by adding "ORDER BY ID" to the SQL statement to guarantee the last received record contains the latest value in the tracking column. 👆 🤝 ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [January 4, 2019, 6:28am UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/14 "2019-01-04T06:28:07Z")
hi @yaauie, I have 28 columns in a table. i have kept order by for 3 columns and the issue repeats again with data mismatch. do i need to order by all the 28 columns. I am still facing this issue and it is effecting me a lot. waiting for your suggestion. Regards, Balu ---
**Author:** ![yaauie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yaauie/32/23363_2.png) [@yaauie](https://discuss.elastic.co/u/yaauie)\ **Post date:** [January 4, 2019, 8:00am UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/15 "2019-01-04T08:00:17Z")
> [@balumurari1](#): > > I have 28 columns in a table. Does this table not have a primary key (even a compound primary key)? If not, then as I stated before, the only way to guarantee order is to specify _all_ selected columns in your `ORDER BY` clause. ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [January 4, 2019, 8:12am UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/16 "2019-01-04T08:12:39Z")
Thanks for the reply @yaauie yes for the 28 columns there is no primary key. i will try by keeping all selected columns in order by clause. ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [January 4, 2019, 9:28am UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/17 "2019-01-04T09:28:52Z")
Hi @yaauie, I have tried by specifying all the 28 columns in ORDER BY clause but observes data mismatch is occurring again. Can I add a column to SQL statement to get row number and use it to order by rownum. can you please suggest me to eliminate the issue. Waiting for your response, Thanks inadvance. ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [January 7, 2019, 7:32am UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/18 "2019-01-07T07:32:42Z")
Hi @yaauie, @Igor_Motov I have updated the table with a new column row\_number and updated that column data with the rownumber sequentially. So, i can just say ```auto SELECT TOP 638858 company, name, customer_group_name,row_number from TEST ORDER BY row_number DESC ``` It had run successfully. Thanks a lot for your help. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 4, 2019, 7:46am UTC](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/19 "2019-02-04T07:46:47Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.