Basic commands in Hive Query Language

Get FREE domain for 1st year and build your brand new site

Following are the Basic commands in Hive Query Language which will help you in using Hive effectively:

  1. Command to create a databse
create database < Database Name >;
  1. Listing the available databases
show databases;
  1. Creating tables
create table XYZ( XYZ int, XYZ string, XYZ string) row format delimited fields terminated by ',';
  1. To enter the data using a csv file copy it in the warehouse directory of hive using
$ hdfs dfs -put record.csv /user/hive/warehouse/XYZ

And then type this in hive shell

LOAD DATA LOCAL INPATH '/home/cloudera/input.txt' INTO TABLE XYZ;
  1. Display table
select * from XYZ;
  1. Describe table
describe XYZ;

OR

describe extended XYZ;
  1. We can make various types of tables like":

a. External - it helps in storing the data at an external location
b. Managed - it is managed inside the hive warehouse
c. Temporary - it is managed tempoararily for a session

External:

create external table XYZ_external(XYZ int, XYZ string, XYZ string) row format delimited fields terminated by ',' LOCATION '/hive_demo/';

Managed:

create table XYZ_Managed(XYZ int,XYZ string,XYZ string) partitioned by (branch string) clustered by (section) into 2 buckets row format delimited fields terminated by ',';

Temporary:

create temporary table XYZ(XYZ int,XYZ String);

For more better understanding of HiveQL visit this Github repository [LINK] (https://github.com/OpenGenus/hive_guide)