Sqoop Import Failure ORA-01861: literal does not match format string

Showing 1-7 of 7 messages
Sqoop Import Failure ORA-01861: literal does not match format string CDHUser 2/20/13 3:11 PM
I have been trying to get the sqoop imprt working with no luck so far. I have tried the Sqoop Users group but it seems to be disbanded.

See the below command and exception

sqoop import --driver oracle.jdbc.OracleDriver --connect jdbc:oracle:thin:@somehost:1521:sysid --username test --password test --query "select r.id, r.transaction_date from request r where r.id in ('xyz') and \$CONDITIONS" --num-mappers 1 --target-dir hdfs://host/targetdir/ --as-textfile --incremental append --check-column transaction_date --last-value "2013-02-20 10:00:00.0"

I see that it is able to find the upper and lower bounds

13/02/20 15:54:28 INFO tool.ImportTool: Incremental import based on column transaction_date
13/02/20 15:54:28 INFO tool.ImportTool: Lower bound value: '2013-02-20 10:00:00.0'
13/02/20 15:54:28 INFO tool.ImportTool: Upper bound value: '2013-02-20 22:54:15.0'
13/02/20 15:54:28 INFO mapreduce.ImportJobBase: Beginning query import.

Yet I see that fails with the following error

java.sql.SQLDataException: ORA-01861: literal does not match format string

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:457)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:400)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:926)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:476)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:200)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:543)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:238)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1244)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1492)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1710)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4372)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4453)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:6270)
        at org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111)
        at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:236)
        at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:458)
        at org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:76)
        at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.java:85)
        at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:139)
        at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:182)
        at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:645)
        at org.apache.hadoop.mapred.MapTask.run(MapTask.java:325)
        at org.apache.hadoop.mapred.LocalJobRunner$Job.run(LocalJobRunner.java:263)
13/02/20 15:54:30 INFO mapreduce.AutoProgressMapper: Auto-progress thread is finished. keepGoing=false
13/02/20 15:54:30 WARN mapred.LocalJobRunner: job_local_0001
java.io.IOException: SQLException in nextKeyValue
        at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:265)
        at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:458)
        at org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:76)
        at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.java:85)
        at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:139)
        at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:182)
        at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:645)
        at org.apache.hadoop.mapred.MapTask.run(MapTask.java:325)
        at org.apache.hadoop.mapred.LocalJobRunner$Job.run(LocalJobRunner.java:263)
Caused by: java.sql.SQLDataException: ORA-01861: literal does not match format string

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:457)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:400)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:926)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:476)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:200)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:543)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:238)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1244)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1492)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1710)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4372)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4453)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:6270)
        at org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111)
        at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:236)
        ... 8 more

Can anyone of you see what could be going wrong?? Your help is appreciated.

 
Re: Sqoop Import Failure ORA-01861: literal does not match format string Jarek Jarcec Cecho 2/21/13 8:38 AM
Hi sir,
the --driver argument forces Sqoop to use Generic JDBC Connector instead of the database specific Connector. As a start I would recommend dropping this parameter. In case that it won't fix the problem, would you mind attaching the appropriate map task log?

Jarcec
> --
>
>
>
Re: Sqoop Import Failure ORA-01861: literal does not match format string CDHUser 2/21/13 2:46 PM
Jarec,

Thanks a lot for your reply.

Removing the driver did allow me to proceed further. But I have a question regarding the Date/Time manipulations in Sqoop in the same example above I have an issue with the DATE column type

Sqoop Command

sqoop import --connect jdbc:oracle:thin:@dbhost:1521:sysid --username test --password test --query "select r.id, r.ref_no, r.transaction_date  from request r where r.id in ('xyz') and \$CONDITIONS" --num-mappers 1 --target-dir hdfs://host/targetdir/ --as-textfile --incremental append --check-column transaction_date --last-value "TIMESTAMP '2013-02-21 19:41:00'" --verbose
It went through and it created the following Query to get the Boundary Vals

13/02/21 15:19:15 DEBUG tool.ImportTool: Using temporary folder: 21151915584null
13/02/21 15:19:15 INFO tool.ImportTool: Maximal id query for free form incremental import: SELECT MAX(transaction_date) FROM (select r.id, r.ref_no,r.transaction_date from request r where r.id in ('xyz') and (1 = 1)) sqoop_import_query_alias
13/02/21 15:19:15 INFO tool.ImportTool: Incremental import based on column transaction_date
13/02/21 15:19:15 INFO tool.ImportTool: Lower bound value: TO_TIMESTAMP('2013-02-21 19:41:00', 'YYYY-MM-DD HH24:MI:SS.FF')
13/02/21 15:19:15 INFO tool.ImportTool: Upper bound value: TO_TIMESTAMP('2013-02-21 22:19:02.0', 'YYYY-MM-DD HH24:MI:SS.FF')


After that it created the final Query as

select r.id, r.ref_no, r.transaction_date from request r where r.id in ('xyz') and transaction_date > TIMESTAMP '2013-02-21 19:41:00' AND transaction_date <= TO_TIMESTAMP('2013-02-21 22:05:27.0', 'YYYY-MM-DD HH24:MI:SS.FF') AND ( 1=1 ) AND ( 1=1 )

 The issue here with the query is the the Column TRANSACTION_DATE is of Type DATE and not TIME_STAMP. This query above neve returns. I have tried this query independently against the database and it doesn't work as well. If I change the above query changing the TO_TIMESTAMP to TO_DATE then the query works.

But how do I tell sqoop to generate a query with TO_DATE instead of TO_TIMESTAMP.

Thanks
Re: Sqoop Import Failure ORA-01861: literal does not match format string Jarek Jarcec Cecho 2/23/13 9:54 AM
Hi Sir,
please accept my apologies for the late reply, I needed to explore the source code in order to get most correct answer. It seems that the OracleManager is correctly written as the appropriate method [1] have conditional statement based on the column type and is returning either TO_DATE or TO_TIMESTAMP. Unfortunately the caller code [2] seems to be overriding the original column type to Types.Timestamp. I've filled SQOOP-906 [2] to address that.

Jarcec

Links:
1: https://github.com/apache/sqoop/blob/trunk/src/java/org/apache/sqoop/manager/OracleManager.java#L575
2: https://github.com/apache/sqoop/blob/trunk/src/java/org/apache/sqoop/tool/ImportTool.java#L280
3: https://issues.apache.org/jira/browse/SQOOP-906
> --
>
>
>
Re: Sqoop Import Failure ORA-01861: literal does not match format string CDHUser 3/3/13 6:51 PM
I have tried a few workarounds to this issue but one of the option doesn't work and would like to know if I am doing anything. In the same example aboe I have generated the code and modified all java.sql.Timestamp references to java.sql.Date.I am not sure this option would work but wanted to give it a try. Later I packaged it in a jar and used the -libjars option to point to the jar. I used the jar-file and class-name option as well. but I get a ClassNotFoundExcpetion. Can you point me where I might be going wrong???

Thanks
Re: Sqoop Import Failure ORA-01861: literal does not match format string Jarek Jarcec Cecho 3/4/13 8:23 PM
Hi sir,
would you mind sharing with us your entire command line and log generated with argument --verbose?

Jarcec
> --
>  
>  
>  

CDHUser 3/4/13 11:37 PM <This message has been deleted.>