はじめに
この記事は、JPOUG Advent Calendar 2022 22日目の記事です。21日目はyoshikawさんの記事Oracle TimesTen In-Memory Database Express Edition(XE)を試してみるでした。
こちらの記事では Oracle Database をご利用されている方々により使い倒していただくべく、Oracle Machine Learning の機能を利用してSQLで異常検出する手順をご紹介します。
Oracle Machine Learningの紹介
2019年12月以降、Oracle Database をご利用いただいておりましたら追加ライセンス不要で Oracle Machine Learning 機能をご利用いただけるようになりました。
出典：https://blogs.oracle.com/database/post/machine-learning-spatial-and-graph-no-license-required
オンプレクラウド問わず全製品で利用可能になってます。
出典：Oracle Database 19cマニュアル1.3 Oracle Database製品で許可される機能、オプションおよびManagement Pack
Oracle Machine LearningにはインターフェースがSQLやPython / R / Notebook 等ありますが、今回はオンプレ、クラウド問わずご利用いただける Oracle Machine Learning for SQLをご紹介します。
Oracle Machine Learning for SQL では学習したモデルはDB内のオブジェクトとして格納され、学習や適用はSQLやPL/SQLで完結できます。
In - DBで完結するため、データの分析サーバーへの移動は不要、パーティションやパラレルクエリなど他機能との組み合わせも可、データの加工はDBエンジニアの慣れているSQLを使える、既存アプリケーションへの組み込みが容易、と多々メリットがあります。
ただし、知名度が低すぎて「Oracle Machine Learningはライセンス不要ですぐに使える」以前に「Oracle Machine Learning is 何？」という方が大多数ではないでしょうか。
今回は簡単に動かせるOracle Machine Learning for SQL による異常検出のサンプルプログラムをご紹介します。是非皆さまにとって新たな発見やアイディアの創発に繋がれば幸いです。
活用する異常検出アルゴリズム
One Class SVMというアルゴリズムを利用します。こちらはSVM（サポートベクターマシン）を利用した異常検出アルゴリズムです。全体を一つのクラスと仮定したときの外れ値を検出します。学習データそれぞれを独立したサンプルとして扱いますので時系列データの場合は時系列性が無視されます。
ざっくり以下のイメージ図でアルゴリズムの解説をします。ある成人男性の集団における身長と体重をプロットしたイメージです。身長180cmの方もいらっしゃいますし体重40kgの方もいらっしゃいます。ただし、身長180cmで体重40kgのように変数間の組み合わせを考慮した場合に通常の分布から外れる●のデータを抽出します。2変数までであれば可視化すればある程度の外れ値は見出せそうですが、3変数、4変数...と増えるとなかなか人力での異常値検出は困難ですよね。
One Class SVMはOracle Databaseの少なくとも11.2以上であれば利用できます。
Oracle Machine Learning for SQLによる異常検出手順
試行環境
- Oracle Base Database Service（旧称Oracle Database Cloud Service） Enterprise Edition - High Performance 19.11
学習データイメージ
以下のような一意の列と特徴量となるnumber型の複数の列で構成される横持ちの表を用います。
time_id（一意列）ごとの各センサーの観測値が含まれているとします。
| time_id | sensor1 | sensor2 | ・・・ | sensor4 |
|---|---|---|---|---|
| a_00001 | 351 | 106031 | ・・・ | 276 |
| a_00002 | 222 | 57577 | ・・・ | 109 |
| a_00003 | 347 | 135324 | ・・・ | 282 |
| a_00004 | 148 | 74268 | ・・・ | 12 |
| 以下略 |
以下のように縦持ちの場合はPIVOTで横持ちにしてください。
| time_id | name | value |
|---|---|---|
| a_00001 | sensor1 | 351 |
| a_00001 | sensor2 | 106031 |
| a_00001 | sensor3 | 742 |
| a_00001 | sensor4 | 276 |
| a_00002 | sensor1 | 222 |
| 以下略 |
SELECT *
FROM sensor2
PIVOT (
MAX(value) FOR col
IN ('sensor1' as sensor1,
'sensor2' as sensor2,
'sensor3' as sensor3,
'sensor4' as sensor4)
);
手順
権限設定
お使いのスキーマに不足している権限があれば適宜追加してください。
GRANT CREATE MINING MODEL TO dmuser;
GRANT CREATE SESSION TO dmuser;
GRANT CREATE TABLE TO dmuser;
GRANT CREATE VIEW TO dmuser;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO dmuser;
参考：Oracle Data Mining ユーザーズガイド 19c マイニング・モデルおよびデータへのアクセスの制御
モデル作成
モデル作成ケース1 : モデル作成時に設定表を永続化するケース
-- アルゴリズムの設定を格納する表を作成
CREATE TABLE ad_model_setting ( -- adは anomaly detectionの略
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000)
);
-- アルゴリズムとしてSVMを使用、One Class SVMも包含される
INSERT INTO ad_model_setting values ('ALGO_NAME', 'ALGO_SUPPORT_VECTOR_MACHINES');
-- ガウシアンカーネル指定、デフォルトは線形カーネル
INSERT INTO ad_model_setting values ('SVMS_KERNEL_FUNCTION', 'SVMS_GAUSSIAN');
-- 異常と見なす値の割合、ここでは1%を指定
INSERT INTO ad_model_setting values ('SVMS_OUTLIER_RATE', '0.01');
-- 列の値ごとにモデルを分割したい場合に指定
-- INSERT INTO ad_model_setting values ('ODMS_PARTITION_COLUMNS','列名');
COMMIT;
call DBMS_DATA_MINING.CREATE_MODEL(
model_name => 'AD_MODEL', -- モデル名
mining_function => 'CLASSIFICATION', -- マイニングファンクション
data_table_name => 'SENSOR_TABLE', -- 学習対象の表、またはビュー名
case_id_column_name => 'TIME_ID', -- 一意の値を持つ列
target_column_name => NULL, -- One Class SVMは教師なし学習のためターゲット列なし
settings_table_name => 'AD_MODEL_SETTING' --設定テーブル名
);
モデル作成ケース2 : モデル作成時に設定表を永続化しないケース
DBMS_DATA_MINING.CREATE_MODEL2パッケージの中でWHERE句を書けるメリットあり。
-- 既に同名のモデルが存在する場合はdrop
call DBMS_DATA_MINING.DROP_MODEL('AD_MODEL');
DECLARE
v_setlst DBMS_DATA_MINING.SETTING_LIST;
BEGIN
-- 設定表に指定した内容と同義
v_setlst('ALGO_NAME') := 'ALGO_SUPPORT_VECTOR_MACHINES';
v_setlst('SVMS_KERNEL_FUNCTION') := 'SVMS_GAUSSIAN';
v_setlst('SVMS_OUTLIER_RATE') := '0.01';
-- v_setlst('ODMS_PARTITION_COLUMNS') := '列名'; -- 列の値ごとにモデルを分割したい場合に指定
DBMS_DATA_MINING.CREATE_MODEL2(
model_name => 'AD_MODEL',
mining_function => 'CLASSIFICATION',
data_query => 'SELECT time_id,
sensor1,
sensor2,
sensor3,
sensor4
FROM sensor_table
WHERE get_time >= to_date(''2022/12/01 05:00:00'', ''YYYY/MM/DD HH24:MI:SS'')
',
set_list => v_setlst,
case_id_column_name => 'TIME_ID',
target_column_name => NULL
);
END;
/
作成されたモデルは all_mining_models ビューをはじめ、all_mining_models_[attributes|setting|etc...] で確認できます。
モデルへの適用
モデルに適用して異常確率を算出する
-- PREDICTION_DETAILSの戻り値がXMLTypeのため
set long 10000
SELECT a.*,
ROUND(
PREDICTION_PROBABILITY (ad_model, -- モデル名
0 -- 予測するターゲットの値、0は異常を示すため異常確率が算出される、1を指定すると正常
USING *), -- 全ての列を使用
2) AS ad_probability, -- 異常確率
PREDICTION_DETAILS (ad_model, -- モデル名
0, -- 予測するターゲットの値、0は異常を示す
3 ABS -- 予測に影響した上位3属性を表示
USING *) -- 全ての列を使用
AS ad_details -- 異常要因
FROM sensor_table a
ORDER BY 6 DESC -- 異常確率の高い順に表示
FETCH FIRST 10 ROWS ONLY -- 上位10行のみ表示
;
PREDICTION_DETAILSの戻りはXMLTypeで以下の構造。
<Details algorithm="Support Vector Machines" class="0">
<Attribute name="列名" actualValue="値" weight="xxx" rank="1"/>
<Attribute name="列名" actualValue="値" weight="xxx" rank="2"/>
<Attribute name="列名" actualValue="値" weight="xxx" rank="3"/>
</Details>
そのため以下のように要素と属性を指定した抽出も可。
-- 最も影響力の高い列を抽出する例
CAST(XMLQUERY('Details/Attribute[@rank=1]/@name' PASSING ＜XMLType列＞ RETURNING CONTENT) AS VARCHAR2(2000))
適用したいデータが表にない場合は直接ファンクションに値を指定できます。
SELECT ROUND(
PREDICTION_PROBABILITY (ad_model,
0
USING 'aaaaa' as time_id,
42280 as sensor1,
29024 as sensor2,
147236 as sensor3,
57420 as sensor4),
2) AS ad_probability
FROM DUAL;
AD_PROBABILITY
--------------
.62
Oracle Machine Learningの隠れメリット
自動データ準備（ADP）という機能により前処理を簡略化させることができます。デフォルトでADPは有効になっていますが明示的に無効にすることもできます。
- 正規化不要
上記のデータとモデル構築をした際に、「Scikit-learnでのMinMaxScalerのようなことやらなくていいの？」と思いませんでしたか？量的変数に対して勝手にやってくれるため手動での正規化は不要です。 - 一意列に文字列ok
一意列に文字列入っててもいいんです。一意であればいいんです。ちなみにcharなどの特徴量列に文字列が入っていても勝手にエンコーディングしてくれます。 - 欠損値許容
質的データ（char/varchar2）の場合は最頻値に、量的データ（number）の場合は平均値に自動で変換されます。
参考：Oracle Data Mining ユーザーズガイド 19c 自動データ準備の理解
Reference
様々なアルゴリズムごとのサンプルが以下に掲載されていますのでもしよろしければご参照ください。
https://github.com/oracle-samples/oracle-db-examples/tree/main/machine-learning/sql/19c