この記事は、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.自由度の高い方法
こちらなどにも紹介がある手法のちょっとした拡張です。（リンク先の手法だとデータ件数が多くなった時に対応しきれません。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を用いてソートしている部分になります。これにより、ある程度大きなデータも１クエリで自由度高くサンプリングできるようになります。
ただ、こちらの方法には、下記のようなデメリットがあります。
- 可読性が低い
- window関数を利用しているため大規模なデータに適用すると処理が重くなる
１つのクエリで書く必要がなく、基盤に空きのある場合は、中間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