search

confluentinc / kafka-connect-jdbc

Kafka Connect connector for JDBC-compatible databases
Other
18 stars 955 forks source link

Get CURRENT_TIMESTAMP not working for SAP HANA JDBC #445

Open brunodomenici opened 6 years ago

brunodomenici commented 6 years ago

Hi,

I'm trying to configure a source from a SAP HANA View with timestamp increment and I got this error:

 ERROR Failed to get current time from DB using query select CURRENT_TIMESTAMP; on database HDB (io.confluent.connect.jdbc.util.JdbcUtils)
com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [257] (at 8): sql syntax error: incorrect syntax near ";": line 1 col 8 (at pos 8)
    at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:345)
    at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateDatabaseException(SQLExceptionSapDB.java:176)
    at com.sap.db.jdbc.packet.ReplyPacket.buildExceptionChain(ReplyPacket.java:102)
    at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:1029)
    at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:819)
    at com.sap.db.jdbc.StatementSapDB.sendCommand(StatementSapDB.java:896)
    at com.sap.db.jdbc.StatementSapDB.sendSQL(StatementSapDB.java:945)
    at com.sap.db.jdbc.StatementSapDB.execute(StatementSapDB.java:256)
    at com.sap.db.jdbc.StatementSapDB.executeQuery(StatementSapDB.java:401)
    at com.sap.db.jdbc.trace.Statement.executeQuery(Statement.java:184)
    at io.confluent.connect.jdbc.util.JdbcUtils.getCurrentTimeOnDB(JdbcUtils.java:255)
    at io.confluent.connect.jdbc.source.TimestampIncrementingTableQuerier.executeQuery(TimestampIncrementingTableQuerier.java:192)
    at io.confluent.connect.jdbc.source.TableQuerier.maybeStartQuery(TableQuerier.java:85)
    at io.confluent.connect.jdbc.source.TimestampIncrementingTableQuerier.maybeStartQuery(TimestampIncrementingTableQuerier.java:57)
    at io.confluent.connect.jdbc.source.JdbcSourceTask.poll(JdbcSourceTask.java:226)
    at org.apache.kafka.connect.runtime.WorkerSourceTask.execute(WorkerSourceTask.java:186)
    at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:170)
    at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:214)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

It seems that the command generated to get current timestamp (select CURRENT_TIMESTAMP;) doesn't work for HANA. On Hana it could be something like SELECT CURRENT_TIMESTAMP from dummy

Is it possible to include a configuration parameter to set this query for current timestamp?

Thank you.

mariusneo commented 5 years ago

There is a dialect created for SAP Hana DB

io.confluent.connect.jdbc.dialect.SapHanaDatabaseDialect

which extends io.confluent.connect.jdbc.dialect.GenericDatabaseDialect class

The method io.confluent.connect.jdbc.dialect.GenericDatabaseDialect#currentTimestampDatabaseQuery

looks like this:

/**
   * Get the query string to determine the current timestamp in the database.
   *
   * @return the query string; never null or empty
   */
  protected String currentTimestampDatabaseQuery() {
    return "SELECT CURRENT_TIMESTAMP";
  }

I guess that with a PR this could be changed. As I see the SapHanaDatabaseDialect exists since 2016 and I am wondering if it had the issue you're pointing for 2 years.

shoban-kovi commented 5 years ago

We are experiencing the same issue while connecting SAP Hana using Kafka connect jdbc. As @mariusneo mentioned above, do we know if its being worked on to update the existing io.confluent.connect.jdbc.dialect.SapHanaDatabaseDialect or please let me know if there is any work around to fix it. Thanks.

brunodomenici commented 5 years ago

Sorry for the delay. I'm not able to test it anymore. I'll close this issue.

tlxu commented 4 years ago

We are experiencing the same issue while connecting SAP Hana using Kafka connect jdbc. As @mariusneo mentioned above, do we know if its being worked on to update the existing io.confluent.connect.jdbc.dialect.SapHanaDatabaseDialect or please let me know if there is any work around to fix it. Thanks.

I've created a pull request to fix issue. But sadly it's still under review:( We are using a private build for our product.

brunodomenici commented 4 years ago

Alright, I'm reopening this guys...

shoban-kovi commented 4 years ago

There seems to be a patch released( but not merged yet) per https://github.com/confluentinc/kafka-connect-jdbc/pull/692 . When we tried using that JAR keeping the rest of the config/etc. files, we are running into different issue i.e org.apache.kafka.connect.errors.DataException: rowUpdateTimestamp is not a valid field name Here rowUpdateTimestamp is our incremental timestamp column set for timestamp.column.name in config-templates/jdbc-source.properties.template. The said timestamp field is available in HANA and we are able to manually execute the SQL passed to the jdbc-source.properties.template file in the database. Please let me know if you had used that patch version and successfully load incremental data.

lucasmaffazioli commented 4 years ago

Thanks @tlxu, let's hope the Confluent team merge it soon enough

lucasmaffazioli commented 4 years ago

@shoban-kovi can you build Kafka connect on Windows? How did you manage it?