MSCK REPAIR TABLE

Use this statement on Hadoop partitioned tables to identify partitions that were manually added to the distributed file system (DFS). This statement (a Hive command) adds metadata about the partitions to the Hive catalogs.

Syntax

Description

table-name: The name of the table that has been updated.

Usage

Hive stores a list of partitions for each table in its metastore. If partitions are manually added to the distributed file system (DFS), the metastore is not aware of these partitions. Running the MSCK statement ensures that the tables are properly populated. For more information, see Recover Partitions (MSCK REPAIR TABLE).

Restrictions

You can run this statement as a top-level statement only. Do not run it from inside objects such as routines, compound blocks, or prepared statements.

Examples

The table, t1, is created with one partition. A user with permissions to update t1 manually copies the partition file into the distributed file system.


CREATE HADOOP TABLE t1 (
  c1 int, c2 int
  ) 
  PARTITIONED BY (c3 int)
;

When you query the catalog table for partitions, there is nothing to show:


SELECT * FROM SYSHADOOP.HCAT_TABLEPARTS 
  WHERE TABSCHEMA = 'PART_TEST' AND TABNAME = 'T1';


+-----------+---------+------------+---------+-------------+----------+
| TABSCHEMA | TABNAME | HIVESCHEMA | HIVETAB | CREATE_TIME | LOCATION |
+-----------+---------+------------+---------+-------------+----------+
+-----------+---------+------------+---------+-------------+----------+
0 rows in results(total: 1.42s)

Run the following command to synchronize the table with the Hive metastore:


MSCK REPAIR TABLE t1;

Then, query the catalog table again:


SELECT * FROM SYSHADOOP.HCAT_TABLEPARTS 
  WHERE TABSCHEMA = 'PART_TEST' AND TABNAME = 'T1';


+-----------+---------+------------+---------+-------------+----------------------------------------------------------------+
| TABSCHEMA | TABNAME | HIVESCHEMA | HIVETAB | CREATE_TIME | LOCATION                                                       |
+-----------+---------+------------+---------+-------------+----------------------------------------------------------------+
| PART_TEST | T1      | PART_TEST  | T1      |  1400588887 | hdfs://localhost:9000/user/hive/warehouse/part_test.db/t1/c3=5 |
+-----------+---------+------------+---------+-------------+----------------------------------------------------------------+