第5章：HiveQL：数据操作

向管理表中装载数据

1
2
3
LOAD DATA LOCAL INPATH '${env:HOME}/colifornia-employees'
OVERWRITE INTO TABLE employees
PARTITION (country = 'US', state = 'CA');

注意点：

1.如果分区目录不存在的话，会先创建分区目录，然后再将数据拷贝到该目录下。

2.如果目标表是非分区表则省略掉PARTITION子句。

3.通常情况下指定的路径应该是一个目录，而不是单个独立的文件。Hive会将所有文件拷贝到这个目录中。

4.如果使用LOCAL，这个路径应该为本地文件系统路径，数据会被拷贝到目标位置；

5.如果省略LOCAL，这个路径应该是分布式文件系统中的路径。数据会从这个路径转移到目标位置。

6.用户不可以使用LOAD DATA 语句将数据从一个集群的HDFS中转移到另一个集群的HDFS中。

7.如果不使用OVERWRITE，仅仅会把新增的文件增加到目标文件夹中而不会删除之前的数据。然而，如果目标文件夹中已经存在和装载的文件同名的文件，那么旧的同名文件会被覆盖重写。（实际上，如果不使用OVERWRITE，而且目标文件夹已经存在同名文件时，会保留之前的文件并且重命名新文件为 “之前的文件名_序列号” ）

8.对于INPATH子句中使用的文件路径不可以包含任何文件夹。

9.Hive会验证文件格式是否和表结构定义的一致。

例如：如果表在创建时定义的存储格式是SEQUENCEFILE，那么装载进去的文件也应该是SEQUENCEFILE格式。

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

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

只扫描一次输入数据，然后按多种方式进行划分：

1
2
3
4
5
6
7
8
9
10
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';

通过使用这个结构，原表中的某些数据可以被写入目标表的多个分区中或者不被写入任一个分区中。

动态分区插入

如果需要创建非常多的分区，使用前面的语句会写非常多的SQL

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

Hive根据SELECT语句中最后2列来确定分区字段country和state的值。

动态和静态分区结合：

1
2
3
4
5
6
7
8
9
10
11
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 = 'US', state)
SELECT ..., se.cnty, se.st
FROM staged_employees se
WHERE se.cnty = 'US';

静态分区键必须出现在动态分区键之前。

动态分区属性：

属性名称 缺省值 描述
hive.exec.dynamic.partition false 设置成true，表示开启动态分区功能
hive.exec.dynamic.partition.mode strict 设置成nostrict，表示允许所有分区都是动态的
hive.exec.max.dynamic.partitions.pernode 100 每个mapper或者reducer可以创建的最大动态分区个数。
如果某个mapper或者reducer尝试创建大于这个值的分区会抛出一个致命错误信息
hive.exec.max.dynamic.partitions +1000 一个动态分区创建语句可以创建的最大动态分区数。如果超过这个值则抛出一个致命错误信息
hive.exec.max.created.files 100000 全局可以创建的最大文件个数。有一个Hadoop计数器会跟踪记录创建了多少个文件。
如果超出这个值则会抛出一个致命错误信息		 
动态分区默认是关闭的。 
开启后，默认以“严格”模式执行。 要求至少有一列分区字段是静态的。

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

使用一个语句中完成创建表并将查询结果载入这个表：

1
2
3
4
CREATE TABLE ca_employees AS
SELECT name, salary, address
FROM employees
WHERE se.state = 'CA';

使用这个功能的常见情况是从一个大的宽表中选取部分需要的数据集。

这个功能不能用于外部表。使用ALTER TABLE语句可以为外部表“引用”到一个分区，这里本身没有进行数据装载，而是将元数据中指定一个指向数据的路径。

导出数据

如果数据文件恰好是用户需要的格式，只需要简单地拷贝文件夹或者文件：

hadoop fs -cp source_path target_path

否则可以使用INSERT…DIRECTORY…

1
2
3
4
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/ca_employees'
SELECT name, salary, address
FROM employees
WHERE se.state = 'CA';
  • 写入多少个文件取决于调用的reducer个数
  • 不管在源表中数据实际是怎么存储的，Hive会将所有的字段序列化成字符串写入到文件中。Hive使用和Hive内部存储的表相同的编码方式来生成输出文件。

指定多个输出文件夹目录：

1
2
3
4
5
6
7
FROM staged_employees se
INSERT OVERWRITE DIRECTORY '/tmp/or_employees'
    SELECT * WHERE se.cty = 'US' AND se.st = 'OR'
INSERT OVERWRITE DIRECTORY '/tmp/ca_employees'
    SELECT * WHERE se.cty = 'US' AND se.st = 'CA'
INSERT OVERWRITE DIRECTORY '/tmp/il_employees'
    SELECT * WHERE se.cty = 'US' AND se.st = 'IL';