How to Fix Hive Data Wipe Off issue after the UNION of multiple tables.

Ran the SQL :

CREATE TABLE HIVE_DATAPIPELINE.MAISSTORE_ALL_COVERAGES_testsession_nov25
AS
SELECT * FROM HIVETEST.MAISSTORE_PIPA_COVERAGE
UNION ALL
SELECT * FROM HIVETEST.MAISSTORE_BI_COVERAGE
UNION ALL
SELECT * FROM HIVETEST.MAISSTORE_COLL_COVERAGE
Ran Successfully:

Browse the data in the table created above:

hive> select * from hive_datapipeline.maisstore_all_coverages limit 2;

OK

016342676       029     000000000       2       029     2T1BR12E4YC345613       FPBM    First Party Benefits Medical    N       01      2       2       20040501        20040501        AUTO    NA  NA       NA      20120501        20130501        0005000 20120501        20130501        0005000 20120501        PC      20130501        D       000604  NA      12      7114473                              20120501        O       O       1       37      2       T       20100923        41300

016342676       029     000000000       2       029     2T1BR12E4YC345613       FPBM    First Party Benefits Medical    N       01      2       2       20040501        20040501        AUTO    NA  NA       NA      20120501        20130501        0005000 20120501        20130501        0005000 20120501        PC      20130501        D       000604  NA      12      7114473                              20120501        O      
Time taken: 0.095 seconds, Fetched: 2 row(s)

Issues:

  1. The table data does not show up in Hue
  2. The Table Data does not show after closing the Hive Session and Re-enter into Hive Session.

hive> Shutting down tez session.

[hdfs@n01bdl301 /]$ hive

Logging initialized using configuration in file:/etc/hive/conf.dist/hive-log4j.properties
hive> select * from hive_datapipeline.maisstore_all_coverages limit 5;

OK

Time taken: 0.975 seconds

hive>

  1. It shows the table name created in hive_datapipeline database. However it does not show any data where as the data is there in /apps/hive/warehouse/

[hdfs@n01bdl301 /]$ hadoop fs -du -h /apps/hive/warehouse/hive_datapipeline.db/maisstore_all_coverages

105.8 M  /apps/hive/warehouse/hive_datapipeline.db/maisstore_all_coverages/1
348.1 M  /apps/hive/warehouse/hive_datapipeline.db/maisstore_all_coverages/10

How to resolve this issue:

Please set the below two properties in Hive as a default ones.
hive> SET hive.mapred.supports.subdirectories=true;
hive> SET mapred.input.dir.recursive=true;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s