Hive_3. DDL -- 分区表 & 桶表 & 视图

Hive 分区表

默认情况下，Hive 的query 会查询整张表。当遇道大表是，查询性能会变得非常缓慢。你可以通过创建分区表来解决这个问题，Hive 中的分区表跟 RDBMS 中非常相似。

在 Hive 中，每个分区表都关联一个预分区的列来存储这张表在 HDFS 路径下的子目录。当查询分区表的时候，只会查询所需要的分区路径下的数据，这样使得 I/O 和 查询时间变得更加高效。当然，分区表的实现也相当简单，可以通过以下代码进行了解：

--创建表的时候建了分区 
jdbc:hive2://> CREATE TABLE employee_partitioned
. . . . . . .> (
. . . . . . .>   name string,
. . . . . . .>   work_place ARRAY<string>,
. . . . . . .>   sex_age STRUCT<sex:string,age:int>,
. . . . . . .>   skills_score MAP<string,int>,
. . . . . . .>   depart_title MAP<STRING,ARRAY<STRING>>
. . . . . . .> )
. . . . . . .> PARTITIONED BY (Year INT, Month INT)
. . . . . . .> ROW FORMAT DELIMITED
. . . . . . .> FIELDS TERMINATED BY '|'
. . . . . . .> COLLECTION ITEMS TERMINATED BY ','
. . . . . . .> MAP KEYS TERMINATED BY ':';
No rows affected (0.293 seconds)

--显示分区
jdbc:hive2://> SHOW PARTITIONS employee_partitioned;
+------------+
| partition  |
+------------+
+------------+
No rows selected (0.177 seconds)

从上面的结果可以看出：分区不会自动 enabled。 我们需要使用 ALTER TABLE ADD PARTITION 语句为一张表添加分区。 ADD PARTITION 会更改表的元数据，但是不会加载数据。如果分区位置不存在数据，查询就不会返回任何结果。如果你希望擅长分区表的数据和元数据，可以使用：ALTER TABLE DROP PARTITION 语句。如下所示：

--添加多个分区 
jdbc:hive2://> ALTER TABLE employee_partitioned ADD 
. . . . . . .> PARTITION (year=2014, month=11)        
. . . . . . .> PARTITION (year=2014, month=12);
No rows affected (0.248 seconds)

jdbc:hive2://> SHOW PARTITIONS employee_partitioned;
+---------------------+
|      partition      |
+---------------------+
| year=2014/month=11  |
| year=2014/month=12  |
+---------------------+
2 rows selected (0.108 seconds)

--删除分区
jdbc:hive2://> ALTER TABLE employee_partitioned
. . . . . . .> DROP IF EXISTS PARTITION (year=2014, month=11); 

jdbc:hive2://> SHOW PARTITIONS employee_partitioned;
+---------------------+
|      partition      |
+---------------------+
| year=2014/month=12  |
+---------------------+
1 row selected (0.107 seconds)

为了避免手动添加分区，我们就需要在创建表和加载数据的时候 能够实现动态分区插入，这部分内容将在后续进行总结。

为了加载或者覆盖分区表数据，我们可以使用 LOAD 或者 INSERT OVERWRITE 语句。它们之后针对特定的分区进行覆盖读写。尽管分区列是子目录名，我们依然可以通过SELECT or WHERE 语句来查询和指定返回的结果集。你可以通过如下步骤来加载数据到分区表：

将数据加载到分区中：　

jdbc:hive2://> LOAD DATA LOCAL INPATH
. . . . . . .> '/home/dayongd/Downloads/employee.txt' 
. . . . . . .> OVERWRITE INTO TABLE employee_partitioned
. . . . . . .> PARTITION (year=2014, month=12);
No rows affected (0.96 seconds)

检查数据是否加载：

jdbc:hive2://> SELECT name, year, month FROM employee_partitioned;
+----------+-------+--------+
|   name   | year  | month  |
+----------+-------+--------+
| Michael  | 2014  | 12     |
| Will     | 2014  | 12     |
| Shelley  | 2014  | 12     |
| Lucy     | 2014  | 12     |
+----------+-------+--------+
4 rows selected (37.451 seconds)

ALTER Table/ Partition 语句对文件格式，位置，防护功能，串联（concatenation ）都有相同的 Alter table 语法结构，参考如下：

ALTER TABLE table_name PARTITION partition_spec SET FILEFORMAT file_format;
ALTER TABLE table_name PARTITION partition_spec SET LOCATION 'full URI';
ALTER TABLE table_name PARTITION partition_spec ENABLE NO_DROP;
ALTER TABLE table_name PARTITION partition_spec ENABLE OFFLINE;
ALTER TABLE table_name PARTITION partition_spec DISABLE NO_DROP;
ALTER TABLE table_name PARTITION partition_spec DISABLE OFFLINE;
ALTER TABLE table_name PARTITION partition_spec CONCATENATE;

Hive 桶表

除了分区表，还可以使用桶表的技术来让集群数据集更容易管理和查询优化。与分区表不同的是，桶表对应着 HDFS 中文件的分割(segments). 例如：表是根据年和月进行的 top-level 分区。如果有进一步需求 要根据 employee_id 来做第三次分区, 这样会导致许多小的分区和目录。例如，我们使用employee_id  做为桶表employee_partitioned 的桶列。桶列会通过用户定义的数字被 Hash 到桶中，具有相同 employee_id 会存储在相同的桶(分割的文件)中。
通过使用桶表，Hive 可以更加方便和高效地使用抽样(这部分内容将在之后 《Hive_6. 数据聚合&抽样》 进行详细介绍)。这里我们将介绍一下如果去创建桶表：
By using buckets, Hive can easily and efficiently do sampling (seeChapter 6, Data Aggregation and Sampling) and map side joins (see Chapter 4, Data Selection and Scope). An example to create a bucket table is as follows:

