Hadoop Hive Basic Commands

  • date 14th March, 2019 |
  • by Prwatech |
  • 0 Comments

 

Hadoop Hive Basic Commands

 

Hadoop Hive Basic Commands, are you looking for a list of Top Rated Hive commands in Hadoop technology? Or the one who is casually glancing for the best platform which is listing the Hadoop Hive Commands with examples for beginners? Then you’ve landed on the Right Platform which is packed with Tons of Tutorials of Hive commands in Hadoop. Follow the below mentioned Hadoop Hive commands for Beginners which were originally designed by the world-class Trainers of Hadoop Training institute Professionals.

 

If you are the one who is a hunger to become the certified Pro Hadoop Developer? Or the one who is looking for the best Hadoop Training institute which offering advanced tutorials and Hadoop certification course to all the tech enthusiasts who are eager to learn the technology from starting Level to Advanced Level.

 

Hadoop Hive Commands with Examples

 

Create Database

 

Using below given command we create a database.

 

Hadoop Hive Basic Commands

 

Show Database: Using the below-given command we can list all databases.

 

Hadoop Hive Basic Commands

 

Use Database: Using below given command we can use a particular database.

 

Hadoop Hive Basic Commands

 

Drop Database: Using the below-given command we can remove a particular database.

 

Hadoop Hive Basic Commands

Hadoop Hive Basic Commands

 

Create Table: Using the below command create a table with multiple columns and describing datatype of each column.

 

Hadoop Hive Basic Commands

 

Load data into a table:

 

Using the below command we load data into the table from local or cloud storage.

Data Source:

https://drive.google.com/file/d/14rESYcQ5lqNWKRZ6I9KW7b7sA2cZ5xXC/view?usp=sharing

 

Hadoop Hive Basic Commands

 

Select Command: To display a record from the selected table.

 

Hadoop Hive Basic Commands

 

Describe table: Using the below command we can describe the property of a table.

 

Hadoop Hive Basic Commands

 

Alter: Alter command is used to add or to make changes in current table property.

 

Hadoop Hive Basic Commands

 

Adding Column: Using below given command we can new column in our existing table.

 

Hadoop Hive Basic Commands

 

FLIGHT INFORMATION

 

To enter the hive shell:

Command: hive

 

Hadoop Hive Basic Commands

 

To select a particular database:

Command: use prwatech;

 

Hadoop Hive Basic Commands

 

Create table flightinfo2008:

 

Command: create table flightinfo2008(Year SMALLINT, Month TINYINT, DayofMonth TINYINT,

 

DayOfWeek TINYINT,DepTime SMALLINT,

CRSDepTime SMALLINT, ArrTime SMALLINT,

CRSArrTime SMALLINT,UniqueCarrier STRING,

FlightNum STRING, TailNum STRING,

ActualElapsedTime SMALLINT, CRSElapsedTime

SMALLINT,AirTime SMALLINT, ArrDelay

SMALLINT, DepDelay SMALLINT,Origin STRING,

DestSTRING,Distance INT,TaxiIn SMALLINT,

TaxiOut SMALLINT, Cancelled

SMALLINT,CancellationCode STRING, Diverted

SMALLINT,CarrierDelay SMALLINT, WeatherDelay

SMALLINT,NASDelay SMALLINT, SecurityDelay

SMALLINT,LateAircraftDelaySMALLINT)COMMENT

‘Flight InfoTable’ ROW FORMAT DELIMITED

FIELDS TERMINATED BY ‘,’

LINES TERMINATED BY ‘\n’

STORED AS TEXTFILE

TBLPROPERTIES (‘creator’=’Bruce Brown’, ‘created_at’=’Thu Sep 19 10:58:00 EDT 2013’);

 

Hadoop Hive Basic Commands

 

To load data in the table:

 

Command: LOAD DATA LOCAL INPATH ‘/home/cloudera/Desktop/2008.csv’ INTO TABLE flightinfo2008;

 

Hadoop Hive Basic Commands

 

To create table myflightinfo:

 

