MicroAdDay 17

hiveqlでのサンプリングの方法について

この記事は、MicroAd Advent Calendar 2018の17日目の記事です。


はじめに

不均衡なデータを扱かう際に、データのサンプリングが必要になることがよくあります。

このような時、対象とするデータが小さい場合には分析するPCに必要なデータを全件持ってきてサンプリングすることが一般的です。しかし、対象とするデータが大きく、全件のデータを持ってくることができない場合には上記の方法は使えません。そのため、事前にサンプリングし、サンプリング後のデータを持ってきて利用することになります。

今回はこのような状況になった場合に、hiveで自由度高くサンプリングする方法を紹介したいと思います。


方法

基本的な方法について説明したのちに、自由度の高い方法を紹介したいと思います。


1.基本的な方法

hiveのTABLESAMPLEを利用する方法です。

-- 1.一定の割合、件数、容量分サンプリングする場合

SELECT *
FROM <source> TABLESAMPLE(0.1 PERCENT/ 1000 ROWS/ 100M) source_alias;
-- 2.特定のカラムについてサンプリングする場合
SELECT *
FROM <source> TABLESAMPLE(BUCKET 3 OUT OF 32 [ON <colname>]) source_alias;

こちらを利用するメリットとしては、下記の点があげられると思います。


  • サンプリングする意図が明確になりクエリがわかりやすくなる

  • sourceのテーブルがBUCKETED TABLEの場合、上記2のサンプリング方式において処理が高速になる

一方でデメリットとしては、下記の点があげられます。


  • サブクエリに対して適用ができない

  • サンプリング前のデータに対してソートするなど、細かい調整ができない


2.自由度の高い方法

こちらなどにも紹介がある手法のちょっとした拡張です。(リンク先の手法だとデータ件数が多くなった時に対応しきれません。window関数内のorder by部分でタイムアウトして落ちます。)

下記が自由度を高めた方法です。

SELECT

*
FROM
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY < colname >) AS key_rank,
COUNT(1) OVER (PARTITION BY < colname >) AS upper_limit
FROM
(
SELECT
*
FROM
< source >
DISTRIBUTE BY < colname >
SORT BY < colname >
) alias1
) alias2
WHERE
key_rank <= upper_limit * < sample_rate >

抽象的な書き方をしているのでわかりづらいですが、上記クエリで埋めるべき部分は<>で囲われた部分になります。

こちらとの差分はwindow関数内のorder byを排除するためにデータを前もってDISTRIBUTE BYとSORT BYを用いてソートしている部分になります。これにより、ある程度大きなデータも1クエリで自由度高くサンプリングできるようになります。

ただ、こちらの方法には、下記のようなデメリットがあります。


  • 可読性が低い

  • window関数を利用しているため大規模なデータに適用すると処理が重くなる

1つのクエリで書く必要がなく、基盤に空きのある場合は、中間BUCKETED TABLEを作成した上で基本的な方法を利用した方が処理が速くなるので、そちらを試した方がいいと思います。(BUCKETED TABLEの作り方はこちらから確認できます。)


まとめ

今回、大きなデータをサンプリングする方法として、基本的な方法と自由度の大きな方法を紹介しました。

何かの参考にしていただけると嬉しいです。

最後に簡単な宣伝ですが、マイクロアドでは技術ブログを公開しています。下記のリンクから確認できますので、興味のある方は是非ご覧ください。

https://developers.microad.co.jp/


参考

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Sampling

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-BucketedSortedTables