Hive Data Operations (translated from hive wiki + example)

Hive has two data modification methods

Load from file to hive table

Hive does not perform any conversion when loading data to a table. The loading operation is a pure copy/move operation, which moves data files to the corresponding hive table.

Syntax

 
Load data [local] inpath 'filepath' [overwrite] into Table tablename [partition (partcol1 = val1, partcol2 = val2...)]
 

Instance

Assume that the hive warehouse directory is/user/hadoop/warehouse. Here is a login table.

 
Create Table login (UID bigint, IP string) partitioned by (DT string) Row format delimitedfields terminated by ', 'stored as textfile;
 

There is a user logon log file with the following content:

 
888, 8.8.8.8999, 9.9.9.9
 

Note that columns and columns are separated by commas (,). The first column is UID and the second column is the user IP address.

Load data

 
Load data local inpath '/data/login/20130101.csv' overwrite into Table login partition (Dt = '20160301 ');
 

This means that from the local disk, the file '/data/login/20130101.csv' is stored in the table login, And the partition DTIS '3030101' directory (after HDFS success. Success, 20130101.csv will be placed inHDFS: // namenode: 9000/user/hadoop/warehouse/login/dt = 20130101/20130101. CSV.

Overwrite indicates that the target table (or partition) will be deleted before the data is loaded, and then replaced with the new data. If overwrite is not specified, data is appended to the target table (or partition). If the file name conflicts with the file in the target directory, the name is automatically renamed.

If this parameter is not specified, the data is moved from '/ data/login/20130101.csv 'of HDFS to the login table. The partition dt is '123. That is, the original HDFS File '/data/login/20130101.csv' is moved to HDFS: // namenode: 9000/user/hadoop/warehouse/login/dt = 20130101/20130101. CSV.

Note: The loaded file name cannot be a sub-directory. Hive performs some simple checks to ensure that the file being loaded matches the target table. Currently, it checks if the table is stored in the sequencefile format-the file being loaded is deserialized.

 

Insert data from query to hive table

Standard syntax

Insert overwrite table tablename1 [partition (partcol1 = val1, partcol2 = val2 ...) [If not exists] select_statement1 from from_statement; insert into Table tablename1 [partition (partcol1 = val1, partcol2 = val2...)] select_statement1 from from_statement;
 

Example:

 
Insert overwrite table login_user select distinct uid from login_log; -- query the login user from the login log table and insert it into the login_user table. If login_user already has data, overwrite it, otherwise, create the insert overwrite table login_user partition (Dt = '000000') Select distinct uid from login_log where dt = '000000'; -- query the login user of the current day from the log table of 20130101, insert to the login_user table. If the login_user has a partition dt = '20160301', overwrite it. Otherwise, insert into Table login_user select distinct uid from login_log; -- query the login user from the login log table, insert to the login_user table. If login_user already has data, append it. Otherwise, create insert into Table login_user partition (Dt = '000000') Select distinct uid from login_log where dt = '2016 '; -- query the login user of the current day from the login log table of 20130101 and insert it into the login_user table. If the login_user already has a partition dt = '20160301', append it. Otherwise, create
 

 

Extended syntax

Multiple Inserts

 
From from_statementinsert overwrite table tablename1 [partition (partcol1 = val1, partcol2 = 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_statementinsert into Table tablename1 [partition (partcol1 = val1, partcol2 = val2...)] select_statement1 [insert into Table tablename2 [partition...] select_statement2] [insert overwrite table tablename2 [partition... [If not exists] select_statement2]...;
 

Insert the result set to multiple tables or partitions in one query. In practice, I feel that I am using less resources. I will not use this example here.

 

Dynamic partition insertion

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;
 

The standard syntax already includes dynamic partition insertion, which is not described here.

 

Reference https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML