Hi Leo, You can get OraOop to treat them as longs – I assume you have OraOop 1.6? Here is the section from the documentation:
To use Sqoop's handling of date and timestamp data types when importing data from Oracle use the following parameter: -Doraoop.timestamp.string=false Note: Sqoop's handling of date and timestamp data types does not store the timezone. However, some developers may prefer Sqoop's handling as Quest Data Connector for Oracle and Hadoop converts date and timestamp data types to string. This may not work for some developers as the string will require parsing later in the workflow. David From: Leonardo Brambilla [mailto:[email protected]] Sent: Tuesday, 17 June 2014 5:47 AM To: [email protected] Subject: Re: Cannot export TIMESTAMP column to Oracle Yeah dude!, why convert my wonderful LONGs :-) ?? Anyway, good to know what happened, thank you all for your valuable help. Leo On Mon, Jun 16, 2014 at 4:37 PM, Brenden Cobb <[email protected]<mailto:[email protected]>> wrote: That is correct. Sqoop->Oracle expects: Oracle Data Type Required Format of The Data in the HDFS File DATE yyyy-mm-dd hh24:mi:ss TIMESTAMP yyyy-mm-dd hh24:mi:ss.ff TIMESTAMPTZ yyyy-mm-dd hh24:mi:ss.ff TZR TIMESTAMPLTZ yyyy-mm-dd hh24:mi:ss.ff TZR So there will have to be some formatting in your workflow. I had to do the same for dates. Very annoying. From: Leonardo Brambilla <[email protected]<mailto:[email protected]>> Reply-To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Date: Monday, June 16, 2014 3:28 PM To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Subject: Re: Cannot export TIMESTAMP column to Oracle Well, I was able to install OraOop into Sqoop. Now I see that the problem is that I'm trying to export to Oracle a timestamp column which is a long value (the epoch) but the drivers looks like it requires the date format to be in "human" readable string. At least that's what the user guide says. So, I will need to format my data right? From 1402946848 to 2014-06-16 19:27:28 Thanks On Mon, Jun 16, 2014 at 3:27 PM, Leonardo Brambilla <[email protected]<mailto:[email protected]>> wrote: Thank you Brenden. I'll look into that. Leo On Mon, Jun 16, 2014 at 3:26 PM, Brenden Cobb <[email protected]<mailto:[email protected]>> wrote: If you have installed the connector ( http://www.cloudera.com/content/support/en/downloads/connectors/sqoop/oracle/v1-6.html ) You should see Oraoop invoked when you execute sqoop, no special commands needed. Make sure you are not specifying the ojdbc6 jar in your command string, and you must have at least 2 mappers ( if you specify 1 mapper the job will default to ojdbc6). From: Leonardo Brambilla <[email protected]<mailto:[email protected]>> Reply-To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Date: Monday, June 16, 2014 1:56 PM To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Subject: Re: Cannot export TIMESTAMP column to Oracle Thank you Abraham, do you have a sample command line syntax on how to use the OraOop connector? I can't figure it out how to use it. Regards Leo On Sat, Jun 14, 2014 at 6:18 PM, Abraham Elmahrek <[email protected]<mailto:[email protected]>> wrote: Hey there, Are you using the standard connector or the OraOop connector? I think OraOpp should support TIMESTAMP WITH TIME ZONE type. NOTE: There's a review to add it as part of the standard Sqoop: https://reviews.apache.org/r/18452/. The community is in the process of reviewing this. Until then, it seems available here: https://github.com/QuestSoftwareTCD/OracleSQOOPconnector. -Abe On Fri, Jun 13, 2014 at 3:02 PM, Leonardo Brambilla <[email protected]<mailto:[email protected]>> wrote: Hello, I'm trying to use Sqoop to export data to an Oracle table. The table has a Timestamp column with Type TIMESTAMP(6) WITH TIME ZONE. The value is a long representing milliseconds. Here is a sample row: 1400040430901 NULL NULL 7205849 591117613 SkipNotificationExp The first column is the one with the problem. Here is part of the log: 14/06/13 17:56:08 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead. 14/06/13 17:56:08 INFO manager.SqlManager: Using default fetchSize of 1000 14/06/13 17:56:08 INFO tool.CodeGenTool: Beginning code generation 14/06/13 17:56:09 INFO manager.OracleManager: Time zone has been set to GMT 14/06/13 17:56:09 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM etl.expt_email_rec t WHERE 1=0 14/06/13 17:56:09 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM etl.expt_email_rec t WHERE 1=0 14/06/13 17:56:09 ERROR orm.ClassWriter: Cannot resolve SQL type -101 14/06/13 17:56:09 ERROR orm.ClassWriter: Cannot resolve SQL type -101 14/06/13 17:56:09 ERROR orm.ClassWriter: No Java type for SQL type -101 for column timestamp 14/06/13 17:56:09 ERROR orm.ClassWriter: No Java type for SQL type -101 for column timestamp 14/06/13 17:56:09 ERROR orm.ClassWriter: No Java type for SQL type -101 for column timestamp 14/06/13 17:56:09 ERROR orm.ClassWriter: No Java type for SQL type -101 for column timestamp 14/06/13 17:56:09 ERROR orm.ClassWriter: No Java type for SQL type -101 for column timestamp 14/06/13 17:56:09 ERROR orm.ClassWriter: No Java type for SQL type -101 for column timestamp 14/06/13 17:56:09 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException java.lang.NullPointerException at org.apache.sqoop.orm.ClassWriter.parseNullVal(ClassWriter.java:912) at org.apache.sqoop.orm.ClassWriter.parseColumn(ClassWriter.java:937) at org.apache.sqoop.orm.ClassWriter.generateParser(ClassWriter.java:1020) at org.apache.sqoop.orm.ClassWriter.generateClassForColumns(ClassWriter.java:1364) at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1176) at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96) at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:64) at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:100) at org.apache.sqoop.Sqoop.run(Sqoop.java:145) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229) at org.apache.sqoop.Sqoop.main(Sqoop.java:238) Any hint please? I tried with Sqoop 1.4.3 and 1.4.4, with oracle driver 6. Thanks Leonardo
