# Sequential record pull from JDBC to Elasticsearch using Logstash is out of the sequence

**URL:** <https://discuss.elastic.co/t/sequential-record-pull-from-jdbc-to-elasticsearch-using-logstash-is-out-of-the-sequence/186864>\
**Category:** Logstash\
**Created:** [June 21, 2019, 10:36am UTC](https://discuss.elastic.co/t/sequential-record-pull-from-jdbc-to-elasticsearch-using-logstash-is-out-of-the-sequence/186864 "2019-06-21T10:36:30Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![saif3r](https://avatars.discourse-cdn.com/v4/letter/s/49beb7/32.png) [@saif3r](https://discuss.elastic.co/u/saif3r)\
**Post date:** [June 21, 2019, 10:36am UTC](https://discuss.elastic.co/t/sequential-record-pull-from-jdbc-to-elasticsearch-using-logstash-is-out-of-the-sequence/186864/1 "2019-06-21T10:36:31Z")

</div>

Hello All,

I am facing an issue while pulling data from SQL Database. It appears that the sequential document population which is based on an ID is sometimes out of the sequence... Let me explain it on an actual example.  
My dataset contains around 25k of records. It has transnational data with **transaction date** as a tracking column. I'm using **fingerprint** to indicate the **\_id** which is overwritten once the particular set of columns will occur. So here's the sample of my set:

| Transaction Date | Status | Area | City | Street | Building |
| --- | --- | --- | --- | --- | --- |
| 01.01.2019 10:00 | NEW | Manhattan | New York | Columbus Ave | 609 |
| 04.04.2019 12:00 | DELETE | Manhattan | New York | Columbus Ave | 609 |

For **fingerprint** , I'm using the combination of those columns: **Area** , **City** , **Street** , **Building** and this is what i have in my **\_id** field.

Query:

> SELECT  
> Transaction\_date, Status, Area, City, Stree, Building  
> FROM  
> Buildings  
> WHERE Transaction\_date \>= :sql\_last\_value  
> ORDER BY Transaction\_date ASC

I assume with this logic, only the latest record with 'DELETE' status should be visible in Elasticsearch. Yet, that's not the case. I tried to reload the entire set few times, and it's almost always 'NEW' that persists in my index. I tried to narrow it down to just those two using my query, and removed the tracking\_column setup to see what happens if Logstash will reload the entire set each time, and the result is also mostrly 'NEW'. Sometimes 'DELETE' persist but usually after next pipieline run, it get's back to 'DELETE'.

Here's my config:

```
input {
  jdbc {
    jdbc_driver_library => "/usr/share/logstash/lib/drivers/sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://sql:1433"
    jdbc_user => "user"
    jdbc_password => "password"
    schedule => "*/5 * * * *"
	statement_filepath => "/etc/logstash/conf.d/queries/buildings.sql"
	use_column_value => true
	tracking_column_type => "timestamp"
    tracking_column => "transaction_date"
	last_run_metadata_path => "/usr/share/logstash/last_run_metadata/.buildings"
	record_last_run => true
  }
}

filter {
	mutate {
		add_field => {"fingerprint" => "%{area}_%{city}_%{street}_%{building}"}
	}
	fingerprint {
		source => "fingerprint"
		target => "[@metadata][fingerprint]"
		method => "MURMUR3"
}
}

output {
	elasticsearch {
		hosts => ["elastic:9200"]
		index => "buildings"
		document_id => "%{[@metadata][fingerprint]}"
	}
}

```

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [June 21, 2019, 1:08pm UTC](https://discuss.elastic.co/t/sequential-record-pull-from-jdbc-to-elasticsearch-using-logstash-is-out-of-the-sequence/186864/2 "2019-06-21T13:08:25Z")

</div>

Are you using a '--pipeline.workers 1' if not, you will definitely get data out of order. Even with that I do not think order is guaranteed.

---

<div class="post-metadata">

**Author:** ![saif3r](https://avatars.discourse-cdn.com/v4/letter/s/49beb7/32.png) [@saif3r](https://discuss.elastic.co/u/saif3r)\
**Post date:** [June 21, 2019, 1:25pm UTC](https://discuss.elastic.co/t/sequential-record-pull-from-jdbc-to-elasticsearch-using-logstash-is-out-of-the-sequence/186864/3 "2019-06-21T13:25:27Z")

</div>

Hello @Badger,

Thanks for the suggestion! I've been using default value (which was 125). I changed it to 1, checked few cases and it seems like it properly sequenced the records. I will have to conduct more detailed tests early next week.  
As a additional question, is there any mechanism or best practice that might ensure a proper sequencing during 'first load' of data?

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [June 21, 2019, 1:31pm UTC](https://discuss.elastic.co/t/sequential-record-pull-from-jdbc-to-elasticsearch-using-logstash-is-out-of-the-sequence/186864/4 "2019-06-21T13:31:57Z")

</div>

> [@saif3r](#):
>
> is there any mechanism or best practice that might ensure a proper sequencing during 'first load' of data?

--pipeline.workers 1 is all there is.

---

<div class="post-metadata">

**Author:** ![saif3r](https://avatars.discourse-cdn.com/v4/letter/s/49beb7/32.png) [@saif3r](https://discuss.elastic.co/u/saif3r)\
**Post date:** [June 21, 2019, 1:32pm UTC](https://discuss.elastic.co/t/sequential-record-pull-from-jdbc-to-elasticsearch-using-logstash-is-out-of-the-sequence/186864/5 "2019-06-21T13:32:59Z")

</div>

Got it, thank you.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 19, 2019, 1:33pm UTC](https://discuss.elastic.co/t/sequential-record-pull-from-jdbc-to-elasticsearch-using-logstash-is-out-of-the-sequence/186864/6 "2019-07-19T13:33:51Z")

</div>

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