--为桶表准备一个数据集和表
jdbc:hive2://> CREATE TABLE employee_id
. . . . . . .> (
. . . . . . .>   name string,
. . . . . . .>   employee_id int,
. . . . . . .>   work_place ARRAY<string>,
. . . . . . .>   sex_age STRUCT<sex:string,age:int>,
. . . . . . .>   skills_score MAP<string,int>,
. . . . . . .>   depart_title MAP<string,ARRAY<string>>
. . . . . . .> )
. . . . . . .> ROW FORMAT DELIMITED
. . . . . . .> FIELDS TERMINATED BY '|'
. . . . . . .> COLLECTION ITEMS TERMINATED BY ','
. . . . . . .> MAP KEYS TERMINATED BY ':';
No rows affected (0.101 seconds)

jdbc:hive2://> LOAD DATA LOCAL INPATH 
. . . . . . .> '/home/dayongd/Downloads/employee_id.txt' 
. . . . . . .> OVERWRITE INTO TABLE employee_id
No rows affected (0.112 seconds)

--创建桶表 
jdbc:hive2://> CREATE TABLE employee_id_buckets
. . . . . . .> (
. . . . . . .>   name string,
. . . . . . .>   employee_id int,
. . . . . . .>   work_place ARRAY<string>,
. . . . . . .>   sex_age STRUCT<sex:string,age:int>,
. . . . . . .>   skills_score MAP<string,int>,
. . . . . . .>   depart_title MAP<string,ARRAY<string >>
. . . . . . .> )
. . . . . . .> CLUSTERED BY (employee_id) INTO 2 BUCKETS
. . . . . . .> ROW FORMAT DELIMITED
. . . . . . .> FIELDS TERMINATED BY '|'
. . . . . . .> COLLECTION ITEMS TERMINATED BY ','
. . . . . . .> MAP KEYS TERMINATED BY ':';
No rows affected (0.104 seconds)

Note:桶数 [Bucket numbers]

在定义合适的桶数时，我们需要避免每个桶拥有太多/少的数据。最好的选择是使用 2 个左右的桶来存储数据。例如，我们计划往每个桶放512MB 数据，如果 Hadoop Blcok 的大小是256MB，如果有可能的话，使用 2N 作为桶数。

桶表跟底层的数据加载密切相关。为了正确加载数据到桶表中，我们可以设置最大的 reducer 数量跟创建表时指定的桶数相同(例如：2)，或者强制允许使用桶表[enforce.bucketing]。如下所述：

jdbc:hive2://> set map.reduce.tasks = 2;
No rows affected (0.026 seconds)

jdbc:hive2://> set hive.enforce.bucketing = true;
No rows affected (0.002 seconds)

为了将数据加载到桶表中，我们不能使用LOAD 关键字，因为 LOAD 不会验证加载数据的元数据。我们可以使用 INSERT 语句来为桶表加载数据：

jdbc:hive2://> INSERT OVERWRITE TABLE employee_id_buckets 
. . . . . . .> SELECT * FROM employee_id;
No rows affected (75.468 seconds)

--验证桶表是否在 HDFS 中 
-bash-4.1$ hdfs dfs -ls /user/hive/warehouse/employee_id_buckets
Found 2 items
-rwxrwxrwx   1 hive hive        900 2014-11-02 10:54 /user/hive/warehouse/employee_id_buckets/000000_0
-rwxrwxrwx   1 hive hive        582 2014-11-02 10:54 /user/hive/warehouse/employee_id_buckets/000001_0

Hive 视图

在 Hive 中，视图是逻辑数据结构，可以通过隐藏复杂数据操作（Joins, 子查询, 过滤,数据扁平化）来于简化查询操作  。

与关系数据库不同的是， Hive视图并不存储数据或者实例化。一旦创建 HIve 视图，它的 schema 也会立刻确定下来。对底层表后续的更改(如 增加新列)并不会影响视图的 schema。如果底层表被删除或者改变，之后对视图的查询将会 failed。基于以上 Hive view 的特性，我们再ETL 和 数据仓库中对于经常变化的表应慎重使用视图。
如下所示：

jdbc:hive2://> CREATE VIEW employee_skills
. . . . . . .> AS
. . . . . . .> SELECT name, skills_score['DB'] AS DB,
. . . . . . .> skills_score['Perl'] AS Perl, 
. . . . . . .> skills_score['Python'] AS Python,
. . . . . . .> skills_score['Sales'] as Sales, 
. . . . . . .> skills_score['HR'] as HR 
. . . . . . .> FROM employee;
No rows affected (0.253 seconds)

创建视图的时候是不会触发 MapReduce 的 Job，因为只存在元数据的改变。但是，当对视图进行查询的时候依然会触发一个 MapReduce Job 进程：SHOW CREATE TABLE 或者 DESC FORMATTED TABLE 语句来显示通过  CREATE VIEW  语句创建的视图。以下是对Hive 视图的 DDL操作：

更改视图的属性： 

jdbc:hive2://> ALTER VIEW employee_skills 
. . . . . . .> SET TBLPROPERTIES ('comment' = 'This is a view');
No rows affected (0.19 seconds)

重新定义视图： 

jdbc:hive2://> ALTER VIEW employee_skills AS 
. . . . . . .> SELECT * from employee ;
No rows affected (0.17 seconds)

删除视图： 

jdbc:hive2://> DROP VIEW employee_skills; 
No rows affected (0.156 seconds)