Hi,
I use Logstash with jdbc plugin and Cassandra.
Elasticsearch: 6.1.2
logstash-input-jdbc: 4.3.3
Cassandra: 3.11.1
Jdbc driver: dbschema.CassandraJdbcDriver
This is my config:
input {
jdbc {
clean_run => "false"
jdbc_connection_string => "jdbc:cassandra://0.0.0.0:9042"
jdbc_user => "myuser"
jdbc_password => "mypass"
jdbc_driver_library => "/home/logstash_driver/CassandraDBSchema/cassandrajdbc1.1.jar"
jdbc_driver_class => "com.dbschema.CassandraJdbcDriver"
statement => "select * from mydatabase.products_to_elastic WHERE lastmodified > :sql_last_value ALLOW FILTERING"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
schedule => "* * * * *"
tracking_column => "lastmodified"
tracking_column_type => "timestamp"
record_last_run => "true"
sql_log_level => "info"
jdbc_default_timezone => "UTC"
}
}
I have this error when i enable pagination:
[2018-01-30T12:53:03,090][ERROR][logstash.inputs.jdbc ] Java::ComDatastaxDriverCoreExceptions::SyntaxError: line 1:32 no viable alternative at input '(' (...() AS "COUNT" FROM [(]...): SELECT count() AS "COUNT" FROM (select * from mydatabase.products_to_elastic WHERE lastmodified > '2018-01-30 11:59:02.709784' ALLOW FILTERING) AS "T1" LIMIT 1
[2018-01-30T12:53:03,097][WARN ][logstash.inputs.jdbc ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::ComDatastaxDriverCoreExceptions::SyntaxError: line 1:32 no viable alternative at input '(' (...(*) AS "COUNT" FROM [(]...)>}
Is possible than other DB support this query, but with Cassandra it doesn't work.
If I set jdbc_paging_enabled => "false"
I don't have this error, but I obtain another error:
[2018-01-30T13:31:00,462][ERROR][logstash.inputs.jdbc ] Java::ComDatastaxDriverCoreExceptions::InvalidQueryException: Unable to coerce '2018-01-30 11:59:02.709784' to a formatted date (long): select * from mydatabase.products_to_elastic WHERE lastmodified > '2018-01-30 11:59:02.709784' ALLOW FILTERING
[2018-01-30T13:31:00,464][WARN ][logstash.inputs.jdbc ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::ComDatastaxDriverCoreExceptions::InvalidQueryException: Unable to coerce '2018-01-30 11:59:02.709784' to a formatted date (long)>}
It seems that the cassandra date and logstash are not compatible.
If I try this query directly on Cassandra doesn't work: ( select * from mydatabase.products_to_elastic WHERE lastmodified > '2018-01-30 11:59:02.709784' ALLOW FILTERING )
If I change the date format it works well: (select * from mydatabase.products_to_elastic WHERE lastmodified > '2018-01-30 11:59:02.709' ALLOW FILTERING )
'2018-01-30 11:59:02.709784' -> '2018-01-30 11:59:02.709'
I try to use filters to change the data, but filter works with data after the input.
it's possible that I have to change any jdbc configurations, but I don't know where.