外部ローダーでスクリプトが用意されているのは以下のDBになります。
- Db2
- Netezza
- MS SQL Server
- Oracle
- Teradata
しかしながら、スクリプトを用意すればそのほかのDBに対しても同じ仕組みを使うことができます。
スクリプトのカスタマイズの参考として、SPSS Modelerの外部ローダーのアーキテクチャーとスクリプトの中身をDb2を例に解説します。
アーキテクチャー
アーキテクチャーを図にすると以下のようになっています。
「DBエクスポート」ノードの詳細設定
外部ローダーは「DBエクスポート」ノードの詳細で設定します。なお、「テーブルの作成」にチェックを付けておくと、外部ローダーを使う場合にもModelerがCREATE TABLE文を発行してくれます。
「外部ローダー経由」にチェックを付けます。ログは問題判別のために出力してくことをお勧めします。
なお、カスタマイズをしたスクリプトを使う場合には「ローダープログラムを指定」にpythonのスクリプトを指定します。
①DB2エクスポートノードの実行
これで「DBエクスポート」ノードを実行すると、まずスキーマファイルとデータファイルがC:\Program Files\IBM\SPSS\Modeler\18.4\config\options.cfgで指定したtemp_directoryに出力されます。
このテスト環境ではtemp_directory,"C:/temp/modelertmp"と指定してあります。ファイル名は実行の度に変化します。そして実行後には自動的に削除されます。データファイルがいったん全て出力されますので、このディレクトリには十分な空き容量が必要です。
7234-1A40-3B8C-2-1.binはバイナリ・ファイルですが、特に使っている形跡がありませんでした。
7234-1A40-3B8C-2-2.bin(①a)は中身はXMLファイルでテーブルのスキーマ情報が入っています。列名はHEXにエンコーディングされていました。
<?xml version="1.0" encoding="UTF-8"?><DBSCHEMA version="1.0">
<table append_existing="false" commit_every="10000" date_format="YYYY-MM-DD" delete_datafile="false" delimiter="\t" time_format="HH:MM:SS">
<column encoded_name="494E54" name="INT" position="0" type="integer"/>
<column encoded_name="464C4F4154" name="FLOAT" position="0" type="real"/>
<column encoded_name="535452" name="STR" position="0" size="1" type="char"/>
<column encoded_name="444159" name="DAY" position="0" type="date"/>
<column encoded_name="5453" name="TS" position="0" type="datetime"/>
<column encoded_name="54494D45" name="TIME" position="0" type="time"/>
</table>
</DBSCHEMA>
7234-1A40-3B8C-2-bulkload-1.txt(①b)がデータファイルです。デフォルトではタブ区切りのファイルになっています。外部ローダーの詳細画面で指定ができます。
なお、Windows環境で実行をしたので、ファイルの文字コードはShift JISでした。
1 1.000 あ 2000-01-31 2022-01-31 23:59:59 13:20:58
②db2load.pyの実行
そしてこれらのファイルを引数にして、外部ローダースクリプトのdb2load.pyが起動します。起動のコマンドはexloder.log_Server(②a)に記録されています。スクリプト内のprintなどの標準出力の結果もこのファイルに記録されますので、スクリプトのデバッグにも活用できます。
Execute Command: "C:\Users\xxxxx\AppData\Local\Programs\Python\Python38\python.exe"
"C:\Program Files\IBM\SPSS\Modeler\18.4\scripts\db2_loader.py"
C:\temp\MODELE~1\7234-1A40-3B8C-2-2.bin
C:\temp\MODELE~1\7234-1A40-3B8C-2-bulkload-1.txt
"" BLUDB "hkwd " "xxxxxx " DATATYPETEST "" C:\temp\exloder.log 1
③LOADコマンドの実行
db2load.pyはLOADのコマンドをcontrolfileとして出力します。ファイル名のtmpwep3s33u(②b)はやはり毎回変わる一時的な名前です。
このコマンドファイルがdb2load.pyの中で呼ばれてLOADが実行されます。
スキーマファイルから読み取った日付のフォーマットなどが指定されています。
また、codepageも指定されています。
CONNECT TO "BLUDB" USER "hkwd " USING 'xxxxxxxxx';
LOAD CLIENT FROM "C:\temp\MODELE~1\7234-1A40-3B8C-2-bulkload-1.txt" OF del
MODIFIED BY coldel0x09 anyorder
timeformat="HH:MM:SS"
dateformat="YYYY-MM-DD"
timestampformat="YYYY-MM-DD HH:MM:SS"
codepage=943
REPLACE INTO "DATATYPETEST"(
"INT" ,
"FLOAT" ,
"STR" ,
"DAY" ,
"TS" ,
"TIME"
) NONRECOVERABLE;
LOADコマンドのログはログファイルexloder.log(③a)として出力されます。
データベース接続情報
データベース・サーバー = DB2/LINUXX8664 11.5.7.0
SQL 許可 ID = HKWD
ローカル・データベース別名 = BLUDB
SQL3109N ユーティリティーが、ファイル
"C:\temp\MODELE~1\7234-1A40-3B8C-2-bulkload-1.txt"
からデータのロードを開始しています。
SQL3500W ユーティリティーが "11/17/2022 07:10:15.451408" に "ANALYZE"
フェーズを開始しています。
SQL3519W ロード整合点が開始されました。 入力レコード・カウント = "0"
SQL3520W ロード整合点が成功しました。
SQL3515W ユーティリティーは、"11/17/2022 07:10:17.289947" に "ANALYZE"
フェーズを完了しました。
SQL3500W ユーティリティーが "11/17/2022 07:10:17.315527" に "LOAD"
フェーズを開始しています。
SQL3110N ユーティリティーが処理を完了しました。 "1"
行が、入力ファイルから読み取られました。
SQL3519W ロード整合点が開始されました。 入力レコード・カウント = "1"
SQL3520W ロード整合点が成功しました。
SQL3515W ユーティリティーは、"11/17/2022 07:10:18.460559" に "LOAD"
フェーズを完了しました。
SQL3500W ユーティリティーが "11/17/2022 07:10:18.584980" に "BUILD"
フェーズを開始しています。
SQL3213I 索引付けモードは "REBUILD" です。
SQL3515W ユーティリティーは、"11/17/2022 07:10:19.983359" に "BUILD"
フェーズを完了しました。
読み込まれた行数 = 1
スキップされた行数 = 0
ロードされた行数 = 1
拒否された行数 = 0
削除された行数 = 0
コミットされた行数 = 1
外部ローダースクリプトの解説
db2load.pyの解説をします。
まず39行目からcodepageの設定があります。
データファイルのcodepageとDBのcodepageが異なる場合に指定することになっています。
# optional:
# if the operating system encoding of the host running this script and the
# database encoding are different, you should set the codepage here to
# reflect the operating system ncoding
codepage = '943'
# examples
#
# Windows running japanese SJIS
# codepage = '943'
200行目からがメインのプログラムになります。
まず、206－215で引数を受け取っています。
schemafile = getarg(sys.argv[1])
datafile = getarg(sys.argv[2])
servername = getarg(sys.argv[3])
databasename = getarg(sys.argv[4])
username = getarg(sys.argv[5])
password = getarg(sys.argv[6])
tablename = getarg(sys.argv[7])
ownername = getarg(sys.argv[8])
logfilename = getarg(sys.argv[9])
rowcount = getarg(sys.argv[10])
これは以下のようにコマンドログで入力される引数が確認できます。なお、rowcountは使われていませんでした。
Execute Command: "C:\Users\xxxxx\AppData\Local\Programs\Python\Python38\python.exe"
"C:\Program Files\IBM\SPSS\Modeler\18.4\scripts\db2_loader.py"
C:\temp\MODELE~1\7234-1A40-3B8C-2-2.bin
C:\temp\MODELE~1\7234-1A40-3B8C-2-bulkload-1.txt
"" BLUDB "hkwd " "xxxxxx " DATATYPETEST "" C:\temp\exloder.log 1
それ以外の引数は「ローダーの付加オプション」で指定できるようになっています。
222－232行で「ローダーの付加オプション」に「-alias」の引数が含まれていたら、databasenameをそのalias名に置き換えています。
#
# search extra arguments after position 10
# for -alias <db2-alias> and remove
#
for arg in range(11, len(args)):
if args[arg] == '-alias':
if arg + 1 < len(args):
del args[arg]
databasename = args[arg]
del args[arg]
break
234－242行目でスキーマファイルのXMLを読み込んで、それをもとにcontrolfileつまりLOADコマンドのファイルを生成しています。
# parse the schemafile
#
dom = xml.dom.minidom.parse(schemafile)
#
# write the control file
#
controlfile = TemporaryFile().name
write_control(dom, controlfile)
関数write_controlは149－196行目で定義されています。
以下はwrite_controlの中でCONNECTコマンドとLOADコマンドを生成している部分です。
outfile.write('CONNECT TO "' + databasename + '" USER "' + username + '" USING \'' + password + '\';\n')
outfile.write('LOAD CLIENT FROM "' + datafile + '" OF del MODIFIED BY ')
その後はデリミター、Timeformat、codepage、INSERTかREPLACEか、テーブル名などを指定しています。
write_controlで生成されるcontrolfileは、先ほども確認した以下のような内容です。
CONNECT TO "BLUDB" USER "hkwd " USING 'xxxxxxxxx';
LOAD CLIENT FROM "C:\temp\MODELE~1\7234-1A40-3B8C-2-bulkload-1.txt" OF del
MODIFIED BY coldel0x09 anyorder
timeformat="HH:MM:SS"
dateformat="YYYY-MM-DD"
timestampformat="YYYY-MM-DD HH:MM:SS"
codepage=943
REPLACE INTO "DATATYPETEST"(
"INT" ,
"FLOAT" ,
"STR" ,
"DAY" ,
"TS" ,
"TIME"
) NONRECOVERABLE;
controlfileができるとまたmainに戻り、今度はdb2コマンドを生成します。
追加の引数はこの時に一番後ろに並べられています。
これを見ると、LOADコマンドに追加できる引数ではないことがわかります。LOADコマンドをカスタマイズしたい場合には上の-aliasの指定のように、別途抜き出してcontrolfile生成の際に利用することができるでしょう。
#
# build the command to invoke db2 clp
#
commandstr = db2_cmd
commandstr += ' -tf '
commandstr += ' \"' + controlfile + '\" '
for arg in range(11, len(args)):
commandstr += ' '
commandstr += args[arg]
if logfilename == '':
if os.name == 'nt':
logfilename = 'NUL'
else:
logfilename = '/dev/null'
commandstr += '> \"' + logfilename + '\" 2>&1'
以下のようなコマンドが生成されます。これはログには出力されないので、確認したい場合にはprint(commandstr)でコマンドログ exloder.log_Serverに出力します。
db2cmd /c /w /i db2 -tf "C:\Users\xxxxx\AppData\Local\Temp\tmpwep3s33u" > "C:\temp\exloder.log" 2>&1
db2コマンドはos.systemで外部プロセスとして実行されます。
#
# execute the command
#
returncode = os.system(commandstr)
カスタマイズのヒント
- Db2やOracleなど歴史的にLOADコマンドは別exeやCLIで用意されることが多かったのでこのようにos.systemで呼び出しているのだと思いますが、pythonのAPI経由でLOADを呼び出せるようなRDBであれば、わざわざcontrolfileにコマンドを書き出す必要はないと思います。その方がオプションの指定もきれいに書けそうな気がします。
- 最近のDBaaSはデータファイルを一度オブジェクトストレージに格納してからテーブルにコピーするというオペレーションが多いと思います。その場合はオブジェクトストレージのAPIをPythonから呼び出して、データファイルをアップロードするような処理を入れれば実現可能だと思います。
- Modelerからどんなデータファイル、スキーマファイルそしてcontrolfileが出力されているかを確認したくなると思います。ただ、これは終了時にきえてしまうので、スクリプトを書き換えて退避しておくのがおすすめです。268行目の前で、以下のように別ディレクトリに退避してしまえば削除されずに済みます。特にデータファイルはスクリプトではなくModelerが消してしまいます。
import shutil
shutil.copyfile(schemafile, "c:/temp/"+ os.path.basename(schemafile))
shutil.copyfile(controlfile, "c:/temp/"+ os.path.basename(controlfile))
shutil.copyfile(datafile, "c:/temp/"+ os.path.basename(datafile))
- 毎回Modelerから呼び出してテストするのは大変だと思います。引数の部分をスクリプト内に書いてしまって、退避しておいたデータファイルとスキーマファイルをおいて、単独で動くスクリプトにしてVS Codeなどのプログラミング・エディターで書けば、ブレークポイントなども設定できて各段に書きやすくなりますのでお勧めします。具体的には206－215をコメントアウトして以下のように引数をベタ書きしてしまいます。
schemafile = "C:\\temp\\MODELE~1\\304-4A54-1289-2-2.bin"
datafile = "C:\\temp\\MODELE~1\\304-4A54-1289-2-bulkload-1.txt"
servername = ""
databasename = "BLUDB"
username = "hkwd"
password = "xxxxxxx "
tablename = "DATATYPETEST"
ownername = ""
logfilename = "C:\\temp\\exloder1.log"
rowcount = "1"
VS Codeで読み込んだ例です。キーワードも色分けしてくれて読みやすくなっています。
参考：SPSS Modelerの外部ローダーを使う（Db2編) - Qiita