+ sqoop user

The answer is in your exception! Check your data, your hitting unique key
violation.

Thanks,
Abhijeet

On Tue, Mar 5, 2013 at 7:24 PM, Ajit Kumar Shreevastava <
[EMAIL PROTECTED]> wrote:

>  Hi All,****
>
> ** **
>
> I am facing following issue while exporting table from hive to Oracle.
> Importing table from Oracle to Hive and HDFS is working fine. Please let me
> know where I lag. I am pasting my screen output here.****
>
> ** **
>
> ** **
>
> *[hadoop@NHCLT-PC44-2 sqoop-oper]$ sqoop export --connect
> jdbc:oracle:thin:@10.99.42.11:1521/clouddb --username HDFSUSER  --table
> BTTN_BKP --export-dir  /home/hadoop/user/hive/warehouse/bttn  -P --verbose
> -m 1  --input-fields-terminated-by '\001'*
>
> Warning: /usr/lib/hbase does not exist! HBase imports will fail.****
>
> Please set $HBASE_HOME to the root of your HBase installation.****
>
> 13/03/05 19:20:11 DEBUG tool.BaseSqoopTool: Enabled debug logging.****
>
> Enter password:****
>
> 13/03/05 19:20:16 DEBUG sqoop.ConnFactory: Loaded manager factory:
> com.cloudera.sqoop.manager.DefaultManagerFactory****
>
> 13/03/05 19:20:16 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
> com.cloudera.sqoop.manager.DefaultManagerFactory****
>
> 13/03/05 19:20:16 DEBUG manager.DefaultManagerFactory: Trying with scheme:
> jdbc:oracle:thin:@10.99.42.11****
>
> 13/03/05 19:20:16 DEBUG manager.OracleManager$ConnCache: Instantiated new
> connection cache.****
>
> 13/03/05 19:20:16 INFO manager.SqlManager: Using default fetchSize of 1000
> ****
>
> 13/03/05 19:20:16 DEBUG sqoop.ConnFactory: Instantiated ConnManager
> org.apache.sqoop.manager.OracleManager@2abe0e27****
>
> 13/03/05 19:20:16 INFO tool.CodeGenTool: Beginning code generation****
>
> 13/03/05 19:20:16 DEBUG manager.OracleManager: Using column names query:
> SELECT t.* FROM BTTN_BKP t WHERE 1=0****
>
> 13/03/05 19:20:16 DEBUG manager.OracleManager: Creating a new connection
> for jdbc:oracle:thin:@10.99.42.11:1521/clouddb, using username: HDFSUSER**
> **
>
> 13/03/05 19:20:16 DEBUG manager.OracleManager: No connection paramenters
> specified. Using regular API for making connection.****
>
> 13/03/05 19:20:16 INFO manager.OracleManager: Time zone has been set to GMT
> ****
>
> 13/03/05 19:20:16 DEBUG manager.SqlManager: Using fetchSize for next
> query: 1000****
>
> 13/03/05 19:20:16 INFO manager.SqlManager: Executing SQL statement: SELECT
> t.* FROM BTTN_BKP t WHERE 1=0****
>
> 13/03/05 19:20:16 DEBUG manager.OracleManager$ConnCache: Caching released
> connection for jdbc:oracle:thin:@10.99.42.11:1521/clouddb/HDFSUSER****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter: selected columns:****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   BTTN_ID****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   DATA_INST_ID****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   SCR_ID****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   BTTN_NU****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   CAT****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   WDTH****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   HGHT****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   KEY_SCAN****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   KEY_SHFT****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   FRGND_CPTN_COLR****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   FRGND_CPTN_COLR_PRSD****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   BKGD_CPTN_COLR****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   BKGD_CPTN_COLR_PRSD****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   BLM_FL****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   LCLZ_FL****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   MENU_ITEM_NU****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   BTTN_ASGN_LVL_ID****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   ON_ATVT****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   ON_CLIK****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   ENBL_FL****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   BLM_SET_ID****
>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   BTTN_ASGN_LVL_NAME****

It seems your violating unique key constraint in second task attempt which
is obvious if  there is already some data committed by Sqoop in first
attempt. This is an issue with Sqoop!

