機械学習とBIツールでデータ可視化してみた＜実践編＞～OracleMachineLearning実装～

1．検証環境

2．予測テーマ

Kaggle入門として有名な「タイタニック号の生存予測」
https://www.kaggle.com/c/titanic

今回はこちらの題材をお借りして ADW で実装していきます。

3．手順

（１）データをCSVファイルで用意し、ADW にロードします。

今回使用するCSVファイルはこちら
https://www.kaggle.com/c/titanic/data

①　「SQL Developer Web」を使用

こちらの方法では CSV を画面にドラッグするだけでロードできます。ただし、「SQL Developer Web」には管理者権限のある Oracle ユーザでなければ入れないという注意点があります。

各種設定していきます。
「次へ」のボタンがアクティブにならない場合は表名にカーソルをあわせ、Enterキーを押します。

②　「DBMS_CLOUD.CREATE_CREDENTIAL」を使用

「管理者権限がないユーザで実行したい！」「クライアントPCの SQL Developer からもストアドで実行したい！」という方はこちら。

・クライアントPCの SQL Developer から ADW に接続できるように設定

※ウォレットのダウンロード方法は＜構築編＞に記載しています。

・OCI の画面からオブジェクト・ストレージにCSVを配置

左上のメニューから オブジェクト・ストレージ に移動

バケットを作成

作成したバケットにCSVをアップロード

リストの右の方をクリックし、オブジェクト詳細を表示。URLパスを取得します。

・認証トークンを作成

右上のプロファイルから「ユーザ設定」を開き、左下の「認証トークン」を選択します。

トークンの生成を行い、生成されたトークンをコピーします。

・ストアドの実行

あとは テーブルを用意し、SQL Developer から下記のコマンドを発行すると読み込み完了です！

CREATE TABLE TITANIC_TRAIN										
    (										
    PASSENGERID NUMBER(4)										
  , SURVIVED    NUMBER(1)										
  , PCLASS      NUMBER(1)										
  , NAME        VARCHAR2(100)										
  , SEX         VARCHAR2(6)										
  , AGE         NUMBER(3)										
  , SIBSP       NUMBER(2)										
  , PARCH       NUMBER(2)										
  , TICKET      VARCHAR2(20)										
  , FARE        NUMBER(8,4)										
  , CABIN       VARCHAR2(15)										
  , EMBARKED    VARCHAR2(1)										
    )										
;										

SET DEFINE OFF																		
BEGIN																		
  DBMS_CLOUD.CREATE_CREDENTIAL(																		
    credential_name => 'CRED_TEST',																		
    username => 'xxxxx@xxxxxxxxxx.co.jp',  -- OCIにログインするときに使用しているメールアドレス		
    password => 'k3C1AxxxxxxxvAiK)KT)'	   -- 前手順で生成したトークン		
  );																		
END;																		
/																		

BEGIN				
 DBMS_CLOUD.COPY_DATA(				
    table_name =>'TITANIC_TRAIN',				
    credential_name =>'CRED_TEST',				
    file_uri_list =>'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/nrta8likyvwe/b/kaggle_titanic/o/train.csv',				
    format => json_object('type' value 'csv','ignoremissingcolumns' value 'true','skipheaders' value 1)				
    );				
END;				

（２）Oracle Machine Learning ノートブックから機械学習を試す

クライアントPC の SQL Developer からでも機械学習のストアドは実行できますが、せっかくなので ADW ならではの「Oracle Machine Learningノートブック」を使っていきます。

・MLユーザで「Oracle Machine Learningノートブック」を使うための手順

Oracle Machine Learningノートブック を立ち上げるユーザに権限を与えます。

1.Oracle MLユーザ管理に「ADMIN」でログインします。

2.すべてのユーザを表示にチェックを入れ、権限を付与したいユーザ名の編集画面を表示し、保存しなおすだけで権限付与は完了です。

・「Oracle Machine Learningノートブック」にログイン

ADW の画面から「サービス・コンソール」→「開発」を開き、立ち上げます。

・訓練データを確認

今回使用する訓練データの内容はこちら。

データの中身はこのような感じになっています。

チケットクラスごと、性別ごと、乗船ポートごと、年齢ごとの生存状況も確認していきます。
ノートブックを使用すると、SELECT結果を集計してチャートで見ることができます。

・データの前処理

年齢と乗船ポートに欠損値があるので、年齢を中央値、乗船ポートを一番多い「S」で埋めていきます。

今回はVIEWを作成してNVL関数で欠損値を埋めるようにしていきます。また、今回は予測にこのままでは使えなさそうな「Name」「SibSp」「Parch」「Fare」「Cabin」をSELECT句からのぞきます。

CREATE				
OR  REPLACE VIEW V_TITANIC_TRAIN AS				
    SELECT				
        PASSENGERID,				
        SURVIVED,				
        PCLASS,				
        SEX,				
        NVL(AGE,28) AS AGE,				
        NVL(EMBARKED,'S') AS EMBARKED				
    FROM				
        TITANIC_TRAIN				
;				

