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.

HCatalog CLI Commands

Learn HCatalog CLI Commands (Create, Alter, View)

Hadoop Quiz

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

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:

Sr. no. Field nameData Type
1Student idint
2Namestring
3Salaryfloat
4Designationstring

Further, below data explains the supported fields like Comment, Row formatted fields like Field terminator, Lines terminator, as well as Stored File type.
Let’s revise HCatalog loader and storer
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.

Test how much you learned about HCatalog

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

1201Gaurav45000Technical manager
1202Mehul45000 Proofreader
1203Monika40000 Technical writer
1204Kajal40000Hr Admin
1205Karishma30000Op Admin

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.
Let’s discuss HCatalog and Pig Integration
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.
Let’s learn HCatalog Applications and Its Use Cases
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.

IDNameSalaryDesignationDept
1201Gaurav45000Technical managerTP
1202Mehul45000 ProofreaderPR
1203Monika30000Technical writerTP
1204Kajal40000Hr AdminHR
1205Karishma30000Op AdminAdmin

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.
Let’s learn HCatalog InputOutput Interface
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.
Have a look at HCatalog Quiz
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.

If these professionals can make a switch to Big Data, so can you:
Rahul Doddamani Story - DataFlair
Rahul Doddamani
Java → Big Data Consultant, JDA
Follow on
Mritunjay Singh Success Story - DataFlair
Mritunjay Singh
PeopleSoft → Big Data Architect, Hexaware
Follow on
Rahul Doddamani Success Story - DataFlair
Rahul Doddamani
Big Data Consultant, JDA
Follow on
I got placed, scored 100% hike, and transformed my career with DataFlair
Enroll now
Deepika Khadri Success Story - DataFlair
Deepika Khadri
SQL → Big Data Engineer, IBM
Follow on
DataFlair Web Services
You could be next!
Enroll now

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.
Related Topic –
HCatalog Command Line Interface(CLI)
For reference

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.