欢迎光临
感受代码之美

记一次logstash 7配置jdbc插件使用实践

logstash.jpg

环境

  • logstash-7.1.1.rpm;
  • elasticsearch-7.1.1-linux-x86_64.tar.gz;
  • 使用的是mysql数据库;

前期准备

  • 数据库驱动文件:/home/inspur/elasticsearch/lib/mysql-connector-java-8.0.12.jar;
  • SQL文件放置目录:/home/inspur/elasticsearch/sqls/jw_reporter_info.sql;

SQL文件内容

SELECT 
`REPORTER_ID`,
`NAME`,
`SEX`,
ifnull(`BIRTHDAY`,'') AS `BIRTHDAY` ,
`ID_NUMBER`,
`POLITIC`,
`NATION`,
`NATIVE_PLACE`,
`BIRTH_PLACE`,
`PARTY_IN_DATE`,
`WORK_DATE`,
`HEALTH_CONDITION`,
`TECHNICAL_POSITION`,
`PROFESSIONAL_TITLE`,
`FULLTIME_EDUCATION`,
`FULLTIME_EDUCATION_COLLEGE`,
`INSERVICE_EDUCATION`,
`INSERVER_EDUCATION_COLLEGE`,
`PRESENT_POST`,
`PROPOSED_POST`,
`PROPOSED_REMOVAL_POST`,
`RESUME`,
`REWARDS_PUNISHMENT`,
`ANNUAL_ASSESSMENT_RESULTS`,
`APPOINT_REMOVE_REASONS`,
`CHENG_BAO_DW`,
`CALCULATE_AGE_TIME`,
`REPORTING_TIME`,
`REPORTER`,
`PHOTO`,
`REMARK`,
`CRT_USER_ID`,
`CRT_USER_NAME`,
`CRT_TIME`,
`UPDATE_USER_ID`,
`UPDATE_USER_NAME`,
`UPDATE_TIME`,
`SORT`,
`DATA_SOURCE`,
`IS_DELETE` 
FROM jw_reporter_info t 
where str_to_date(ifnull(UPDATE_TIME,'1970-01-01 00:00:00'),'%Y-%m-%d %H:%i:%s')  >= str_to_date(:sql_last_value,'%Y-%m-%d %H:%i:%s')
and is_delete = 0

logstash配置

input {
  jdbc {
    jdbc_driver_library => "/home/inspur/elasticsearch/lib/mysql-connector-java-8.0.12.jar"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://your_ip_address:3306/data?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Hongkong"
    jdbc_user => "your_user_name"
    jdbc_password => "your_user_name_passwd"
    schedule => "* * * * *"
    statement_filepath => "/home/inspur/elasticsearch/sqls/jw_reporter_info.sql"
    use_column_value => true
    tracking_column => "update_time"
    tracking_column_type => "timestamp"
    clean_run => true
    type => "jw_reporter_info"
  }
}

output {
   if [type] == 'jw_reporter_info' {
       elasticsearch { 
          hosts => ["10.47.0.96:9200"]
          index => "data_archives_jw_reporter_info"
          document_id => "%{reporter_id}"
       }
   } 
}

启动

sudo /usr/share/logstash/bin/logstash  -f /etc/logstash/conf.d/ --path.settings=/etc/logstash
转载请注明来源:四个空格 » 记一次logstash 7配置jdbc插件使用实践

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址