Hi Jerac,

Thanks for your suggession.

I have applied the below suggestion and re-do all the process again with the SQOOP1.4.3 (sqoop-1.4.3.bin__hadoop-1.0.0.tar.gz) but I have face same below error again. Please suggest me.

Here I have created table in hive as  suggested by you.

hive> create table bttn_bkp like bttn_bkp_test;

hive> insert OVERWRITE table bttn_bkp

    > select * from bttn_bkp_test;

Regards,

Ajit Kumar Shreevastava

-----Original Message-----
From: Jarek Jarcec Cecho [mailto:[EMAIL PROTECTED]]
Sent: Sunday, March 17, 2013 4:26 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: Data mismatch when importing data from Oracle to Hive through Sqoop without an error

Hi Ajit,

please accept my apologies for late reply. Please upgrade to latest Sqoop version 1.4.3, we've improved the error message so it should be much easier to triangulate the problem.

Jarcec

On Wed, Mar 13, 2013 at 08:44:24AM +0000, Ajit Kumar Shreevastava wrote:

> Thanks Chalcy and Jarek

>

> Now, I am facing one new problem with Sqoop export utility.

>

> I have implemented what both of you suggested in chain mail and my problem is solved for importing the data to Hive.

>

> Now I have truncate the table in oracle and try to export the data from just populated table in Hive is working fine and no record mismatch was found.

> Command for export to oracle is :-->

> [hadoop@NHCLT-PC44-2 sqoop-oper]$ sqoop export --connect jdbc:oracle:thin:@10.99.42.11:1521/clouddb --username HDFSUSER  --table BTTN_BKP_TEST --export-dir  /home/hadoop/user/hive/warehouse/bttn_bkp_test -P -m 1  --input-fields-terminated-by '\0001' -verbose

>

> Now I want to create a table in hive as a copy of bttn_bkp_test.

> I described the bttn_bkp_test table and created the table column described in bttn_bkp_test like :-->

>

> hive> CREATE TABLE IF NOT EXISTS BTTN_BKP(

>     > bttn_id                 double,

>     > data_inst_id            double,

>     > scr_id                  double,

>     > bttn_nu                 double,

>     > cat                     double,

>     > wdth                    double,

>     > hght                    double,

>     > key_scan                double,

>     > key_shft                double,

>     > frgnd_cptn_colr         string,

>     > frgnd_cptn_colr_prsd    string,

>     > bkgd_cptn_colr          string,

>     > bkgd_cptn_colr_prsd     string,

>     > blm_fl                  double,

>     > lclz_fl                 double,

>     > menu_item_nu            double,

>     > bttn_asgn_lvl_id        double,

>     > on_atvt                 double,

>     > on_clik                 double,

>     > enbl_fl                 double,

>     > blm_set_id              double,

>     > bttn_asgn_lvl_name      string,

>     > mkt_id                  double,

>     > crte_ts                 string,

>     > crte_user_id            string,

>     > updt_ts                 string,

>     > updt_user_id            string,

>     > del_ts                  string,

>     > del_user_id             string,

>     > dltd_fl                 double,

>     > menu_item_na            string,

>     > prd_cd                  double,

>     > blm_set_na              string,

>     > sound_file_id           double,

>     > is_dynmc_bttn           double,

>     > frgnd_cptn_colr_id      double,

>     > frgnd_cptn_colr_prsd_id double,

>     > bkgd_cptn_colr_id       double,

>     > bkgd_cptn_colr_prsd_id  double);

>

> Then I populate this table by following command:-->

> hive> insert OVERWRITE table bttn_bkp

>    > select * from BTTN_BKP_TEST;

>

> And its insert the same data in bttn_bkp without any error.

> Now I have truncated the oracle table bttn_bkp_test and try to populate the table by bttn_bkp table data of Hive through below Sqoop command :-->

>

> [hadoop@NHCLT-PC44-2 sqoop-oper]$ sqoop export --connect jdbc:oracle:thin:@10.99.42.11:1521/clouddb --username HDFSUSER  --table BTTN_BKP_TEST --export-dir  /home/hadoop/user/hive/warehouse/bttn_bkp_test -P -m 1  --input-fields-terminated-by '\0001' -verbose

Hi Jarcec,

Thanking you for your valuable input and your suggestion seems to be valid. But I have some doubt about the SQOOP  behavior :-->

1.       If null create the confusion then some value similar to the below mentioned value are inserted to the oracle table with null treated as   string.

     hive> select * from bttn_bkp_testing

              > where bttn_id=39126;

39126.0 32436.0 3276.0  3.0     28.0    1.0     1.0     1.0     1.0     #FFFFFF #0000FF #0000FF #FFFFFF 0.0     0.0     NULL    NULL    1.0     1.0     1.0     NULL   null     20.0    2010-05-04 14:31:17.0   dbmigration     2013-01-18 09:11:18.37  DP_CQ4540       2010-11-29 15:45:03.976 ei009724        1.0     null    NULL    null   NULL     0.0     61253.0 61124.0 61124.0 61253.0

