使用Maxcomputer SQL对数据进行抽样

一、概述

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

二、构造数据

表tb有如下数据

with tb as(select    id,    det
fromvalues(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 orderby rand()limit5;id  det
1 a
4 d
22  v
13  m
11  k

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

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

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

select*from tb orderby rand(unix_timestamp())limit5;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()limit5;id  det
9 i
7 g
24  x
17  q
16  p

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

3.cluster by rand()

select*from tb cluster by rand()limit5;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 5on rand());

2.基于字段列名抽样

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

4.分层抽样(ODPS/Hive)

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

1.row_number()

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

2.ntile()

--针对每个id组，对组内分5份数据，取每组第2份数据select*from(select    id,    det,    ntile(5) over(partition by id orderby 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 orderby rand()) cs
from tb
) tmp
where cs =true;

四、总结

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

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

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

3.借助row_number实现分层抽样