|
|
|
|
公众号矩阵

Hive进阶—抽样的各种玩法

我们一般情况下是使用排序函数和rand() 函数来完成随机抽样,limit关键字限制抽样返回的数据,不同之处再有我们使用哪个排序函数呢?

作者:刘不二 来源:Java大数据与数据仓库|2021-01-14 05:21

本文转载自微信公众号「Java大数据与数据仓库」,作者刘不二。转载本文请联系Java大数据与数据仓库公众号。 

抽样

抽样在Hive 中也是比较常用的一种手段,主要用在下面的几个场景中

  1. 一些机器学习的场景中,数仓作为数据的提供方提供样本数据
  2. 数据的计算结果异常或者是指标异常,这个时候如果我们往往需要确认数据源的数据是否本身就有异常
  3. SQL的性能有问题的时候我们也会使用抽样的方法区查看数据,然后进行SQL调优
  4. 在大规模数据量的数据分析及建模任务中,往往针对全量数据进行挖掘分析时会十分耗时和占用集群资源,因此一般情况下只需要抽取一小部分数据进行分析及建模操作。

随机抽样(rand()函数)

我们一般情况下是使用排序函数和rand() 函数来完成随机抽样,limit关键字限制抽样返回的数据,不同之处再有我们使用哪个排序函数呢

利用 rand() 函数进行抽取,这是因为rand() 返回一个0到1之间double 类型的随机值。

下面我们用到了前面我们使用过的一张表大概4603089 条记录,这里我就不给大家准备数据了,大家可以看Hive进阶之数据存储格式来获取测试数据

  1. create table ods_user_bucket_log( 
  2.      id int
  3.      name string, 
  4.      city string, 
  5.      phone string, 
  6.      acctime string) 
  7. CLUSTERED BY (`id` ) INTO 5 BUCKETS  
  8. row format delimited fields terminated by '\t' 
  9. stored as textfile; 
  10. insert overwrite table ods_user_bucket_log select * from ods_user_log; 

order by rand()

order by只会启用一个reduce所以比较耗时,至于为什么我们在前面的文章中解释过了Hive语法之常见排序方式

因为order by 是全局的,所以可以做到随机抽样的目的

  1. select * from ods_user_bucket_log order by rand() limit 10; 

sort by rand()

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

  1. select * from ods_user_bucket_log sort by rand() limit 10; 

distribute by rand() sort by rand()

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

  1. select * from ods_user_bucket_log distribute by rand() sort by rand() limit 10; 

cluster by rand()

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

  1. select * from ods_user_bucket_log cluster by rand() limit 10; 

tablesample()抽样函数

分桶抽样(桶表抽样)

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桶

  1. SELECT 
  2.     * 
  3. FROM 
  4.     ods_user_bucket_log 
  5. TABLESAMPLE (BUCKET 1 OUT OF 100000 ON rand()) ; 

数据块抽样

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

  1. SELECT * FROM ods_user_data TABLESAMPLE(1000 ROWS); 
  2. SELECT * FROM ods_user_data TABLESAMPLE (20 PERCENT);  
  3. SELECT * FROM ods_user_data TABLESAMPLE(1M);  

按比例抽样 ABLESAMPLE (20 PERCENT)

这将允许 Hive 至少获取 n%的数据

  1. SELECT 
  2.     * 
  3. FROM 
  4.     ods_user_bucket_log 
  5. TABLESAMPLE(0.0001 PERCENT); 

抽取特定大小的数据TABLESAMPLE(100M)

  1. SELECT 
  2.     * 
  3. FROM 
  4.     ods_user_bucket_log 
  5. TABLESAMPLE(1M); 

需要注意的是这里必须是整数M ,以为我尝试零点几的时候报错了

抽取特定的行数 TABLESAMPLE(10 ROWS)

  1. SELECT 
  2.     * 
  3. FROM 
  4.     ods_user_bucket_log 
  5. TABLESAMPLE(10 rows); 

扩展

随机抽样如何实现按比例抽样

前面我们介绍了TABLESAMPLE 可以实现按比例抽样,随机抽样可以借助limit 可以实现抽取特定记录数,其实我们如果对随机抽样进行改进也可以实现按照比例抽样,因为rand() 的函数值是随机的,所以我们可以对其返回值做条件过滤从而实现按照比例的抽样

  1. select 
  2.     * 
  3. from
  4.     select 
  5.         * ,rand() as radix 
  6.     from 
  7.         ods_user_bucket_log 
  8. ) tmp 
  9. where 
  10.     radix>=0.0 
  11.     and radix<=0.0001 

分层抽样(分组抽样)

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

分层抽个数

  1. select 
  2.     * 
  3. from ( 
  4.     select 
  5.         id,ctime, 
  6.         row_number() over(partition by id order by rand() ) as rn 
  7.     from 
  8.         ods_user_log 
  9. ) tmp 
  10. where rn<=3 

分层按比例的抽样,也可以按照上面的方式实现

总结

  1. TABLESAMPLE 抽样函数本身是不走MR 的所以执行速度很快(注意抽取多少M的时候,只能是整数M)
  2. 随机抽样函数需要走MR的,所以执行性能上没有TABLESAMPLE那么快,而且表达能力有限,只能获取特定的条数(limit n)
  3. 借助row_number实现分层抽样

【编辑推荐】

  1. 数据仓库工具方法-维度表同步 MySQL表结构同步到Hive
  2. [老汤]Spark 2.x实战应用系列八之Spark SQL with Hive
  3. Hive 中的四种排序详解,再也不会混淆用法了
  4. Hive窗口函数Row Number的用法, 你肯定都会吧!
  5. Hive在大数据分析和大数据仓库中应用实战
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

数据湖与数据仓库的分析实践攻略

数据湖与数据仓库的分析实践攻略

助力现代化数据管理:数据湖与数据仓库的分析实践攻略
共3章 | 创世达人

3人订阅学习

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

30人订阅学习

数据中心和VPDN网络建设案例

数据中心和VPDN网络建设案例

漫画+案例
共20章 | 捷哥CCIE

209人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微