MSCK REPAIR TABLE <tablename> showing no error but not detecting any partitions

Question

I am very new to the HIVE Environment.

I am trying to create an external table in hive with partitions.

There are only two partitions for example:

I created an external table:

CREATE EXTERNAL TABLE test
(col1 INT, col2 BIGINT, col3 STRING, col4 STRING, col5 STRING, col6 STRING, 
col7 BOOLEAN, col8 INT, col9 TIMESTAMP, col10 INT, col11 TIMESTAMP, 
col12 STRING, col13 DOUBLE, col14 DOUBLE, col15 DOUBLE, 
col16 DOUBLE,col17 BIGINT, col18 INT)
PARTITIONED BY (dataDate STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
tblproperties ("skip.header.line.count"="1");

The table is successfully created. Then I add the first partition:

ALTER TABLE test ADD IF NOT EXISTS
PARTITION(dataDate='20171014')
LOCATION '/user/test/Partition_Trial';

Then the partition is written in the table.

Then I tried adding the next partition using

MSCK REPAIR TABLE test;

There is no error.

But the partition was not read.

I was expecting that the MSCK function would be able to read other partitions.

I have been reading other people's similar problem but it doesn't appear similar to this problem.

Thank you for any help.

notNull notNull · Accepted Answer · 2019-04-26T14:38:59

Your test table is created at /user/test/ hdfs directory.

when we run msck repair table then hive checks is there any new partitions added to /user/test/ directory but not all sub directories recursively.
i.e. all your partitions are under /user/test/Partition_Trial directory (inside test directory), That's the reason msck repair table is not able to find newly added partitions.

Options to fix this issue:

Add all partitions using ALTER TABLE test ADD IF NOT EXISTS PARTITION.

(or)
Change the table location to /user/test/Partition_Trial

(or)
Keep the table location as is and Add all new partitions to /user/test directory.

MSCK REPAIR TABLE <tablename> showing no error but not detecting any partitions

1 Answers