HCatalog InputOutput Interface

HCatalog InputOutput Interface | HCatalog MapReduce Integration

---

1. Objective

In our last HCatalog Tutorial, we discussed HCatalog Applications. Today, we will see Hcatalog InputOutput Interfaces. In this HCatalog InputOutputtutorial, we will discuss the input and output formats of HCatalog. Moreover, we will learn HCatalog Mapreduce Integration. In this HCatalog tutorial, we will see HCatalog Mapreduceexamples, filters, and authentication. Basically, to read data from HDFS and write the resultant data into HDFS after processing especially using MapReduce job we use HCatalog InputOutput Format interfaces. 

So, let’s start HCatalog InputOutput Interface,

HCatalog InputOutput Interface | HCatalog MapReduce Integration

2. HCatInputFormat

In order to read data from HCatalog-managed tables, we can use the HCatInputFormat along with MapReduce jobs.

In addition, for reading data, HCatInputFormat exposes a Hadoop 0.20 MapReduceAPI as if it had been published to a table.

a. API of HCatInputFormat

HCatInputFormat APIs are:

1. setInput
2. setOutputSchema
3. getTableSchema

At very first, if HCatInputFormat.setInput has not been called instantiate an InputJobInfo, in order to use HCatInputFormat to read data, afterward call setInput with the InputJobInfo.

Further, to specify the output fields, we can use the setOutputSchema method to include a projection schema. So, all the columns in the table will be returned, if a schema is not specified.

Moreover, to determine the table schema for a specified input table we can use the getTableSchema method.

1. /**
2. * Set the input to use for the Job. This queries the metadata server with
3. * the specified partition predicates, gets the matching partitions, puts
4. * the information in the conf object. The inputInfo object is updated with
5. * information needed in the client context
6. * @param job the job object
7. * @param inputJobInfo the input info for the table to read
8. * @throws IOException the exception in communicating with the metadata server
9. */
10. public static void setInput(Job job,
11. InputJobInfo inputJobInfo) throws IOException;

1. /**
2. * by HCatInputFormat, set the schema for the HCatRecord data returned.
3. * @param job the job object
4. * the schema to use as the consolidated schema @param hcatSchema
5. */
6. public static void setOutputSchema(Job job,HCatSchema hcatSchema)
7. throws IOException; <b> </b>

1. /**
2. * for the table specified in the HCatInputFormat.setInput, get the HCatTable schema
3. * then call on the specified job context. it is available only after
4. * HCatInputFormat.setInput has been called for a JobContext.
5. * @param context the context
6. * @return the table schema
7. * if HCatInputFormat.setInput has not been called @throws IOException
8. * for the current context
9. */
10. public static HCatSchema getTableSchema(JobContext context)
11. throws IOException;

3. HCatOutputFormat

Now, to write data to HCatalog-managed tables we use HCatOutputFormat with MapReduce jobs.

In addition, for writing data to a table, HCatOutputFormat exposes a Hadoop 0.20 MapReduce API. So, basically, we can use the default OutputFormat which is configured for the table and then after the job completes new partition is published to the table at the time a MapReduce job uses HCatOutputFormat to write output.

a. API of HCatOutputFormat

HCatOutputFormat APIs are:

1. setOutput
2. setSchema
3. getTableSchema

It is must that setOutput is the first call on HCatOutputFormat, because any call other than that will throw an exception, which will say that the output format is not initialized. Moreover, by the setSchema method, the schema for the data being written out is specified. Also, we can use HCatOutputFormat.getTableSchema() to get the table schema and then pass that along to setSchema(), if our data has the same schema as the table schema.

1. /**
2. * to write for the job, set the information about the output. This queries the metadata
3. * server to find the StorageHandler to use for the table. It throws an error if the
4. * partition is already published.
5. * @param job the job object
6. * @param outputJobInfo the table output information for the job
7. * @throws IOException the exception in communicating with the metadata server
8. */
9. @SuppressWarnings("unchecked")
10. public static void setOutput(Job job, OutputJobInfo outputJobInfo) throws IOException;

