1、增量更新的配置:
如果需要增量更新。其配置文件如下:
input {
stdin { }
jdbc {
jdbc_driver_library => "G:/MvnRepository/mysql/mysql-connector-java/5.1.41/mysql-connector-java-5.1.41.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://localhost:3306/xnc"
jdbc_user => "root"
jdbc_password => "123456"
schedule => "* * * * *"
#在文件中记录列的值
use_column_value => true
#需要记录哪个列的值
tracking_column => "update_time"
#列值记录在哪个文件 默认值是 "/home/ph/.logstash_jdbc_last_run"
last_run_metadata_path => "G:\logstash-5.5.1\myPlugin\.logstash_jdbc_last_run"
# :sql_last_value 引用记录的列值
statement => "SELECT id, product_spec_id,zone_id,recorded_by,CAST(price_per_unit AS CHAR) price_per_unit,uom,latest,recording_date,create_time,update_time from price p where update_time > :sql_last_value order by update_time asc"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
}
}
output {
#标准输出。为了测试
stdout { codec => rubydebug }
jdbc {
driver_class => "com.mysql.jdbc.Driver"
driver_jar_path => "G:/MvnRepository/mysql/mysql-connector-java/5.1.41/mysql-connector-java-5.1.41.jar"
connection_string => "jdbc:mysql://localhost:3306/xncprice"
username => "root"
password => "123456"
statement => [ "REPLACE INTO price (id, product_spec_id, zone_id,recorded_by,price_per_unit,uom,latest,recording_date,create_time,update_time) VALUES(?,?,?,?,CAST(? AS decimal),?,?,?,?,?)", "id", "product_spec_id", "zone_id", "recorded_by","price_per_unit", "uom" , "latest" , "recording_date" , "create_time" , "update_time" ]
}
}
说明:
#在文件中记录列的值
use_column_value => true
#需要记录哪个列的值
tracking_column => "update_time"
#列值记录在哪个文件 默认值是 "/home/ph/.logstash_jdbc_last_run"
last_run_metadata_path => "G:\logstash-5.5.1\myPlugin\.logstash_jdbc_last_run"
上面这3个参数联合起来表示: 在G:\logstash-5.5.1\myPlugin\.logstash_jdbc_last_run 文件中记录update_time列的值。也就是记录查询出中的price的最后一条数据中,update_time的值。
注意sql语句需要order by update_time desc ,这样记录的最后一条。后面的更新就以这个为标准。
2、参考文档