spring cloud 引入elasticsearch做oracle数据库全文检索，ouput会冲突的问题（二）

之前我们，通过java直接put对象，让elasticsearch作为数据库，可以通过java api增删查改。

 

现在，我们通过jdbc直接将数据库的数据全部导入到elasticsearch，并定时什么时候进行同步。

elasticsearch相当于redis一样的作为暂时存储，并快速查询的功能，之前遇到过docker容器被移除，日志与elasticsearch存储丢失，所以采用该方式更加安全

 

elasticsearch借助logstash，将数据库数据定期，放入elasticsearch。

配置logstash

input {
    jdbc {
      jdbc_connection_string => "jdbc:oracle:thin:@192.168.12.119:1522:ORCL10G"
      jdbc_user => "SPSS_MODEL"
      jdbc_password => "SPSS_MODEL"
      jdbc_driver_library => "/opt/logstash/ojdbc6.jar"
      jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
      jdbc_paging_enabled => "true"
      # jdbc_page_size => "50000"
      # statement_filepath => "jdbc.sql"
      statement => "SELECT ID id, DATASETABLENAME dataSetTableName, NAME name, URLTYPE             
                           urlType, REQUESTYPE requestType,
		                   OPERATOR operator, RELEASE_DATE releaseDate, 
                           DATASETNAME dataSetName, URL url
		            from S_RELEASE_DATA where 1 = 1"
      # 设置监听间隔 各字段含义（从左至右）分、时、天、月、年，全为*默认含义为每分钟都更新
      schedule => "1 * * * *"
      # 指定type方便在output时判断输出至哪个index
      type => "inter_info"
    }
}
output {
    elasticsearch {
        index => "release_%{+YYYY.MM.dd}"
        hosts => ["192.168.12.122:9200"]
        document_id => "%{id}"
    }
}

首先查看是否能访问通 telnet 192.168.12.119 1522

ubuntu 使用apt-get install telnetd 安装，我发现是可以访问通的。

那么可能是没有安装jdbc插件。

//切换国内镜像
vi /etc/apt/sources.list
//全部删除替换下述文本
deb http://mirrors.163.com/ubuntu/ trusty main restricted universe multiverse
deb http://mirrors.163.com/ubuntu/ trusty-security main restricted universe multiverse
deb http://mirrors.163.com/ubuntu/ trusty-updates main restricted universe multiverse
deb http://mirrors.163.com/ubuntu/ trusty-proposed main restricted universe multiverse
deb http://mirrors.163.com/ubuntu/ trusty-backports main restricted universe multiverse
deb-src http://mirrors.163.com/ubuntu/ trusty main restricted universe multiverse
deb-src http://mirrors.163.com/ubuntu/ trusty-security main restricted universe multiverse
deb-src http://mirrors.163.com/ubuntu/ trusty-updates main restricted universe multiverse
deb-src http://mirrors.163.com/ubuntu/ trusty-proposed main restricted universe multiverse
deb-src http://mirrors.163.com/ubuntu/ trusty-backports main restricted universe multiverse
//保存之后更新
apt-get update
//安装logstash jdbc插件  cd /opt/logstasg/bin
./logstash-plugin install logstash-input-jdbc

 

注意： 如果你的logstash 读取的是多个配置文，其中重复的就会冲突：

比如：我配置了一个

vim 02-beats-input.conf

input {    
    tcp {         
        port => 5044         
        codec => json_lines     
    }     
} 
output{  
    elasticsearch { 
        hosts => ["192.168.12.122:9200"] 
        index => "share_app_logs_index_%{+YYYY.MM.dd}"
    } 
}

这次我们配置了jdbc的。那么就会发现 share的索引，会有jdbc的记录也会有日志。 release中也会有记录和日志。会出现混乱。原因，就是我们有2个elasticsearch没有任何判断，就会出现传输到 2者的某一种。

 

解决方案就是加判断。

output {
    if[type] == "inter_info"{
        elasticsearch {
            index => "release_%{+YYYY.MM.dd}"
            hosts => ["192.168.12.122:9200"]
        }
    }  
}

终极版：

input {
    tcp {
        port => 5044
        codec => json_lines
        type => "log"
    }
}
input{
    jdbc {
      jdbc_connection_string => "jdbc:oracle:thin:@192.168.12.119:1522:ORCL10G"
      jdbc_user => "SPSS_MODEL"
      jdbc_password => "SPSS_MODEL"
      jdbc_driver_library => "/opt/logstash/ojdbc6.jar"
      jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
      jdbc_paging_enabled => "true"
      statement => "SELECT ID id, DATASETABLENAME dataSetTableName, NAME name, URLTYPE
                           urlType, REQUESTYPE requestType,
                                   OPERATOR operator, RELEASE_DATE releaseDate,
                           DATASETNAME dataSetName, URL url
                            from S_RELEASE_DATA where 1 = 1"
      schedule => "1 * * * *"
      type => "inter_info"
    }
}
filter{
   if[type] == "log"{
       mutate{
          lowercase=>["app"]
       }
   }
}
output{
    if[type] == "log"{
        elasticsearch {
            hosts => ["192.168.12.122:9200"]
            action => "index"
            index => "%{app}"
        }
    }else if[type] == "inter_info"{
        elasticsearch {
            index => "release_%{+YYYY.MM.dd}"
            hosts => ["192.168.12.122:9200"]
            document_id => "%{id}"
        }
    }
}