作成したlightGBMモデルをPMMLにしてTeradata内でスコア付与する

2022年6月25日 18:16

表記件、試してみた結果を個人的な忘備として記載します。TeradataはVMWare内にある17.10を、PythonはAnacondaで導入した3.9.12を使っています。あらゆるデータ、環境で試したわけではないので、ご参考になさる際は各自の自己責任でお願いします。
Teradataの環境については以下を前提にしています。

全体的な流れ

仕組みとして、Teradata側にBYOM: Bring Your Own Modelという追加モジュールをセットアップします。これはデータベース上で動かすJavaのプログラムらしく(SQLからは関数で呼び出す)、合わせてこれを動かすためのメモリー設定を行います。
そしてlgbmのモデルを作成し、scikit-learnのPMMLモジュールにて一部加工を加え、PMML形式でlgbmのモデルを吐き出します。作成されたモデルは.pmml形式のファイルなのでこれをTeradataのテーブル列にBLOB: Binary Large Object型で挿入します。
ユーザーに対して関数の実行権限付与を行い、挿入したモデルを用いてスコアリングを行うSQLを実行することにより、データベース内でのスコア付与が完了します。

何が良いのか

データベース内で変数表を作成してから、その一部を切り出してダウンロードし、ローカルのPythonでモデリングを行う、というやり方は機械学習のモデル構築を実務環境で行う際によくあるパターンの1つかと思います。
その際、学習用データはさほど大きくなくとも十分に安定する件数があればよいのですが、スコアリングは全分析対象に対して付与したい、しかしながらそのデータが大量で、しかもモデルがあるローカル側にダウンロードしなければならない、となると大変です。ローカル側のリソース、ネットワークリソース、そして貴重な時間をそれに費やさなければなりません。
データがあるデータベース側にモデルを持っていき、データベース側でスコアリングできればこのリソース消費を最小化できます。PMML: Predictive Model Markup LanguageはXML形式で作成したモデルを記述し、ポータブルにするための仕様で、BYOMは主にscikit-learn系で作成したPMMLモデルをTeradata内に持ち込んで、データベース内でのスコアリングを実現してくれるものですが、sciki-learnではないlgbmでこれをやってみた、のが以降でご紹介する内容です。色々探していたら以下の内容を見つけ、試してみました。

Teradata側での準備

まず、以下からBYOMのモジュールをダウンロードします(会員登録が必要)。欲しいのは.rpmのファイルです。記載時のファイル名はBYOM__linux_noarch.03.00.00.00-1.rpmとなっています。

あと、以下も参考にしました。

次にjvmop.txtというファイルを作成し、ファイル内に1行以下を書き込み用意します(UTF8)。

JVMOptions: -Xms2048m -Xmx8192m

ファイル名は以降で記載するコマンドと整合すればなんでもいいです。これはJavaのプログラムに割り当てるメモリーを拡張するもので、上述のユーザーガイドを参考にしました。これを行わないと、SQL実行時に関数実行用に割り当てられたメモリーが足りないとエラーになります。

RLoginのようなSFTP/SSHツールでTeradataが動いているLinuxOS上にアクセスし、上記の2ファイルを転送します。場所はどこでも良いですが、通常rootの下がデフォルトで開くと思うのでそこに置きました。

BYOM__linux_noarch.03.00.00.00-1.rpm
jvmop.txt

ファイルを置いたディレクトリで以下のコマンドを実行します。まずBYOMのインストールです。新たにmldbユーザーが作成され、そこに関数が格納されます。

export BYOM_CREATE_USER="y"
rpm -ivh BYOM__linux_noarch.03.00.00.00-1.rpm

応答メッセージは以下:

warning: BYOM__linux_noarch.03.00.00.00-1.rpm: Header V4 RSA/SHA256 Signature, key ID 4b121135: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:BYOM-03.00.00.00-1               ################################# [100%]
Fresh Install
03.00.00.00
BYOM_USER was not set. Using default BYOM_USER as 'mldb' to proceed with installation
BYOM_PASS is not set. Using default to proceed with installation
Using new user in batch_mode
DBA_USER is not set. Using default to proceed with installation
DBA_PASS is not set. Using default to proceed with installation
***********************************************
** DONE                                      **
** Please check /tmp/install_byom.log for details **
***********************************************

次にメモリーの拡張です。以下を実行します。

cufconfig -f jvmop.txt

応答メッセージは以下


 _______
    |                                |              |
    |    ___     __     ____         |    ____    __|__    ____
    |   /      |/  \    ____|    ____|    ____|     |      ____|
    |   ---    |       /    |   /    |   /    |     |     /    |
    |   \___   |       \____|   \____|   \____|     |__   \____|
 
    Release 17.10.03.01 Version 17.10.03.01
    UDF GDO Configuration Utility (Mar 2006)
 


UDFConfig_t size 122112 gdo size 122112 version 11


Exiting cufconfig...

設定を反映するため、最後にTeradataを再起動します。以下のコマンドを実行。

tpareset -f force

応答メッセージ。yで返答してあげます。

    You are about to restart the database
             on the system
                'localhost'
    Do you wish to continue (default: n) [y,n] y