CREATE				
OR  REPLACE VIEW V_TITANIC_TEST AS				
    SELECT				
        PASSENGERID,				
        PCLASS,				
        SEX,				
        NVL(AGE,28) AS AGE,				
        NVL(EMBARKED,'S') AS EMBARKED				
    FROM				
        TITANIC_TEST				
;				

・モデルの設定テーブルの作成

「SETTING_NAME」「SETTING_VALUE」2カラムのテーブルを用意し、設定値を挿入します。後述のモデル作成「DBMS_DATA_MINING.CREATE_MODEL」の引数でこのテーブルを指定します。

CREATE TABLE TITANIC_DECISION_TREE_SETTINGS (				
    SETTING_NAME  VARCHAR2(30),				
    SETTING_VALUE VARCHAR2(4000));				

今回はディシジョンツリーのアルゴリズムを使用する設定にします。

BEGIN		
 INSERT INTO TITANIC_DECISION_TREE_SETTINGS (SETTING_NAME, SETTING_VALUE) 		
 VALUES (DBMS_DATA_MINING.ALGO_NAME, DBMS_DATA_MINING.ALGO_DECISION_TREE);		
		
 INSERT INTO TITANIC_DECISION_TREE_SETTINGS (SETTING_NAME, SETTING_VALUE)		
 VALUES (DBMS_DATA_MINING.PREP_AUTO, DBMS_DATA_MINING.PREP_AUTO_ON);  		
		
 COMMIT;		
END;		

・モデルの作成

BEGIN 				
DBMS_DATA_MINING.CREATE_MODEL(				
   MODEL_NAME          => 'TITANIC_DECISION_TREE_MODEL',				
   MINING_FUNCTION     => DBMS_DATA_MINING.CLASSIFICATION,				
   DATA_TABLE_NAME     => 'V_TITANIC_TRAIN',				
   TARGET_COLUMN_NAME  => 'SURVIVED',				
   CASE_ID_COLUMN_NAME => 'PASSENGERID',				
   SETTINGS_TABLE_NAME => 'TITANIC_DECISION_TREE_SETTINGS');				
END;				
/				

（CREATE_MODEL2 を使用すれば設定テーブルを作らず、変数で設定することも可能です。）

モデルを作成するとディクショナリテーブルに登録されたり、作成したモデルに紐づくテーブルやVIEWが作成されます。

▽登録されたモデルの設定値の確認

SELECT * FROM USER_MINING_MODEL_SETTINGS 					
WHERE MODEL_NAME = 'TITANIC_DECISION_TREE_MODEL'					

▽作成されたビューの確認

SELECT * FROM USER_MINING_MODEL_VIEWS 	
WHERE MODEL_NAME = 'TITANIC_DECISION_TREE_MODEL'	

・モデルを使った予測

本来は訓練データから6割ほどランダムに抜き取って学習させ、残りの4割で予測値と実際の値を並べてどのくらい正解するかを見ますが、今回は訓練データを分ける工程を省いたので、モデル作成に使用した訓練データ全件の実際の値と予測値を比較し、どのくらい一致するか見ていきます。

SELECT							
    SUB.*,							
    CASE							
        WHEN SURVIVED = PREDICTION THEN '一致'							
        ELSE '不一致'							
    END AS MATCH							
FROM							
    (							
    SELECT							
        TRAIN.PASSENGERID,							
        TRAIN.PCLASS,							
        TRAIN.SEX,							
        TRAIN.AGE,							
        TRAIN.EMBARKED,							
        TRAIN.SURVIVED,							
        PREDICTION(TITANIC_DECISION_TREE_MODEL USING *) AS PREDICTION,							
        PREDICTION_PROBABILITY(TITANIC_DECISION_TREE_MODEL USING *) 							
                                            AS PREDICTION_PROBABILITY							
    FROM							
        V_TITANIC_TRAIN TRAIN							
    ) SUB							

上のSELECT結果を集計してみると、正答率80%となりました。

一つのテストデータを与える場合はUSING句に記載します。

SELECT				
    PREDICTION(TITANIC_DECISION_TREE_MODEL USING 				
        3 AS PCLASS				
      , 'male' AS SEX				
      , 'S' AS EMBARKED				
    ) AS PREDICTION				
FROM				
    DUAL				

Kaggleに投稿する用に、テストデータにもモデルを適用してみます！

SELECT			
    PASSENGERID			
  , PREDICTION(TITANIC_DECISION_TREE_MODEL USING *) AS SURVIVED 			
FROM 			
    V_TITANIC_TEST TEST			

結果をKaggleのSubmit Predictionsから投稿してみます。

スコアは「0.77033」でした。

使用するアルゴリズムを変えたり、パラメータを変更したり、データの前処理で今回使用しなかった「Name」から「Miss」や「Mrs」などを切り出して分析に使ったりまだまだ改善の余地ありですね。

やってみた感想としては、SQL に慣れている人にはとっつきやすい印象でした。

次回は、同じタイタニックのテーマを使って、MotionBoardからモデルの作成・予測を実装してきます！