1. /**
2. * Set the schema for the data being written out to the partition. The
3. * table schema is used by default for the partition if this is not called.
4. * @param job the job object
5. * @param schema the schema for the data
6. * @throws IOException
7. */
8. public static void setSchema(final Job job, final HCatSchema schema) throws IOException;<b> </b>

1. /**
2. * Get the table schema for the table specified in the HCatOutputFormat.setOutput call
3. * on the specified job context.
4. * @param context the context
5. * @return the table schema
6. * @throws IOException if HCatOutputFormat.setOutput has not been called
7. * for the passed context
8. */
9. public static HCatSchema getTableSchema(JobContext context) throws IOException;

4. HCatalog InputOutput – HCatRecord

Basically, for storing values in HCatalog tables, HCatRecord is supported type in HCatalog InputOutput Interface.

In addition, for different fields in HCatRecord, the types in an HCatalog table schema determine the types of objects returned. Now, here you can see the mappings between Java classes for MapReduce programs as well as HCatalog data types:

1. HCatalog data types- TINYINT

Java classes for MapReduce- java.lang.Byte

Values- -128 to 127

2. HCatalog data types-SMALLINT

Java classes for MapReduce-  java.lang.Short

Values- -(2^15) to (2^15)-1, which is -32,768 to 32,767

3. HCatalog data types- INT

Java classes for MapReduce- java.lang.Integer

Values- -(2^31) to (2^31)-1, which is -2,147,483,648 to 2,147,483,647

4. HCatalog data types- BIGINT

Java classes for MapReduce- java.lang.Long

Values- -(2^63) to (2^63)-1, which is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

5. HCatalog data types- BOOLEAN

Java classes for MapReduce- java.lang.Boolean

Values- true or fals

6. HCatalog data types- FLOAT

Java classes for MapReduce- java.lang.Float

Values- single-precision floating-point value

7.  HCatalog data types- DOUBLE

Java classes for MapReduce- java.lang.Double

Values- double-precision floating-point value

8.  HCatalog data types- DECIMAL

Java classes for MapReduce- java.math.BigDecimal

Values-exact floating-point value with 38-digit precision

9. HCatalog data types- BINARY

Java classes for MapReduce- byte []

Values-Binary Data

10. HCatalog data types-STRING

Java classes for MapReduce- java.lang.String

Values- character string

11. HCatalog data types- STRUCT

Java classes for MapReduce- java.util.List

Values- structured data

12. HCatalog data types- ARRAY

Java classes for MapReduce- java.util.List

Values- values of one data type

13. HCatalog data types- MAP

Java classes for MapReduce- java.util.Map

Values- key-value pairs

5. Running MapReduce With HCatalog

For Running MapReduce with HCatalog, our MapReduce program must know where the Thrift server is. So, the best way to do this is that we can pass the location as an argument to our Java program. Then we need to pass the Hive as well as HCatalog jars to MapReduce as well, with the -libjars argument.

export HADOOP_HOME=<path_to_hadoop_install>

export HCAT_HOME=<path_to_hcat_install>

export HIVE_HOME=<path_to_hive_install>

export LIB_JARS=$HCAT_HOME/share/hcatalog/hcatalog-core-0.5.0.jar,

$HIVE_HOME/lib/hive-metastore-0.10.0.jar,

$HIVE_HOME/lib/libthrift-0.7.0.jar,

$HIVE_HOME/lib/hive-exec-0.10.0.jar,

$HIVE_HOME/lib/libfb303-0.7.0.jar,

$HIVE_HOME/lib/jdo2-api-2.3-ec.jar,

$HIVE_HOME/lib/slf4j-api-1.6.1.jar

export HADOOP_CLASSPATH=$HCAT_HOME/share/hcatalog/hcatalog-core-0.5.0.jar:

$HIVE_HOME/lib/hive-metastore-0.10.0.jar:

$HIVE_HOME/lib/libthrift-0.7.0.jar:

$HIVE_HOME/lib/hive-exec-0.10.0.jar:

$HIVE_HOME/lib/libfb303-0.7.0.jar:

$HIVE_HOME/lib/jdo2-api-2.3-ec.jar:

$HIVE_HOME/conf:$HADOOP_HOME/conf:

$HIVE_HOME/lib/slf4j-api-1.6.1.jar

$HADOOP_HOME/bin/hadoop –config $HADOOP_HOME/conf jar <path_to_jar>

<main_class> -libjars $LIB_JARS <program_arguments>

Although Hadoop will ship libjars every time we run the MapReduce program, so, that is not efficient and as a result, it also may deplete the Hadoop distributed cache.

Rather than that, by using HDFS locations we can optimize to ship libjars. However, Hadoop will reuse the entries in the distributed cache, by doing this.

bin/hadoop fs -copyFromLocal $HCAT_HOME/share/hcatalog/hcatalog-core-0.5.0.jar /tmp

bin/hadoop fs -copyFromLocal $HIVE_HOME/lib/hive-metastore-0.10.0.jar /tmp

bin/hadoop fs -copyFromLocal $HIVE_HOME/lib/libthrift-0.7.0.jar /tmp

bin/hadoop fs -copyFromLocal $HIVE_HOME/lib/hive-exec-0.10.0.jar /tmp

bin/hadoop fs -copyFromLocal $HIVE_HOME/lib/libfb303-0.7.0.jar /tmp

bin/hadoop fs -copyFromLocal $HIVE_HOME/lib/jdo2-api-2.3-ec.jar /tmp

bin/hadoop fs -copyFromLocal $HIVE_HOME/lib/slf4j-api-1.6.1.jar /tmp

export LIB_JARS=hdfs:///tmp/hcatalog-core-0.5.0.jar,

hdfs:///tmp/hive-metastore-0.10.0.jar,

hdfs:///tmp/libthrift-0.7.0.jar,

hdfs:///tmp/hive-exec-0.10.0.jar,

hdfs:///tmp/libfb303-0.7.0.jar,

hdfs:///tmp/jdo2-api-2.3-ec.jar,

hdfs:///tmp/slf4j-api-1.6.1.jar

# (Other statements remain the same.)

6. HCatalog InputOuput – Authentication

Make sure you have run “kinit <username>@FOO.COM” to get a Kerberos ticket and to be able to authenticate to the HCatalog server if a failure results in a message like “2010-11-03 16:17:28,225 WARN hive.metastore … – Unable to connect metastore with URI thrift://…” in /tmp/<username>/hive.log.

7. Filter Operators

There can be  operators in a filter like’and’, ‘or’, ‘like’, ‘()’, ‘=’, ‘<>’ (not equal), ‘<‘, ‘>’, ‘<=’ and ‘>=’.

For example:

ds > “20110924”

ds < “20110925”

ds <= “20110925” and ds >= “20110924”

8. HCatalog InputOutput- Scan Filter

let’s suppose we have a web_logs table that is partitioned by the column “ds”. hence, we could select one partition of the table by changing:

HCatInputFormat.setInput(job, InputJobInfo.create(dbName, inputTableName, null));

to

HCatInputFormat.setInput(job,

                        InputJobInfo.create(dbName, inputTableName, “ds=\”20110924\””));

This filter must reference only partition columns. Values from other columns will cause the job to fail.

9. HCatalog InputOutput- Write Filter

Moreover, we can change the above example to have a Map of key-value pairs that describe all of the partition keys and values for that partition, to write to a single partition. In our example web_logs table, there is only one partition column (ds), so our Map will have only one entry. Change

HCatOutputFormat.setOutput(job, OutputJobInfo.create(dbName, outputTableName, null));

to

