欢迎您访问 最编程 本站为您分享编程语言代码，编程技术文章！

热门搜索/Hot Search

您现在的位置是： 首页

使用 Maxcomputer SQL 提取数据样本

最编程 2024-07-15 13:16:48
...

一、概述

在Hive中有三种方式可以进行对数据抽样：随机抽样、数据块抽样、分桶抽样、分层抽样。现在我们逐一看下在MaxComputer中如何使用

二、构造数据

表tb有如下数据

with tb as (
  select 
    id,
    det
  from values(1,'a'),
             (2,'b'),
             (3,'c'),
             (4,'d'),
             (5,'e'),
             (6,'f'),
             (7,'g'),
             (8,'h'),
             (9,'i'),
             (10,'j'),
             (11,'k'),
             (12,'l'),
             (13,'m'),
             (14,'n'),
             (15,'o'),
             (16,'p'),
             (17,'q'),
             (18,'r'),
             (19,'s'),
             (20,'t'),
             (21,'u'),
             (22,'v'),
             (23,'w'),
             (24,'x'),
             (25,'y'),
             (26,'z')
             t(id,det)
)

三、数据抽样

1.随机抽样(ODPS)

MaxComputer中有个随机函数rand()，可以通过rand()函数对数据进行抽样；

MaxComputer中cluster_sample()函数随机抽取N条数据或按比例抽取

1.order by rand()

select * from tb order by rand() limit 5;
id  det
1 a
4 d
22  v
13  m
11  k

运行几次之后发现结果都一样，好像没有达到随机取数的效果，从sql来理解，上面的语句相当于生成了一个随机序列，每条记录都分配了一个随机值，然后所有记录按照随机值排序，从排序结果中取前5条，以此达到随机选择的目的。

   然后rand()方法产生随机数需要使用随机数种子，相同随机数种子产生的随机数永远都是一样的。当不传递随机数种子时，函数将使用默认的固定随机数种子，所以无论运行多少次，产生的随机数都是固定的(可以通过seed确定随机数序列，seed确定后，即可固定该函数的返回结果。执行环境是一样的情况下，seed取值不变的话，返回值是一样的，如果需要返回不一样的结果，需要修改seed取值)

解决这个问题，我们传入随时变化的seed即可

select * from tb order by rand(unix_timestamp()) limit 5;
id  det
22  v
3 c
1 a
19  s
13  m

2.distribute by rand() sort by rand()

其中rand函数前的distribute和sort关键字可以保证数据在mapper和reducer阶段是随机分布的。

select * from tb distribute by rand() sort by rand() limit 5;
id  det
9 i
7 g
24  x
17  q
16  p

同样如果想要每次运行随机结果都不一样，rand()函数里加上随机值seed

3.cluster by rand()

select * from tb cluster by rand() limit 5;
id  det
9 i
1 a
15  o
13  m
6 f

如果想要每次运行随机结果都不一样，rand()函数里加上随机值seed。

这里插一个知识点：order by、sort by、 distribute by和 cluster by区别：

order by主要是做全局排序。只要hive的sql中指定了order by，那么所有的数据都会到同一个reducer进行处理，不管有多少map，也不管文件有多少的block，只会启动一个reducer 。但是对于大量数据这将会消耗很长的时间去执行。

sort by是局部排序，每个reduce端都会进行排序，也就是局部有序，可以指定多个reduce。distribute by的功能是控制map结果的分发，它会将具有相同字段的map输出分发到一个reduce节点上做处理。某种情况下，我们需要控制某个特定行到某个reducer中，这种操作一般是为后续可能发生的聚集操作做准备。通常和sort by一起用，distribute by必须要写在sort by之前，理解成：按照XX字段分区，再按照XX字段排序。        当distribute by和sorts by字段相同时，可以使用cluster by方式代替。cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能像distribute by一样去指定排序的规则为ASC或者DESC。

4.cluster_sample()

用户随机抽样。返回True表示该行数据被抽中。

  • 命令说明
  • cluster_sample(bigint <N>)：表示随机抽取N条数据。
  • cluster_sample(bigint <N>, bigint <M>)：表示按比例（M/N）随机抽取。即抽取partition_row_count×M / N条数据。partition_row_count指分区中的数据行数。
  • 参数说明
  • N：必填。BIGINT类型常量。N为NULL时，返回值为NULL。
  • M：必填。BIGINT类型常量。M为NULL时，返回值为NULL。
  • partition_clause：可选。
--随机抽取N条数据
select 
  id,
  det
from (
  select 
    id,
    det,
    cluster_sample(5) over() flag
  from tb
) tmp
where flag = true;

id  det
4   d
7   g
9   i
12  l
13  m

--按比例抽取 eg:20%
select 
  id,
  det
from (
  select 
    id,
    det,
    cluster_sample(5,1) over() flag
  from tb
) tmp
where flag = true;

id  det
3   c
11  k
20  t
22  v
26  z

2.数据块抽样(Hive)

tablesample语法MaxComouter不支持，这里介绍在hive中使用

注意：测试过程中发现，select语句不能带where条件且不支持子查询，可通过新建中间表或使用随机抽样解决(tablesample不支持子查询和where，可以另外建中间表，或者用临时表with as)

1.基于比例抽样

抽样的最小单元是一个HDFS数据块，一般一个HDFS数据块大小为128M

如果表的数据少于一个HDFS数据块的大小，那么会返回所有的数

--根据hive表数据的大小按比例抽取数据，例如：取10%。
select * from tb tablesample(10 percent);

2.基于抽样数据的大小

如果抽样数据的大小N(tablesample(N))未达到一个HDFS块大小，会输出全部的记录

--单位为M，例如取10M大小数据。
select * from tb tablesample(10M);

3.基于抽样数据的行数

tablesample(n rows) 指定抽样数据的行数，其中n代表每个map任务均取n行数据；对每个split的数据都n条数据

--其中n代表每个map任务均取n行数据，例如取10行数据
select * from tb tablesample(10 rows);

3.分桶抽样(Hive)

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

1.基于随机抽样

--将tb表中的数据随机分为5个bucket，然后抽取编号为1的bucket的数据
select * from tb tablesample(bucket 1 out of 5 on rand());

2.基于字段列名抽样

--基于id列对tb表抽样，一个id一个bucket，这里抽取第一个bucket id的数据
-- 如果表在创建时已经使用cluster by分桶，而且tablesample指定的列正是用于分桶的列，那么在抽样时，可以只涉及到表相应的hash分区的数据，而无需扫描整张表。因为表中的数据已经按列的hash值分割到不同的分区。
select * from tb tablesample(bucket 1 out of 5 on id);

4.分层抽样(ODPS/Hive)

分层抽一定数量可以使用开窗函数

1.row_number()

--针对每个id组，取组内3条数据
select 
  *
from (
  select 
    id,
    det,
    row_number() over(partition by id order by rand()) rn
  from tb
) tmp
where rn <= 5;

2.ntile()

--针对每个id组，对组内分5份数据，取每组第2份数据
select 
  *
from (
  select 
    id,
    det,
    ntile(5) over(partition by id order by rand()) nt
  from tb
) tmp
where nt = 2;

3.cluster_sample() 

--针对每个id组，取每组5条数据
select 
  *
from (
  select 
    id,
    det,
    cluster_sample(5) over(partition by id order by rand()) cs
  from tb
) tmp
where cs = true;

四、总结

以上就是关于抽样数据的使用介绍，当然实现的方式有

上一篇： 编织数据，重构数据管理新模式

下一篇： 常见的 mysql 监控指标

推荐阅读