Hive中的视图与分区

转载自 lxw1234@qq.com的博客

同关系型数据库一样，Hive中也支持视图(View)和分区(Partition)，但与关系型数据库中的有所区别，本文简单介绍Hive中视图和分区的示例。
在真实业务场景下，视图的应用比较少，分区使用的非常多，因此建议对分区这块多花的时间来了解。

Hive中的视图

和关系型数据库一样，Hive中也提供了视图的功能，注意Hive中视图的特性，和关系型数据库中的稍有区别：

只有逻辑视图，没有物化视图；
视图只能查询，不能Load/Insert/Update/Delete数据；
视图在创建时候，只是保存了一份元数据，当查询视图的时候，才开始执行视图对应的那些子查询；
创建视图

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], …) ]

[COMMENT view_comment]

[TBLPROPERTIES (property_name = property_value, …)]

AS SELECT …;

例如：

CREATE VIEW IF NOT EXISTS v_lxw1234 (url COMMENT ‘url’)

COMMENT ‘view lxw1234′

AS SELECT url FROM lxw1234

WHERE url LIKE ‘http://%’

LIMIT 100;

删除视图

DROP VIEW IF EXISTS v_lxw1234;

修改视图

ALTER VIEW v_lxw1234 AS
SELECT url FROM lxw1234 limit 500;

更多关于Hive视图的介绍，请参考官方文档

Hive中的表分区

Hive中的表分区比较简单，就是将同一组数据放到同一个HDFS目录下，当查询中过滤条件指定了某一个分区值时候，只将该分区对应的目录作为Input，从而减少MapReduce的输入数据，提高查询效率。

创建分区表

CREATE EXTERNAL TABLE t_lxw1234 (

id INT,

ip STRING COMMENT ‘访问者IP’,

avg_view_depth DECIMAL(5,1),

bounce_rate DECIMAL(6,5)

) COMMENT ‘lxw的大数据田地-lxw1234.com’

PARTITIONED BY (month STRING, day STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ‘,’

STORED AS textfile;

在创建表时候，使用PARTITIONED BY关键字来指定该表为分区表，后面括号中指定了分区的字段和类型，分区字段可以有多个，在HDFS中对应多级目录。
比如，上面的表t_lxw1234分区month=’2015-06’,day=’2015-06-15’对应HDFS上的路径为：/user/hive/warehouse/default.db/t_lxw1234/month=2015-06/day=2015-06-15/，当查询中指定了month=’2015-06’ AND day=’2015-06-15’,MapReduce直接从该目录中读取数据，如果只指定了month=’2015-06’，那么MapReduce将/month=2015-06/下所有的子目录都作为Input。

添加分区
使用INSERT添加分区：
往分区中追加数据：

INSERT INTO TABLE t_lxw1234 PARTITION (month = ‘2015-06′,day = ‘2015-06-15′)
SELECT * FROM dual;

覆盖分区数据

INSERT overwrite TABLE t_lxw1234 PARTITION (month = ‘2015-06′,day = ‘2015-06-15′)
SELECT * FROM dual;

删除分区

ALTER TABLE t_lxw1234 DROP PARTITION (month = ‘2015-01’, day = ‘2015-01-25’);

删除一个分区，同内部表和外部表，如果该分区表为外部表，则分区对应的HDFS目录数据不会被删除。

动态分区
关系型数据库（如Oracle）中，对分区表Insert数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用Hive的动态分区，需要进行相应的配置。

先看一个应用场景，源表t_lxw1234的数据如下：

SELECT day,url FROM t_lxw1234;
2015-05-10      url1
2015-05-10      url2
2015-06-14      url1
2015-06-14      url2
2015-06-15      url1
2015-06-15      url2
……

目标表为：

CREATE TABLE t_lxw1234_partitioned (
url STRING
) PARTITIONED BY (month STRING,day STRING) 
stored AS textfile;

需求：将t_lxw1234中的数据按照时间(day)，插入到目标表t_lxw1234_partitioned的相应分区中。

如果按照之前介绍的往指定一个分区中Insert数据，那么这个需求很不容易实现。

这时候就需要使用动态分区来实现，使用动态分区需要注意设定以下参数：

hive.exec.dynamic.partition
默认值：false
是否开启动态分区功能，默认false关闭。使用动态分区时候，该参数必须设置成true;

hive.exec.dynamic.partition.mode
默认值：strict
动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。一般需要设置为nonstrict

hive.exec.max.dynamic.partitions.pernode
默认值：100
在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。

hive.exec.max.dynamic.partitions
默认值：1000
在所有执行MR的节点上，最大一共可以创建多少个动态分区。

hive.exec.max.created.files
默认值：100000
整个MR Job中，最大可以创建多少个HDFS文件。一般默认值足够了，除非你的数据量非常大，需要创建的文件数大于100000，可根据实际情况加以调整。

hive.error.on.empty.partition
默认值：false
当有空分区生成时，是否抛出异常。一般不需要设置。

那么，上面的需求可以使用如下的语句来完成：

SET hive.exec.dynamic.partition=true;  
SET hive.exec.dynamic.partition.mode=nonstrict; 
SET hive.exec.max.dynamic.partitions.pernode = 1000;
SET hive.exec.max.dynamic.partitions=1000;
 
INSERT overwrite TABLE t_lxw1234_partitioned PARTITION (month,day) 
SELECT url,substr(day,1,7) AS month,day 
FROM t_lxw1234;

注意：在PARTITION (month,day)中指定分区字段名即可；

在SELECT子句的最后两个字段，必须对应前面PARTITION (month,day)中指定的分区字段，包括顺序。

执行结果如下：

Loading data to table liuxiaowen.t_lxw1234_partitioned partition (month=null, day=null)

Loading partition {month=2015-05, day=2015-05-10}

Loading partition {month=2015-06, day=2015-06-14}

Loading partition {month=2015-06, day=2015-06-15}

Partition liuxiaowen.t_lxw1234_partitioned{month=2015-05, day=2015-05-10} stats: [numFiles=1, numRows=2, totalSize=10, rawDataSize=8]

Partition liuxiaowen.t_lxw1234_partitioned{month=2015-06, day=2015-06-14} stats: [numFiles=1, numRows=2, totalSize=10, rawDataSize=8]

Partition liuxiaowen.t_lxw1234_partitioned{month=2015-06, day=2015-06-15} stats: [numFiles=1, numRows=2, totalSize=10, rawDataSize=8]

使用show partitions t_lxw1234_partitioned;查看目标表有哪些分区：

hive> show partitions t_lxw1234_partitioned;

OK

month=2015-05/day=2015-05-10

month=2015-06/day=2015-06-14

month=2015-06/day=2015-06-15