sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brenden Cobb <Brenden.C...@humedica.com>
Subject Re: Cannot export TIMESTAMP column to Oracle
Date Mon, 16 Jun 2014 19:37:44 GMT
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 <lbrambilla@contractor.elance-odesk.com<mailto:lbrambilla@contractor.elance-odesk.com>>
Reply-To: "user@sqoop.apache.org<mailto:user@sqoop.apache.org>" <user@sqoop.apache.org<mailto:user@sqoop.apache.org>>
Date: Monday, June 16, 2014 3:28 PM
To: "user@sqoop.apache.org<mailto:user@sqoop.apache.org>" <user@sqoop.apache.org<mailto: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<mailto: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<mailto:Brenden.Cobb@humedica.com>>
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 <lbrambilla@contractor.elance-odesk.com<mailto:lbrambilla@contractor.elance-odesk.com>>
Reply-To: "user@sqoop.apache.org<mailto:user@sqoop.apache.org>" <user@sqoop.apache.org<mailto:user@sqoop.apache.org>>
Date: Monday, June 16, 2014 1:56 PM
To: "user@sqoop.apache.org<mailto:user@sqoop.apache.org>" <user@sqoop.apache.org<mailto: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<mailto: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<mailto: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







Mime
View raw message