# Ingest with Jdbc input plugin in append

**URL:** <https://discuss.elastic.co/t/ingest-with-jdbc-input-plugin-in-append/140525>\
**Category:** Logstash\
**Created:** [July 18, 2018, 11:16am UTC](https://discuss.elastic.co/t/ingest-with-jdbc-input-plugin-in-append/140525 "2018-07-18T11:16:06Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Fedele\_Mantuano](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fedele_mantuano/32/9225_2.png) [@Fedele\_Mantuano](https://discuss.elastic.co/u/Fedele_Mantuano)\
**Post date:** [July 18, 2018, 11:16am UTC](https://discuss.elastic.co/t/ingest-with-jdbc-input-plugin-in-append/140525/1 "2018-07-18T11:16:06Z")

</div>

Hi at all,

I'm working with `Jdbc input plugin` that I use to ingest a table from mysql to elasticsearch.  
Now I have a problem. Is it possible to ingest only the new data in Elasticsearch? I'd like to store data in append.

I thought that maybe it's possible with the `WHERE` closure and timestamp, like example snippet:

```auto
input {
  jdbc {
    jdbc_driver_library => "mysql-connector-java-5.1.36-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/mydb"
    jdbc_user => "mysql"
    parameters => { "timestamp" => now - 1d }
    schedule => "0 23 * * *"
    statement => "SELECT * from songs where timestamp>= :timestamp"
  }
}

```

But I don't know how store `now`.

Can you help me?  
Maybe there is another method that I don't know.

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:** [July 18, 2018, 12:35pm UTC](https://discuss.elastic.co/t/ingest-with-jdbc-input-plugin-in-append/140525/2 "2018-07-18T12:35:41Z")

</div>

Take a look at the documentation for [tracking\_column](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-tracking_column) and :sql\_last\_value

---

<div class="post-metadata">

**Author:** ![Fedele\_Mantuano](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fedele_mantuano/32/9225_2.png) [@Fedele\_Mantuano](https://discuss.elastic.co/u/Fedele_Mantuano)\
**Post date:** [July 18, 2018, 12:38pm UTC](https://discuss.elastic.co/t/ingest-with-jdbc-input-plugin-in-append/140525/3 "2018-07-18T12:38:38Z")

</div>

Thanks, I will check it and I will give you a feedback.

---

<div class="post-metadata">

**Author:** ![Fedele\_Mantuano](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fedele_mantuano/32/9225_2.png) [@Fedele\_Mantuano](https://discuss.elastic.co/u/Fedele_Mantuano)\
**Post date:** [July 21, 2018, 7:36am UTC](https://discuss.elastic.co/t/ingest-with-jdbc-input-plugin-in-append/140525/4 "2018-07-21T07:36:58Z")

</div>

I solved with this code:

```auto
input {
  jdbc {
    jdbc_driver_library => "/opt/mysql-connector-java-5.1.46/mysql-connector-java-5.1.46.jar"
    jdbc_connection_string => "jdbc:mysql://database:3306/otrs?zeroDateTimeBehavior=convertToNull"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_user => "user"
    jdbc_password => "password"
    schedule => "* * * * *"
    statement => "SELECT * from dummy WHERE id > :sql_last_value"
    use_column_value => true
    tracking_column => "id"
    tracking_column_type => "numeric"
    jdbc_paging_enabled => "true"
    jdbc_page_size => "50000"
    tags => ["table_dummy"]
  }
}

```

For very huge table I enabled paging (`jdbc_paging_enabled`, `jdbc_page_size`), then I tracked `id` (`tracking_column`) and I put it in `WHERE` statement (`:sql_last_value` point to last `id`).

Thanks for your help.

---

<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:** [August 18, 2018, 7:37am UTC](https://discuss.elastic.co/t/ingest-with-jdbc-input-plugin-in-append/140525/5 "2018-08-18T07:37:08Z")

</div>

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