Apache Hive Tutorial

Learn How Hive Work in Simple Example

# Hive startup commande
hive

# show TABLES in Hive 
SHOW TABLES;

# Hive shutdown
exit

Create Hive TABLE :

The syntax to create TABLE :

# commande to create Hive TABLE
CREATE [TEMPORARY] [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]

# show Hive TABLE structure
DESCRIBE 'table_name'

hiking Table Structure :

  hiking(id, name, region, distance , Altitude, suiteHiking)

example :

# create TABLE
CREATE TABLE IF NOT EXISTS hiking (
	id INT,
	name STRING,
	region STRING,
	distance FLOAT,
	Altitude INT,
	suiteHiking INT )
	ROW FORMAT DELIMITED
	FIELDS TERMINATED BY '\t'
	LINES TERMINATED BY '\n' ;

LOAD DATA to 'hiking' TABLE :

The syntax for load data :

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename 

example :

LOAD DATA LOCAL INPATH '<repository Path>/input/hiking.txt' #LOAD DATA FROM File
OVERWRITE INTO TABLE hiking;

Query :

hiking more than 20 km :

SELECT * FROM hiking WHERE distance >=20;

The hiking that have a suite :

SELECT * FROM hiking WHERE suiteHiking IS NOT NULL;

The maximum / average distance by region :

SELECT region, max(distance) max FROM hiking GROUP BY region;

SELECT region, avg(distance) moy FROM hiking GROUP BY region;