Map partitions = new HashMap<String, String>(1);

partitions.put(“ds”, “20110924”);

HCatOutputFormat.setOutput(job, OutputJobInfo.create(dbName, outputTableName, partitions));

Alo, we can leave the Map null, to write multiple partitions simultaneously, still, in the data, we are writing, all of the partitioning columns must be present.

So, this was all about HCatalog InputOutPut and HCatalog- MapReduce Integration. Hope you like our explanation

10. Conclusion

Hence, we have seen the concept of HCatalog InputOutput Format interfaces in detail. Along with this, we discussed how to run MapReduce with HCatalog. Moreover, we also see HCatInput and HCatOutput Formats in HCatalog InputOutput tutorial. Also, we discussed Hcatalog Mapreduce example. However, if any doubt occurs regarding HCatalog InputOutput, feel free to ask in the comment section.

HCatalog and Pig Integration

HCatalog and Pig Integration | Accessing Pig With HCatalog

---

1. HCatalog and Pig Integration

In our last HCatalog tutorial, we discussed HCatalog loader and storer. Today, we will see HCatalog and Pig Integration. We can easily integrate HCatalog with Pig. Moreover, we will also see the example of HCatalog and Pig Integration to understand it well.

So, let’s start HCatalog and Pig Integration.

HCatalog and Pig Integration | Accessing Pig With HCatalog

2. Running Pig with HCatalog

Generally, it is not possible for Pig to pick up HCatalog jars. So, either we can use a flag in the pig command or we can set the environment variables PIG_CLASSPATH and PIG_OPTS,  to bring in the necessary jars, such as:

a. The -useHCatalog Flag

Hence, for working with HCatalog, simply include the following flag, to bring in the appropriate jars:

pig -useHCatalog

b. Jars and Configuration Files

Make sure we need to tell Pig where to find our HCatalog jars and the Hive jars used by the HCatalog client, for Pig commands that omit -useHCatalog. Hence, we need to define the environment variable PIG_CLASSPATH with the appropriate jars, to do this.

In addition, HCatalog can tell us the jars it needs. Though, it needs to know where Hadoop and Hive are installed, for that. Also, in the PIG_OPTS variable, we need to tell Pig the URI for our metastore.

Further, we can perform following in the case where we have installed Hadoop and Hive via tar:

export HADOOP_HOME=<path_to_hadoop_install>

export HIVE_HOME=<path_to_hive_install>

export HCAT_HOME=<path_to_hcat_install>

export PIG_CLASSPATH=$HCAT_HOME/share/hcatalog/hcatalog-core*.jar:\

$HCAT_HOME/share/hcatalog/hcatalog-pig-adapter*.jar:\

$HIVE_HOME/lib/hive-metastore-*.jar:$HIVE_HOME/lib/libthrift-*.jar:\

$HIVE_HOME/lib/hive-exec-*.jar:$HIVE_HOME/lib/libfb303-*.jar:\

$HIVE_HOME/lib/jdo2-api-*-ec.jar:$HIVE_HOME/conf:$HADOOP_HOME/conf:\

$HIVE_HOME/lib/slf4j-api-*.jar

export PIG_OPTS=-Dhive.metastore.uris=thrift://<hostname>:<port>

Also, we can pass the jars in your command line:

1. <path_to_pig_install>/bin/pig -Dpig.additional.jars=\
2. $HCAT_HOME/share/hcatalog/hcatalog-core*.jar:\
3. $HCAT_HOME/share/hcatalog/hcatalog-pig-adapter*.jar:\
4. $HIVE_HOME/lib/hive-metastore-*.jar:$HIVE_HOME/lib/libthrift-*.jar:\
5. $HIVE_HOME/lib/hive-exec-*.jar:$HIVE_HOME/lib/libfb303-*.jar:\
6. $HIVE_HOME/lib/jdo2-api-*-ec.jar:$HIVE_HOME/lib/slf4j-api-*.jar <script.pig>

