HIVE Commands

  • Home
  • /
  • HIVE Commands
HIVE Commands

HIVE Commands

Hadoop 06 May 2018
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)

post thumb
  • 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;  
post thumb
  • 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;  
post thumb post thumb

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’

post thumb
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.

post thumb
  • 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;
post thumb
  • 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;  
post thumb
Count number of records in a table
SELECT COUNT(*) FROM DRIVERS;
post thumb

More to come …

Share: