Hi David, thank you very much for the hint. Luckily I was able to convert my data to string without much burden. Anyway for future issues, I will try to follow this and use the Long timestamps.
Regards, Leo On Tue, Jun 17, 2014 at 5:29 AM, David Robson < [email protected]> wrote: > 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]> > 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]> > *Reply-To: *"[email protected]" <[email protected]> > > *Date: *Monday, June 16, 2014 3:28 PM > > > *To: *"[email protected]" <[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]> wrote: > > Thank you Brenden. > > I'll look into that. > > > > Leo > > > > On Mon, Jun 16, 2014 at 3:26 PM, Brenden Cobb <[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]> > *Reply-To: *"[email protected]" <[email protected]> > *Date: *Monday, June 16, 2014 1:56 PM > *To: *"[email protected]" <[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]> > 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]> 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 > > > > > > > > > > > > > > >
