使用 Logstash 拷贝 ORACLE 的数据
使用 Logstash 拷贝 ORACLE 的数据到 MySQL
因为一些原因，公司需要把 ORACLE 数据库里的数据迁移到 MySQL 内，这里记录我使用 Logstash 解决的过程。
输入源选择: JDBC
因为需要读数据库，所以使用 JDBC 作为数据源，相关文档参考： JDBC Input Plugin
输入源配置
input {
jdbc {
jdbc_driver_library => "D:/Middleware/ELK/logstash-8.5.2/lib/jdbc/ojdbc8-21.3.0.0.jar"
jdbc_driver_class => "oracle.jdbc.driver.OracleDriver"
jdbc_connection_string => "jdbc:oracle:thin:@NE:1521:XE"
jdbc_user => "c##assetsana"
jdbc_password => "123456"
statement => "SELECT * FROM assets"
}
}jdbc_driver_library => string
该项为 jdbc 库的文件路径 JDBC driver library path to third party driver library. In case of multiple libraries being required you can pass them separated by a comma.文档原文
jdbc_driver_class => string
驱动的类名 JDBC driver class to load, for example, "org.apache.derby.jdbc.ClientDriver"文档原文
jdbc_connection_string => string
连接 url 字符串 文档原文
jdbc_user => string
鉴权用户名 文档原文
jdbc_password => string
鉴权密码 文档原文
statement => string
需要执行的 sql 文档原文
输出配置
由于 logstash 默认没有 jdbc output plugin，所以需要额外安装。 Logstash Output JDBC 输入以下命令安装插件：
logstash-plugin install logstash-output-jdbc使用：
output {
jdbc {
driver_class => "com.mysql.cj.jdbc.Driver"
driver_jar_path => "D:/Middleware/ELK/logstash-8.5.2/lib/jdbc/mysql-connector-java-8.0.28.jar"
connection_string => "jdbc:mysql://localhost:3306/assetsana"
username => "root"
password => "123456"
statement => ["INSERT INTO assets(id, name, brand, model, serial, capitalization_date, purchase_date, purchase_amount, type, code) VALUES(?,?,?,?,?,?,?,?,?,?)",
"id", "name", "brand", "model", "serial", "capitalization_date", "purchase_date", "purchase_amount", "type", "code"]
}
}driver_class => string
驱动类名 Specify a driver class if autoloading fails
driver_jar_path => string
驱动 jar 包位置 File path to jar file containing your JDBC driver. This is optional, and all JDBC jars may be placed in $LOGSTASH_HOME/vendor/jar/jdbc instead.
connection_string => string
连接字符串 JDBC connection URL
statement => array
sql 语句，下标 0 为 prepared statement，后面为参数 An array of strings representing the SQL statement to run. Index 0 is the SQL statement that is prepared, all other array entries are passed in as parameters (in order). A parameter may either be a property of the event (i.e. "@timestamp", or "host") or a formatted string (i.e. "%{host} - %{message}" or "%{message}"). If a key is passed then it will be automatically converted as required for insertion into SQL. If it's a formatted string then it will be passed in verbatim.
扩展内容
数据处理
Mutate Filter Plugin 因为命名规则的问题，使用 rename 可以重命名字段
filter {
mutate {
rename => {
"capitalization_date" => "capitalizationDate"
...
}
}
}