Hive – Useful Commands

Hive is a data warehousing infrastructure based on Apache Hadoop. Hadoop provides massive scale out and fault tolerance capabilities for data storage and processing on commodity hardware.

Q. Which directory is created after creating Hive Table?

To see table primary info of Hive table, use describe table_name;

To see more detailed information about the table, use describe extended table_name;

To see code in a clean manner use describe formatted table_name; command to see all information. also describe all details in a clean manner.

Q. How to generate the create statement for an existing hive table?

show create table

Q. How to see query plan in Hive?

explain select * from

Q. How to run Query from command line?

hive -e ‘select a.col from’

Q. How to dump data out from a query into a file using silent mode?

hive -S -e ‘select a.col from tab1 a’ > a.txt

Q How to list all databases?

Show databases;

Q How to list all tables?

Show tables;

Q How to list all partitions in table?

Show Partitions;

Q How to delete partition?

ALTER TABLE <tablename> Drop PARTITION (<partitionname>);

Example

ALTER TABLE db1.person Drop PARTITION (year=2016, month=01);

Q How to rename a table?

ALTER TABLE RENAME TO ;

Q How to check locks on a table?

Show locks;

Q How to lock on a table?

Lock table;

Q How to unlock on a table?

Unlock table;

Q How to check concurrency in hive?

set hive.support.concurrency;

Q How to check indexes in hive table?

SHOW INDEX ON;

SHOW CREATE TABLE – shows the CREATE TABLE statement that creates a given table, or the CREATE VIEW statement that creates a given view.

SHOW CREATE TABLE ([db_name.]<table_name|view_name>);

Q How to copy a hive table?

create table . as select * from .<tablename>;