文档

TABLESAMPLE采样

更新时间:

MaxCompute支持通过TABLESAMPLE功能来对表数据进行采样,包含三种采样方式:分桶采样、指定采样百分比采样和随机返回指定记录数采样。本文为您介绍使用TABLESAMPLE采样的命令语法和使用示例。

令格式

  • 分桶采样。

    TABLESAMPLE (BUCKET <x> OUT OF <y> [ON <col_name> | rand()])

    参数说明如下。

    • x,y:必填。将源表中的数据划分为y个桶,取其中的第x个桶,桶从1开始编号。

    • col_name:分桶列名即要进行采样的列名。当表不是聚簇表时,col_namerand()函数必须二选一,当使用rand()函数时表示对输入的数据随机进行分桶。ON语句中最多支持指定10个列。

  • 指定采样百分比采样。

    TABLESAMPLE (<n> PERCENT)

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

  • 随机返回指定记录数采样。

    TABLESAMPLE (<m> ROWS)

    其中m为指定随机返回的记录数。如果源表中的总记录个数小于m,则返回源表中的全部记录。m最大值为10000。

示例数据

本文使用示例需要用到如下两个表。

  • maxcompute_public_data.dwd_product_phoneno_basic_info_2020。

    maxcompute_public_data.dwd_product_phoneno_basic_info_2020表是MaxCompute公开数据集中的表,详情请参见公开数据集参考

  • tblsample_test。

    tblsample_test表为聚簇表,建表DDL及数据插入命令如下。

    --创建cluster表
    CREATE TABLE tblsample_test(a bigint, b string, c string) 
      CLUSTERED BY (a, c) SORTED by (a, c) INTO 32 BUCKETS;
    --插入数据
    insert overwrite table tblsample_test values(1,"b1","c1"),
        (2,"b2","c2"),
        (3,"b3","c3"),
        (4,"b4","c4");
    --查询表数据
    select * from tblsample_test;
    --返回结果
    +------------+---+-----+
    | a          | b | c   |
    +------------+---+-----+
    | 2          | b2 | c2 |
    | 4          | b4 | c4 |
    | 3          | b3 | c3 |
    | 1          | b1 | c1 |
    +------------+---+---+

使用示例

  • 示例1:根据列的值分桶进行采样。

    --对maxcompute_public_data.dwd_product_phoneno_basic_info_2020进行采样
    SELECT isp_code,
    			 phoneno,
    			 province
      FROM maxcompute_public_data.dwd_product_phoneno_basic_info_2020 
    TABLESAMPLE (BUCKET 1 OUT of 1000000 ON isp_code, 
                          									phoneno, 
                          									province) s;
                                                
    --返回结果
    +----------+---------+----------+
    | isp_code | phoneno | province |
    +----------+---------+----------+
    | 185      | 1853500 | 山西   |
    | 187      | 1878332 | 四川   |
    +----------+---------+----------+

  • 示例2:使用RAND()函数,对输入的数据随机分桶进行采样。

    --对maxcompute_public_data.dwd_product_phoneno_basic_info_2020进行采样
    SELECT isp_code,
    			 phoneno,
           province
      FROM maxcompute_public_data.dwd_product_phoneno_basic_info_2020 
    TABLESAMPLE (BUCKET 3 OUT OF 500000 ON RAND()) s;
    
    --返回结果
    +----------+---------+----------+
    | isp_code | phoneno | province |
    +----------+---------+----------+
    | 131      | 1312224 | 上海   |
    | 135      | 1353936 | 广东   |
    | 158      | 1586377 | 山东   |
    +----------+---------+----------+

  • 示例3:对聚簇表tblsample_test可省略ON条件进行采样。

    select a, b from tblsample_test TABLESAMPLE (BUCKET 1 OUT OF 2) as ts;
    
    --返回结果
    +------------+---+
    | a          | b |
    +------------+---+
    | 2          | b2|
    +------------+---+
    说明

    因为聚簇表中的数据在存储的时候已经分桶存储,因此在采样的时候会直接从某个桶里取数据,这样做可以提高采样的性能。tblsample_test表在创建的时候是分了32个桶,采样的时候,只要总桶数y的取值为32的倍数(32/64/128...)或者是能整除32(16/8/4/...),都可以取得性能优化的效果。

  • 示例4:对tblsample_test表进行采样,取其中n%的数据,即采样返回的数据记录个数和源表中总记录个数之比大概是n%(不能保证绝对精确)。

    --取tblsample_test 50%的数据
    SELECT * FROM tblsample_test TABLESAMPLE (50 PERCENT) s;
    
    --返回结果
    +------------+---+---+
    | a          | b | c |
    +------------+---+---+
    | 2          | b2 | c2 |
    | 3          | b3 | c3 |
    +------------+---+---+

  • 示例5:对tblsample_test表进行采样,随机返回n条记录。

    select * FROM tblsample_test TABLESAMPLE (2 ROWS);
    
    --返回结果
    +------------+---+---+
    | a          | b | c |
    +------------+---+---+
    | 2          | b2 | c2 |
    | 3          | b3 | c3 |
    +------------+---+---+

  • 本页导读 (1)
文档反馈