Debian
JDBC
Logstash
Oracle11g

logstash jdbc plugin で oracle を利用するための環境設定

Oracle instant client の deb化

$ sudo apt-get install alien
$ alien -d oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
$ alien -d oracle-instantclient11.2-devel_11.2.0.4.0-2_amd64.rpm
$ alien -d oracle-instantclient11.2-sqlplus_11.2.0.4.0-2_amd64.rpm
$ ls
oracle-instantclient11.2-basic_11.2.0.4.0-2_amd64.deb
oracle-instantclient11.2-devel_11.2.0.4.0-2_amd64.deb
oracle-instantclient11.2-sqlplus_11.2.0.4.0-2_amd64.deb
$

Oracle instant client の install

$ sudo aptitude install libaio1
$ sudo dpkg -i oracle-instantclient11.2-basic_11.2.0.4.0-2_amd64.deb
$ sudo dpkg -i oracle-instantclient11.2-devel_11.2.0.4.0-2_amd64.deb
$ sudo dpkg -i oracle-instantclient11.2-sqlplus_11.2.0.4.0-2_amd64.deb
$ sudo sh -c "echo "/usr/lib/oracle/11.2/client64/lib" > /etc/ld.so.conf.d/oracle.conf"
$ sudo ldconfig

oracle への CLI 接続確認

$ sqlplus64 USER/PASSWORD@HOSTNAME:PORT/DBNAME
???
SQL> quit
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production???
$

logstash jdbc plugin

oracle-jdbc.conf
## http://qiita.com/tsgkdt/items/4c90e87ae00982f6b608
$ sudo ln -s /usr/lib/oracle/11.2/client64/lib/ojdbc6.jar /usr/share/logstash/bin/odjbc6.jar
$ sudo vi /etc/logstash/conf.d/oracle-jdbc.conf
$ cat /etc/logstash/conf.d/oracle-jdbc.conf
input {
  jdbc {
    jdbc_driver_library => "ojdbc6.jar"
    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
    jdbc_connection_string => "jdbc:oracle:thin:@HOSTNAME:PORT/DBNAME"
    jdbc_user => "USER"
    jdbc_password => "PASSWORD"
    schedule => "*/15 * * * *"
    statement_filepath => "SQL_STATEMENT_FILE_PATH"
    last_run_metadata_path => "./jdbc_last_run.dat"
  }
}
output {
  elasticsearch {
    hosts => "ELASTICSEARCH_HOST:9200"
    manage_template => false
    index => "INDEXNAME-%{+YYYY.MM.dd}"
    document_type => "%{[@metadata][type]}"
#    user => "elastic"
#    password => "changeme"
  }
}
$

SQL文はファイル化したものを statement_filepath で参照する方法を採っています。

SQL_STATEMENT_FILE_PATH
select
  row1,
  row2
from
  some_table