39126.0 50805.0 3276.0  3.0     28.0    1.0     1.0     1.0     1.0     #FFFFFF #0000FF #0000FF #FFFFFF 0.0     0.0     NULL    NULL    1.0     1.0     1.0     NULL   null     20.0    2010-05-23 23:18:54.604 ei103215        2013-01-18 09:11:18.37  DP_CQ4540       2010-11-29 15:45:03.976 ei009724        1.0     null    NULL    null   NULL     0.0     61253.0 61124.0 61124.0 61253.0

39126.0 63196.0 3276.0  3.0     28.0    1.0     1.0     1.0     1.0     #FFFFFF #0000FF #0000FF #FFFFFF 0.0     0.0     NULL    NULL    1.0     1.0     1.0     NULL   null     20.0    2010-11-04 18:25:23.956 ei103215        2013-01-18 09:11:18.37  DP_CQ4540       2010-11-29 15:45:03.976 ei009724        1.0     null    NULL    null   NULL     0.0     61253.0 61124.0 61124.0 61253.0

These values are inserted into the oracle table BTTN_BKP_TEST as follows:--> .

SQL> Select * from BTTN_BKP_TEST where bttn_id=39126;

39126    32436    3276       3              28           1              1              1              1              #FFFFFF               #0000FF               #0000FF                #FFFFFF               0              0                                              1              1              1                              null         20           05/04/2010 2:31:17.000000 PM          dbmigration       01/18/2013 9:11:18.370000 AM  DP_CQ4540        11/29/2010 3:45:03.976000 PM                ei009724              1              null                         null                         0              61253    61124    61124    61253

39126    50805    3276       3              28           1              1              1              1              #FFFFFF               #0000FF               #0000FF                #FFFFFF               0              0                                              1              1              1                              null         20           05/23/2010 11:18:54.604000 PM        ei103215              01/18/2013 9:11:18.370000 AM  DP_CQ4540        11/29/2010 3:45:03.976000 PM                ei009724              1              null                         null                         0              61253    61124    61124    61253

39126    63196    3276       3              28           1              1              1              1              #FFFFFF               #0000FF               #0000FF                #FFFFFF               0              0                                              1              1              1                              null         20           11/04/2010 6:25:23.956000 PM          ei103215              01/18/2013 9:11:18.370000 AM  DP_CQ4540        11/29/2010 3:45:03.976000 PM                ei009724              1              null                         null                         0              61253    61124    61124    61253

But the raised exception for below value:-->

hive> select * from bttn_bkp_testing

        > where bttn_id= 194628.0;

194628.0        577019.0        8910.0  19.0    1.0     1.0     1.0     0.0     0.0     #FFFFFF #FF0000 #FF0000 #FFFFFF 0.0     0.0     1646.0  NULL    NULL    NULL   1.0      NULL    null    20.0    2012-04-19 23:25:48.78  ei009724        2013-01-18 09:11:30.245 DP_CQ4540       null    null    0.0     BLUEBERRY MUFFIN        7836.0 null     NULL    0.0     61259.0 61230.0 61230.0 61259.0

194628.0        706360.0        8910.0  19.0    1.0     1.0     1.0     0.0     0.0     #FFFFFF #FF0000 #FF0000 #FFFFFF 0.0     0.0     1646.0  NULL    NULL    NULL   1.0      NULL    null    20.0    2012-05-21 01:01:53.629 ei103215        2013-01-18 09:11:30.245 DP_CQ4540       null    null    0.0     BLUEBERRY MUFFIN        7836.0 null     NULL    0.0     61259.0 61230.0 61230.0 61259.0

194628.0        1620395.0       8910.0  19.0    1.0     1.0     1.0     0.0     0.0     #FFFFFF #FF0000 #FF0000 #FFFFFF 0.0     0.0     1646.0  NULL    NULL    NULL   1.0      NULL    null    20.0    2012-08-10 04:34:00.203 ei103215        2013-01-18 09:11:30.245 DP_CQ4540       null    null    0.0     BLUEBERRY MUFFIN        7836.0 null     NULL    0.0     61259.0 61230.0 61230.0 61259.0

194628.0        1694103.0       8910.0  19.0    1.0     1.0     1.0     0.0     0.0     #FFFFFF #FF0000 #FF0000 #FFFFFF 0.0     0.0     1646.0  NULL    NULL    NULL   1.0      NULL    null    20.0    2012-11-08 01:09:15.136 ei103215        2013-01-18 09:11:30.245 DP_CQ4540       null    null    0.0     BLUEBERRY MUFFIN        7836.0 null     NULL    0.0     61259.0 61230.0 61230.0 61259.0

194628.0        1831767.0       8910.0  19.0    1.0     1.0     1.0     0.0     0.0     #FFFFFF #FF0000 #FF0000 #FFFFFF 0.0     0.0     1646.0  NULL    NULL    NULL   1.0      NULL    null    20.0    2012-12-19 23:44:44.241 e0025129        2013-01-18 09:11:30.245 DP_CQ4540       null    null    0.0     BLUEBERRY MUFFIN        7836.0 null     NULL    0.0     61259.0 61230.0 61230.0 61259.0

2.       For your information I have mentioned two interesting fact here for you regarding SQOOP behavior. First, I have imported Bttn table from Oracle into Hive bttn_bkp_test_new table using following command

[hadoop@NHCLT-PC44-2 ~]$ sqoop import --connect jdbc:oracle:thin:@10.99.42.