一. 分桶抽樣：TABLESAMPLE 函式

說明：TABLESAMPLE 將資料分成多個bucket，抽取其中一個bucket

語法：按照colname欄位分成bucketNum個桶，抽取其中的第bucketId桶

-- bucketId：抽取的bucket的編號
-- bucketNum表示bucket的數量
   -- 如果基于某列來分桶，colName就是該列的列名，如果要隨機分桶，那么colName可以用rand()來代替
TABLESAMPLE (BUCKET bucketId OUT OF bucketNum [ON colName])

實際用法：

-- *** 1. 整體基于行數抽樣：可實作隨機抽樣
    -- 對每個split的資料都r條資料
select * from musics tablesample(2 rows) s;

-- *** 2. 基于rand()抽樣
    -- 將musics表中資料隨機分為5個bucket，然后抽取編號為2的bucket的資料
    -- 因為是隨機分的，所以每次執行的結果都不同
select * from musics tablesample(bucket 2 out of 5 on rand()) s;

-- *** 3. 基于列名抽樣
    -- 基于singerid(歌手)列對musics表抽樣，一個歌手一個bucket，這里抽取第一個bucket歌手的資料
    -- 如果表在創建時已經使用cluster by分桶，而且tablesample指定的列正是用于分桶的列，那么在抽樣時，可以只涉及到表相應的hash磁區的資料，而無需掃描整張表，因為表中的資料已經按列的hash值分割到不同的磁區，
select * from musics tablesample(bucket 1 out of 4 on singerid) s;

-- *** 4. 基于百分比抽樣
    -- 根據資料資料塊大小的百分比進行抽樣
    -- 抽樣的最小單元是一個HDFS資料塊，一般一個HDFS資料塊大小為128M
    -- 如果表的資料少于一個HDFS資料塊的大小，那么會回傳所有的數
select * from musics tablesample(0.5 percent) s;

-- *** 5. 基于資料大小抽樣
    -- 抽樣資料大小為10M，但是因為表資料量過小，未達到一個HDFS塊大小，會輸出全部的記錄
select * from musics tablesample(10M) s;

注意：tablesample不支持子查詢和where，可以另外建中間表，或者用臨時表with as

 with tmp_a as (
     select * from muscis where pk_month ='2021-08'
 )
  select * from tmp_a tablesample (200 rows)

二. 隨機抽樣：rand()函式

說明：排序函式+rand()函式完成隨機抽樣

其中rand() 回傳一個0到1之間double 型別的隨機值

limit控制抽樣回傳的資料量

-- *** 1. order by rand()
   -- order by只會啟用一個reduce，所以比較耗時
   -- 因為order by 是全域的，所以可以做到隨機抽樣的目的
select * from ods_user_bucket_log order by rand() limit 10;

-- *** 2. sort by rand()
   -- sort by 提供了單個 reducer 內的排序功能，但不保證整體有序，這個時候其實不能做到真正的隨機的，
   -- 因為此時的隨機是針對磁區去的，所以如果我們可以通過控制進入每個磁區的資料也是隨機的話，那我們就可以做到隨機了
select * from ods_user_bucket_log sort by rand() limit 10;

-- *** 3. distribute by rand() sort by rand()
   -- rand函式前的distribute和sort關鍵字可以保證資料在mapper和reducer階段是隨機分布的，
   -- 這個時候我們也能做到真正的隨機
select * from ods_user_bucket_log distribute by rand() sort by rand() limit 10;

-- *** 4. cluster by rand()
   -- cluster by 的功能是 distribute by 和 sort by 的功能相結合
   -- distribute by rand() sort by rand() 進行了兩次隨機，cluster by rand() 僅一次隨機，所以速度上會比上一種方法快
select * from ods_user_bucket_log cluster by rand() limit 10;

三. 按比例抽樣

-- 1. TABLESAMPLE
    -- 根據資料資料塊大小的百分比進行抽樣
    -- 抽樣的最小單元是一個HDFS資料塊，一般一個HDFS資料塊大小為128M
    -- 如果表的資料少于一個HDFS資料塊的大小，那么會回傳所有的數
select * from musics tablesample(0.5 percent) s;

-- 2. rand()
select * from 
    (
        select* ,rand() as radix from ods_user_bucket_log
    ) tmp
where radix>=0.0 and radix<=0.0001;

四. 分層抽樣

1. 分層抽一定數量：row_number()

-- 這里對id分層抽樣，每層抽取3個
select * from 
    (
        select id,ctime,
            row_number() over(partition by id order by rand()) as rn from ods_user_log
    ) tmp where rn<=3

2. 分層抽一定比例

同按比例抽樣

五. 注意：

1. TABLESAMPLE 抽樣函式本身是不走MR 的，所以執行速度很快(注意抽取多少M的時候，只能是整數M)

2. rand()需要走MR的，所以執行性能上沒有TABLESAMPLE那么快，而且表達能力有限，只能獲取特定的條數(limit n)

參考：

Hive 資料抽樣的各種玩法(43)_mb5ffbc8f4a5a17的技術博客_51CTO博客

Hive 表格采樣(table sample) @狐貍教程:~#

Hive 資料抽樣的各種玩法(43)_mb5ffbc8f4a5a17的技術博客_51CTO博客

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/430285.html

標籤：其他

上一篇：資料湖之Hudi（6）：Hudi與Spark和HDFS的集成安裝使用

下一篇：公司來了個卷王之王，讓人崩潰