jdbc output plugin logstash using UPSERT function

Question

I'm using this plugin as output for my logstash logs.

I need to use the upsert function to check if a row exists then update, if it doesn't exist then simply add.

I'm using PostgreSQL as db and it supports the usage of UPSERT, very good described here. As input, the logs are coming from elasticsearch.

The problem with my configuration is that I correctly add new rows in my table but cannot update an existing one.

Here's my configuration :

jdbc {
        driver_jar_path => '/home/vittorio/Downloads/postgresql-42.1.1.jre6.jar'
        connection_test => false
        connection_string => 'jdbc:postgresql://127.0.0.1:5432/postgres'
statement => ["

        INSERT INTO userstate VALUES(?,?,?,?,?) on conflict (username)
        do update set (business_name, iban, status, timestamp) = ('%{[resource][response_attributes][business_name]}','%{[resource][response_attributes][iban]}','%{[resource][response_attributes][status]}','%{@timestamp}')
        where userstate.username = '%{[request][username]}';", "%{[request][username]}","%{[resource][response_attributes][business_name]}","%{[resource][response_attributes][iban]}","%{[resource][response_attributes][status]}","%{@timestamp}"
        ]
 username => "myuser"
 password => "mypass"
}

Am I doing something wrong? thanks

ennon ennon · Accepted Answer · 2017-05-17T15:03:12

I manged to make it work by myself and this is what I've done so far :

  jdbc {
          driver_jar_path => '/home/vittorio/Downloads/postgresql-42.1.1.jre6.jar'
          connection_test => false
          connection_string => 'jdbc:postgresql://127.0.0.1:5432/postgres'
          statement => ["

          INSERT INTO userstate VALUES(?,?,?,?,?)
          on conflict (username)
          do update set (business_name, iban, status, timestamp) = (?,?,?,?)
          where userstate.username = ?"
          , "%{[request][username]}","%{[resource][response_attributes][business_name]}","%{[resource][response_attributes][iban]}","%{[resource][response_attributes][status]}","%{@timestamp}","%{[resource][response_attributes][business_name]}","%{[resource][response_attributes][iban]}","%{[resource][response_attributes][status]}","%{@timestamp}","%{[request][username]}"

          ]
          username => "myusername"
          password => "mypass"

      }

Basically,I've changed the where statement using ? instead of %{[request][username]} and then map each ? with the corresponding value from the log. I know, it's pretty long stuff after the coma but this is the only way I found to make it work. If anyone knows a better way to do it please let me know.

Thank you

jdbc output plugin logstash using UPSERT function

1 Answers