Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

work around for SQL syntax error on sqoop job from incremental import from SAP Hana

work around for SQL syntax error on sqoop job from incremental import from SAP Hana

I am looking for a workaround for a sqoop import of data incrementally from SAP HANA to HDP. I set up the following sqoop job:

sqoop job --create itemincjob -- import --username SYSTEM --password my_pw --connect jdbc:sap://1.2.3.4:30215/?currentschema=PRODUCTS --driver com.sap.db.jdbc.Driver --table ITEM --target-dir /user/hive/item_incremental --incremental lastmodified --check-column LAST_MODIFIED -m 1 --merge-key ID

The source table has a TIMESTAMP column called LAST_MODIFIED that I hope to use for incremental loads. There is a similar entry for Teradata (https://community.hortonworks.com/questions/41080/sqoop-incremental-load-failed-with-timestamp.html) The workaround is to use an int column instead but this might not be possible if your HANA schema is fixed.

However, I get an SQL exception accessing the current timestamp. The error message is below followed by the entire session output.

16/09/23 15:02:53 ERROR manager.SqlManager: SQL exception accessing current timestamp: 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)

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)
[root@sandbox ~]# sqoop job --exec itemincjob

Warning: /usr/hdp/2.4.0.0-169/accumulo does not exist! Accumulo imports will fail.

Please set $ACCUMULO_HOME to the root of your Accumulo installation.

16/09/23 15:02:45 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.4.0.0-169

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/usr/hdp/2.4.0.0-169/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/usr/hdp/2.4.0.0-169/zookeeper/lib/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

Enter password: 

16/09/23 15:02:49 WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time.

16/09/23 15:02:49 INFO manager.SqlManager: Using default fetchSize of 1000

16/09/23 15:02:49 INFO tool.CodeGenTool: Beginning code generation

16/09/23 15:02:50 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM ITEM AS t WHERE 1=0

16/09/23 15:02:50 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM ITEM AS t WHERE 1=0

16/09/23 15:02:50 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/hdp/2.4.0.0-169/hadoop-mapreduce

Note: /tmp/sqoop-root/compile/eb3e10116c9f91dd6a16b53e33a334b0/ITEM.java uses or overrides a deprecated API.

Note: Recompile with -Xlint:deprecation for details.

16/09/23 15:02:52 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/eb3e10116c9f91dd6a16b53e33a334b0/ITEM.jar

16/09/23 15:02:53 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM ITEM AS t WHERE 1=0

16/09/23 15:02:53 ERROR manager.SqlManager: SQL exception accessing current timestamp: 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)

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:1033)

	at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:823)

	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 org.apache.sqoop.manager.SqlManager.getCurrentDbTimestamp(SqlManager.java:968)

	at org.apache.sqoop.tool.ImportTool.initIncrementalConstraints(ImportTool.java:328)

	at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:488)

	at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)

	at org.apache.sqoop.tool.JobTool.execJob(JobTool.java:228)

	at org.apache.sqoop.tool.JobTool.run(JobTool.java:283)

	at org.apache.sqoop.Sqoop.run(Sqoop.java:148)

	at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)

	at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184)

	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226)

	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235)

	at org.apache.sqoop.Sqoop.main(Sqoop.java:244)

16/09/23 15:02:53 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: Could not get current time from database

	at org.apache.sqoop.tool.ImportTool.initIncrementalConstraints(ImportTool.java:330)

	at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:488)

	at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)

	at org.apache.sqoop.tool.JobTool.execJob(JobTool.java:228)

	at org.apache.sqoop.tool.JobTool.run(JobTool.java:283)

	at org.apache.sqoop.Sqoop.run(Sqoop.java:148)

	at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)

	at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184)

	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226)

	at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235)

	at org.apache.sqoop.Sqoop.main(Sqoop.java:244)



1 REPLY 1

Re: work around for SQL syntax error on sqoop job from incremental import from SAP Hana

Super Guru