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 <David.Robson@software.dell.com> 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:lbrambilla@contractor.elance-odesk.com]
Sent: Tuesday, 17 June 2014 5:47 AM
To: user@sqoop.apache.org


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 <Brenden.Cobb@humedica.com> 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.

 

Date: Monday, June 16, 2014 3:28 PM


To: "user@sqoop.apache.org" <user@sqoop.apache.org>
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 <lbrambilla@contractor.elance-odesk.com> wrote:

Thank you Brenden. 

I'll look into that.

 

Leo

 

On Mon, Jun 16, 2014 at 3:26 PM, Brenden Cobb <Brenden.Cobb@humedica.com> wrote:

 

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 <lbrambilla@contractor.elance-odesk.com>
Reply-To: "user@sqoop.apache.org" <user@sqoop.apache.org>
Date: Monday, June 16, 2014 1:56 PM
To: "user@sqoop.apache.org" <user@sqoop.apache.org>
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 <abe@cloudera.com> 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 <lbrambilla@contractor.elance-odesk.com> 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