HiveQL(四):数据操作(装载数据、动态分区、导出数据)

1 向表中装载数据

1.1 向管理表中装载数据

Hive没有行级别的数据插入、数据更新和删除操作，往表中装载数据的唯一途径就是使用一种“大量”的数据装载操作。或者通过其他方式仅仅将文件写入到正确的目录下。

下述语句将本地数据装载到分区表employees：

LOAD DATA LOCAL INPATH '/data/california-employees'
OVERWRITE INTO TABLE employees
PARTITION (country = 'US', state = 'CA');

如果分区目录不存在的话，这个命令会先创建分区目录，然后再将数据拷贝到该目录下。如果目标表是非分区表，那么语句中省略PARTITION子句。

通常情况下指定的路径应该是一个目录，而不是单个独立的文件。hive会将所有文件都拷贝到这个目标表路径下，而且文件名会保持不变。

如果使用了LOCAL关键字，那么这个路径应该为本地文件系统路径。数据将会被拷贝到目标位置。如果省略了LOCAL关键字，那么这个路径就是分布式文件系统中的路径，这种情况下，数据是从这个路径转移到目标位置的。

如果指定了OVERWRITE关键字，那么目标文件夹中之前存在的数据将会被删除掉。如果没有这个关键字，仅仅会把新增的文件增加到目标文件夹中而不会删除之前的数据。如果没有OVERWRITE，而目标文件夹下已经存在同名的文件时，会保留之前的文件并且会重命名新文件为“之前的文件名_序列号”

如果目标表是分区表那么需要使用PARTITION子句，而且用户还必须为每个分区的健指定一个值。

执行上述语句后，数据将会存放到如下这个文件夹中：
hdfs://master:9000/hive/warehouse/mydb.db/employess/country=US/state=CA

对于INPATH子句中使用的文件路径还有一个限制，那就是这个路径下不可以包含任何文件夹。

hive并不会验证用户装载的数据和表的模式是否匹配（读时模式）。然而，hive会验证文件存储格式是否和表结构定义的一致。例如，如果表在创建时定义的存储格式是SEQUENCEFILE，那么装载进去的文件也应该是SEQUENCEFILE格式的才行。

1.2 通过查询语句向表中插入数据

INSERT语句允许用户通过查询语句向目标表中插入数据。假设另一张名为staged_employees的表里已经有相关数据了。在表staged_employees中我们使用不同的名字来表示国家和州，分别称作cnty和st。下面语句从staged_employees表中查询出OR州的数据，并装载到employees表中。

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

这里使用了OVERWRITE关键字，因此之前分区中的内容（如果是非分区表，就是之前表中的内容）将会被覆盖掉。

这里如果没有使用OVERWRITE关键字或者使用INTO关键字替换掉它的话，那么hive将会以追加的方式写入数据而不会覆盖掉之前已经存在的内容。

然而，如果表staged_employees非常大，而且用户需要对65个州都执行这些语句的话，那么需要扫描staged_employees表65次。对此，hive提供了另一种INSERT语法，可以只扫描一次输入数据，然后按多种方式进行划分。如下例子显示了如何为3个州创建表employees分区：

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'

这样从staged_employees表中读取的每条记录都会经过一条SELECT … WHERE …句子进行判断。

1.3 动态分区插入

前面所说的语法中还是有一个问题，即：如果需要创建非常多的分区，那么用户就需要写非常多的SQL。不过，hive提供了一个动态分区功能，其可以基于查询参数推断出需要创建的分区名称。下述是动态分区的例子：

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

hive根据SELECT语句中最后2列来确定分区字段country和state的值。这就是为什么在表staged_employees中使用了不同的命名，就是为了强调源表字段值和输出分区值之间的关系是根据位置而不是根据命名来匹配的。

假设表staged_employees中共有100个国家和州的话，执行完上述语句后employees表就将会有100个分区。

用户也可以混合使用动态和静态分区。如下这个例子中指定了country字段的值为静态的US，而分区字段state是动态值：

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

静态分区健必须出现在动态分区键之前。动态分区功能默认情况下没有开启，开启后，默认是以“严格”模式执行的，在严格模式下要求至少有一列分区字段是静态的。动态分区属性如下表：

因此，前面我们使用的第一个动态分区的例子看上去应该是像下面这样子：

 set hive.exec.dynamic.partition=true;
 set hive.exec.dynamic.partition.mode=nonstrict;
 set hive.exec.max.dynamic.partitions.pernode=1000;

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

1.4 单个查询语句中创建表并加载数据

用户同样可以在一个语句中完成创建表并将查询结果载入这个表的操作：

CREATE TABLE ca_employees
AS SELECT name, salary, address
FROM employees
WHERE state = 'CA';

这张表只含有employees表中来自CA州的name,salary和address3个字段的信息。新表的模式是根据SELECT语句来生成的。使用这个功能的常见情况是从一个大的宽表中选取部分需要的数据集。这个功能不能用于外部表。

1.5 导出数据

如下所示使用INSERT … DIRECTORY …：

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/ca_employees'
SELECT name, salary, address
FROM employees
WHERE state = 'CA';

这里OVERWRITE和LOCAL关键字与之前所述的一样。一个或者多个文件将会被写入到/tmp/ca_employees，具体文件的个数取决于调用的reducer个数。

和向表中插入数据一样，用户也是可以通过如下方式在一次扫描中指定多个输出文件夹目录的：

FROM staged_employees se
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/or_employees'
	SELECT * WHERE se.cty = 'US' AND se.st = 'OR'
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/ca_employees'
	SELECT * WHERE se.cty = 'US' AND se.st = 'CA'
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/il_employees'
	SELECT * WHERE se.cty = 'US' AND se.st = 'IL'

参考：《hive编程指南》