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:
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.
./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
Technology is evolving rapidly!
Stay updated with DataFlair on WhatsApp!!
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:
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.
./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.
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.
./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.
Did we exceed your expectations?
If Yes, share your valuable feedback on Google