Hi Jarek Jarcec Cecho
 
   Thanks for your response.
 
  I am sorry that the failed job because of the oraoop plugin which I add before. Anyways I post the command and the log[1].

  The mixed update/insert job succeeds when I adopt the pure sqoop . Thanks a  lot.




[1]---------------------------------------------------------------------------------------------------
[hadoop@Hadoop01 ~]$  /home/sqoop-1.4.1-cdh4.1.2/bin/sqoop export --connect jdbc:oracle:thin:@10.167.14.225:1521:wxoss --username XUJINGYU --password 123456  --export-dir sqoop/CMTSIFTABLE/201305131241  --table XUJINGYU.CMTSIFTABLE   --fields-terminated-by "|"    --columns IFID,DEVICEID,IFDESC,IFINDEX,IFTYPE,IFSPEED,IFALIAS,IFNAME --update-key IFID,DEVICEID   --update-key IFID,CMTSID --update-mode  allowinsert
Warning: /usr/lib/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
13/05/24 12:58:55 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/05/24 12:58:56 INFO manager.SqlManager: Using default fetchSize of 1000
13/05/24 12:58:57 INFO oraoop.OraOopOracleQueries: Current schema is: XUJINGYU
13/05/24 12:58:57 INFO oraoop.OraOopManagerFactory: 
***********************************************************************
*** Using Quest® Data Connector for Oracle and Hadoop 1.6.0-cdh4-20 ***
*** Copyright 2012 Quest Software, Inc.                             ***
*** ALL RIGHTS RESERVED.                                            ***
***********************************************************************
13/05/24 12:58:57 WARN oraoop.OraOopManagerFactory: 
*****************************************************************************************************************************
The table "XUJINGYU"."CMTSIFTABLE" does not have a valid index on the column(s) IFID,DEVICEID.
As a consequence, this export may take a long time to complete.
If performance is unacceptable, consider reattempting this job after creating an index on this table via the SQL...
create index <index_name> on "XUJINGYU"."CMTSIFTABLE"(IFID,DEVICEID);
*****************************************************************************************************************************
13/05/24 12:58:57 INFO oraoop.OraOopManagerFactory: Oracle Database version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
13/05/24 12:58:57 INFO oraoop.OraOopManagerFactory: This Oracle database is not a RAC.
13/05/24 12:58:57 WARN conf.Configuration: mapred.map.max.attempts is deprecated. Instead, use mapreduce.map.maxattempts
13/05/24 12:58:57 INFO manager.SqlManager: Executing SQL statement: SELECT IFID,DEVICEID,IFDESC,IFINDEX,IFTYPE,IFSPEED,IFNAME,IFALIAS FROM XUJINGYU.CMTSIFTABLE WHERE 1=0
13/05/24 12:58:57 INFO tool.CodeGenTool: Beginning code generation
13/05/24 12:58:57 INFO manager.SqlManager: Executing SQL statement: SELECT "IFID","DEVICEID","IFDESC","IFINDEX","IFTYPE","IFSPEED","IFNAME","IFALIAS" FROM XUJINGYU.CMTSIFTABLE WHERE 0=1
13/05/24 12:58:57 INFO orm.CompilationManager: HADOOP_HOME is /usr/local/hadoop
Note: /tmp/sqoop-hadoop/compile/7f64b56b53b34f3fe3d6eab03a5255ae/XUJINGYU_CMTSIFTABLE.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
13/05/24 12:58:59 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/7f64b56b53b34f3fe3d6eab03a5255ae/XUJINGYU.CMTSIFTABLE.jar
13/05/24 12:58:59 ERROR tool.ExportTool: Error during export: Mixed update/insert is not supported against the target database yet




 


2013/5/23 Jarek Jarcec Cecho <jarcec@apache.org>
Hi YouPeng,
Sqoop's Oracle connector do supports upsert mode [1]. Would you mind sharing with us entire Sqoop command line and log generated with parameter --verbose?

Jarcec

Links:
1: https://github.com/apache/sqoop/blob/trunk/src/java/org/apache/sqoop/manager/OracleManager.java#L401

On Wed, May 22, 2013 at 06:20:06PM +0800, YouPeng Yang wrote:
> Hi Jarek Jarcec Cecho
>
>   I also have found the tip. Thank you.
>
>   Here comes another question.
>
>   I find that  sqoop-1.4  support one  to update rows if they exist in the
> database already or insert rows if they do not exist yet  by using
> --update-key <col-name> --update-mode <mode>.
>
>   However, I got the error when I tried it:
>   ERROR tool.ExportTool: Error during export: Mixed update/insert is not
> supported against the target database yet
>
>  Note: the database is oracle.
>
>  1.Does it only supports the MySQL,but I found nothing that hint this in
> docs.
>
>  2.Is there any solutions that fullfil my issue to update rows if they
> exist in the database already or insert rows if they do not exist yet
>
>
>
> Thanks you.
>
>
> Regards.
>
>
> 2013/5/22 Jarek Jarcec Cecho <jarcec@apache.org>
>
> > Hi YouPeng,
> > Sqoop 1 do not supports custom insert query when exporting data from HDFS.
> > I think that in your use case you can use parameter --columns to specify
> > which columns and in what order are present on HDFS, for example:
> >
> >   sqoop ... --columns ID,TIMEID,COLA,COLB
> >
> > Jarcec
> >
> > On Wed, May 22, 2013 at 02:49:04PM +0800, YouPeng Yang wrote:
> > > Hi
> > >   I want to  export data on the HDFS to the oracle database with
> > > sqoop-1.4(sqoop-1.4.1-cdh4.1.2). However the columns betwean HDFS and
> > > Oracle table  are  not exactly same to each other.
> > >
> > >  For example,Data on HDFS:
> > > -------------------------------------------------------------
> > > | ID |      TIMEID          |   COLA  | COLB  |
> > > -------------------------------------------------------------
> > > | 6  |  201305221335    |    0        |  20      |
> > > -------------------------------------------------------------
> > >
> > > the Oracle table:
> > > ------------------------------------------------------------------------
> > > | ID |      TIMEID          |   COLC  | COLB   | COLA |
> > > ------------------------------------------------------------------------
> > > | 7  |  201305221335    |     kk      |  20      |  1       |
> > > ------------------------------------------------------------------------
> > > Note:Additional COLC and unsame order.
> > >
> > >
> > > I notice the sqoop export command:
> > > --export-dir   HDFS source path for the export
> > > --table  Table to populate.
> > > It seams not to export the data to oracle in free from just as Free-form
> > > Query Imports
> > > using the --query argument.
> > >
> > > Could I acheive that goal ?
> > >
> > > Thanks very much
> > >
> > > Regards
> >