例えば他のユーザーでこの関数を実行する場合には権限が必要です。jumboというユーザーにこれを使わせたいので、管理者(dbc)権限でTeradata Studioから以下のコマンドを実行します。

grant execute function on mldb.PMMLPredict to jumbo;

以上でTeradata側の環境設定は完了です。

Python上でのモデル作成

まずはストレートにモデルを構築する手順を踏みます。データはirisを使いました。これはscikit-learnに入っていますが、データベース側で変数表を作成した体にしたいので、scikit-learnからロードしてデータフレームにして、sqlalchemyおよびteradatasqlalchemyにてテーブルを作成し、番号付与したirisをそこに挿入しています。これがデータベース側で作成された変数表であるという体でそれをダウンロードしています。あとは普通にlgbmでモデルを構築し、評価します。
納得のいくモデルができたら、これを改めてpmmlのモデルとして作成し直します。[pmmlでのモデル作成]のセクションがこれに該当します。ここの部分は上述のページを参考にしました。何をしているかはいまいちわかりませんが、scikit-learnのgbmをpmmlにする仕組みとの差異をここで吸収しているのだと思います。[pmmlファイルのオフロード]のところで、これをローカルにファイルとしてエクスポートしています。

モデルのアップロード

作成されたモデルはlgbm_model.pmmlというファイル名で、Pythonの作業ディレクトリに吐き出されているはずです。Teradata Studioからこれをアップロードします。
まず、モデルを格納する空のテーブルを作成します。モデルを一意に引っ張ってくるためのmodel_id列をつけ、バイナリの形式でファイルを格納します(model列)。

--drop table jumbo.pmml_models; 
create set table pmml_models ( 
model_id integer, 
model blob 
) primary index (model_id) 
;

続いてこのテーブルにファイルを挿入します。?はパラメーターになっており、Teradata Studioでは実行するとパラメーターの入力を促されます。このポップアップ時に作成したlgbm_model.pmmlのファイル位置を指定します。これでアップロードは完了です。

insert into jumbo.pmml_models values ('1', ?);

SQLでのスコアリング

以下のSQLを実行します。上述の通り、mldbというデータベースに関数をインストールしました。このうちの1つがpmmlpredictというテーブル関数になります。パラメーターとしてスコアリングをしたい変数表(今回は同じデータをとりあえず利用)テーブル、モデルとして利用したいテーブルを指定してあげます。accumulateはそのまま出力させたい列を指定します。ここでは各行のユニークな識別子(serial_num)を追加しています。

select * from mldb.pmmlpredict ( 
	on (
		select 
		sepal_length, 
		sepal_width, 
		petal_length, 
		petal_width, 
		serial_num 
		from jumbo.lgb01_iris 
	) as inputtable 
	on ( 
		select * from jumbo.pmml_models where model_id=1 
	) as modeltable dimension 
	using 
	accumulate ('serial_num') 
) a1 
;

出力されるのはaccumulateで指定したserial_num列、何も入っていないprediction列(おそらく通常はここに判定結果が入るのでしょう)、そしてjson_report列の3つです。json_reportの中にjson形式で各3クラス(setosa, virginica, versicolor)への所属確率が格納されています。json_report列内は以下のような感じです。

{"probability(setosa)":1.1334930320743646E-5,"probability(virginica)":1.3507148177549787E-4,"probability(versicolor)":0.9998535935879038}

一旦これを別テーブルに格納します。

--drop table jumbo.lgb02_predictresult; 
create multiset table jumbo.lgb02_predictresult as ( 
	select * from mldb.pmmlpredict ( 
		on (
			select 
			sepal_length, 
			sepal_width, 
			petal_length, 
			petal_width, 
			serial_num 
			from jumbo.lgb01_iris 
		) as inputtable 
		on ( 
			select * from jumbo.pmml_models where model_id=1 
		) as modeltable dimension 
		using 
		accumulate ('serial_num') 
	) a1 
) with data 
;

後は好きに抽出して使えばよいと思いますが、とりあえず今回は横持ちのまま列分解し、数値型に変換しましょう。あまりきれいではないですが抽出用のSQLが以下です。unpack関数でカンマ区切りごとに列分解し、その後不要な文字列を削除したうえで数値型に変換しています。

select 
serial_num, 
cast(oreplace(setosa,'{"probability(setosa)":','') as decimal(17,16)) as setosa, 
cast(oreplace(virginica,'"probability(virginica)":','') as decimal(17,16)) as virginica, 
cast(oreplace(oreplace(versicolor,'"probability(versicolor)":','') , '}','') as decimal(17,16)) as versicolor 
from unpack ( 
	on jumbo.lgb02_predictresult
	using 
	targetcolumn ('json_report') 
	outputcolumns ('setosa','virginica','versicolor') 
	outputdatatypes ('varchar','varchar','varchar') 
	delimiter(',') 
) a1 
;

結果は以下のようになります。最初のテーブルと結合すればよかったのですが面倒くさがってこのままで。50行ずつで分類されているはずなのでよさそうです。

以上です。

///

#sql #analytics #pmml #python #lgbm #teradata

この記事が気に入ったらサポートをしてみませんか？