Hive Data Manipulation Language

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.