# How to configure Logstash and JDBC to upload new records based on ascending serial number

**URL:** <https://discuss.elastic.co/t/how-to-configure-logstash-and-jdbc-to-upload-new-records-based-on-ascending-serial-number/206032>\
**Category:** Logstash\
**Created:** [October 31, 2019, 12:15pm UTC](https://discuss.elastic.co/t/how-to-configure-logstash-and-jdbc-to-upload-new-records-based-on-ascending-serial-number/206032 "2019-10-31T12:15:41Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![MAvD](https://avatars.discourse-cdn.com/v4/letter/m/f08c70/32.png) [@MAvD](https://discuss.elastic.co/u/MAvD)\
**Post date:** [October 31, 2019, 12:15pm UTC](https://discuss.elastic.co/t/how-to-configure-logstash-and-jdbc-to-upload-new-records-based-on-ascending-serial-number/206032/1 "2019-10-31T12:15:41Z")

</div>

Hello,

Currently, we are updating our data in Elasticsearch by uploading a lot of data once in a while from a firebird database which uses quite some data. We want this this to be synchronized by uploading only new and edited records from this database, for example once every 10 minutes. For this we would like to use Logstash and JDBC. I am fairly new to Elasticsearch and especially new to Logstash, so I need some help.

From [https://www.elastic.co/blog/how-to-keep-elasticsearch-synchronized-with-a-relational-database-using-logstash](https://www.elastic.co/blog/how-to-keep-elasticsearch-synchronized-with-a-relational-database-using-logstash), we came up with this:

```auto
input {
  jdbc {
    jdbc_driver_library => "c:/bug/jaybird-full-3.0.6.jar"	    
    jdbc_driver_class => "org.firebirdsql.jdbc.FBDriver"
    jdbc_connection_string => "jdbc:firebirdsql://path"
    jdbc_user => <my username>
    jdbc_password => <my password>
    jdbc_paging_enabled => false
    jdbc_page_size => "50000"
    jdbc_fetch_size => 50000
    tracking_column => "SERNR"
    use_column_value => true
    tracking_column_type => "numeric"
    schedule => "*/5 * * * * *"
    statement => "SELECT * FROM YIELD WHERE SERNR > 1418300" #1418300 should be SERNR from previous instance
  }
}
filter {
  mutate {
    copy => { "SERNR" => "[@metadata][_id]"}
    remove_field => ["@version"]
  }
}
output {
  elasticsearch {
	hosts => <host>
	user => <my username>
	password => <my password>
	index => <my index>
	document_id => "%{[@metadata][_id]}"
  }
}

```

"my index" should contain all the records that were uploaded after the previous tracking\_column and the records that already existed. SERNR is in this case the specific id belonging to the record. I would like to assign SERNR to document\_id which did not work during my multiple attempts. Next to that, I want to add some inner joins. I tried to include this by inserting it like this:

```auto
statement => "SELECT * FROM OPBRNGST INNER JOIN KOPER ON OPBRNGST.KOPER = KOPER.KOPER_NR INNER JOIN PRODUKT ON OPBRNGST.PRODUKT = PRODUKT.PROD_NR WHERE VOLGNR > 1418300 ORDER BY VOLGNR ASC"

```

This resulted in errors. Could you please help me with configuring Logstash to upload new records? Or do you know some examples and documentation?  
Thanks!

---

<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:** [October 31, 2019, 2:12pm UTC](https://discuss.elastic.co/t/how-to-configure-logstash-and-jdbc-to-upload-new-records-based-on-ascending-serial-number/206032/2 "2019-10-31T14:12:23Z")

</div>

To continue a SELECT from where it previously left off you can use [:sql\_last\_value](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#_state) in the WHERE clause.

---

<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:** [November 28, 2019, 2:12pm UTC](https://discuss.elastic.co/t/how-to-configure-logstash-and-jdbc-to-upload-new-records-based-on-ascending-serial-number/206032/3 "2019-11-28T14:12:33Z")

</div>

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