# SQL data upload using JDBC-logstash

**URL:** <https://discuss.elastic.co/t/sql-data-upload-using-jdbc-logstash/353435>\
**Category:** Logstash\
**Created:** [February 16, 2024, 6:10am UTC](https://discuss.elastic.co/t/sql-data-upload-using-jdbc-logstash/353435 "2024-02-16T06:10:00Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ritikapawar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ritikapawar/32/98580_2.png) [@Ritikapawar](https://discuss.elastic.co/u/Ritikapawar)\
**Post date:** [February 16, 2024, 6:10am UTC](https://discuss.elastic.co/t/sql-data-upload-using-jdbc-logstash/353435/1 "2024-02-16T06:10:00Z")

</div>

Hi,  
I'm uploading data using jdbc-logstash script but when i run ths script it adds allover data again and again so index is showing count of duplicate data too.

This is the script which i am using

```auto
input {
  jdbc {
    jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.2.0.jar"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/app_medixcel_base"
    jdbc_user => "root"
    jdbc_password => "plus91"
    use_column_value => true
    tracking_column => "leave_id"
    tracking_column_type => "numeric"
    statement => "SELECT leave_id, staff_id, start_date, end_date, end_time, leave_reason, is_full_day, approved, added_on, approval_reason, approved_by, 
         clinic_id, leave_in_days FROM app_medixcel_base.mxcel_staff_leaves WHERE start_date BETWEEN '2023-01-27' AND '2024-03-27';"
  }
}

filter {
  mutate {
    copy => { "id" => "[@metadata][_id]"}
    remove_field => ["@version"]
  }
}

output {
  elasticsearch {
    hosts => ["http://localhost:9200"]
    user => "elastic"
    password => "plus91"
    index => "index_mxcel_patient_leaves"
  }
  stdout { codec => "rubydebug" }
}
 My tracking coloumn is leave_id 
this script is running successfully and not giving any error how I can avoid the duplicate data upload?

```

---

<div class="post-metadata">

**Author:** ![Rios](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rios/32/95745_2.png) [@Rios](https://discuss.elastic.co/u/Rios)\
**Post date:** [February 16, 2024, 10:25am UTC](https://discuss.elastic.co/t/sql-data-upload-using-jdbc-logstash/353435/2 "2024-02-16T10:25:06Z")

</div>

If the `leave_id` field is the primary key, you can use as doc\_id. If is not, then you should use fingerprint. Check [the similar topic](https://discuss.elastic.co/t/problem-using-fingerprint-for-my-documents-stored-in-elasticsearch/295101).

```auto
output {
  elasticsearch {
    hosts => ["http://localhost:9200"]
    user => "elastic"
    index => "index_mxcel_patient_leaves"
    document_id => "%{leave_id}"
    doc_as_upsert => true
  }

```

---

<div class="post-metadata">

**Author:** ![Ritikapawar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ritikapawar/32/98580_2.png) [@Ritikapawar](https://discuss.elastic.co/u/Ritikapawar)\
**Post date:** [February 17, 2024, 7:05am UTC](https://discuss.elastic.co/t/sql-data-upload-using-jdbc-logstash/353435/3 "2024-02-17T07:05:56Z")

</div>

HI @Rios  
leave\_id is primary key here is my updated script

```auto
  jdbc {
    jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.2.0.jar"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/app_medixcel_base"
    jdbc_user => "root"
    jdbc_password => "plus91"
    use_column_value => true
    tracking_column => "leave_id"
    tracking_column_type => "numeric"
    statement => "SELECT leave_id, staff_id, start_date, end_date, end_time, leave_reason, is_full_day, approved, added_on, approval_reason, approved_by,
         clinic_id, leave_in_days FROM app_medixcel_base.mxcel_staff_leaves WHERE start_date BETWEEN '2023-01-27' AND '2024-03-27';"
  }
}

filter {
  mutate {
    copy => { "id" => "[@metadata][_id]"}
    remove_field => ["@version"]
  }
}

output {
  elasticsearch {
    hosts => ["http://localhost:9200"]
    user => "elastic"
    password => "plus91"
    index => "index_mxcel_patient_leaves"
    document_id => "%{leave_id}"
    doc_as_upsert => true

  }
  stdout { codec => "rubydebug" }
}

```

---

<div class="post-metadata">

**Author:** ![Rios](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rios/32/95745_2.png) [@Rios](https://discuss.elastic.co/u/Rios)\
**Post date:** [February 17, 2024, 8:23am UTC](https://discuss.elastic.co/t/sql-data-upload-using-jdbc-logstash/353435/4 "2024-02-17T08:23:32Z")

</div>

Have you tested?

---

<div class="post-metadata">

**Author:** ![Ritikapawar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ritikapawar/32/98580_2.png) [@Ritikapawar](https://discuss.elastic.co/u/Ritikapawar)\
**Post date:** [February 17, 2024, 9:19am UTC](https://discuss.elastic.co/t/sql-data-upload-using-jdbc-logstash/353435/5 "2024-02-17T09:19:18Z")

</div>

Yes I did. not working

---

<div class="post-metadata">

**Author:** ![Rios](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rios/32/95745_2.png) [@Rios](https://discuss.elastic.co/u/Rios)\
**Post date:** [February 17, 2024, 10:20am UTC](https://discuss.elastic.co/t/sql-data-upload-using-jdbc-logstash/353435/6 "2024-02-17T10:20:54Z")

</div>

Please copy a message which come to the filter, replace anything which is classified. Simply need a data sample 🙂

---

<div class="post-metadata">

**Author:** ![Ritikapawar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ritikapawar/32/98580_2.png) [@Ritikapawar](https://discuss.elastic.co/u/Ritikapawar)\
**Post date:** [February 17, 2024, 10:35am UTC](https://discuss.elastic.co/t/sql-data-upload-using-jdbc-logstash/353435/7 "2024-02-17T10:35:17Z")

</div>

this is my data sample

```auto
{
    "approval_reason" => "Done",
        "is_full_day" => 1,
      "leave_in_days" => 0,
           "staff_id" => 138,
         "start_date" => 2023-01-26T18:30:00.000Z,
          "clinic_id" => 0,
           "end_date" => 2023-01-26T18:30:00.000Z,
           "added_on" => 2024-01-19T20:29:39.000Z,
           "approved" => true,
       "leave_reason" => "Vacation",
           "end_time" => 2024-02-17T00:00:00.000Z,
        "approved_by" => 0,
         "@timestamp" => 2024-02-17T06:53:56.527Z,
           "leave_id" => 1
}

```

---

<div class="post-metadata">

**Author:** ![Rios](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rios/32/95745_2.png) [@Rios](https://discuss.elastic.co/u/Rios)\
**Post date:** [February 17, 2024, 9:56pm UTC](https://discuss.elastic.co/t/sql-data-upload-using-jdbc-logstash/353435/8 "2024-02-17T21:56:10Z")

</div>

I have made the version which use same logic, it's working fine.

- new leave\_id, new doc
- existing leave\_id in ES, updated doc  
There is [the article](https://www.elastic.co/blog/logstash-lessons-handling-duplicates) which address duplicated records.

```auto
input {
  generator {
       message => 'approval_reason:Done, is_full_day:0, leave_in_days:0, staff_id:138, start_date:2023-01-26T18:30:00.000Z, clinic_id:0, end_date:2023-01-26T18:30:00.000Z, added_on:2024-01-19T20:29:39.000Z, approved:true, leave_reason:Vacation, end_time:2024-02-17T00:00:00.000Z, approved_by:0, leave_id:1'
	   count => 1
  }
} 

filter {

   kv { 
      source => "message"
      value_split => ":"
      field_split => ", "
   }
   
   # fingerprint {
   # source => "message"
   # target => "[@metadata][fingerprint]"
   # method => "SHA1"
   # base64encode => true
   # }

   date {
        match => ["start_date", "ISO8601"]
       target => "start_date"
   }
	  
   date {
        match => ["end_date", "ISO8601"]
       target => "end_date"
   }
   date {
        match => ["added_on", "ISO8601"]
       target => "added_on"
   }
   date {
        match => ["end_time", "ISO8601"]
       target => "end_time"
   }
   mutate { convert => {
          "is_full_day" => "integer"
          "leave_id" => "integer"
          "approved_by" => "integer"
          "staff_id" => "integer"
          "clinic_id" => "integer"
          "approved" => "boolean"
          "leave_in_days" => "integer" }
   } 
}
output {
   	elasticsearch {
        hosts => ["localhost:9200"]
        index => "testupsert_%{+YYYY.MM.dd}"
        user => "elastic"
        document_id => "%{[leave_id]}" # [@metadata][fingerprint]
        doc_as_upsert => true
        action => 'update'
     }
    stdout { codec => rubydebug{ metadata => true} }          
}

```

---

<div class="post-metadata">

**Author:** ![Rios](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rios/32/95745_2.png) [@Rios](https://discuss.elastic.co/u/Rios)\
**Post date:** [February 18, 2024, 10:04am UTC](https://discuss.elastic.co/t/sql-data-upload-using-jdbc-logstash/353435/9 "2024-02-18T10:04:22Z")

</div>

I forgot to mention, you don't need fingerprint since you have _leave\_id_ as PK. However there is the code for fingerprint to make the unique id and you just set as document\_id.

---

<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:** [March 17, 2024, 10:05am UTC](https://discuss.elastic.co/t/sql-data-upload-using-jdbc-logstash/353435/10 "2024-03-17T10:05:19Z")

</div>

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