sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Leonardo Brambilla <lbrambi...@contractor.elance-odesk.com>
Subject Re: Cannot export TIMESTAMP column to Oracle
Date Tue, 17 Jun 2014 13:16:47 GMT
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.
>
>
>
> *From: *Leonardo Brambilla <lbrambilla@contractor.elance-odesk.com>
> *Reply-To: *"user@sqoop.apache.org" <user@sqoop.apache.org>
>
> *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:
>
> 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>
> *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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

Mime
View raw message