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.

sqoop incremental import last modified error

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

4 REPLIES 4

Re: sqoop incremental import last modified error

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

Re: sqoop incremental import last modified error

Super Collaborator

it doesnt work

Re: sqoop incremental import last modified error

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



Highlighted

Re: sqoop incremental import last modified error

Super Collaborator

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

109934-captura.jpg