数仓之TABLESAMPLE采样

前言

在日常工作中，我们会对表中记录随机抽样然后探查，如何进行抽样就是本章要讲的重点。数仓中的抽样方法有很多，这里主要介绍一下：随机抽样、块抽样、桶抽样、分组抽样这四种，话不多说，直接上干货。

随机抽样

随机抽样就是给每行数据赋值一个随机数 ，排序之后进行抽样，主要分为：

• order by，直接全局排序。
• distribute by + sort by，先分组再排序。

orderBy

select *
  from userinfo 
 where pt = '${yyyymmdd}'
 order by `column` -- 全局排序 
 limit 100;

sortBy

select *
  from userinfo 
 where pt = '${yyyymmdd}'
 distribute by `column1` -- 按照 某字段 分组
 sort by `column2`       -- 组内按照某字段 排序
 limit 100;

桶采样

• TABLESAMPLE (BUCKET x OUT OF y [ON <col_name> | rand()])
  • x，y：必填。将源表中的数据划分为y个桶，取其中的第x个桶，桶从1开始编号。
  • col_name：分桶列名即要进行采样的列名。当表不是聚簇表时，col_name与rand()函数必须二选一，当使用rand()函数时表示对输入的数据随机进行分桶。ON语句中最多支持指定10个列。

现在有一张分区用户表 usr_info ，总用户量 1700W 左右，想从中抽取 10% 的用户进行分析，具体代码如下：

select count(1)
from userinfo 
tablesample(bucket 1 out of 10 on rand())
where pt = '${yyyymmdd}'
;

块采样

指定记录数抽样

• TABLESAMPLE (m ROWS) ，其中 m 为指定随机返回的记录数。

切记，此时要考虑 HDFS中block 个数，因为 TABLESAMPLE 本身是基于 block 抽样的，如果此时有 10个 block ，那么如下代码最终返回结果等于：m x block 个数 。

select count(1)
from (select uid
        from userinfo 
       where pt = '${yyyymmdd}'
       ) t 
tablesample(1000 rows)
;

指定百分比采样

• TABLESAMPLE ( PERCENT)，其中n为采样百分比，取其中n%的数据，即采样返回的数据记录个数和源表中总记录个数之比大概是n%，非精确值。

select count(1)
from (select uid
        from userinfo 
       where pt = '${yyyymmdd}'
       ) t 
tablesample(20 percent)
;

按照数据大小抽样

• TABLESAMPLE(M MB/GB/GB)

select count(1)
from (select uid
        from userinfo 
       where pt = '${yyyymmdd}'
       ) t 
tablesample(100M) -- 抽取 100MB 大小的数据，抽取的是数据块，是压缩的数据。
;

分组抽样

ClusterSample

此函数并非 Hive 原生函数，而是 MaxCompute 独有。

用户随机抽样。返回 True 表示该行数据被抽中。命令如下：

• cluster_sample(bigint ) OVER ([partition_clause]) ，表示随机抽取N条数据。
• cluster_sample(bigint , bigint ) OVER ([partition_clause])，表示按比例（M/N）随机抽取。即抽取partition_row_count×M / N条数据。partition_row_count 指分区中的数据行数。

案例： 按照不同意向职位（软开,硬件,机械,产运,金融,市场营销）、是否新老用户（1；0）分组，各抽取 10 个用户入样，代码如下：

with usr as (
select *
       ,CLUSTER_SAMPLE(10) over(partition by careerjob,isnew) as flag
from userinfo 
where pt = '${yyyymmdd}'
and careerjob in ('软开','硬件','机械','产运','金融','市场营销')
)
select count(uid)
from usr 
where flag = true 
;

结果如下所示：

上述案例中，careerjob 有 6 个枚举，isnew 有 2 个枚举，组合方案有 12 组，每组抽取10人，一共抽取 120 人。

ntile

将数据顺序切分成N等份，返回数据所在等份的编号（从1到N）。

• ntile(bigint N) over ([partition_clause] [orderby_clause])

select id
from (select 
             id
            ,ntile(9) over (order by rand()) as seed
        from values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) as t(id)
       )
where seed=1;