HIVE数据抽样

HIVE数据抽样

一、随机抽样

ORDER BY RAND()

order by 是全局排序，可以实现随机抽样

SELECT*FROM t_student_info
ORDERBY RAND()LIMIT10;-- 按比例抽取SELECT*FROM(SELECT*,rand()as radix
      FROM t_student_info
) t
WHERE radix>=0.0AND radix<=0.0001;

SORT BY RAND()

sort by 提供了单个 reducer 内的排序功能，但不保证整体有序，这个时候其实不能做到真正的随机的，因为此时的随机是针对分区去的，所以如果我们可以通过控制进入每个分区的数据也是随机的话，那我们就可以做到随机了

SELECT*FROM t_student_info
SORT BY RAND()LIMIT10;

DISTRIBUTE BY RAND()

rand函数前的distribute和sort关键字可以保证数据在mapper和reducer阶段是随机分布的，这个时候我们也能做到真正的随机，前面我们也介绍过cluster by 其实基本上是和distribute by sort by 等价的

SELECT*FROM t_student_info
DISTRIBUTE BY RAND()
SORT BY RAND()LIMIT10;

CLUSTER BY RAND()

cluster by 的功能是 distribute by 和 sort by 的功能相结合，distribute by rand() sort by rand() 进行了两次随机，cluster by rand() 仅一次随机，所以速度上会比上一种方法快

SELECT*FROM t_student_info
CLUSTER BY RAND()LIMIT10;

二、分桶抽样(桶表抽样)

hive中分桶其实就是根据某一个字段Hash取模，放入指定数据的桶中，比如将表table按照ID分成100个桶，其算法是hash(id) % 100，这样，hash(id) % 100 = 0的数据被放到第一个桶中，hash(id) % 100 = 1的记录被放到第二个桶中。

分桶抽样语法： TABLESAMPLE (BUCKET x OUT OF y [ON colname])

其中x是要抽样的桶编号，桶编号从1开始，colname表示抽样的列(也就是按照那个字段分桶)，y表示桶的数量。 所以表达的意思是按照colname字段分成y桶，抽取其中的第x桶

SELECT*FROM t_student_info
TABLESAMPLE (BUCKET 1OUTOF100000ON rand());

三、数据块抽样

从 Hive 0.8 开始提供块抽样，使用 tablesample 抽取指定的 行数/比例/大小

-- 抽样1000行SELECT*FROM t_student_info 
TABLESAMPLE(1000ROWS);-- 获取20%的数据SELECT*FROM t_student_info 
TABLESAMPLE (20PERCENT);-- 抽取特定大小的数据SELECT*FROM t_student_info 
TABLESAMPLE(1M);

四、分层抽样

分层抽样，这里可以分为两种，一种是分层抽个数另外一种是分层抽比例

select*from(select
        id,ctime,
        row_number()over(partitionby id orderby rand())as rn
    from
        ods_user_log
) tmp
where rn<=3;