Hadoop-Hive Commands #1

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

FLIGHT INFORMATION:

  • To enter hive shell:

Command: hive

  • To select particular database:

Command: use prwatech;

  • 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’);

  • To load data in the table:

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

  • 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’);

  • To load partition data in myflightinfo from flightinfo2008:

Command: FROM FlightInfo2008

INSERT INTO TABLE myflightinfo

PARTITION (Month=2)

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

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

CancellationCode WHERE Month=2;

  • To see which partitioned data is stored:

Command: SHOW PARTITIONS myflightinfo;

  • 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’);

 

  • To see the data in myflightinfo2008:

Command: SELECT* FROM myflightinfo2008;

Leave a Reply

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

Quick Support

image image