Droped and recreated hive external table, but no data shown

Question

First a hive external table is created:

create external table user_tables.test
(col1 string, col2 string)
partitioned by (date_partition date);

records are inserted:

INSERT INTO TABLE user_tables.test
PARTITION (date_partition='2017-11-16') VALUES ('abc', 'xyz'), ('abc1', 'xyz1');

Now, the table is dropped and recreated with the same script. When I try-

SELECT * FROM user_tables.test WHERE date_partition='2017-11-16';`

I get Done. 0 results.

Answer 1

This is because the table you created is a partitioned table. The insert you ran would have created a partition for date_partition='2017-11-16'. When you drop and re-create the table, Hive looses the information about the partition, it only knows about the table.

Run the command below to get hive to re-create the partitions based on the data.

MSCK REPAIR TABLE user_tables.test;

Now you will see the data when you run SELECT.

If you want to know the partitions in the table run the statement below:

SHOW PARTITIONS user_tables.test; 

Run this before and after MSCK to see the effect.