>From the exception in first attempt it looks like there is some issue when
auto-generated class (BTTN_BKP.java in your case) tries parsing the data.
Can you validate the data being inserted (not the unique constraint but
some invalid data for some column in input files)?

If data is huge - a way of narrowing down your search for the problematic
row:
Run sqoop with just one mapper (-m 1) . Make sure you have one file that
contains the whole data. If multiple files, the way I can think of right
now is to run sqoop per file or merge the data in one file. I am not sure
if this info is enough for you to control number of mappers but the motive
here is to make sure only one map task is created for the job.
After running sqoop and getting that exception; looking at the inserted
data in the table, you can figure out which was the next batch that Sqoop
was trying to insert!
Try and find which is the erroneous row - usually a batch would contain
1000 rows (rows per insert statement (100) * no. of insert statements
(100)) before a commit is fired. So, check next 1000 rows! If you want to
narrow this down further set this in your command line:

$ sqoop import *-Dsqoop.export.records.per.statement=1 -D
sqoop.export.statements.per.transaction=1* --connect ...
This will make sure you commit after insertion of every row. Haven't tried
this with Oracle, but I was able to set the batch size using these options
for SqlServer/mysql.

Let us hope this helps you find any invalid data values if any! If no
invalid data, I would suggest continuing this discussion in sqoop
user/sqoop dev mailing lists; your still posting via hive user.

Thanks,
Abhijeet

On Wed, Mar 6, 2013 at 11:14 AM, Ajit Kumar Shreevastava <
[EMAIL PROTECTED]> wrote:

>  Hi Abhijeet,****
>
> Data is fine. Firstly map reducer running for 48% then failed. After that map reducer again tried to load the same data due to that unique constraints error came.****
>
> ** **
>
> Regards,****
>
> Ajit Kumar Shreevastava****
>
> abhijeet gaikwad <[EMAIL PROTECTED]> wrote:****
>
> ** **
>
>  + sqoop user
>
> The answer is in your exception! Check your data, your hitting unique key
> violation.
>
> Thanks,
> Abhijeet****
>
> On Tue, Mar 5, 2013 at 7:24 PM, Ajit Kumar Shreevastava <
> [EMAIL PROTECTED]> wrote:****
>
> Hi All,****
>
>  ****
>
> I am facing following issue while exporting table from hive to Oracle.
> Importing table from Oracle to Hive and HDFS is working fine. Please let me
> know where I lag. I am pasting my screen output here.****
>
>  ****
>
>  ****
>
> *[hadoop@NHCLT-PC44-2 sqoop-oper]$ sqoop export --connect
> jdbc:oracle:thin:@10.99.42.11:1521/clouddb --username HDFSUSER  --table
> BTTN_BKP --export-dir  /home/hadoop/user/hive/warehouse/bttn  -P --verbose
> -m 1  --input-fields-terminated-by '\001'*****
>
> Warning: /usr/lib/hbase does not exist! HBase imports will fail.****
>
> Please set $HBASE_HOME to the root of your HBase installation.****
>
> 13/03/05 19:20:11 DEBUG tool.BaseSqoopTool: Enabled debug logging.****
>
> Enter password:****
>
> 13/03/05 19:20:16 DEBUG sqoop.ConnFactory: Loaded manager factory:
> com.cloudera.sqoop.manager.DefaultManagerFactory****
>
> 13/03/05 19:20:16 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
> com.cloudera.sqoop.manager.DefaultManagerFactory****
>
> 13/03/05 19:20:16 DEBUG manager.DefaultManagerFactory: Trying with scheme:
> jdbc:oracle:thin:@10.99.42.11****
>
> 13/03/05 19:20:16 DEBUG manager.OracleManager$ConnCache: Instantiated new
> connection cache.****
>
> 13/03/05 19:20:16 INFO manager.SqlManager: Using default fetchSize of 1000
> ****
>
> 13/03/05 19:20:16 DEBUG sqoop.ConnFactory: Instantiated ConnManager
> org.apache.sqoop.manager.OracleManager@2abe0e27****
>
> 13/03/05 19:20:16 INFO tool.CodeGenTool: Beginning code generation****

Hi Ajit,
would you mind upgrading to Sqoop 1.4.3 RC 0 [1]? It has been already voted to be released as the final 1.4.3, so it should be safe to use.

One of the improvements in 1.4.3 is SQOOP-720 [2] that significantly improves the error message in this scenario.

