HIVE Commands

Sunday, May 6, 2018

HIVE Commands




Login into hive

Command : hive

Logoff from hive
Command : quit;
Command : exit;

Create managed table hive
CREATE TABLE IF NOT EXISTS snDrivers
(driverId int,
name string,
ssn int,
location string,
certified string,
wageplan string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

Create external table hive
CREATE EXTERNAL TABLE IF NOT EXISTS DriversExternal
(driverId int,
name string,
ssn int,
location string,
certified string,
wageplan string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

Describe Table Name
Command : desc extended drivers

Load data into hive table – File From Local


Step 01 : Download the driver data file from here.
Step 02 : Copy into your Linux machine on which you have hadoop installed (Please note this is not HDFS file system)

Step 03 : Execute the command in hive command line OR in Ambari
LOAD DATA LOCAL INPATH '/entechlog/hive/input/drivers.csv' OVERWRITE INTO TABLE drivers;

Step 04 : Validate the load by querying the table, After executing this command the results area in hive view will show the results.
select * from drivers order by name;


Step 05 : You can also see the HDFS file by navigating to "/apps/hive/warehouse/" OR "/hive/warehouse/". This default location can be overridden by adding below line to your create table statement.
LOCATION 'HDFS path where you would live to save'
Load data into hive table – File From HDFS
Step 01 : Download the driver data file from here.

Step 02 : Upload the file to HDFS using Ambari Files view.

Step 03 : Execute the command in hive command line OR in Ambari
LOAD DATA INPATH '/user/admin/hive/input/drivers.csv' OVERWRITE INTO TABLE drivers;

Step 04 : Validate the load by querying the table, After executing this command the results area in hive view will show the results.
select * from drivers order by name;

Count number of records in a table
SELECT COUNT(*) FROM DRIVERS;
More to come ...

No comments

Post a Comment

Error 404

The page you were looking for, could not be found. You may have typed the address incorrectly or you may have used an outdated link.

Go to Homepage