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.自由度の高い方法

こちらなどにも紹介がある手法のちょっとした拡張です。（リンク先の手法だとデータ件数が多くなった時に対応しきれません。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