Jarcec

Links:
1: http://people.apache.org/~hshreedharan/sqoop-1.4.3-rc0/
2: https://issues.apache.org/jira/browse/SQOOP-720

On Wed, Mar 06, 2013 at 05:44:44AM +0000, Ajit Kumar Shreevastava wrote:
> Hi Abhijeet,
>
> Data is fine. Firstly map reducer running for 48% then failed. After that map reducer again tried to load the same data due to that unique constraints error came.
>
>
>
> Regards,
>
> Ajit Kumar Shreevastava
>
> abhijeet gaikwad <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:
>
>
> + sqoop user
>
> The answer is in your exception! Check your data, your hitting unique key violation.
>
> Thanks,
> Abhijeet
> On Tue, Mar 5, 2013 at 7:24 PM, Ajit Kumar Shreevastava <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:
> Hi All,
>
> I am facing following issue while exporting table from hive to Oracle. Importing table from Oracle to Hive and HDFS is working fine. Please let me know where I lag. I am pasting my screen output here.
>
>
> [hadoop@NHCLT-PC44-2 sqoop-oper]$ sqoop export --connect jdbc:oracle:thin:@10.99.42.11:1521/clouddb<http://jdbc:oracle:thin:@10.99.42.11:1521/clouddb> --username HDFSUSER  --table BTTN_BKP --export-dir  /home/hadoop/user/hive/warehouse/bttn  -P --verbose  -m 1  --input-fields-terminated-by '\001'
> Warning: /usr/lib/hbase does not exist! HBase imports will fail.
> Please set $HBASE_HOME to the root of your HBase installation.
> 13/03/05 19:20:11 DEBUG tool.BaseSqoopTool: Enabled debug logging.
> Enter password:
> 13/03/05 19:20:16 DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
> 13/03/05 19:20:16 DEBUG sqoop.ConnFactory: Trying ManagerFactory: com.cloudera.sqoop.manager.DefaultManagerFactory
> 13/03/05 19:20:16 DEBUG manager.DefaultManagerFactory: Trying with scheme: jdbc:oracle:thin:@10.99.42.11<mailto:jdbc%3Aoracle%3Athin%[EMAIL PROTECTED]>
> 13/03/05 19:20:16 DEBUG manager.OracleManager$ConnCache: Instantiated new connection cache.
> 13/03/05 19:20:16 INFO manager.SqlManager: Using default fetchSize of 1000
> 13/03/05 19:20:16 DEBUG sqoop.ConnFactory: Instantiated ConnManager org.apache.sqoop.manager.OracleManager@2abe0e27<mailto:org.apache.sqoop.manager.OracleManager@2abe0e27>
> 13/03/05 19:20:16 INFO tool.CodeGenTool: Beginning code generation
> 13/03/05 19:20:16 DEBUG manager.OracleManager: Using column names query: SELECT t.* FROM BTTN_BKP t WHERE 1=0
> 13/03/05 19:20:16 DEBUG manager.OracleManager: Creating a new connection for jdbc:oracle:thin:@10.99.42.11:1521/clouddb<http://jdbc:oracle:thin:@10.99.42.11:1521/clouddb>, using username: HDFSUSER
> 13/03/05 19:20:16 DEBUG manager.OracleManager: No connection paramenters specified. Using regular API for making connection.
> 13/03/05 19:20:16 INFO manager.OracleManager: Time zone has been set to GMT
> 13/03/05 19:20:16 DEBUG manager.SqlManager: Using fetchSize for next query: 1000
> 13/03/05 19:20:16 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM BTTN_BKP t WHERE 1=0
> 13/03/05 19:20:16 DEBUG manager.OracleManager$ConnCache: Caching released connection for jdbc:oracle:thin:@10.99.42.11:1521/clouddb/HDFSUSER<http://jdbc:oracle:thin:@10.99.42.11:1521/clouddb/HDFSUSER>
> 13/03/05 19:20:16 DEBUG orm.ClassWriter: selected columns:
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   BTTN_ID
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   DATA_INST_ID
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   SCR_ID
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   BTTN_NU
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   CAT
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   WDTH
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   HGHT
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   KEY_SCAN
> 13/03/05 19:20:16 DEBUG orm.ClassWriter:   KEY_SHFT