HIVE四种排序order by、sort by、distribute by sort by、cluster by及随机抽取数据的方法

一、四种排序

1.order by 全局排序

select * from ${table_name} order by id 
--默认升序 可以通过desc使降序

order by只会启用一个reduce所以比较耗时,因此order by 是全局的。

2.sort by 局部排序

select * from ${table_name} sort by id 
--默认升序 可以通过desc使降序

局部排序，再每个reducer中是有序的，当设置reducer的个数=1时，全局排序=order by。

3.distribute by sort by 指定分区方式局部排序

select * from ${table_name} distribute by dt sort by id
--默认升序 可以通过desc使降序

指定分区排序，如案例按照dt分区，相同dt的数据可以分到同一分区，在此分区下是有序的

4.cluster by 指定分区方式局部排序

select * from ${table_name} cluster id
--默认升序 不可用desc降序

当distribute by id sort by id 是相同字段时，可用cluster by 。按照id分区后再排序。会比distribute by id sort by id要快

二、随机抽取数据

1.使用排序函数和rand() 函数来完成随机抽样

select * from table_name order by rand() limit 200

order by是全局排序，因此此方法是完全随机的

select * from table_name sort by rand() limit 200

sort by是reduce局部排序，因此此方法不是完全随机的

select * from table_name distribute by rand() sort by rand() limit 200

完全的随机

select * from table_name cluster by rand() limit 200

cluster by是完全随机的，且效率比distribute by rand() sort by rand()快，因为只做了一次随机。

使用tablesample()抽样函数完成抽样

select * from table_name tablesample(100 rows)

抽取指定的行数

select * from table_name tablesample(20 percent)

抽取指定的比例

select * from table_name tablesample(1M)

抽取指定的大小（只能为整数大小）

分组抽样

select * 
	from
	 	(select *,
		row_number() over(partition by ${partiton_col} order by rand()) as rn
		from ${table_name})
	where rn<=10

使用窗口函数，按照分组字段分组后，每个分组随机排序。抽取前十条

select * 
	from
	 	(select *,
		cume_dist() over(partition by ${partiton_col} order by rand()) as pre
		from ${table_name})
	where pre<=0.2

使用窗口函数，按照分组字段分组后，每个分组随机排序。抽取前20%