preloader
blog-post

HIVE Commands

Table of Contents

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;
Share this blog:
Comments

Related Articles