Posted at

HEX文字列でPostgreSQLへデータを登録してSQLで検索してみる

現在システム間連携を行うシステム(GWシステム)の開発に携わっており、受信電文および送信電文をログファイルに出力するようにしています。電文ログは、利用実績のレポートや障害発生時の解析などで利用します。

アプリケーションとしては・・・ロガー経由(具体的にはSLF4JのAPI経由)でログファイルにTSV形式で出力しておいて、PostgreSQLのCOPYコマンドを使って定期的またはオンデマンドでDBへ反映するような仕組みにしようかと思っています(=リアルタイム性は求められていないので)。


ログファイル

電文はバイナリ(intなど)を含むものがあるのでバイト配列をHEX文字列化し、出力項目はタブで区切る形式(TSV形式)にしようかと思っています。実際のログファイルとは異なりますが、以下のような感じです。

2019-10-05 23:00:00.001 XML  UTF-8 3C6D65737361676520747970653D22393939223E0A20203C646174613E666F6F3C2F646174613E0A3C2F6D6573736167653E

2019-10-05 23:00:00.002 JSON UTF-8 7B0A2020227479706522203A2022393939222C0A2020226461746122203A2022666F6F220A7D
2019-10-05 23:00:00.003 TEXT SJIS 30303130393939666F6F
2019-10-05 23:00:00.004 BIN SJIS 0000000903E7666F6F


NOTE:

HEX化が必須なのはバイナリデータ(例、intなど)が含まれる場合のみななのですが、XMLやJSON形式の電文には改行が含まれる可能性があるため何らかの対処が必要になります。これは項目を囲い文字(デフォルトだと「"」)で囲めば良いのですが・・・項目囲い文字のエスケープを行わないと正しくデータをインポートすることができません。そのため・・・テキストデータのみで構成される電文もHEX化してしまうことでエスケープ問題を解決しようかと思っています。

なお、ローカル環境やCI環境など電文ログをDBへ登録しない環境では、テキストデータのみで構成される電文はHEX化せずに出力するようにするつもりです。



1行目:XML電文

テキスト表現だと以下のようなXMLになります。

<message type="999">

<data>foo</data>
</message>


  1. type : 電文種別


2行目:JSON電文

テキスト表現だと以下のようなJSONになります。

{

"type" : "999",
"data" : "foo"
}


3行目:固定項目長(テキスト)電文

テキスト表現だと以下のような文字列になります。

0010999foo

↑ ↑ ↑
1 2 3


  1. 総電文長: 10

  2. 電文タイプ: 999

  3. データ: foo


4行目:固定項目長(バイナリ+テキスト)電文

テキスト表現だと以下のような文字列になります。なお{0x..}と表記している部分はバイナリデータ部になります。

{0x00000009}{0x03E7}foo

↑ ↑ ↑
1 2 3


  1. 総電文長(int = 4バイト): 9

  2. 電文タイプ(short = 2バイト): 999

  3. データ: foo


テーブル作成

電文ログをインポートするためのテーブルを用意します。


create table messages (
id bigserial primary key
, datetime timestamp not null
, format varchar(10) not null
, encoding varchar(20) not null
, hex_message text not null
);


ビュー作成

実システムでビューを使うかはわかりませんが、本エントリーではHEX文字列から生メッセージ(bytea型)とテキストメッセージ(text型)を参照できるようにするためのビューを作ります。


create view v_messages as
select
id
,datetime
,format
,encoding
,hex_message
,decode(hex_message,'hex') as raw_message
,case when format <> 'BIN' then convert_from(decode(hex_message,'hex'),encoding) else null end as text_message
from messages;

decode関数を利用するとHEX文字列をbyteaデータへ変換することができ、さらにconvert_from関数を利用するとbytea型の値をテキストデータへ戻すことができます。


インポート

TSVファイルからデータをインポートします。

dev=# \copy messages(datetime,format,encoding,hex_message) from 'messages.tsv' csv delimiter E'\t' header;


検索

電文形式毎に条件を指定して電文を検索します。

select id, text_message, hex_message from v_messages where 

(
format = 'JSON' and
json_extract_path_text(text_message::json,'type') = '999' and
json_extract_path_text(text_message::json,'data') = 'foo'
)
or
(
format = 'XML' and
xpath('/message[@type="999"]/data/text()', text_message::xml)::text[] = '{foo}'
)
or
(
format = 'TEXT' and
substring(raw_message, 5, 3) = '999' and
substring(text_message, 8, 3) = 'foo'
)
or
(
format = 'BIN' and
substring(raw_message, 5, 2) = '\x03e7' and
substring(raw_message, 7, 3) = 'foo'
)
;

検索結果は以下のようになります。

 id |     text_message     |                                             hex_message                                              

----+----------------------+------------------------------------------------------------------------------------------------------
1 | <message type="999">+| 3C6D65737361676520747970653D22393939223E0A20203C646174613E666F6F3C2F646174613E0A3C2F6D6573736167653E
| <data>foo</data> +|
| </message> |
2 | { +| 7B0A2020227479706522203A2022393939222C0A2020226461746122203A2022666F6F220A7D
| "type" : "999", +|
| "data" : "foo" +|
| } |
3 | 0010999foo | 30303130393939666F6F
4 | | 0000000903E7666F6F
(4 rows)


最後に

実システムではもうちょっと考慮すべきことはあると思いますが、やりたいことはできそうです。

ちなみに・・・当初はアプリケーションからリアルタイムでDBに登録する案もあがったのですが、DBが落ちていたらファイルへ出力してほしい!という要望があがったので・・・、それなら最初からファイルに出力しておいて、集計時や解析時にDBに入れればいんじゃない?ということになりました。