-
RE: Exporting hive table data into oracle give date format errorAjit Kumar Shreevastava 2013-03-19, 07:41
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
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
