Hive, mail # user - RE: Error while exporting table data from hive to Oracle through Sqoop

Error while exporting table data from hive to Oracle through Sqoop

Ajit Kumar Shreevastava 2013-03-05, 13:54

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
13/03/05 19:20:16 DEBUG orm.ClassWriter: MKT_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: CRTE_TS
13/03/05 19:20:16 DEBUG orm.ClassWriter: CRTE_USER_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: UPDT_TS
13/03/05 19:20:16 DEBUG orm.ClassWriter: UPDT_USER_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: DEL_TS
13/03/05 19:20:16 DEBUG orm.ClassWriter: DEL_USER_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: DLTD_FL
13/03/05 19:20:16 DEBUG orm.ClassWriter: MENU_ITEM_NA
13/03/05 19:20:16 DEBUG orm.ClassWriter: PRD_CD
13/03/05 19:20:16 DEBUG orm.ClassWriter: BLM_SET_NA
13/03/05 19:20:16 DEBUG orm.ClassWriter: SOUND_FILE_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: IS_DYNMC_BTTN
13/03/05 19:20:16 DEBUG orm.ClassWriter: FRGND_CPTN_COLR_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: FRGND_CPTN_COLR_PRSD_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: BKGD_CPTN_COLR_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: BKGD_CPTN_COLR_PRSD_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: Writing source file: /tmp/sqoop-hadoop/compile/8d22103beede09e961b64d0ff8e61e7e/BTTN_BKP.java
13/03/05 19:20:16 DEBUG orm.ClassWriter: Table name: BTTN_BKP
13/03/05 19:20:16 DEBUG orm.ClassWriter: Columns: BTTN_ID:2, DATA_INST_ID:2, SCR_ID:2, BTTN_NU:2, CAT:2, WDTH:2, HGHT:2, KEY_SCAN:2, KEY_SHFT:2, FRGND_CPTN_COLR:12, FRGND_CPTN_COLR_PRSD:12, BKGD_CPTN_COLR:12, BKGD_CPTN_COLR_PRSD:12, BLM_FL:2, LCLZ_FL:2, MENU_ITEM_NU:2, BTTN_ASGN_LVL_ID:2, ON_ATVT:2, ON_CLIK:2, ENBL_FL:2, BLM_SET_ID:2, BTTN_ASGN_LVL_NAME:12, MKT_ID:2, CRTE_TS:93, CRTE_USER_ID:12, UPDT_TS:93, UPDT_USER_ID:12, DEL_TS:93, DEL_USER_ID:12, DLTD_FL:2, MENU_ITEM_NA:12, PRD_CD:2, BLM_SET_NA:12, SOUND_FILE_ID:2, IS_DYNMC_BTTN:2, FRGND_CPTN_COLR_ID:2, FRGND_CPTN_COLR_PRSD_ID:2, BKGD_CPTN_COLR_ID:2, BKGD_CPTN_COLR_PRSD_ID:2,
13/03/05 19:20:16 DEBUG orm.ClassWriter: sourceFilename is BTTN_BKP.java
13/03/05 19:20:16 DEBUG orm.CompilationManager: Found existing /tmp/sqoop-hadoop/compile/8d22103beede09e961b64d0ff8e61e7e/
13/03/05 19:20:16 INFO orm.CompilationManager: HADOOP_HOME is /home/hadoop/hadoop-1.0.3/libexec/..
13/03/05 19:20:16 DEBUG orm.CompilationManager: Adding source file: /tmp/sqoop-hadoop/compile/8d22103beede09e961b64d0ff8e61e7e/BTTN_BKP.java
13/03/05 19:20:16 DEBUG orm.CompilationManager: Invoking javac with args:
13/03/05 19:20:16 DEBUG orm.CompilationManager: -sourcepath
13/03/05 19:20:16 DEBUG orm.CompilationManager: /tmp/sqoop-hadoop/compile/8d22103beede09e961b64d0ff8e61e7e/
13/03/05 19:20:16 DEBUG orm.CompilationManager: -d
13/03/05 19:20:16 DEBUG orm.CompilationManager: /tmp/sqoop-hadoop/compile/8d22103beede09e961b6

Re: Error while exporting table data from hive to Oracle through Sqoop

Dean Wampler 2013-03-05, 13:58

>From the exceptions near the bottom, it looks like you're inserting data
that doesn't have unique keys, so it could be a data problem.

On Tue, Mar 5, 2013 at 7:54 AM, 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****

*Dean Wampler, Ph.D.*
thinkbiganalytics.com
+1-312-339-1330

RE: Error while exporting table data from hive to Oracle through Sqoop

Ajit Kumar Shreevastava 2013-03-06, 05:44

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
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
13/03/05 19:20:16 DEBUG orm.ClassWriter: MKT_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: CRTE_TS
13/03/05 19:20:16 DEBUG orm.ClassWriter: CRTE_USER_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: UPDT_TS
13/03/05 19:20:16 DEBUG orm.ClassWriter: UPDT_USER_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: DEL_TS
13/03/05 19:20:16 DEBUG orm.ClassWriter: DEL_USER_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: DLTD_FL
13/03/05 19:20:16 DEBUG orm.ClassWriter: MENU_ITEM_NA
13/03/05 19:20:16 DEBUG orm.ClassWriter: PRD_CD
13/03/05 19:20:16 DEBUG orm.ClassWriter: BLM_SET_NA
13/03/05 19:20:16 DEBUG orm.ClassWriter: SOUND_FILE_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: IS_DYNMC_BTTN
13/03/05 19:20:16 DEBUG orm.ClassWriter: FRGND_CPTN_COLR_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: FRGND_CPTN_COLR_PRSD_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: BKGD_CPTN_COLR_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: BKGD_CPTN_COLR_PRSD_ID
13/03/05 19:20:16 DEBUG orm.ClassWriter: Writing source file: /tmp/sqoop-hadoop/compile/8d22103beede09e961b64d0ff8e61e7e/BTTN_BKP.java
13/03/05 19:20:16 DEBUG orm.ClassWriter: Table name: BTTN_BKP
13/03/05 19:20:16 DEBUG orm.ClassWriter: Columns: BTTN_ID:2, DATA_INST_ID:2, SCR_ID:2, BTTN_NU:2, CAT:2, WDTH:2, HGHT:2, KEY_SCAN:2, KEY_SHFT:2, FRGND_CPTN_COLR:12, FRGND_CPTN_COLR_PRSD:12, BKGD_CPTN_COLR:12, BKGD_CPTN_COLR_PRSD:12, BLM_FL:2, LCLZ_FL:2, MENU_ITEM_NU:2, BTTN_ASGN_LVL_ID:2, ON_ATVT:2, ON_CLIK:2, ENBL_FL:2, BLM_SET_ID:2, BTTN_ASGN_LVL_NAME:12, MKT_ID:2, CRTE_TS:93, CRTE_USER_ID:12, UPDT_TS:93, UPDT_USER_ID:12, DEL_TS:93, DEL_USER_ID:12, DLTD_FL:2, MENU_ITEM_NA:12, PRD_CD:2, BLM_SET_NA:12, SOUND_FILE_ID:2, IS_DYNMC_BTTN:2, FRGND_CPTN_COLR_ID:2, FRGND_CPTN_COLR_PRSD_ID:2, BKGD_CPTN_COLR_ID:2, BKGD_CPTN_COLR_PRSD_ID:2,
13/03/05 19:20:16 DEBUG orm.Cl