我应该如何在logstash中使用sql_last_value? [英] How should I use sql_last_value in logstash?

问题描述

当我这样声明时，我还不清楚sql_last_value是做什么的:

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.

所以我想做的是像这样使用logstash创建索引:

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.

推荐答案

如果表中有一个时间戳列(例如last_updated)，则最好使用它而不是ID.这样，当记录更新时，您也可以修改该时间戳，并且jdbc输入插件将提取记录(即ID列不会更改其值，更新的记录也不会被提取)

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"
    }
}

如果您仍然选择保留ID列，则应删除$HOME/.logstash_jdbc_last_run文件，然后重试.

If you decide to stay with the ID column nonetheless, you should delete the $HOME/.logstash_jdbc_last_run file and try again.

这篇关于我应该如何在logstash中使用sql_last_value?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！