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;