I'm quite unclear of what sql_last_value
does when I give my statement as such:
statement => "SELECT * from mytable where id > :sql_last_value"
I can slightly understand the reason behind using it, where it doesn't browse through the whole db table in order to update fields instead it only updates the records which were added newly. Correct me if I'm wrong.
So what I'm trying to do is, creating the index using logstash
as such:
input {
jdbc {
jdbc_connection_string => "jdbc:mysql://hostmachine:3306/db"
jdbc_user => "root"
jdbc_password => "root"
jdbc_validate_connection => true
jdbc_driver_library => "/path/mysql_jar/mysql-connector-java-5.1.39-bin.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
schedule => "* * * * *"
statement => "SELECT * from mytable where id > :sql_last_value"
use_column_value => true
tracking_column => id
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
}
}
output {
elasticsearch {
#protocol => http
index => "myindex"
document_type => "message_logs"
document_id => "%{id}"
action => index
hosts => ["http://myhostmachine:9402"]
}
}
Once I do this, the docs aren't getting uploaded at all to the index. Where am I going wrong?
Any help could be appreciated.
If you have a timestamp column in your table (e.g. last_updated
), you should preferably use it instead of the ID one. So that when a record gets updated, you modify that timestamp as well and the jdbc
input plugin will pick up the record (i.e. the ID column won't change its value and the updated record won't get picked up)
input {
jdbc {
jdbc_connection_string => "jdbc:mysql://hostmachine:3306/db"
jdbc_user => "root"
jdbc_password => "root"
jdbc_validate_connection => true
jdbc_driver_library => "/path/mysql_jar/mysql-connector-java-5.1.39-bin.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
schedule => "* * * * *"
statement => "SELECT * from mytable where last_updated > :sql_last_value"
}
}
If you decide to stay with the ID column nonetheless, you should delete the $HOME/.logstash_jdbc_last_run
file and try again.