分区表

create  table IF NOT EXISTS p (//创建分区表
id int,
dt string,
other string
)  
PARTITIONED BY(year string)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' ;
hive> desc p; //查看表结构
OK
id                      int                                         
dt                      string                                      
other                   string                                      
year                    string                                      
                 
# Partition Information          
# col_name              data_type               comment             
                 
year                    string                                      
Time taken: 0.588 seconds, Fetched: 9 row(s)

分区字段不能在创建表时写在字段列表里，但创建完成后会放在列的最后面，像正常列一样用。

创建一些数据：

1,'20150209','aaa','2015'
2,'20150712','bbb','2015'
3,'20140921','ccd','2014'
4,'20160518','ddd','2016'

加载数据：

hive> load data local inpath 'p.txt' into table p partitions(year='2015');

然后看hdfs上的文件：

drwxrwxr-x   - root supergroup          0 2016-10-11 18:11 /user/hive/warehouse/p/year=2015

发现不管是2014 2015 2015都到了2015这个分区，也就是说，hive在load数据时不检查分区情况。

现在查询一下：

    > select * from p where year='2014';
OK
Time taken: 5.409 seconds
hive> select * from p where year='2015';
OK
1       20150209        aaa     2015
2       20150712        bbb     2015
3       20140921        ccd     2015
4       20160518        ddd     2015
Time taken: 0.387 seconds, Fetched: 4 row(s)
hive> select * from p;
OK
1       20150209        aaa     2015
2       20150712        bbb     2015
3       20140921        ccd     2015
4       20160518        ddd     2015
Time taken: 0.258 seconds, Fetched: 4 row(s)

结果令人惊讶！！！ 如果load数据时指定分区，则hive会将分区表强制转换为load时的数值。

2.动态分区：

hive> insert into p select * from t4;
FAILED: SemanticException 1:12 Need to specify partition columns because the destination table is partitioned. Error encountered near token 'p'

把数据从非分区表导入到分区表，如果不指定分区，则数据插入报错。这时候要使用动态分区，相当于oracle里的分区。默认是不使用动态分区的。参考：

http://www.cnblogs.com/1130136248wlxk/articles/5515304.html

1. SELECT day,url FROM t_lxw1234;
2. 2015-05-10 url1
3. 2015-05-10 url2
4. 2015-06-14 url1
5. 2015-06-14 url2
6. 2015-06-15 url1
7. 2015-06-15 url2
8. ……
9.  

 

目标表为：

2. CREATE TABLE t_lxw1234_partitioned (
3. url STRING
4. ) PARTITIONED BY (month STRING,day STRING)
5. stored AS textfile;
6.  

需求：将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

当有空分区生成时，是否抛出异常。

一般不需要设置。

 

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

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

注意：在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

测试：

INSERT OVERWRITE TABLE p partition(year) select * from t4;//必须在目标表加上partition(分区字段)

执行成功。

网上说不要使用动态分区，因为会排序：

如果分区是可以确定的话，千万不要用动态分区，动态分区的值是在reduce运行阶段确定的.也就是会把所有的记录distribute by。 可想而知表记录非常大的话，只有一个reduce 去处理，那简直是疯狂的。如果这个值唯一或者事先已经知道，比如按天分区(i_date=20140819) 那就用静态分区吧。静态分区在编译阶段已经确定，不需要reduce处理。

只有一篇文章这样写，故不能确定正确性。因此先认为动态分区没有问题。

总结：

1.hive的静态分区更像是一个数据标签，hive本身不考虑分区逻辑是否正确，只按标签处理。

2.hive的动态分区跟oracle类似，但必须在插入数据时标明分区字段，这样一来也可以插入数据时修改分区字段甚至使用不同的分区字段。

来自为知笔记(Wiz)