HiveSql抽样_胖胖大王叫我来巡山_hive sql 随机抽样

大大的周 02-07 8044

一. 分桶抽样：TABLESAMPLE 函数

二. 随机抽样：rand()函数

三. 按比例抽样

四. 分层抽样

1. 分层抽一定数量：row_number()

2. 分层抽一定比例

五. 注意：

一. 分桶抽样：TABLESAMPLE 函数

说明：TABLESAMPLE 将数据分成多个bucket，抽取其中一个bucket

语法：按照colname字段分成bucketNum个桶，抽取其中的第bucketId桶

-- bucketId：抽取的bucket的编号 -- bucketNum表示bucket的数量 -- 如果基于某列来分桶，colName就是该列的列名，如果要随机分桶，那么colName可以用rand()来代替 TABLESAMPLE (BUCKET bucketId OUT OF bucketNum [ON colName])

实际用法：

-- *** 1. 整体基于行数抽样：可实现随机抽样 -- 对每个split的数据都r条数据 select * from musics tablesample(2 rows) s; -- *** 2. 基于rand()抽样 -- 将musics表中数据随机分为5个bucket，然后抽取编号为2的bucket的数据 -- 因为是随机分的，所以每次执行的结果都不同 select * from musics tablesample(bucket 2 out of 5 on rand()) s; -- *** 3. 基于列名抽样 -- 基于singerid(歌手)列对musics表抽样，一个歌手一个bucket，这里抽取第一个bucket歌手的数据 -- 如果表在创建时已经使用cluster by分桶，而且tablesample指定的列正是用于分桶的列，那么在抽样时，可以只涉及到表相应的hash分区的数据，而无需扫描整张表。因为表中的数据已经按列的hash值分割到不同的分区。 select * from musics tablesample(bucket 1 out of 4 on singerid) s; -- *** 4. 基于百分比抽样 -- 根据数据数据块大小的百分比进行抽样 -- 抽样的最小单元是一个HDFS数据块。一般一个HDFS数据块大小为128M -- 如果表的数据少于一个HDFS数据块的大小，那么会返回所有的数 select * from musics tablesample(0.5 percent) s; -- *** 5. 基于数据大小抽样 -- 抽样数据大小为10M，但是因为表数据量过小，未达到一个HDFS块大小，会输出全部的记录 select * from musics tablesample(10M) s;

注意：tablesample不支持子查询和where，可以另外建中间表，或者用临时表with as

with tmp_a as ( select * from muscis where pk_month ='2021-08' ) select * from tmp_a tablesample (200 rows)

二. 随机抽样：rand()函数

说明：排序函数+rand()函数完成随机抽样

? ? ? ? ? ?其中rand() 返回一个0到1之间double 类型的随机值

? ? ? ? ? ? ? ? ? limit控制抽样返回的数据量

-- *** 1. order by rand() -- order by只会启用一个reduce，所以比较耗时 -- 因为order by 是全局的，所以可以做到随机抽样的目的 select * from ods_user_bucket_log order by rand() limit 10; -- *** 2. sort by rand() -- sort by 提供了单个 reducer 内的排序功能，但不保证整体有序，这个时候其实不能做到真正的随机的， -- 因为此时的随机是针对分区去的，所以如果我们可以通过控制进入每个分区的数据也是随机的话，那我们就可以做到随机了 select * from ods_user_bucket_log sort by rand() limit 10; -- *** 3. distribute by rand() sort by rand() -- rand函数前的distribute和sort关键字可以保证数据在mapper和reducer阶段是随机分布的， -- 这个时候我们也能做到真正的随机 select * from ods_user_bucket_log distribute by rand() sort by rand() limit 10; -- *** 4. cluster by rand() -- cluster by 的功能是 distribute by 和 sort by 的功能相结合 -- distribute by rand() sort by rand() 进行了两次随机，cluster by rand() 仅一次随机，所以速度上会比上一种方法快 select * from ods_user_bucket_log cluster by rand() limit 10;

三. 按比例抽样 -- 1. TABLESAMPLE -- 根据数据数据块大小的百分比进行抽样 -- 抽样的最小单元是一个HDFS数据块。一般一个HDFS数据块大小为128M -- 如果表的数据少于一个HDFS数据块的大小，那么会返回所有的数 select * from musics tablesample(0.5 percent) s; -- 2. rand() select * from ( select* ,rand() as radix from ods_user_bucket_log ) tmp where radix>=0.0 and radix<=0.0001;

四. 分层抽样 1. 分层抽一定数量：row_number() -- 这里对id分层抽样，每层抽取3个 select * from ( select id,ctime, row_number() over(partition by id order by rand()) as rn from ods_user_log ) tmp where rn<=3 2. 分层抽一定比例

同按比例抽样

五. 注意：

1. TABLESAMPLE 抽样函数本身是不走MR 的，所以执行速度很快(注意抽取多少M的时候，只能是整数M)

2. rand()需要走MR的，所以执行性能上没有TABLESAMPLE那么快，而且表达能力有限，只能获取特定的条数(limit n)

参考：

Hive 数据抽样的各种玩法(43)_mb5ffbc8f4a5a17的技术博客_51CTO博客

Hive 表格采样(table sample) @狐狸教程:~#

Hive 数据抽样的各种玩法(43)_mb5ffbc8f4a5a17的技术博客_51CTO博客