Basic commands in Hive Query Language

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 >;

2. Listing the available databases

show databases;

3. Creating tables

create table XYZ( XYZ int, XYZ string, XYZ string) row format delimited fields terminated by ',';

4. 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;

5. Display table

select * from XYZ;

6. Describe table

describe XYZ;

OR

describe extended XYZ;

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