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.