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 Mon, 16 Jun 2014 19:46:54 GMT
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