HiveQL: Data Manipulation

create table t1(c1 string) stored as textfile;

LOAD DATA [LOCAL] INPATH () [OVERWRITE] INTO TABLE T1;

如果使用 LOCAL INPATH，则从本地拷贝文件到表的存储路径。
如果不用 LOCAL, 则需要数据在HDFS 上，从INPATH 的路径移动到表的存储路径。
使用OVERWRITE 则表原来的数据会删除。
如果不用 OVERWRITE，如果文件名已经在表中存在，则会在新的文件名后加上“copy${seq}”，seq 从 1 开始编号。

INSERT OVERWRITE TABLE employees
PARTITION (country = 'US', state = 'OR')
SELECt * FROM staged_employees se
WHERe se.cnty = 'US' AND se.st = 'OR';

FROM staged_employees se
INSERT OVERWRITE TABLE employees
PARTITION (country = 'US', state = 'OR')
SELECt * WHERe se.cnty = 'US' AND se.st = 'OR'
INSERT OVERWRITE TABLE employees
PARTITION (country = 'US', state = 'CA')
SELECT * WHERe se.cnty = 'US' AND se.st = 'CA'
INSERT OVERWRITE TABLE employees
PARTITION (country = 'US', state = 'IL')
SELECT * WHERe se.cnty = 'US' AND se.st = 'IL';

• Specify a partiton column.

INSERT OVERWRITE TABLE employees
PARTITION (country = 'US', state)
SELECT ..., se.cnty, se.st
FROM staged_employees se
WHERe se.cnty = 'US';

• All partition columns are dynamic.

INSERT OVERWRITE TABLE employees
PARTITION (country, state)
SELECt ..., se.cnty, se.st
FROM staged_employees se;

Table 5-1. Dynamic partitions properties

hive.exec.max.dynamic.par
titions
+1000 The total number of dynamic partitions that can be created by
one statement with dynamic partitioning. Raises a fatal error if the limit is exceeded.
| hive.exec.max.created.files | 100000 | The maximum total number of files that can be created globally. A Hadoop counter is used to track the number of files created. Raises a fatal error if the limit is exceeded.

CREATE TABLE ca_employees
AS SELECt name, salary, address
FROM employees
WHERe se.state = 'CA';

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/ca_employees'
SELECt name, salary, address
FROM employees
WHERe se.state = 'CA';

with LOCAL， Data insert into the computer hive driver runs on.