logstash-input-jdbc实现oracle 与elasticsearch实时同步详解

目标:

实现了oracle与ES同步增、删、改、查。logstash-input-jdbc实现oracle 与elasticsearch实时同步详解 1、配置文件

[root@5b9dbaaa148a logstash_jdbc_test]# cat jdbc_oracle.conf

input {

 stdin {

 }

 jdbc {

 # oracle jdbc connection string to our backup databse

 jdbc_connection_string => "jdbc:oracle:thin:system/123456@//100.1.1.31:1521/xe"

 # the user we wish to excute our statement as

 jdbc_user => "system"

 jdbc_password => "123456"

 # the path to our downloaded jdbc driver

 jdbc_driver_library => "/elasticsearch-jdbc-2.3.2.0/lib/ojdbc6.jar"

 # the name of the driver class for oracle

 jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"


 #new add  begin 2016-6-28

 record_last_run => "true"

 use_column_value => "false"

 tracking_column => "id"

 last_run_metadata_path => "/etc/logstash/run_metadata.d/my_info"

 clean_run => "false"

 #new add by end


 jdbc_paging_enabled => "true"

 jdbc_page_size => "50000"

 statement_filepath => "/usr/local/logstash/bin/logstash_jdbc_test/jdbc_oracle.sql"

 schedule => "* * * * *"

 type => "tstype"

 }

}


filter {

 json {

 source => "message"

 remove_field => ["message"]

 }

 #grok {

 #match => { "message" => "%{COMBINEDAPACHELOG}" }

 #match => { "message" => "test" }

#}

 date {

 match => [ "timestamp" , "dd/MMM/yyyy:HH:mm:ss Z" ]

 }

}


output {

 elasticsearch {

 hosts => "10.8.5.101:9200"

 index => "tsuser"

 document_id => "%{user_id}"

 }

 stdout {

 codec => json_lines

 }

}


[root@5b9dbaaa148a logstash_jdbc_test]# cat jdbc_oracle.sql

select

 *

from

 TS_USER


2、其中:

(1) jdbc_connection_string配置

jdbc_connection_string => "jdbc:oracle:thin:system/123456@//100.1.1.31:1521/xe"

system/123456@// 100.1.1.31:1521 /xe 含义:

用户名/密码@//oracleIP地址:端口/标识符SID


(2) jdbc_driver_library 配置

jdbc_driver_library => "/elasticsearch-jdbc-2.3.2.0/lib/ojdbc6.jar"

ojdbc6.jar所在oracle机器的安装位置如下:

[root@W01 ~]# find / -name "ojdbc6.jar"

/u01/app/oracle/product/11.2.0/xe/jdbc/lib/ojdbc6.jar

ojdbc6.jar 需要ES所在机器放置的位置:

[root@5b9dbaaaa lib]# find / -name "ojdbc6.jar"

/elasticsearch-jdbc-2.3.2.0/lib/ojdbc6.ja


(3) jdbc_driver_class配置

 jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"


同步执行脚本:

[root@5b9dbaa148a bin]# ./logstash -f ./logstash_jdbc_test/jdbc_oracle.conf


update,insert测试ok。

参考详解(原理同):

http://blog.csdn.net/laoyang360/article/details/51747266

NB参考:

http://o7planning.org/en/10227/jdbc-driver-libraries-for-different-types-of-database-in-java

上一篇:Python 开发工具集:关于文档、测试、调试、程序的优化和分析


下一篇:ASP.NET Web Forms 4.5的新特性(三):Model Binding