【HQL】Hive 设计模式

  Hive 看上去和关系型数据库类似，但实际上只是使用了相似的 SQL 方言，它们的模式有很大不同。

1.按天划分表

数据集增长很快，可以一天一个表，表名加上时间戳。

不过 Hive 中通常通过分区实现。可以按天分区，查询时通过 WHERE 选择指定的分区，提高效率。

# 创建分区表
CREATE TABLE supply (id INT,part STRING,quantity INT)
PARTITIONED BY (INT day);

# 添加分区
ALTER TABLE supply add PARTITION (day=20110102)
ALTER TABLE supply add PARTITION (day=20110103)

# where过滤分区
SELECT part,quantity FROM supply
WHERE day>=20110102 and day<=20110103 AND quantity<4;

2.关于分区

优点：分区可以避免全盘扫描，优化查询。

缺点：

• 分区过多创建大量 Hadoop 文件和文件夹，最终超过 NameNode 的处理能力。
• 小文件过多，一个小文件会开启一个 JVM，开启和消耗的时间超过数据处理的时间。

解决思路：

• 每个文件应该是文件块大小的若干倍。
• 按不同时间粒度缺点合适的数据累积量，保证按照这个时间粒度，分区数量增长是均匀的。
• 使用两个级别的分区，并且是在不同的维度上。

3.唯一键和标准化

Hive 没有主键或子增键的概念。

复杂的数据类型如 array、map 和 struct 有利于在单行存储一对多的数据：

• 优点：最小化磁盘寻道，比如需要外键关系的情况。
• 缺点：数据重复，数据不一致的风险。

4.同一份数据多种处理

Hive 的独特语法，可以从一个数据源产生多个数据聚合，不用多次扫描，节约时间。例如：

# 下面的语句需要 2 次扫描
INSERT OVERWRITE TABLE sales
SELECT * FROM history WHERE action='purchased';
INSERT OVERWRITE TABLE credits
SELECT * FROM history WHERE action='returned';

# 优化为 1 次扫描
FROM history
INSERT OVERWRITE TABLE sales SELECT * WHERE action='purchased';
INSERT OVERWRITE TABLE credits SELECT * WHERE action='returned';

5.对中间表分区

• 背景：对一个查询或处理出现问题会导致所有问题重跑，所以会建立中间表存储中间结果。
• 问题：中间表没有分区，执行每天的程序都会写入同一个中间表。如果同时跑了两个日期的任务，那么同时都在写入，会影响结果正确性。
• 解决：中间表也按日期建立分区。不同日期的任务写到中间表不同的分区中。
• 新的问题：中间表分区会越来越多，要用户删除旧的分区。
• 解决：自动化处理。

举个例子：

# 建立 distinct_ip_in_logs 中间表，后序步骤用到
hive -hiveconf dt=2011-01-01
INSERT OVERWRITE TABLE distinct_ip_in_logs
SELECT distinct(ip) as ip from weblogs
WHERE hit_date='${hiveconf:dt}';

CREATE TABLE state_city_for day (state STRING, city STRING);

INSERT OVERWRITE state_city_for
SELECT distinct(state,city) FROM distinct_ip_in_logs
JOIN geodate on (distinct_ip_in_logs.ip=geodate.ip);


# 问题：运行一天的任务会覆盖掉中间表 distinct_ip_in_logs 的数据。
#      同时运行两天的任务会互相影响
# 解决：给 distinct_ip_in_logs 建立分区
hive -hiveconf dt=2011-01-01
INSERT OVERWRITE TABLE distinct_ip_in_logs
PARTITION (hit_date=${dt})
SELECT distinct(ip) as ip from weblogs
WHERE hit_date='${hiveconf:dt}';

CREATE TABLE state_city_for day (state STRING, city STRING)
PARTITIONED BY (hit_date STRING);

INSERT OVERWRITE state_city_for PARTITION(${hiveconf:dt})
SELECT distinct(state,city) FROM distinct_ip_in_logs
JOIN geodate on (distinct_ip_in_logs.ip=geodate.ip)
WHRER (hit_date='${hiveconf:dt}');

6.分桶表数据存储

并非所有的数据集都能划分为合理的分区（考虑到每个分区文件大小、数据是否均匀分布、随时间推移是否均匀增长等）。

分桶则更容易分解和管理。

例如下面对日期和用户 id 分区改为对日期分区，对用户 id 分桶：

CREATE TABLE weblog (url STRING, source_ip STRING)
PARTITIONED BY (dt STRING, user_id INT)

# 改为如下方式
CREATE TABLE weblog (user_id INT, url STRING, source_ip STRING)
PARTITIONED BY (dt STRING,)
CLUSTER BY (user_id) INTO 96 BUCKETS;

CREATE TABLE 只定义了元数据，不影响实际填充表的命令。下面介绍如何将数据正确插入到表中：

# 首先设置属性，强制 Hive 为目标表的分桶初始化过程设置一个正确的 reducer 个数
# 然后再执行一个查询来填充分区
SET hive.enforce.bucketing = true;
FROM raw_logs
INSERT OVERWRITE TABLE weblog
PARITION (dt='2009-02-25')
SELECT user_id,url,source_ip WHERE dt='2009-02-25';

如果没有设置 hive.enforce.bucketing 属性，那么也可以手动设置 reducer 个数，set mapred.reduce.tasks=96，然后在 INSERT 语句中，在 SELECT 语句后增加 CLUSTER BY。

7.为表增加列

Hive 是在原始数据文件上映射成表，所有不要求按特定格式导入数据。如果文件字段更少，那么表中多的列全为 NULL；如果文件字段更多，那么多的字段会被省略掉。

同时可以很方便的通过 ALTER TABLE table_name ADD COLUMN (col_name, TYPE) 增加列。但无法在已有字段的中间增加列。这在底层文件增加了一个字段时十分方便。

8.使用列示存储

列示存储在以下场景表现很好：

• 每个字段下面有很多重复数据。
• 字段很多，但通常只会查询一个字段或很少几个字段。

9.压缩

• 优点：压缩可以极大减小数据量，降低 IO 提高查询执行速度。
• 缺点：压缩和解压会消耗 CPU 资源。

由于 MapReduce 任务往往时 I/O 密集型，因此 CPU 开销通常不是问题。

欢迎关注，每天分分享大数据开发面经和技术。关注回复 803 获取 Hive 编程指南