|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
Yet I see that fails with the following error
java.sql.SQLDataException: ORA-01861: literal does not match format string
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|
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?
|Re: Sqoop Import Failure ORA-01861: literal does not match format string||CDHUser||2/21/13 2:46 PM|
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 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
After that it created the final Query as
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.
|Re: Sqoop Import Failure ORA-01861: literal does not match format string||Jarek Jarcec Cecho||2/23/13 9:54 AM|
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  have conditional statement based on the column type and is returning either TO_DATE or TO_TIMESTAMP. Unfortunately the caller code  seems to be overriding the original column type to Types.Timestamp. I've filled SQOOP-906  to address that.
|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???
|Re: Sqoop Import Failure ORA-01861: literal does not match format string||Jarek Jarcec Cecho||3/4/13 8:23 PM|
would you mind sharing with us your entire command line and log generated with argument --verbose?