Hive進階—抽樣的各種玩法


抽樣

抽樣在Hive 中也是比較常用的一種手段,主要用在下面的幾個場景中

  1. 一些機器學習的場景中,數倉作為數據的提供方提供樣本數據
  2. 數據的計算結果異?;蛘呤侵笜水惓?,這個時候如果我們往往需要確認數據源的數據是否本身就有異常
  3. SQL的性能有問題的時候我們也會使用抽樣的方法區查看數據,然后進行SQL調優
  4. 在大規模數據量的數據分析及建模任務中,往往針對全量數據進行挖掘分析時會十分耗時和占用集群資源,因此一般情況下只需要抽取一小部分數據進行分析及建模操作。

隨機抽樣(rand()函數)

我們一般情況下是使用排序函數和rand() 函數來完成隨機抽樣,limit關鍵字限制抽樣返回的數據,不同之處再有我們使用哪個排序函數呢

利用 rand() 函數進行抽取,這是因為rand() 返回一個0到1之間double 類型的隨機值。

下面我們用到了前面我們使用過的一張表大概4603089 條記錄,這里我就不給大家準備數據了,大家可以看Hive進階之數據存儲格式來獲取測試數據

create table ods_user_bucket_log(
     id int,
     name string,
     city string,
     phone string,
     acctime string)
CLUSTERED BY (`id` ) INTO 5 BUCKETS 
row format delimited fields terminated by '\t'
stored as textfile;
insert overwrite table ods_user_bucket_log select * from ods_user_log;
SQL

order by rand()

order by只會啟用一個reduce所以比較耗時,至于為什么我們在前面的文章中解釋過了Hive語法之常見排序方式

因為order by 是全局的,所以可以做到隨機抽樣的目的

select * from ods_user_bucket_log order by rand() limit 10;

sort by rand()

sort by 提供了單個 reducer 內的排序功能,但不保證整體有序,這個時候其實不能做到真正的隨機的,因為此時的隨機是針對分區去的,所以如果我們可以通過控制進入每個分區的數據也是隨機的話,那我們就可以做到隨機了

select * from ods_user_bucket_log sort by rand() limit 10;

distribute by rand() sort by rand()

rand函數前的distribute和sort關鍵字可以保證數據在mapper和reducer階段是隨機分布的,這個時候我們也能做到真正的隨機,前面我們也介紹過cluster by 其實基本上是和distribute by sort by 等價的

select * from ods_user_bucket_log distribute by rand() sort by rand() limit 10;

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;

tablesample()抽樣函數

分桶抽樣(桶表抽樣)

hive中分桶其實就是根據某一個字段Hash取模,放入指定數據的桶中,比如將表table按照ID分成100個桶,其算法是hash(id) % 100,這樣,hash(id) % 100 = 0的數據被放到第一個桶中,hash(id) % 100 = 1的記錄被放到第二個桶中。

分桶抽樣語法:
TABLESAMPLE (BUCKET x OUT OF y [ON colname])

其中x是要抽樣的桶編號,桶編號從1開始,colname表示抽樣的列(也就是按照那個字段分桶),y表示桶的數量。 所以表達的意思是按照colname字段分成y桶,抽取其中的第x桶

SELECT
    *
FROM
    ods_user_bucket_log
TABLESAMPLE (BUCKET 1 OUT OF 100000 ON rand()) ;
SQL

數據塊抽樣

從 Hive 0.8 開始提供塊抽樣,使用 tablesample 抽取指定的 行數/比例/大小

SELECT * FROM ods_user_data TABLESAMPLE(1000 ROWS);
SELECT * FROM ods_user_data TABLESAMPLE (20 PERCENT); 
SELECT * FROM ods_user_data TABLESAMPLE(1M); 
SQL

按比例抽樣 ABLESAMPLE (20 PERCENT)

這將允許 Hive 至少獲取 n%的數據

SELECT
    *
FROM
    ods_user_bucket_log
TABLESAMPLE(0.0001 PERCENT);
SQL

抽取特定大小的數據TABLESAMPLE(100M)

SELECT
    *
FROM
    ods_user_bucket_log
TABLESAMPLE(1M);
SQL

需要注意的是這里必須是整數M ,以為我嘗試零點幾的時候報錯了

抽取特定的行數 TABLESAMPLE(10 ROWS)

SELECT
    *
FROM
    ods_user_bucket_log
TABLESAMPLE(10 rows);
SQL

擴展

隨機抽樣如何實現按比例抽樣

前面我們介紹了TABLESAMPLE 可以實現按比例抽樣,隨機抽樣可以借助limit 可以實現抽取特定記錄數,其實我們如果對隨機抽樣進行改進也可以實現按照比例抽樣,因為rand() 的函數值是隨機的,所以我們可以對其返回值做條件過濾從而實現按照比例的抽樣

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

分層抽樣(分組抽樣)

分層抽樣,這里可以分為兩種,一種是分層抽個數另外一種是分層抽比例

分層抽個數

select
    *
from (
    select
        id,ctime,
        row_number() over(partition by id order by rand() ) as rn
    from
        ods_user_log
) tmp
where rn<=3
;
SQL

分層按比例的抽樣,也可以按照上面的方式實現

總結

  1. TABLESAMPLE 抽樣函數本身是不走MR 的所以執行速度很快(注意抽取多少M的時候,只能是整數M)
  2. 隨機抽樣函數需要走MR的,所以執行性能上沒有TABLESAMPLE那么快,而且表達能力有限,只能獲取特定的條數(limit n)
  3. 借助row_number實現分層抽樣





作者:柯廣的網絡日志

微信公眾號:Java大數據與數據倉庫