Moreover, in each filepath, the version number found will be substituted for *. As an example here release 0.5.0 of HCatalog uses following jars and conf files:

$HCAT_HOME/share/hcatalog/hcatalog-core-0.5.0.jar

$HCAT_HOME/share/hcatalog/hcatalog-pig-adapter-0.5.0.jar

$HIVE_HOME/lib/hive-metastore-0.10.0.jar

$HIVE_HOME/lib/libthrift-0.7.0.jar

$HIVE_HOME/lib/hive-exec-0.10.0.jar

$HIVE_HOME/lib/libfb303-0.7.0.jar

$HIVE_HOME/lib/jdo2-api-2.3-ec.jar

$HIVE_HOME/conf

$HADOOP_HOME/conf

$HIVE_HOME/lib/slf4j-api-1.6.1.jar

c. Authentication

Make sure you have run “kinit <username>@FOO.COM” to get a Kerberos ticket and to be able to authenticate to the HCatalog server, if you are using a secure cluster and a failure results in a message like “2010-11-03 16:17:28,225 WARN hive.metastore … – Unable to connect metastore with URI thrift://…” in /tmp/<username>/hive.log.

3. Example of HCatalog and Pig Integration

For Example-

Now, let’s suppose we have a file employee_details.txt in HDFS, its content is:

employee_details.txt

001, Mehul, Chourey, 21, 9848022337, Hyderabad
002, Prerna, Tripathi, 22, 9848022338, Chennai
003, Shreyash, Tiwari, 22, 9848022339, Delhi
004, Kajal, Jain, 21, 9848022330, Goa
005, Revti, Vadjikar, 23, 9848022336, Banglore
006, Rishabh, Jaiswal, 23, 9848022335, Pune
007, Sagar, Joshi, 24, 9848022334, Mumbai
008, Vaishnavi, Dubey, 24, 9848022333, Indore

Now, there is one sample script we have with the name sample1_script.pig, in the same HDFS directory. Also, it have some statements performing operations and transformations on the employee relation,like:

employee = LOAD ‘hdfs://localhost:9000/pig_data/employee_details.txt’ USING 
PigStorage(‘,’) as (id:int, firstname:chararray, lastname:chararray,
phone:chararray, city:chararray);

employee_order = ORDER employee BY age DESC;
STORE employee_order INTO ’employee_order_table’ USING org.apache.HCatalog.pig.HCatStorer();
employee_limit = LIMIT employee_order 4;
Dump employee_limit;

Now,see, data in the file named employee_details.txt as a relation named employee is stored in the first statement of the script.

Afterward,  the tuples of the relation are arranged in the second statement of the script in the descending order,  on the basis of age, as well as store it as employee_order.

Moreover, the processed data employee_order results in a separate table named employee_order_table is stored in the third statement.

And, the first four-tuples of employee_order as employee_limit will be stored in the fourth statement of the script.

Ultimately, the last and the fifth statement will dump the content of the relation employee_limit.

Further execute the sample1_script.pig, like:

1. $./pig -useHCatalog hdfs://localhost:9000/pig_data/sample1_script.pig

Hence, for the output (part_0000, part_0001),  check output directory (hdfs: user/tmp/hive).

So, this was all about HCatalog and Pig Integration. Hope, it helps.

4. Conclusion

Hence, we have seen the concept of HCatalog and Pig Integration in detail. Also, we discussed how to run Pig with HCatalog and its example. Still, if any doubt regarding HCatalog and Pig Integration, ask in the comment tab.

Learn HCatalog CLI Commands (Create, Alter, View)

---

1. Objective

In our last HCatalog Tutorial, we discussed HCatalog features. Today, we will see HCatalog CLI Commands. There are various commands like, create table, alter table, view, Show table etc are supported by HCatalog. So, in this HCatalog CLI Command tutorial, we will learn all these HCatalog CLI Commands in detail.

