MSCK REPAIR TABLE (DATALAKE) statement

Use the MSCK REPAIR TABLE statement on DATALAKE partitioned tables to identify partitions that were manually added to object storage. 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 object storage, the metastore is not aware of these partitions. Running the MSCK REPAIR TABLE 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, mytable, is created with one partition. A user with permissions to update mytable manually copies the partition file into the object store.

db2 "create datalake table myschema.mytable (c1 int, c2 int) partitioned by (c3 int) location 'db2remote://odfdefault//default/t1' tblproperties ('external.data.purge'='true')"

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

select varchar(tabschema,25) as tabschema, varchar(tabname,25) as tabname, varchar(location, 100) as location from syshadoop.hcat_tableparts where tabschema='MYSCHEMA' and tabname='MYTABLE'


TABSCHEMA                 TABNAME                   LOCATION
------------------------- ------------------------- ------------------------------------------------


0 record(s) selected.

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

MSCK REPAIR TABLE MYTABLE;

Then, query the catalog table again:

select varchar(tabschema,25) as tabschema, varchar(tabname,25) as tabname, varchar(location, 100) as location from syshadoop.hcat_tableparts where tabschema='MYSCHEMA' and tabname='MYTABLE'


TABSCHEMA                 TABNAME                   LOCATION
------------------------- ------------------------- ------------------------------------------------
MYSCHEMA                  MYTABLE                   s3a://default/default/t1/c3=99

1 record(s) selected.