こんにちは。onunuです。
IESHILでエンジニアをしています。主にSQLを描くのがお仕事です。
以前はマッハバイト(旧ジョブセンス)で開発していたのですが、SQLで座標平面を描く能力を評価してもらい、座標上の2点間の距離計算とかいっぱいする不動産のサービスに異動になりました。地球は球なので大変です。
さてさて、本記事はLivesense -自- Advent Calendar 2017のために書かれています。
テーマは 自 、ということなので、自分の仕事領域であるSQLに関して書こうと思います。
Hivemallとは
HivemallはHadoop/Spark上でSQLクエリを用いて機械学習を行うためのライブラリです。
実際にはHadoop/Spark用のデータウェアハウスであるHive上で動作するUDF(User Defined Function)として実装されています。
ユーザーはHive上のデータに対してSQLのみで機械学習を行うことができます。
Hivemallにはロジスティック回帰等の回帰分析やランダムフォレストによるクラス分類など、さまざまな機械学習アルゴリズムが梱包されていて、本格的な機械学習をすぐに導入できるのが強みです。
Getting Start!
データセットの準備
ではさっそく始めてみましょう。
Hadoop, Hive, Hivemallのセットアップとかは省きます(TresureDataでやってしまったので・・・)。
では、Kaggleのチュートリアルとして名高いtitanicのデータセットを使ってランダムフォレストによる2値分類を行なってみましょう。
ざっくり説明しますと、titanic号の乗客の情報から生き残ったかどうかを学習させ、予測しましょう、ということですね。Kaggleで提供されるtestデータには正解が記載されていないので、trainデータの一部をtestデータとして学習用のデータからは省くこととします。
元のデータは以下のような形式です。
| passengerid | survived | pclass | name | sex | age | sibsp | parch | ticket | fare | cabin | embarked |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 0 | 3 | Braund, Mr. Owen Harris | male | 22 | 1 | 0 | A/5 21171 | 7.25 | 0 | S |
| 3 | 1 | 3 | Heikkinen, Miss. Laina | female | 26 | 0 | 0 | STON/O2. 3101282 | 7.925 | 0 | S |
説明変数を数値に変更する
機械学習する上では文字列は扱えないので数値に変換します。
SELECT
QUANTIFY(true,
passengerid, survived, pclass, name, sex, age, sibsp, parch, ticket, fare, cabin, embarked
) AS (passengerid, survived, pclass, name, sex, age, sibsp, parch, ticket, fare, cabin, embarked)
FROM
titanic
QUANTIFY()は文字列情報を数値に変換してくれる関数です。これを使うととても早いですね。結果は以下のようになりました。
| passengerid | survived | pclass | name | sex | age | sibsp | parch | ticket | fare | cabin | embarked |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 358 | 0 | 2 | 0 | 0 | 38 | 0 | 0 | 0 | 13 | 0 | 0 |
| 359 | 1 | 3 | 1 | 0 | 0 | 0 | 0 | 1 | 7.8792 | 0 | 1 |
| 360 | 1 | 3 | 2 | 0 | 0 | 0 | 0 | 2 | 7.8792 | 0 | 1 |
説明変数を配列にまとめる
ここで学習用データとtest用データを分けておきます。
passengerid が800未満を学習用、800以上をtest用として分けることにしました。
説明変数を配列にまとめるのはこのあとで使うHivemallの関数が引数を配列で取るための配慮です。
WITH quantified AS (
SELECT
QUANTIFY(true,
passengerid, survived, pclass, name, sex, age, sibsp, parch, ticket, fare, cabin, embarked
) AS (passengerid, survived, pclass, name, sex, age, sibsp, parch, ticket, fare, cabin, embarked)
FROM
titanic
), train AS (
SELECT
passengerid,
ARRAY(pclass, name, sex, age, sibsp, parch, ticket, fare, cabin, embarked) AS features,
survived
FROM
quantified
WHERE
passengerid < 800
), test AS (
SELECT
passengerid,
ARRAY(pclass, name, sex, age, sibsp, parch, ticket, fare, cabin, embarked) AS features,
survived AS survived_correct
FROM
quantified
WHERE
passengerid >= 800
)
結果は以下の通りです。
| passengerid | features | survived |
|---|---|---|
| 1 | [3.0, 0.0, 0.0, 22.0, 1.0, 0.0, 0.0, 7.25, 0.0, 0.0] | 0 |
| 3 | [3.0, 1.0, 1.0, 26.0, 0.0, 0.0, 1.0, 7.925, 0.0, 0.0] | 1 |
SQL長くなっちゃうのでここからは適切に分けていきますね。
学習する
いよいよ学習です。TRAIN_RANDOMFOREST_CLASSIFIER()を使ってクラス分類を行います。
TRAIN_RANDOMFOREST_CLASSIFIER() 関数では-treesで決定木の数、-attrsでは各説明変数が[Q:量的]か[C:質的]かどうかを明示する必要があります。
-- table:model
SELECT
TRAIN_RANDOMFOREST_CLASSIFIER(features, survived, "-trees 500 -attrs C,C,C,Q,Q,Q,C,Q,C,C")
FROM
train
結果は以下のようになりました。これだけだと何がなんだかよくわかりませんね。このmodelを使って予測を次のステップで行います。
| model_id | model_type | pred_model | var_importance | oob_errors | oob_tests |
|---|---|---|---|---|---|
| 7a3f3ae9-3862-47ca-b9f7-cfc37c4e37da | -3 | 省略 | [0.09576779301680655, 1.2733288209825033, 0.3358146372035047, 0.6587979973279458, 0.03684900957628229, 0.31999999999999984, 0.8978685276906975, 1.9327439756054603, 0.0, 0.04884845185461929] | 0 | 0 |
| 7bccf92b-fb0c-4fa8-92d7-49390a5dfbb5 | -3 | 省略 | [0.14843401059181255, 1.1773166948759979, 0.15179900993895604, 0.14748706533080252, 0.06508875739644956, 0.08341309362133248, 0.5143381765613078, 0.8223971522757472, 0.0, 0.7444881277704621] | 0 | 0 |
ちなみにここでvar_importanceを集計すれば、どの説明変数が重要度の高い説明変数なのか計算することができます。
予測する
上記で作成したmodelを用いてtestデータを予測してみましょう。
-- table:predicted_raw
SELECT
t.passengerid,
TREE_PREDICT(p.model_id, p.model_type, p.pred_model, t.features, true) AS predicted
FROM
model AS p
LEFT JOIN test AS t
| passengerid | predicted |
|---|---|
| 800 | 0 |
| 801 | 0 |
model x testデータの数だけレコードが作成されました。
この時点ではまだ予測の途中です。
続いてこの生予測データを予測として成立するよう集計します。
集計する
-- table: predicted_reduced
SELECT
passengerid,
RF_ENSEMBLE(predicted) AS predicted
FROM
predicted_raw
GROUP BY
passengerid
| passengerid | predicted |
|---|---|
| 800 | [0,0.676,[0.676,0.324]] |
| 801 | [0,0.994,[0.994,0.006]] |
RF_ENSEMBLE()は学習結果をまとめるための関数です。
先ほどの予測結果はmodelの数だけレコードがある状態なので集計します。
ENSEMBLEってフランス語なんですね。
集計結果を見やすくする
-- table: predicted
SELECT
passengerid,
predicted.label,
predicted.probability,
predicted.probabilities
FROM
predicted_reduced
| passengerid | label | probability | probabilities |
|---|---|---|---|
| 800 | 0 | 0.982 | [0.982,0.018] |
| 801 | 1 | 0.986 | [0.014,0.986] |
labelは分類結果で、probabilityは分類結果の信頼性ですね。
probabilitiesは分類結果以外のものも含めた確率の配列です。合計すると1になります。
testデータの成否をたしかめる
先ほど作成していたtestデータを再度結合し、成否を確かめます。
最後のクエリなので、全てご覧ください。
WITH quantified AS (
SELECT
QUANTIFY(true,
passengerid, survived, pclass, name, sex, age, sibsp, parch, ticket, fare, cabin, embarked
) AS (passengerid, survived, pclass, name, sex, age, sibsp, parch, ticket, fare, cabin, embarked)
FROM
titanic
), train AS (
SELECT
passengerid,
ARRAY(pclass, name, sex, age, sibsp, parch, ticket, fare, cabin, embarked) AS features,
survived
FROM
quantified
WHERE
passengerid < 800
), test AS (
SELECT
passengerid,
ARRAY(pclass, name, sex, age, sibsp, parch, ticket, fare, cabin, embarked) AS features,
survived AS survived_correct
FROM
quantified
WHERE
passengerid >= 800
), model AS (
SELECT
TRAIN_RANDOMFOREST_CLASSIFIER(features, survived, "-trees 500 -attrs C,C,C,Q,Q,Q,C,Q,C,C")
FROM
train
), predicted_raw AS (
SELECT
t.passengerid,
TREE_PREDICT(p.model_id, p.model_type, p.pred_model, t.features, true) AS predicted
FROM
model AS p
LEFT JOIN test AS t
), predicted_reduced AS (
SELECT
passengerid,
RF_ENSEMBLE(predicted) AS predicted
FROM
predicted_raw
GROUP BY
passengerid
), predicted AS (
SELECT
passengerid,
predicted.label,
predicted.probability,
predicted.probabilities
FROM
predicted_reduced
)
SELECT
t.passengerid,
t.survived_correct,
p.label AS predict,
p.probability,
p.probabilities
FROM
predicted AS p
INNER JOIN test AS t ON p.passengerid = t.passengerid
| passengerid | survived_correct | predict | probability | probabilities |
|---|---|---|---|---|
| 800 | 0 | 0 | 0.69 | [0.69, 0.31] |
| 801 | 0 | 0 | 0.996 | [0.996, 0.004] |
| 802 | 1 | 1 | 0.982 | [0.018, 0.982] |
| 805 | 1 | 0 | 0.992 | [0.992, 0.008] |
| 806 | 0 | 0 | 0.942 | [0.942, 0.058] |
| 808 | 0 | 1 | 0.686 | [0.314, 0.686] |
うーん、あんまりあってない!!(全部載せると大変なので一部抜粋)
自信満々に間違えてるやつとかいるので、要チューニングです。
まあ説明変数やtreeの数を精査していないので、仕方ないかもしれません。
機械学習のチューニングはやはりそれ相応の技術が必要ですね。
まとめ
Hivemallを用いた機械学習のクエリを紹介しました。
最終的にSQL60行ちょっとで機械学習を実装できているので、そこそこ簡単、と言えるのではないでしょうか？
実際のプロダクトで使いどころとしては、色々なデータがある中で機械学習をプロトタイプで導入する時などでしょうか？
TresureDataで利用できるHiveではデフォルトでHivemallが利用できる環境になっています。
もしTresureDataを利用していれば、上記のようにとりあえず簡単に機械学習をおこなって、ビジネスチャンスを伺うのもいいかもしれません。
それではぼくはここらへんで失礼しましょう。今年もみなさんお疲れ様でした！