So, let’s start HCatalog CLI Commands.

Learn HCatalog CLI Commands (Create, Alter, View)

2.  HCatalog CLI Commands

Here we are discussing all the HCatalog CLI Commands in detail:

i. Creating Table in HCatalog

Basically, to create a table in Hive metastore using HCatalog, we use Create Table statement CLI command in HCatalog. To create it, follow these steps:

Syntax of creating table in HCatalog

1. CREATE [TStudent1ORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name
2. [(col_name data_type [COMMENT col_comment], ...)]
3. [COMMENT table_comment]
4. [ROW FORMAT row_format]
5. [STORED AS file_format]

For Example

Suppose, we are creating a table with name Student by using CREATE TABLE statement. So, the table lists the fields and their data types in the Student table are:

Sr. no.	Field name	Data Type
1	Student id	int
2	Name	string
3	Salary	float
4	Designation	string

Further, below data explains the supported fields like Comment, Row formatted fields like Field terminator, Lines terminator, as well as Stored File type.

COMMENT ‘Student details’

FIELDS TERMINATED BY ‘\t’

LINES TERMINATED BY ‘\n’

STORED IN TEXT FILE

By using the above data, the following query creates a table named Student.

1. ./hcat –e "CREATE TABLE IF NOT EXISTS Student ( eid int, name String,
2. salary String, destination String) \
3. COMMENT 'Student details' \
4. ROW FORMAT DELIMITED \
5. FIELDS TERMINATED BY ‘\t’ \
6. LINES TERMINATED BY ‘\n’ \
7. STORED AS TEXTFILE;"

Although HCatalog ignores the statement in case the table already exists, If we add the option IF NOT EXISTS.

Output

OK

Time taken: 5.905 seconds

ii. Load Data Statement

To insert data in HCatalog we can use the LOAD DATA statement.

Basically, it is better to use LOAD DATA CLI Command in HCatalog to store bulk records, while inserting data into HCatalog. Generally, we can load data in two ways:

• From local file system
• From Hadoop file system

Syntax of load data statement

1. LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename
2. [PARTITION (partcol1=val1, partcol2=val2 ...)]
3. to specify the local path, LOCAL is the identifier. Though, it is optional.
4. Also, to overwrite the data in the table, OVERWRITE is optional.
5. And, PARTITION is also optional.

For Example

Here we are inserting the following data into the table. So, a text file named sample1.txt in /home/user directory:

1201	Gaurav	45000	Technical manager
1202	Mehul	45000	Proofreader
1203	Monika	40000	Technical writer
1204	Kajal	40000	Hr Admin
1205	Karishma	30000	Op Admin

Below query loads the given text into the table.

1. ./hcat –e "LOAD DATA LOCAL INPATH '/home/user/sample1.txt'
2. OVERWRITE INTO TABLE Student;

Output

OK

Time taken: 15.905 seconds

iii. Alter Table Statement

To alter a table, we can use the ALTER TABLE statement HCatalog CLI Command.

Syntax of alter table statement

1. ALTER TABLE name RENAME TO new_name
2. ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
3. ALTER TABLE name DROP [COLUMN] column_name
4. ALTER TABLE name CHANGE column_name new_name new_type
5. ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])

iv. Drop Table Statement

On dropping a table from the metastore, it removes the table/column data and their metadata. Tables can be of two types: –

• a normal table (stored in metastore)
• an external table (stored in local file system);

However, irrespective of their types, HCatalog treats both in the same manner.

Syntax of drop table statement

1. DROP TABLE [IF EXISTS] table_name;

So, below query drops a table named Student −

1. ./hcat –e "DROP TABLE IF EXISTS Student;"

Output

OK

Time taken: 5.3 seconds

v. Create View Statement

Moreover, CREATE VIEW statement creates a view with the given name. Although make sure, if a table or view with the same name already exists, an error is thrown, then to skip the error, we can use IF NOT EXISTS.

