Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Sqoop >> mail # user >> Data mismatch when importing data from Oracle to Hive through Sqoop without an error


Copy link to this message
-
RE: Data mismatch when importing data from Oracle to Hive through Sqoop without an error
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

And I got below error error:-->

Warning: /usr/lib/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
13/03/13 14:00:04 DEBUG tool.BaseSqoopTool: Enabled debug logging.
Enter password:
13/03/13 14:00:09 DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
13/03/13 14:00:09 DEBUG sqoop.ConnFactory: Trying ManagerFactory: com.cloudera.sqoop.manager.DefaultManagerFactory
13/03/13 14:00:09 DEBUG manager.DefaultManagerFactory: Trying with scheme: jdbc:oracle:thin:@10.99.42.11
13/03/13 14:00:09 DEBUG manager.OracleManager$ConnCache: Instantiated new connection cache.
13/03/13 14:00:09 INFO manager.SqlManager: Using default fetchSize of 1000
13/03/13 14:00:09 DEBUG sqoop.ConnFactory: Instantiated ConnManager org.apache.sqoop.manager.OracleManager@5557c2bd
13/03/13 14:00:09 INFO tool.CodeGenTool: Beginning code generation
13/03/13 14:00:09 DEBUG manager.OracleManager: Using column names query: SELECT t.* FROM BTTN_BKP_TEST t WHERE 1=0
13/03/13 14:00:09 DEBUG manager.OracleManager: Creating a new connection for jdbc:oracle:thin:@10.99.42.11:1521/clouddb, using username: HDFSUSER
13/03/13 14:00:09 DEBUG manager.OracleManager: No connection paramenters specified. Using regular API for making connection.
13/03/13 14:00:10 INFO manager.OracleManager: Time zone has been set to GMT
13/03/13 14:00:10 DEBUG manager.SqlManager: Using fetchSize for next query: 1000
13/03/13 14:00:10 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM BTTN_BKP_TEST t WHERE 1=0
13/03/13 14:00:10 DEBUG manager.OracleManager$ConnCache: Caching released connection for jdbc:oracle:thin:@10.99.42.11:1521/clouddb/HDFSUSER
13/03/13 14:00:10 DEBUG orm.ClassWriter: selected columns:
13/03/13 14:00:10 DEBUG orm.ClassWriter:   BTTN_ID
13/03/13 14:00:10 DEBUG orm.ClassWriter:   DATA_INST_ID
13/03/13 14:00:10 DEBUG orm.ClassWriter:   SCR_ID
13/03/13 14:00:10 DEBUG orm.ClassWriter:   BTTN_NU
13/03/13 14:00:10 DEBUG orm.ClassWriter:   CAT
13/03/13 14:00:10 DEBUG orm.ClassWriter:   WDTH
13/03/13 14:00:10 DEBUG orm.ClassWriter:   HGHT
13/03/13 14:00:10 DEBUG orm.ClassWriter:   KEY_SCAN
13/03/13 14:00:10 DEBUG orm.ClassWriter:   KEY_SHFT
13/03/13 14:00:10 DEBUG orm.ClassWriter:   FRGND_CPTN_COLR
13/03/13 14:00:10 DEBUG orm.ClassWriter:   FRGND_CPTN_COLR_PRSD
13/03/13 14:00:10 DEBUG orm.ClassWriter:   BKGD_CPTN_COLR
13/03/13 14:00:10 DEBUG orm.ClassWriter:   BKGD_CPTN_COLR_PRSD
13/03/13 14:00:10 DEBUG orm.ClassWriter:   BLM_FL
13/03/13 14:00:10 DEBUG orm.ClassWriter:   LCLZ_FL
13/03/13 14:00:10 DEBUG orm.ClassWriter:   MENU_ITEM_NU
13/03/13 14:00:10 DEBUG orm.ClassWriter:   BTTN_ASGN_LVL_ID
13/03/13 14:00:10 DEBUG orm.ClassWriter:   ON_ATVT
13/03/13 14:00:10 DEBUG orm.ClassWriter:   ON_CLIK
13/03/13 14:00:10 DEBUG orm.ClassWriter:   ENBL_FL
13/03/13 14:00:10 DEBUG orm.ClassWriter:   BLM_SET_ID
13/03/13 14:00:10 DEBUG orm.ClassWriter:   BTTN_ASGN_LVL_NAME
13/03/13 14:00:10 DEBUG orm.ClassWriter:   MKT_ID
13/03/13 14:00:10 DEBUG orm.ClassWriter: