HCatalog - Show Partitions

A partition is a condition for tabular data which is used for creating a separate table or view. SHOW PARTITIONS psts all the existing partitions for a given base table. Partitions are psted in alphabetical order. After Hive 0.6, it is also possible to specify parts of a partition specification to filter the resulting pst.

You can use the SHOW PARTITIONS command to see the partitions that exist in a particular table. This chapter describes how to pst out the partitions of a particular table in HCatalog.

Show Partitions Statement

The syntax is as follows −

SHOW PARTITIONS table_name;

The following query drops a table named employee −

./hcat –e "Show partitions employee;"

On successful execution of the query, you get to see the following response −

OK
Designation = IT
Time taken: 5.3 seconds

Dynamic Partition

HCatalog organizes tables into partitions. It is a way of spaniding a table into related parts based on the values of partitioned columns such as date, city, and department. Using partitions, it is easy to query a portion of the data.

For example, a table named Tab1 contains employee data such as id, name, dept, and yoj (i.e., year of joining). Suppose you need to retrieve the details of all employees who joined in 2012. A query searches the whole table for the required information. However, if you partition the employee data with the year and store it in a separate file, it reduces the query processing time. The following example shows how to partition a file and its data −

The following file contains employeedata table.

/tab1/employeedata/file1

id, name,   dept, yoj
1,  gopal,   TP, 2012
2,  kiran,   HR, 2012
3,  kaleel,  SC, 2013
4, Prasanth, SC, 2013

The above data is partitioned into two files using year.

/tab1/employeedata/2012/file2

1, gopal, TP, 2012
2, kiran, HR, 2012

/tab1/employeedata/2013/file3

3, kaleel,   SC, 2013
4, Prasanth, SC, 2013

Adding a Partition

We can add partitions to a table by altering the table. Let us assume we have a table called employee with fields such as Id, Name, Salary, Designation, Dept, and yoj.

Syntax

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec
[LOCATION  location1 ] partition_spec [LOCATION  location2 ] ...;
partition_spec:
: (p_column = p_col_value, p_column = p_col_value, ...)

The following query is used to add a partition to the employee table.

./hcat –e "ALTER TABLE employee ADD PARTITION (year =  2013 ) location  /2012/part2012 ;"

Renaming a Partition

You can use the RENAME-TO command to rename a partition. Its syntax is as follows −

./hact –e "ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;"

The following query is used to rename a partition −

./hcat –e "ALTER TABLE employee PARTITION (year=’1203’) RENAME TO PARTITION (Yoj= 1203 );"

Dropping a Partition

The syntax of the command that is used to drop a partition is as follows −

./hcat –e "ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec,.
   PARTITION partition_spec,...;"

The following query is used to drop a partition −

./hcat –e "ALTER TABLE employee DROP [IF EXISTS] PARTITION (year=’1203’);"