In addition, from the defining SELECT expression, if no column names are supplied, the names of the view’s columns will be derived automatically.

Also, make sure the resulting view column names will be generated in the form _C0, _C1, etc if the SELECT contains un-aliased scalar expressions like as x+y.

However, if somehow the view’s defining SELECT expression is invalid, then a CREATE VIEW statement will get fail.

Syntax of create view statement

1. CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], ...) ]
2. [COMMENT view_comment]
3. [TBLPROPERTIES (property_name = property_value, ...)]
4. AS SELECT ...;

For Example

Let’s suppose we have a Student table data. Now, to create a view named Student1_Deg_View containing the fields id, name, Designation, and salary of a Student having a salary greater than 35,000.

ID	Name	Salary	Designation	Dept
1201	Gaurav	45000	Technical manager	TP
1202	Mehul	45000	Proofreader	PR
1203	Monika	30000	Technical writer	TP
1204	Kajal	40000	Hr Admin	HR
1205	Karishma	30000	Op Admin	Admin

To create a view based on the above-given data below is the command.

1. ./hcat –e "CREATE VIEW Student1_Deg_View (salary COMMENT ' salary more than 35,000')
2. AS SELECT id, name, salary, designation FROM Student WHERE salary ≥ 35000;"

Output

OK

Time taken: 5.3 seconds

vi. Drop View Statement

So, for the specified view, DROP VIEW removes metadata.

Syntax of drop view statement

1. DROP VIEW [IF EXISTS] view_name;

For Example

To drop a view named Student1_Deg_View, below is the command.

1. DROP VIEW Student1_Deg_View;

vii. Show Tables Statement

To display the names of all tables, we use the Show Tables statement HCatalog CLI Command. So, it lists tables from the current database, or with the IN clause, in a specified database, by default.

Syntax of show table statement

1. SHOW TABLES [IN database_name] ['identifier_with_wildcards'];

To display, a list of tables, below is the query −

1. ./hcat –e "Show tables;"

Output

OK

Student1

Student

Time taken: 5.3 seconds

viii. Show Partitions Statement

To see the partitions that exist in a particular table, we can use the SHOW PARTITIONS CLI HCatalog command.

Syntax of show partitions statement

1. SHOW PARTITIONS table_name;

So, below query drops a table named Student −

1. ./hcat –e "Show partitions Student;"

Output

OK

Designation = IT

Time taken: 5.3 seconds

ix. Creating an Index

A pointer on a particular column of a table, is what we call an Index. So, we can say creating an index is as same as creating a pointer on a particular column of a table.

Syntax of creating an index

1. CREATE INDEX index_name
2. ON TABLE base_table_name (col_name, ...)
3. AS 'index.handler.class.name'
4. [WITH DEFERRED REBUILD]
5. [IDXPROPERTIES (property_name = property_value, ...)]
6. [IN TABLE index_table_name]
7. [PARTITIONED BY (col_name, ...)][
8. [ ROW FORMAT ...] STORED AS ...
9. | STORED BY ...
10. ]
11. [LOCATION hdfs_path]
12. [TBLPROPERTIES (...)]

For Example

Hence, to understand the concept of index, here is an example. On using the same Student table that we have used earlier. Here also with the same fields Id, Name, Salary, Designation, and Dept. Now,  on the salary column of the Student table, create an index named index_salary.

Below query creates an index −

1. ./hcat –e "CREATE INDEX inedx_salary ON TABLE Student(salary)

x. Dropping an Index

Syntax of dropping an index

1. DROP INDEX <index_name> ON <table_name>

Output

./hcat –e “DROP INDEX index_salary ON Student;”

So, this was all about HCatalog CLI Commands. Hope, you like our explanation.

3. Conclusion

Hence, we have learned all the HCatalog CLI Commands in detail. This will definitely help you to use various commands easily. Still, if any doubt regarding HCatalog CLI Commands, ask in the comment tab.