Support Questions

Find answers, ask questions, and share your expertise

sqoop incremental import last modified error

New Contributor

Hi,

I am trying to do incremental last modified using sqoop

sqoop job --create IncrementalImport \

-- import \

--connect 'jdbc:sqlserver://servername;username=xxxxx;password=xxxx;database=xxxx' \

--driver "com.microsoft.sqlserver.jdbc.SQLServerDriver" \

--table table_name \

--target-dir /dir_name \

--incremental lastmodified \

--check-column date \

--merge-key primary_key_column \

-m 1

I am getting the issue when executing the sqoop job --exec IncrementalImport

Warning: /usr/hdp/2.6.5.0-292/accumulo does not exist! Accumulo imports will fail. Please set $ACCUMULO_HOME to the root of your Accumulo installation. Picked up JAVA_TOOL_OPTIONS: -Xmx1024m -Djava.io.tmpdir=/h/tmp 18/10/24 08:27:40 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.6.5.0-292 ssound13@hpchdd2e:~> sqoop job --exec IncrementalImportLastModified Warning: /usr/hdp/2.6.5.0-292/accumulo does not exist! Accumulo imports will fail. Please set $ACCUMULO_HOME to the root of your Accumulo installation. Picked up JAVA_TOOL_OPTIONS: -Xmx1024m -Djava.io.tmpdir=/h/tmp 18/10/24 08:27:57 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.6.5.0-292 18/10/24 08:27:58 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. 18/10/24 08:27:58 INFO manager.SqlManager: Using default fetchSize of 1000 18/10/24 08:27:58 INFO tool.CodeGenTool: Beginning code generation 18/10/24 08:27:59 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM table_name AS t WHERE 1=0 18/10/24 08:27:59 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM table_name AS t WHERE 1=0 18/10/24 08:27:59 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/hdp/2.6.5.0-292/hadoop-mapreduce Note: /tmp/sqoop-ssound13/compile/48dc5192c1b8869fb61635da887c3aa0/table_name.java uses or overrides a deprecated API. Note: Recompile with -Xlint:deprecation for details. 18/10/24 08:28:00 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-ssound13/compile/48dc5192c1b8869fb61635da887c3aa0/Attrib_Type.jar 18/10/24 08:28:01 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM table_name AS t WHERE 1=0 18/10/24 08:28:01 ERROR manager.SqlManager: SQL exception accessing current timestamp: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'. com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:616) at org.apache.sqoop.manager.SqlManager.getCurrentDbTimestamp(SqlManager.java:987) at org.apache.sqoop.tool.ImportTool.initIncrementalConstraints(ImportTool.java:328) at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:498) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:615) at org.apache.sqoop.tool.JobTool.execJob(JobTool.java:243) at org.apache.sqoop.tool.JobTool.run(JobTool.java:298) at org.apache.sqoop.Sqoop.run(Sqoop.java:147) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234) at org.apache.sqoop.Sqoop.main(Sqoop.java:243) 18/10/24 08:28:01 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:498) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:615) at org.apache.sqoop.tool.JobTool.execJob(JobTool.java:243) at org.apache.sqoop.tool.JobTool.run(JobTool.java:298) at org.apache.sqoop.Sqoop.run(Sqoop.java:147) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234) at org.apache.sqoop.Sqoop.main(Sqoop.java:243)

can anyone please help reg the issue

in the sql server the last modified column has datatype as datetime - yyyy-mm-dd 00:00:000.0000

5 REPLIES 5

Expert Contributor

@Suresh Kumar S, Thanks for posting the logs about the exception and LastModified Column being datetime. To me, it seems like because there is a space between the date and timestamp is what is causing the issue. What you can try is to set the lastmodified or the checkcolumn to string using --map-column-java parameter

Super Collaborator

it doesnt work

Super Collaborator

I have the same issue with sql server, please how can I fix it??

manager.SqlManager: Executing SQL statement: select id_encuesta, persona, pregunta1, respuesta1 FROM dbo.lime_survey WHERE  (1 = 0) 
19/07/15 16:23:33 ERROR manager.SqlManager: SQL exception accessing current timestamp: com.microsoft.sqlserver.jdbc.SQLServerException: Sintaxis incorrecta cerca de ')'.
com.microsoft.sqlserver.jdbc.SQLServerException: Sintaxis incorrecta cerca de ')'.
19/07/15 16:23:33 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: Could not get current time from database

The command i am executing is this:

sqoop import -D mapred.job.name=app_lbk_lime_survey --connect "jdbc:sqlserver://hostname:1433;DatabaseName=xxxxxxx" \
--connection-manager "org.apache.sqoop.manager.GenericJdbcManager" \
--driver "com.microsoft.sqlserver.jdbc.SQLServerDriver" --username xxxxxx--password xxxxx\
--query "select id_encuesta, persona, pregunta1, respuesta1 FROM dbo.lime_survey WHERE \$CONDITIONS" \
--num-mappers 1 --incremental lastmodified --check-column fecha_survey--last-value "2019-07-15 16:19:17.117" \
--target-dir /temp/lime_survey_staging/ --compress --compression-codec snappy \
--as-parquetfile



Contributor

Did you make it work? I'm having the same issue.

Super Collaborator

I tried this, but it doesnt work, with datetime, help please

109934-captura.jpg