Learn HCatalog CLI Commands (Create, Alter, View)

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.

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

CREATE [TStudent1ORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[ROW FORMAT row_format]
[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:

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.

./hcat –e "CREATE TABLE IF NOT EXISTS Student ( eid int, name String,
  salary String, destination String) \
COMMENT 'Student details' \
ROW FORMAT DELIMITED \
FIELDS TERMINATED BY ‘\t’ \
LINES TERMINATED BY ‘\n’ \
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

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename
[PARTITION (partcol1=val1, partcol2=val2 ...)]
to specify the local path, LOCAL is the identifier. Though, it is optional.
Also, to overwrite the data in the table, OVERWRITE is optional.
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:

Below query loads the given text into the table.

./hcat –e "LOAD DATA LOCAL INPATH '/home/user/sample1.txt'
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

ALTER TABLE name RENAME TO new_name
ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
ALTER TABLE name DROP [COLUMN] column_name
ALTER TABLE name CHANGE column_name new_name new_type
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

DROP TABLE [IF EXISTS] table_name;

So, below query drops a table named Student −

./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

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], ...) ]
[COMMENT view_comment]
[TBLPROPERTIES (property_name = property_value, ...)]
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.

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

./hcat –e "CREATE VIEW Student1_Deg_View (salary COMMENT ' salary more than 35,000')
  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

DROP VIEW [IF EXISTS] view_name;

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

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

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

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

./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

SHOW PARTITIONS table_name;

So, below query drops a table named Student −

./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

CREATE INDEX index_name
ON TABLE base_table_name (col_name, ...)
AS 'index.handler.class.name'
[WITH DEFERRED REBUILD]
[IDXPROPERTIES (property_name = property_value, ...)]
[IN TABLE index_table_name]
[PARTITIONED BY (col_name, ...)][
  [ ROW FORMAT ...] STORED AS ...
  | STORED BY ...
]
[LOCATION hdfs_path]
[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 −

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

x. Dropping an Index

Syntax of dropping an index

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.

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.