Hive Data Manipulation Language

1.Loading files into the tables LOAD DATA [local] inpath ' filepath ' [OVERWRITE] into TABLE tablename [PARTITION (Partcol1=val1


, Partcol2=val2 ...)] 2.Inserting data into Hive Tables from queries Standard syntax:insert OVERWRITE TABLE tablename1 [PARTITION (Partcol1=val 1, Partcol2=val2 ...) [IF not EXISTS]]
Select_statement1 from From_statement;
INSERT into TABLE tablename1 [PARTITION (Partcol1=val1, Partcol2=val2 ...)] select_statement1 from From_statement; INSERT into TABLE tablename1 [PARTITION (Partcol1=val1, Partcol2=val2 ...)]
 
(z,y) select_statement1 from From_statement; Hive extension (multiple inserts): from From_statement INSERT OVERWRITE TABLE tablename1 [PARTITION (Partcol1=val1, Partco L2=val2 ...) [IF not EXISTS]] select_statement1 [INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF not EXISTS]]
SELECT_STATEMENT2] [INSERT into TABLE tablename2 [PARTITION ...] select_statement2] ...; From from_statement inserts into TABLE tablename1 [PARTITION (Partcol1=val1, Partcol2=val2...)] select_statement1 [INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] [insert OVERWRITE table tablename2 [PAR Tition ... [IF not EXISTS]]
 
Select_statement2] ...; Hive extension (Dynamic partition Inserts): INSERT OVERWRITE TABLE tablename partition (PARTCOL1[=VAL1), Partcol2[=val2]. ..)
Select_statement from From_statement;


INSERT into TABLE tablename PARTITION (Partcol1[=val1], Partcol2[=val2] ...) select_statement from From_statement;
       3.Dynamic Partition inserts from Page_view_stg PVs INSERT OVERWRITE TABLE page_view Partition (dt= ' 2008-06-08 ', country) SELECT Pvs.viewtime, Pvs.userid, Pvs.page_url, pvs.referrer_url, NULL, NULL, PVS.IP, pvs.cnt 4.Writing dat A into the filesystem from queries Standard syntax:insert OVERWRITE [local] DIRECTORY directory1 [ROW FORMAT Row_format ] [STORED as File_format] (Note:only available starting with Hive 0.11.0) SELECT ...
 
From ... Hive extension (multiple inserts): from From_statement INSERT OVERWRITE[Local] Directory Directory1 select_statement1 [INSERT OVERWRITE [local] DIRECTORY directory2 Select_statement2] ... Row_forma t:delimited [FIELDS terminated by Char [escaped by Char]] [COLLECTION ITEMS terminated by char] [MAP KEYS TERM
		
		
Inated by Char] [LINES terminated by Char] [NULL DEFINED as Char] (note:only available starting with Hive 0.13) 5.Inserting values into the tables from SQL Standard syntax:insert into TABLE tablename [PARTITION (Partcol1[=val1], PA RTCOL2[=VAL2] ...)
 
VALUES Values_row [, Values_row ...] Where Values_row is: (value [, Value ...]) where a value is either null or any valid SQL literal Example CREATE TABLE
 
Students (name VARCHAR, age INT, GPA DECIMAL (3, 2)) CLUSTERED by (age) into 2 buckets STORED as ORC;
 
 
INSERT into TABLE students VALUES (' Fred Flintstone ', 1.28), (' Barney Rubble ', 32, 2.32); CREATE TABLE pageviews (userid VARCHAR (), link string, Came_from string) partitioned by (Datestamp string) CLUStered by (userid) into 256 buckets STORED as ORC; INSERT into TABLE pageviews PARTITION (datestamp = ' 2014-09-23 ') VALUES (' jsmith ', ' mail.com ', ' sports.com '), (' JDoe ', '
 
Mail.com ', null); INSERT into TABLE pageviews PARTITION (Datestamp) VALUES (' Tjohnson ', ' sports.com ', ' finance.com ', ' 2014-09-23 '), (' Tlee
		

', ' finance.com ', null, ' 2014-09-21 '); 6.Update Standard syntax:update tablename SET column = value [, column = value ...]


[WHERE expression] 7.Delete Standard syntax:delete from TableName [WHERE expression]

More info, refer to Page:https://cwiki.apache.org/confluence/display/hive/languagemanual+dml