Category: hcatalog

HCatalog – Show Partitions ”; Previous Next A partition is a condition for tabular data which is used for creating a separate table or view. SHOW PARTITIONS lists all the existing partitions for a given base table. Partitions are listed in alphabetical order. After Hive 0.6, it is also possible to specify parts of a partition specification to filter the resulting list. You can use the SHOW PARTITIONS command to see the partitions that exist in a particular table. This chapter describes how to list 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 dividing 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’);” Print Page Previous Next Advertisements ”;

HCatalog – CLI ”; Previous Next HCatalog Command Line Interface (CLI) can be invoked from the command $HIVE_HOME/HCatalog/bin/hcat where $HIVE_HOME is the home directory of Hive. hcat is a command used to initialize the HCatalog server. Use the following command to initialize HCatalog command line. cd $HCAT_HOME/bin ./hcat If the installation has been done correctly, then you will get the following output − SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory] usage: hcat { -e “<query>” | -f “<filepath>” } [ -g “<group>” ] [ -p “<perms>” ] [ -D”<name> = <value>” ] -D <property = value> use hadoop value for given property -e <exec> hcat command given from command line -f <file> hcat commands in file -g <group> group for the db/table specified in CREATE statement -h,–help Print help information -p <perms> permissions for the db/table specified in CREATE statement The HCatalog CLI supports these command line options − Sr.No Option Example & Description 1 -g hcat -g mygroup … The table to be created must have the group “mygroup”. 2 -p hcat -p rwxr-xr-x … The table to be created must have read, write, and execute permissions. 3 -f hcat -f myscript.HCatalog … myscript.HCatalog is a script file containing DDL commands to execute. 4 -e hcat -e ”create table mytable(a int);” … Treat the following string as a DDL command and execute it. 5 -D hcat -Dkey = value … Passes the key-value pair to HCatalog as a Java system property. 6 – hcat Prints a usage message. Note − The -g and -p options are not mandatory. At one time, either -e or -f option can be provided, not both. The order of options is immaterial; you can specify the options in any order. Sr.No DDL Command & Description 1 CREATE TABLE Create a table using HCatalog. If you create a table with a CLUSTERED BY clause, you will not be able to write to it with Pig or MapReduce. 2 ALTER TABLE Supported except for the REBUILD and CONCATENATE options. Its behavior remains same as in Hive. 3 DROP TABLE Supported. Behavior the same as Hive (Drop the complete table and structure). 4 CREATE/ALTER/DROP VIEW Supported. Behavior same as Hive. Note − Pig and MapReduce cannot read from or write to views. 5 SHOW TABLES Display a list of tables. 6 SHOW PARTITIONS Display a list of partitions. 7 Create/Drop Index CREATE and DROP FUNCTION operations are supported, but the created functions must still be registered in Pig and placed in CLASSPATH for MapReduce. 8 DESCRIBE Supported. Behavior same as Hive. Describe the structure. Some of the commands from the above table are explained in subsequent chapters. Print Page Previous Next Advertisements ”;