hive中表分區(qū)

Hive中的表分區(qū)比較簡單，就是將同一組數(shù)據(jù)放到同一個HDFS目錄下，當(dāng)查詢中過濾條件指定了某一個分區(qū)值時候，只將該分區(qū)對應(yīng)的目錄作為Input，從而減少MapReduce的輸入數(shù)據(jù)，提高查詢效率。

CREATE EXTERNAL TABLE t_lxw1234 (

id INT,

ip STRING COMMENT ‘訪問者IP’,

avg_view_depth DECIMAL(5,1),

bounce_rate DECIMAL(6,5)

) COMMENT ‘lxw的大數(shù)據(jù)田地-lxw1234.com’

PARTITIONED BY (month STRING, day STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ‘,’

STORED AS textfile;

• 在創(chuàng)建表時候，使用PARTITIONED BY關(guān)鍵字來指定該表為分區(qū)表，后面括號中指定了分區(qū)的字段和類型，分區(qū)字段可以有多個，在HDFS中對應(yīng)多級目錄。
• 比如，上面的表t_lxw1234分區(qū)month=’2015-06’,day=’2015-06-15’對應(yīng)HDFS上的路徑為：/user/hive/warehouse/default.db/t_lxw1234/month=2015-06/day=2015-06-15/，當(dāng)查詢中指定了month=’2015-06’ AND day=’2015-06-15’,MapReduce直接從該目錄中讀取數(shù)據(jù)，如果只指定了month=’2015-06’，那么MapReduce將/month=2015-06/下所有的子目錄都作為Input。

添加分區(qū)

• 使用INSERT添加分區(qū)：

往分區(qū)中追加數(shù)據(jù)：

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

覆蓋分區(qū)數(shù)據(jù)：

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

• 使用ALTER TABLE添加分區(qū)：

ALTER TABLE t_lxw1234 ADD PARTITION (month = ‘2015-06′,day = ‘2015-06-15′) location ‘hdfs://namenode/tmp/lxw1234/month=2015-06/day=2015-06-15/';

查看分區(qū)對應(yīng)的HDFS路徑

• 使用命令 show partitions t_lxw1234; 查看表的所有分區(qū)：

hive> show partitions t_lxw1234;
OK
month=2015-01/day=2015-01-25
month=2015-01/day=2015-01-31
month=2015-02/day=2015-02-15
month=2015-02/day=2015-02-28
month=2015-03/day=2015-03-15
month=2015-03/day=2015-03-31

• 使用desc formatted t_lxw1234 partition (month = ‘2015-01’ , day = ‘2015-01-25′);

查看該分區(qū)的詳細(xì)信息，包括該分區(qū)在HDFS上的路徑：

Location:               hdfs://namenode/user/hive/warehouse/default.db/t_lxw1234/month=2015-01/day=2015-01-25/

刪除分區(qū)

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

刪除一個分區(qū)；同內(nèi)部表和外部表，如果該分區(qū)表為外部表，則分區(qū)對應(yīng)的HDFS目錄數(shù)據(jù)不會被刪除。

hive動態(tài)分區(qū)

關(guān)系型數(shù)據(jù)庫（如Oracle）中，對分區(qū)表Insert數(shù)據(jù)時候，數(shù)據(jù)庫自動會根據(jù)分區(qū)字段的值，將數(shù)據(jù)插入到相應(yīng)的分區(qū)中，Hive中也提供了類似的機(jī)制，即動態(tài)分區(qū)(Dynamic Partition)，只不過，使用Hive的動態(tài)分區(qū)，需要進(jìn)行相應(yīng)的配置。

先看一個應(yīng)用場景，源表t_lxw1234的數(shù)據(jù)如下：

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

目標(biāo)表

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

需求：將t_lxw1234中的數(shù)據(jù)按照時間(day)，插入到目標(biāo)表t_lxw1234_partitioned的相應(yīng)分區(qū)中。

如果按照之前介紹的往指定一個分區(qū)中Insert數(shù)據(jù)，那么這個需求很不容易實現(xiàn)。

這時候就需要使用動態(tài)分區(qū)來實現(xiàn)，使用動態(tài)分區(qū)需要注意設(shè)定以下參數(shù)：

• hive.exec.dynamic.partition

默認(rèn)值：false

是否開啟動態(tài)分區(qū)功能，默認(rèn)false關(guān)閉。

使用動態(tài)分區(qū)時候，該參數(shù)必須設(shè)置成true;

• hive.exec.dynamic.partition.mode

默認(rèn)值：strict

動態(tài)分區(qū)的模式，默認(rèn)strict，表示必須指定至少一個分區(qū)為靜態(tài)分區(qū)，nonstrict模式表示允許所有的分區(qū)字段都可以使用動態(tài)分區(qū)。

一般需要設(shè)置為nonstrict

• hive.exec.max.dynamic.partitions.pernode

默認(rèn)值：100

在每個執(zhí)行MR的節(jié)點上，最大可以創(chuàng)建多少個動態(tài)分區(qū)。

該參數(shù)需要根據(jù)實際的數(shù)據(jù)來設(shè)定。

比如：源數(shù)據(jù)中包含了一年的數(shù)據(jù)，即day字段有365個值，那么該參數(shù)就需要設(shè)置成大于365，如果使用默認(rèn)值100，則會報錯。

• hive.exec.max.dynamic.partitions

默認(rèn)值：1000

在所有執(zhí)行MR的節(jié)點上，最大一共可以創(chuàng)建多少個動態(tài)分區(qū)。

同上參數(shù)解釋。

• hive.exec.max.created.files

默認(rèn)值：100000

整個MR Job中，最大可以創(chuàng)建多少個HDFS文件。

一般默認(rèn)值足夠了，除非你的數(shù)據(jù)量非常大，需要創(chuàng)建的文件數(shù)大于100000，可根據(jù)實際情況加以調(diào)整。

• hive.error.on.empty.partition

默認(rèn)值：false

當(dāng)有空分區(qū)生成時，是否拋出異常。

一般不需要設(shè)置。

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)中指定分區(qū)字段名即可；

在SELECT子句的最后兩個字段，必須對應(yīng)前面PARTITION (month,day)中指定的分區(qū)字段，包括順序。

執(zhí)行結(jié)果如下：

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}