{"id":871,"date":"2019-03-14T05:56:01","date_gmt":"2019-03-14T05:56:01","guid":{"rendered":"https:\/\/prwatech.in\/blog\/?p=871"},"modified":"2024-04-06T09:27:51","modified_gmt":"2024-04-06T09:27:51","slug":"hadoop-hive-basic-commands","status":"publish","type":"post","link":"https:\/\/prwatech.in\/blog\/hadoop\/hive\/hadoop-hive-basic-commands\/","title":{"rendered":"Hadoop Hive Basic Commands"},"content":{"rendered":"

\"\"<\/p>\n

 <\/p>\n

Hadoop Hive Basic Commands<\/h1>\n

 <\/p>\n

Hadoop Hive Basic Commands<\/strong>, are you looking for a list of Top Rated Hive commands in Hadoop technology<\/a>? Or the one who is casually glancing for the best platform which is listing the Hadoop Hive Commands with examples for beginners? Then you\u2019ve 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<\/a> Professionals.<\/p>\n

 <\/p>\n

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

 <\/p>\n

Hadoop Hive Commands with Examples<\/h2>\n

 <\/p>\n

Create Database<\/strong><\/h3>\n

 <\/p>\n

Using below given command we create a database.<\/p>\n

 <\/p>\n

\"Hadoop<\/p>\n

 <\/p>\n

Show Database:<\/strong> Using the below-given command we can list all databases.<\/h3>\n

 <\/p>\n

\"Hadoop<\/p>\n

 <\/p>\n

Use Database:<\/strong> Using below given command we can use a particular database.<\/h3>\n

 <\/p>\n

\"Hadoop<\/p>\n

 <\/p>\n

Drop Database:<\/strong> Using the below-given command we can remove a particular database.<\/h3>\n

 <\/p>\n

\"Hadoop<\/p>\n

\"Hadoop<\/p>\n

 <\/p>\n

Create Table:<\/strong> Using the below command create a table with multiple columns and describing datatype of each column.<\/h3>\n

 <\/p>\n

\"Hadoop<\/p>\n

 <\/p>\n

Load data into a table:<\/strong><\/h3>\n

 <\/p>\n

Using the below command we load data into the table from local or cloud storage.<\/p>\n

Data Source<\/strong>:<\/p>\n

https:\/\/drive.google.com\/file\/d\/14rESYcQ5lqNWKRZ6I9KW7b7sA2cZ5xXC\/view?usp=sharing<\/a><\/p>\n

 <\/p>\n

\"Hadoop<\/p>\n

 <\/p>\n

Select Command:<\/strong> To display a record from the selected table.<\/h3>\n

 <\/p>\n

\"Hadoop<\/p>\n

 <\/p>\n

Describe table:<\/strong> Using the below command we can describe the property of a table.<\/h3>\n

 <\/p>\n

\"Hadoop<\/p>\n

 <\/p>\n

Alter:<\/strong> Alter command is used to add or to make changes in current table property.<\/h3>\n

 <\/p>\n

\"Hadoop<\/p>\n

 <\/p>\n

Adding Column:<\/strong> Using below given command we can new column in our existing table.<\/h3>\n

 <\/p>\n

\"Hadoop<\/p>\n

 <\/p>\n

FLIGHT INFORMATION<\/h3>\n

 <\/p>\n

To enter the hive shell:<\/strong><\/p>\n

Command: hive<\/strong><\/p>\n

 <\/p>\n

\"Hadoop<\/strong><\/p>\n

 <\/p>\n

To select a particular database:<\/strong><\/p>\n

Command: use prwatech;<\/strong><\/p>\n

 <\/p>\n

\"Hadoop<\/strong><\/p>\n

 <\/p>\n

Create table flightinfo2008:<\/strong><\/p>\n

 <\/p>\n

Command: create table flightinfo2008(Year SMALLINT, Month TINYINT, DayofMonth TINYINT,<\/strong><\/p>\n

 <\/p>\n

DayOfWeek TINYINT,DepTime SMALLINT,<\/strong><\/p>\n

CRSDepTime SMALLINT, ArrTime SMALLINT,<\/strong><\/p>\n

CRSArrTime SMALLINT,UniqueCarrier STRING,<\/strong><\/p>\n

FlightNum STRING, TailNum STRING,<\/strong><\/p>\n

ActualElapsedTime SMALLINT, CRSElapsedTime<\/strong><\/p>\n

SMALLINT,AirTime SMALLINT, ArrDelay<\/strong><\/p>\n

SMALLINT, DepDelay SMALLINT,Origin STRING,<\/strong><\/p>\n

DestSTRING,Distance INT,TaxiIn SMALLINT,<\/strong><\/p>\n

TaxiOut SMALLINT, Cancelled<\/strong><\/p>\n

SMALLINT,CancellationCode STRING, Diverted<\/strong><\/p>\n

SMALLINT,CarrierDelay SMALLINT, WeatherDelay<\/strong><\/p>\n

SMALLINT,NASDelay SMALLINT, SecurityDelay<\/strong><\/p>\n

SMALLINT,LateAircraftDelaySMALLINT)COMMENT<\/strong><\/p>\n

‘Flight InfoTable’ ROW FORMAT DELIMITED<\/strong><\/p>\n

FIELDS TERMINATED BY ‘,’<\/strong><\/p>\n

LINES TERMINATED BY ‘\\n’<\/strong><\/p>\n

STORED AS TEXTFILE<\/strong><\/p>\n

TBLPROPERTIES (‘creator’=’Bruce Brown’, ‘created_at’=’Thu Sep 19 10:58:00 EDT 2013’);<\/strong><\/p>\n

 <\/p>\n

\"Hadoop<\/strong><\/p>\n

 <\/p>\n

To load data in the table:<\/strong><\/p>\n

 <\/p>\n

Command: LOAD DATA LOCAL INPATH ‘\/home\/cloudera\/Desktop\/2008.csv’ INTO TABLE flightinfo2008;<\/strong><\/p>\n

 <\/p>\n

\"Hadoop<\/strong><\/p>\n

 <\/p>\n

To create table myflightinfo:<\/strong><\/p>\n

 <\/p>\n

Command: create table myflightinfo(Year<\/strong><\/p>\n

SMALLINT, DontQueryMonth TINYINT, DayofMonth<\/strong><\/p>\n

TINYINT, DayOfWeek TINYINT,DepTime SMALLINT,<\/strong><\/p>\n

ArrTime SMALLINT,UniqueCarrier STRING,<\/strong><\/p>\n

FlightNum STRING,AirTime SMALLINT, ArrDelay<\/strong><\/p>\n

SMALLINT, DepDelay SMALLINT,Origin STRING,<\/strong><\/p>\n

Dest STRING, Cancelled SMALLINT,CancellationCode<\/strong><\/p>\n

STRING)COMMENT ‘FlightInfoTable’PARTITIONED<\/strong><\/p>\n

BY(Month TINYINT)ROW FORMAT DELIMITED<\/strong><\/p>\n

FIELDS TERMINATED BY ‘,’LINES TERMINATED BY<\/strong><\/p>\n

‘\\n’ STORED AS RCFILETBLPROPERTIES<\/strong><\/p>\n

(‘creator’=’Bruce Brown’, ‘created_at’=’MonSep 2 14:24:19 EDT 2013’);<\/strong><\/p>\n

 <\/p>\n

\"Hadoop<\/strong><\/p>\n

 <\/p>\n

To load partition data in my flight info from flightinfo2008:<\/strong><\/p>\n

Command: FROM FlightInfo2008<\/strong><\/p>\n

INSERT INTO TABLE my flight info<\/strong><\/p>\n

PARTITION (Month=2)<\/strong><\/p>\n

SELECT Year, Month, DayofMonth, DayOfWeek, DepTime,ArrTime, UniqueCarrier, FlightNum,<\/strong><\/p>\n

AirTime, ArrDelay, DepDelay, Origin, Dest, Cancelled,<\/strong><\/p>\n

CancellationCode WHERE Month=2;<\/strong><\/p>\n

 <\/p>\n

\"Hadoop<\/strong><\/p>\n

\"Hadoop<\/strong><\/p>\n

 <\/p>\n

To see which partitioned data is stored:<\/strong><\/p>\n

Command: SHOW PARTITIONS my flight info;<\/strong><\/p>\n

 <\/p>\n

\"Hadoop<\/strong><\/p>\n

 <\/p>\n

To create table myflightinfo2008:<\/strong><\/p>\n

Command: CREATE TABLE myflightinfo2008 AS<\/strong><\/p>\n

SELECT Year, Month, DepTime, ArrTime, FlightNum,<\/strong><\/p>\n

Origin, Dest FROM FlightInfo2007WHERE (Month =<\/strong><\/p>\n

7 AND DayofMonth = 3) AND (Origin=’JFK’ AND<\/strong><\/p>\n

Dest=’ORD’);<\/strong><\/p>\n

 <\/p>\n

\"Hadoop<\/strong><\/p>\n

 <\/p>\n

To see the data in myflightinfo2008:<\/strong><\/p>\n

Command: SELECT* FROM myflightinfo2008;<\/strong><\/p>\n

 <\/p>\n

\"Hadoop<\/strong><\/p>\n

 <\/p>\n