こんにちは。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	age	ticket	fare	embarked
358	0	2	0	38	0	13	0
359	1	3	1	0	1	7.8792	1
360	1	3	2	0	2	7.8792	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を利用していれば、上記のようにとりあえず簡単に機械学習をおこなって、ビジネスチャンスを伺うのもいいかもしれません。

それではぼくはここらへんで失礼しましょう。今年もみなさんお疲れ様でした！

SQLだけで機械学習するぞ-Hivemall入門-