hive中的table和partition可以通过clustered by进一步分bucket,内部通过sorted by进行排序。
hive> select * from new_test;
OK
1 20 qiu 20140101
2 43 liu 20140101
3 23 zheng 20140101
4 32 yang 20140101
5 24 qian 20140101
Time taken: 0.106 seconds
hive> create table student(
> id int,
> age int,
> name string
> )partitioned by (dt string)
> clustered by(id) sorted by(age) into 3 buckets
> row format delimited fields terminated by ',' lines terminated by '\n';
OK
Time taken: 0.348 seconds
from new_test
insert overwrite table student partition(dt='20140102')
select id,age,name where datekey='20140101' sort by age;
查看buckets。
[root@hadoop00 /]# hadoop fs -ls /user/hive/warehous/hbmsdb.db/student/dt=20140102
Found 3 items
-rw-r--r-- 3 root supergroup 11 2014-03-04 15:59 /user/hive/warehous/hbmsdb.db/student/dt=20140102/000000_0
-rw-r--r-- 3 root supergroup 19 2014-03-04 15:59 /user/hive/warehous/hbmsdb.db/student/dt=20140102/000001_0
-rw-r--r-- 3 root supergroup 19 2014-03-04 16:00 /user/hive/warehous/hbmsdb.db/student/dt=20140102/000002_0
[root@hadoop00 /]# hadoop fs -cat /user/hive/warehous/hbmsdb.db/student/dt=20140102/000000_0
3,23,zheng
[root@hadoop00 /]# hadoop fs -cat /user/hive/warehous/hbmsdb.db/student/dt=20140102/000001_0
1,20,qiu
4,32,yang
[root@hadoop00 /]# hadoop fs -cat /user/hive/warehous/hbmsdb.db/student/dt=20140102/000002_0
5,24,qian 2,43,liu
读取相应的sampling数据
hive> select * from student tablesample(bucket 2 out of 3 on id);
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201403041248_0004, Tracking URL = http://hadoop00:50030/jobdetails.jsp?jobid=job_201403041248_0004
Kill Command = /warehouse/hadoop/libexec/../bin/hadoop job -Dmapred.job.tracker=http://hadoop00:9001 -kill job_201403041248_0004
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2014-03-04 16:16:45,583 Stage-1 map = 0%, reduce = 0%
2014-03-04 16:16:51,616 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.23 sec
2014-03-04 16:16:52,623 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.23 sec
2014-03-04 16:16:53,630 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.23 sec
2014-03-04 16:16:54,636 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.23 sec
2014-03-04 16:16:55,643 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.23 sec
2014-03-04 16:16:56,650 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.23 sec
2014-03-04 16:16:57,657 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 1.23 sec
MapReduce Total cumulative CPU time: 1 seconds 230 msec
Ended Job = job_201403041248_0004
MapReduce Jobs Launched:
Job 0: Map: 1 Cumulative CPU: 1.23 sec HDFS Read: 251 HDFS Write: 37 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 230 msec
OK
1 20 qiu 20140102
4 32 yang 20140102
Time taken: 19.554 seconds
tablesample是抽样语句,语法:TABLESAMPLE(BUCKET x OUT OF y)y必须是table总bucket数的倍数或者因子。hive根据y的大小,决定抽样的比例。例如,table总共分了64份,当y=32时,抽取(64/32=)2个bucket的数据,当y=128时,抽取(64/128=)1/2个bucket的数据。x表示从哪个bucket开始抽取。例如,table总bucket数为32,tablesample(bucket 3 out of 16),表示总共抽取(32/16=)2个bucket的数据,分别为第3个bucket和第(3+16=)19个bucket的数据。