OFFLINE – Offline The Table Partition in Hive

In the previous post, we looked at how to take a table offline in Hive to avoid being queried. In this post, we’ll look at how to take a specific table partition offline to prevent it from being displayed.

Let’s look at how to alter a Hive table so that its partition data isn’t queried:

Pre-requisits:
Create the partitioned table as specified here.

Syntax:
ALTER TABLE t1 PARTITION (PARTITION_SPEC) ENABLE OFFLINE;

Example:
Hive> ALTER TABLE TownsList_Dynamic PARTITION (country=’England’) ENABLE OFFLINE;
Now, let’s issue the SELECT statement.

Hive> select * from TownsList_Dynamic where country=’England’
Now, this returns the error as we expected.
FAILED: SemanticException [Error 10113]: Query against an offline table or partition Table townslist_dynamic Partition country=England

Bringing back the table to ONLINE.
Hive> ALTER TABLE TownsList_Dynamic PARTITION (country=’England’) DISABLE OFFLINE;