# Can logstash's JDBC input plugin do multiple sql tasks?

**URL:** <https://discuss.elastic.co/t/can-logstashs-jdbc-input-plugin-do-multiple-sql-tasks/259573>\
**Category:** Logstash\
**Created:** [December 24, 2020, 9:31am UTC](https://discuss.elastic.co/t/can-logstashs-jdbc-input-plugin-do-multiple-sql-tasks/259573 "2020-12-24T09:31:21Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![iooi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iooi/32/79231_2.png) [@iooi](https://discuss.elastic.co/u/iooi)\
**Post date:** [December 24, 2020, 9:31am UTC](https://discuss.elastic.co/t/can-logstashs-jdbc-input-plugin-do-multiple-sql-tasks/259573/1 "2020-12-24T09:31:22Z")

</div>

Now there is a JDBC task read from db as

```auto
input {
  jdbc {
    jdbc_connection_string => "jdbc:mysql://${MYSQL_MAIN_HOST}/${MYSQL_DATABASE}"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    jdbc_page_size => 10000
    jdbc_paging_enabled => true
    jdbc_password => "${MYSQL_PASSWORD}"
    jdbc_user => "${MYSQL_USER}"
    schedule => "0 1 * * *"
    statement_filepath => "/usr/share/logstash/pipeline/sql/select_posts.sql"
    tracking_column => "updated_at"
    tracking_column_type => "numeric"
    use_column_value => true
    last_run_metadata_path => "/usr/share/logstash/jdbc_last_run/select_posts_last_value"
  }
}

```

/usr/share/logstash/pipeline/sql/select\_posts.sql

```auto
SELECT
  id,
  title,
  body
FROM posts

```

This task is heavy when the `body` item goes to very large data. So want to remove it at the first search as:

```auto
SELECT
  id,
  title
FROM posts

```

Then get the IDs and use them to find `body` again.

```auto
SELECT
  body
FROM posts
WHERE id in (IDs)

```

To set to output target. Even update the output target is also okay.

So can logstash read statement by statement in this case?

---

<div class="post-metadata">

**Author:** ![fadjar340](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fadjar340/32/43610_2.png) [@fadjar340](https://discuss.elastic.co/u/fadjar340)\
**Post date:** [December 24, 2020, 10:55am UTC](https://discuss.elastic.co/t/can-logstashs-jdbc-input-plugin-do-multiple-sql-tasks/259573/2 "2020-12-24T10:55:22Z")

</div>

Is that means you want to query the data incremently or just get all the data in one go?  
I saw your sql statement will query all the data in one go, with some changes of the query you can get the "delta" from the latest value.

---

<div class="post-metadata">

**Author:** ![iooi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iooi/32/79231_2.png) [@iooi](https://discuss.elastic.co/u/iooi)\
**Post date:** [December 25, 2020, 12:07am UTC](https://discuss.elastic.co/t/can-logstashs-jdbc-input-plugin-do-multiple-sql-tasks/259573/3 "2020-12-25T00:07:54Z")

</div>

I want to search all the data except body in one go. But want to get body again with IDs that searched from the last result.

As search with body cost lots of time during the whole process. So want to separate them with 2 statements.

---

<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:** [December 25, 2020, 12:16am UTC](https://discuss.elastic.co/t/can-logstashs-jdbc-input-plugin-do-multiple-sql-tasks/259573/4 "2020-12-25T00:16:33Z")

</div>

You could use a jdbc input to `SELECT id, title FROM posts`, then a [jdbc\_streaming](https://www.elastic.co/guide/en/logstash/current/plugins-filters-jdbc_streaming.html) filter to fetch the body for each event (i.e. each row of the database). Hard to see how that would actually be cheaper, although it would distribute the work more.

---

<div class="post-metadata">

**Author:** ![iooi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iooi/32/79231_2.png) [@iooi](https://discuss.elastic.co/u/iooi)\
**Post date:** [December 25, 2020, 8:59am UTC](https://discuss.elastic.co/t/can-logstashs-jdbc-input-plugin-do-multiple-sql-tasks/259573/5 "2020-12-25T08:59:06Z")

</div>

I tested it. It became even slower.

---

<div class="post-metadata">

**Author:** ![fadjar340](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fadjar340/32/43610_2.png) [@fadjar340](https://discuss.elastic.co/u/fadjar340)\
**Post date:** [December 25, 2020, 9:13am UTC](https://discuss.elastic.co/t/can-logstashs-jdbc-input-plugin-do-multiple-sql-tasks/259573/6 "2020-12-25T09:13:59Z")

</div>

As per your statement:

> [@iooi](#):
>
> I want to search all the data except body in one go. But want to get body again with IDs that searched from the last result.

At the end you get all the data, instead of the iD, also the body, right? or wrong?

---

<div class="post-metadata">

**Author:** ![iooi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iooi/32/79231_2.png) [@iooi](https://discuss.elastic.co/u/iooi)\
**Post date:** [December 25, 2020, 9:17am UTC](https://discuss.elastic.co/t/can-logstashs-jdbc-input-plugin-do-multiple-sql-tasks/259573/7 "2020-12-25T09:17:12Z")

</div>

I can get all the data includes id, title and body with the method of @Badger .

I'm looking at [jdbc\_static](https://www.elastic.co/guide/en/logstash/current/plugins-filters-jdbc_static.html) to see if it can do well.

---

<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:** [January 22, 2021, 9:17am UTC](https://discuss.elastic.co/t/can-logstashs-jdbc-input-plugin-do-multiple-sql-tasks/259573/8 "2021-01-22T09:17:26Z")

</div>

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