Command: create table myflightinfo(Year

SMALLINT, DontQueryMonth TINYINT, DayofMonth

TINYINT, DayOfWeek TINYINT,DepTime SMALLINT,

ArrTime SMALLINT,UniqueCarrier STRING,

FlightNum STRING,AirTime SMALLINT, ArrDelay

SMALLINT, DepDelay SMALLINT,Origin STRING,

Dest STRING, Cancelled SMALLINT,CancellationCode

STRING)COMMENT ‘FlightInfoTable’PARTITIONED

BY(Month TINYINT)ROW FORMAT DELIMITED

FIELDS TERMINATED BY ‘,’LINES TERMINATED BY

‘\n’ STORED AS RCFILETBLPROPERTIES

(‘creator’=’Bruce Brown’, ‘created_at’=’MonSep 2 14:24:19 EDT 2013’);

 

Hadoop Hive Basic Commands

 

To load partition data in my flight info from flightinfo2008:

Command: FROM FlightInfo2008

INSERT INTO TABLE my flight info

PARTITION (Month=2)

SELECT Year, Month, DayofMonth, DayOfWeek, DepTime,ArrTime, UniqueCarrier, FlightNum,

AirTime, ArrDelay, DepDelay, Origin, Dest, Cancelled,

CancellationCode WHERE Month=2;

 

Hadoop Hive Basic Commands

Hadoop Hive Basic Commands

 

To see which partitioned data is stored:

Command: SHOW PARTITIONS my flight info;

 

Hadoop Hive Basic Commands

 

To create table myflightinfo2008:

Command: CREATE TABLE myflightinfo2008 AS

SELECT Year, Month, DepTime, ArrTime, FlightNum,

Origin, Dest FROM FlightInfo2007WHERE (Month =

7 AND DayofMonth = 3) AND (Origin=’JFK’ AND

Dest=’ORD’);

 

Hadoop Hive Basic Commands

 

To see the data in myflightinfo2008:

Command: SELECT* FROM myflightinfo2008;

 

Hadoop Hive Basic Commands

 

 

Hadoop Hive commands for Beginners

To enter the Hive shell:

Command:  hive

 

Hive commands in Hadoop

 

To create a database:

Command: create database prwatech;

Hive commands in Hadoop

 

To see the list of databases:

Command: show databases;

 

Hive commands in Hadoop

 

To use a particular database to create a table:

 

Command: use prwatech;

 

Hive commands in Hadoop

 

Move data from lfs to hdfs:

 

Command: Hadoop fs -copyFromLocal ‘–LOCATION OR PATH OF DOCUMENT–’ /user/Cloudera (On new terminal)

 

Hive commands in Hadoop

 

Create a table:

Command:

create table cricket (Player_NameSTRING,

Runs_ScoredINT,Balls_FacedINT,Strike_Rate

FLOAT) row format delimited fields terminated by ‘,’

stored as textfile;

 

Hive commands in Hadoop

 

To load data into the table:

 

Command: LOAD DATA INPATH ‘/user/cloudera/cricketdoc.txt’ OVERWRITE INTO table cricket;

 

Hive commands in Hadoop

 

To create an external table:

 

Command: create external table cricket text

(Player_NameSTRING,Runs_ScoredINT,Balls_Face

dINT, Strike_Rate FLOAT) row format delimited

fields terminated by ‘,’ stored as textfile LOCATION

‘/user/Cloudera/external’;

 

Hive commands in Hadoop

 

To insert the data from the cricket table to external table cricket text:

 

Command: from cricket cric insert overwrite table

cricket text select cric.Player_Name,

cric.Runs_Scored, cric.Balls_Faced,cric.Strike_Rate;

 

Hive commands in Hadoop

Hive commands in Hadoop

 

To see the data in the external table:

Command: select * from cricket text;

 

Hive commands in Hadoop

 

Get success in your career as a Hadoop developer by being a part of the Prwatech, India’s leading Hadoop training institute in Bangalore.

Leave a Reply

Your email address will not be published. Required fields are marked *

Quick Support

image image