Unable to perform incremental load

[Deepak_Dandotiya]

Hello,

I am new to elasticsearch and logstash.

Now I am running below emp-index-logstash.conf file using logstash to perform the incremental load using <./logstash -f /data/deepak/logstash-6.2.4/bin/emp-index-logstash.conf> but getting below error:-

Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER

below is the content of config file:-

input {

jdbc {

    jdbc_connection_string => "jdbc:oracle:thin:@//localhost:1521/ORCL"

    jdbc_user => "username"

    jdbc_password => "password"

    jdbc_validate_connection => true

    jdbc_driver_library => "/data/deepak/ojdbc6.jar"

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

    schedule => "*/1 * * * *" #every 3 hours

    statement => 'SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno FROM emp where hiredate >:sql_last_value ORDER BY hiredate ASC'

	use_column_value => true
	
	tracking_column => hiredate
	
	last_run_metadata_path => "metadata/logstash_jdbc_last_run_emp"

}

}

output {

elasticsearch {

    index => "employee"

    document_type => "emp_data"

    document_id => "%{empno}"

    hosts => [ "12.55.95.07:17001" ]

}
    stdout{

            codec=>rubydebug

    }

}

Below is the data from metadata file:-
[deepak@hostname metadata]# vi logstash_jdbc_last_run_emp
0

Could you please help me out as i have to implement the same approach to my project

[Wayne_Taylor]

I'll dig out a working oracle config when in office

[Wayne_Taylor]

input {
    jdbc {
        jdbc_connection_string => "jdbc:oracle:thin:@//endpoint:1521/service"
                jdbc_user => "user"
                jdbc_password => "password"
                jdbc_driver_library => "/opt/logstash-5.3.0/bin/ojdbc8.jar"
                jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
                statement =>

"select customer_id, user_name, email_address, last_login from customer where last_login > cast(:sql_last_value as timestamp)"

		record_last_run => true
		last_run_metadata_path => "/opt/logstash-5.3.0/bin/customerlogins.txt"
    }
}


output {

	elasticsearch {
	hosts => "url"
	index => "myindex"
	user => "elastic"
	password => "mypassword"
	}

}

[Deepak_Dandotiya]

Thanks for your help Wayne!!!

[Wayne_Taylor]

Welcome, hope it worked out for you