spoolという機能を使えば一撃でCSVが出力できることを知らずに
無理矢理batをこねくり回してしまった話である
それ無しにしても流石にもう少し簡単にやれる方法はあるだろと我ながら思う
背景
- 自己紹介
- 元々はSEだったが現在は事業会社で社内SE的なことをしている
- 元々はゴリゴリにpythonを書いていた
- とりわけSeleniumを使っていた期間が長く一時期teratailではタグランキング1位を独占していた
- プログラムを書くのはベンダーさん任せで要件定義ばっかり
- 上述の事業会社をこの記事ではBakkinRobbinsonというアイスクリーム会社ということにしておく
- 元々はゴリゴリにpythonを書いていた
- python以外はほぼやったことがなくSQLもselect * from くらいしか分かっていなかった
- 元々はSEだったが現在は事業会社で社内SE的なことをしている
- 以下のような定形業務が営業部のお姉さんの依頼で3日に1回くらいの頻度でいきなり発生する
- DBにアクセスし以下のSQLを打つ
- 結果をCSV化してお姉さんに渡す
- SQLクライアントソフトを使っているのでselect文を打ったあとは1クリックでCSVにできる
ALTER SESSION SET CURRENT_SCHEMA=bakkin;
select syohin_name,zaikosuu from imanozaiko where saisyuukousinnnichiji > 直近50時間 order by syohin_id asc;
| syohin_name | zaikosuu |
|---|---|
| ポッピーシャワー | 3 |
| シトラスミント | 4 |
| バニラDEバニラ | 1 |
- このテーブルの値は各店舗のレジのシステムなどとつながっており、毎秒レベルで値が更新されていく
- こちらがMtg中などの理由で反応が遅れてしまうことがあり、巡り巡って販売機会損失につながるので、お姉さんの方で勝手に最新状況を取得できないかという話が持ち上がった
- とはいえこれだけのためにベンダーさんにお金を払って実装してもらうのも微妙。。。
- 自力で実装することとなったがセキュリティ的な問題もあり以下の制約がつくこととなった
- お姉さんのWindowsPCにツール類をインストールすることはできない
- pythonもダメ(絶望)
- サーバ等を別途用意するのもダメ
- バッチで使えるライブラリ的なやつもダメ
- デフォルトでWindowsに入っているものだけでなんとかする
- 幸いにも別のシステムの都合でoracleDBのsqlクライアントは入っている
- PCが古いのか知らないがpowershellが入っていないのでpowershellも使えない
- お姉さんがDB接続情報を知り得てはいけない
- そんなもの隠したところでwireshark使えば...というのはなんとか上長を誤魔化せた
- お姉さんのWindowsPCにツール類をインストールすることはできない
- というわけでバッチでなんとかすることに
- 最終的に渡す際はexe化して中身が見えないようにする
完成物
お姉さんのPCにリモートアクセスし以下batをexe化し、batは削除する
会社のPCを見ながら写経したのでミスがあるかもしれない
@echo off
setlocal enabledelayedexpansion
set APP_ROOT=%~dp0
set ORA_USER_NAME=bakkinid
set ORA_PASSWORD=bakkinpw
set ORA_IP_ADDR=bakkin.hogehogehoge.ap-northeast-1.rds.amazonaws.com
set ORA_PORT_NUMBER=1234
set ORA_SERVICE_NAME=robinson
set PLSQL_FILE=!APP_ROOT!¥sql.sql
type nul > !PLSQL_FILE!
set SQL_RESULT_FILE=!APP_ROOT!¥temp.txt
type nul > !SQL_RESULT_FILE!
set time2=!time: =0!
set OUTPUT_FILE=!APP_ROOT!result!date:~0,4!!date:~5,2!!date:~8,2!!time2:~0,2!!time2:~3,2!!time2:~6,2!.csv
type nul > !OUTPUT_FILE!
set sql_result=hensusengennotamenoiminonaimojiretsu
echo set feedback off >> !PLSQL_FILE!
echo set serveroutput on >> !PLSQL_FILE!
echo VARIABLE hoge VARCHAR(10000); >> !PLSQL_FILE!
echo ALTER SESSION SET CURENT_SCHEMA = bakkin; >> !PLSQL_FILE!
echo declare >> !PLSQL_FILE!
echo begin >> !PLSQL_FILE!
echo SELECT REPLCE(REPLACE(RTRIM(XMLAGG(XMLELEMENT(e,FUGA,'iiiii').EXTRACT('//text()') ORDER BY NULL).GetClobVAL(),'iiiii'),' ','_'),' ','_') into :hoge FROM (SELECT syohin_name ^|^| '}' ^|^| zaikosuu ^|^| 'iiiii' ^|^| ROW_NUMBER() over (order by syohin_id asc) AS ^"FUGA^" from imanozaiko where saisyuukousinnnichiji > 直近50時間); >> !PLSQL_FILE!
echo dbms_output.put_line('aaaaaaaaaasyohin_name}zaikosuu' ^|^| :hoge ^|^| 'iiiiiuuuuuuuuuu'); >> !PLSQL_FILE!
echo end; >> !PLSQL_FILE!
echo / >> !PLSQL_FILE!
echo exit WARNING >> !PLSQL_FILE!
echo exit >> !PLSQL_FILE!
for /F "DELIMS=" %%a in ('sqlplus !ORA_USER_NAME!/!ORA_PASSWORD!@!ORA_IP_ADDR!:!ORA_PORT_NUMBER!/!ORA_SERVICE_NAME! @!OUTPUT_FILENAME!) do (
set sql_result=!sql_result!%%a
)
del !PLSQL_FILE!
set lb=(半角スペース)
set la=_
set sql_result0=!sql_result:%lb%=%la%!
set sql_result1=%sql_result0:*aaaaaaaaaa=%
set back=%sql_result1:*uuuuuuuuuu=%
set sql_result2=!sql_result1:uuuuuuuuuu%back%=!
echo %sql_result2% >> %SQL_RESULT_FILE%
for /L %%r in (0,1,10000) do (
for /f "delims=" %%X in (!SQL_RESULT_FILE!) do (
del !SQL_RESULT_FILE!
set X=%%X
set Y=!X:*iiiii%%riiiii=!
if !X!==!Y! (
goto :end
) else (
call :disp !X! iiiii%%riiiii !Y!
set /a s=%%r+1
set SQL_RESULT_FILE=!APP_ROOT!¥temp.txt
type nul > !SQL_RESULT_FILE!
echo !Y! >> !SQL_RESULT_FILE!
)
)
)
:disp
set g=%1
set h=%2
set m=%3
set Z=!g:%h%%m%=!
echo !Z:}=,! >> !OUTPUT_FILE!
exit /b
:end
汚すぎる変数名を直す気にもなれないあたり自分なりに相当苦労したんだなと思う
解説とか
第1関門: バッチからDBへの接続
これはググればすぐに出てきた
サービス名ってなんだろうってくらいoracleDBのことを知らなかったが、
ほぼコピペで割と簡単に接続は確認できた
と言いつつ、sqlplusというのはoracleClientがないと接続できないとか、
PL/SQLがoracleDBのストアドプロシージャの特殊な言語だとか、
そもそもoracleDBにおけるスキーマとは何かとか、
この辺の知識すら持っていなかったので、後々苦労することとなった
ちなみに後からoracle silverの勉強をしたおかげで今はおおよそ把握できている
実行計画とかは全く分かっていない
第2関門: バッチにおけるPL/SQLの制約
beginだendだdeclareだがいまだにはっきりと分かっていない
スキーマの変更をどの位置に書けばいいのかは適当に試行錯誤して何故かうまくいった
バッチからselect文を打った時は1行1列の値しか返せないとググったら出てきたので
最初は元のSQLをwhere rownum < 1のように改変して作った
それをdbms_output.put_lineでバッチの変数sql_resultに当て込んでいる
一部エスケープ文字^を使い必要があることや
into文を用いることでバッチに値を返せることはこの時点で把握した
from句に子selectを打った方がやりやすいのもここで気づいた
実際に出力された1行1列のSQLをバッチで表示してみると
「接続できました」「接続を終了しました」的な余計な文字列が前後に登場することが分かった
それも環境や実行時刻によって値が微妙に変化する
この時点でaaaaaaaaaaなどの余計な文字列をあえて差し込んで
後から文字列置換で削除しようと決めていた
第3関門: 文字列の置換
実際のSQLを当てはめるのはまだ苦戦しそうだったので、
先に欲しいものが取得できた体でそれをカンマ区切りのCSV化するための
プログラムを整えることにした
pythonだったら秒で終わるような文字列整形だが
いかんせんバッチにはろくな文字列置換メソッドがない
そもそも変数の代入時にset hoge = "fuga"などとすると
イコールの直後からの "fuga"という7文字が入ってしまう仕様にも苦しめられた
ChatGTPに聞いてもエラーの出るプログラムばかり出力されて
腹が立ったのでteratail先生に頼った
set sql_result1=%sql_result0:*aaaaaaaaaa=%
set back=%sql_result1:*uuuuuuuuuu=%
set sql_result2=!sql_result1:uuuuuuuuuu%back%=!
欲しい文字列の直前にaaaaaaaaaaと差し込んでおいて、
欲しい文字列の直後にuuuuuuuuuuと差し込むとこれでうまくいった
元の文字列:置換したい文字列=置換後文字列と指定すると
元の文字列の中で当てはまる部分が全て置換されると分かってはいたが
それでもなお苦戦した
この時点で仕事の片手間でかなり恐ろしいことをしようとしている認識を持った
第4関門: CSV化のための整形
第3関門突破+正しくSQLが出力される前提で取り掛かった
正しくSQLが出力されると以下のような文字列になっている想定だった
無論}はデータ上絶対存在しないことは把握済みである
iiiii0iiiii}ポッピーシャワー}3iiiii1iiiiiシトラスミント}4iiiii2iiiiiバニラDEバニラ}1iiiii3iiiii
汚い・・・。あまりにも汚い・・・。
for /L %%r in (0,1,10000) do (
for /f "delims=" %%X in (!SQL_RESULT_FILE!) do (
del !SQL_RESULT_FILE!
set X=%%X
set Y=!X:*iiiii%%riiiii=!
if !X!==!Y! (
goto :end
) else (
call :disp !X! iiiii%%riiiii !Y!
set /a s=%%r+1
set SQL_RESULT_FILE=!APP_ROOT!¥temp.txt
type nul > !SQL_RESULT_FILE!
echo !Y! >> !SQL_RESULT_FILE!
)
)
)
:disp
set g=%1
set h=%2
set m=%3
set Z=!g:%h%%m%=!
echo !Z:}=,! >> !OUTPUT_FILE!
exit /b
:end
もはやどうやってこのような実装になったかは覚えていないが
変数で保持してfor文を回せないため1ブロック(iiiii数字iiiii値}値)を抜き出すごとに
逐一SQL_RESULT_FILEに残りを吐き出して
次のループでもう一度読み込みファイルを即削除する実装にした
変数Yが「残り」に当たる部分で変数Xが今回のforループで処理する値となる
そしてfor文を回しながら毎行毎行echo !Z:}=,! >> !OUTPUT_FILE!で}を,に置換して
最終的に取得するCSVに吐き出して処理している
デリミタ的にiiiii数字iiiiiを使っているがもっといい方法はなかったのだろうか
この数字をカウントアップするためにfor文を2重にする必要があることに
気づくのにも相当な時間がかかった
この辺りでspoolを使えば今やっていることが全部無駄になることが分かったが、
意地だけでこの後も進めていくことになる 本当に良くない
第5関門: PL/SQLの完成
ここが一番きつかった
echo SELECT RTRIM(XMLAGG(XMLELEMENT(e,FUGA,'iiiii').EXTRACT('//text()') ORDER BY NULL).GetClobVAL(),'iiiii') into :hoge FROM (SELECT syohin_name ^|^| '}' ^|^| zaikosuu ^|^| 'iiiii' ^|^| ROW_NUMBER() over (order by syohin_id asc) AS ^"FUGA^" from imanozaiko where saisyuukousinnnichiji > 直近50時間); >> !PLSQL_FILE!
LISTAGGという関数を使えば1行1列になることはすぐに知ったが
これで実装したところ長すぎる文字列には対応できないことが分かった
そこでXMLAGGという関数を使うことになったがこれが本当にしんどかった
何百回やってもエラーが出るのにどこがどう間違っているのかがエラーに出力されず
ひたすら挑戦するしかなかった
最終的に別列名を^" ^"で囲む必要があったことに気づくまで
本当に時間がかかったが、これもoracle silverを並行して勉強していなかったら
おそらく今でも気づくことはできなかったであろう
勉強は本当に大事である
第6関門: プログラム最終調整
ここからはウイニングラン
set sql_result=!sql_result!%%aがforループで回っている時に
何文字かごとに区切られ余計なスペースが勝手に入ってしまうことが分かった
また、元のDBの値のsyohin_nameにも事業部が勝手に
半角スペースや全角スペースの入った名前をつけているケースでは
想定通りに文字列整形が動かないことが分かった
そこで後者はREPLACE関数等を使い余計な文字列をアンダーバーで置き換えてみた
前者はoracleDBとは関係ないのでset sql_result0=!sql_result:%lb%=%la%!で
後から置き換えた
ようやく目的のCSVが出力された時は本当に泣きそうになった
第7関門: exe化
最終的にバイナリファイルにすることで、
DB接続情報を秘匿することは最初から決めていたが、
いざexe化しようとするとwindows標準のものではうまくいかないことが分かった
exe化というよりzip化に近く、exeを実行すると元のbatが復元されてしまう謎仕様となっている
上長に詫びを入れた上で3rd party製のbat->exe変換ツールを
利用することだけはなんとか許してもらえた
完成したexeをまずは同僚のPCで試してもらったところ
Windows Defenderに遮られて派手な警告画面が出てしまい、
結果的に部長にガチギレされてしまった・・・。反省。
ここは悩んだがどうしようもなさそうなので
お姉さんのPCにこの変換ツールを入れることだけは許してもらった
冒頭に書いた通りお姉さんのPCにRDPをして完了
やってみた感想
とにかく辛く時間がかかったが久しぶりに物事に挑戦する機会があったことはよかったと思う
そして勉強はとにかく大事であると改めて気付かされた
思えば大学受験以降ろくに勉強をした記憶がなく、
長い間人生をサボっていたことに気付かされる出来事であった