データ連携はログ情報をデータベースに残すべき

プログラムでデータ連携をするときもETLツールを使用するときも、データベースにログ情報を入れておきましょう。

ETLツールでも標準はテキストログに格納しておくのみなので、データベースに入れるには独自でライブラリを作らねばなりません。工数がかかってしまいますが、メリットはあると思います。

今回はその理由を紹介します。

テキストファイルだとログの確認はどうやるのか

一般的にはログ情報はテキストファイルに保存します。自分でプログラムを作ったときでもテキストファイルにログを出力することを考えますし、ETLツールも出力したログを管理画面から閲覧することになります。

大変お手軽ですし数百万レコードなども保存できるので、保存には便利ですが、いざ何かがあったときに確認することが大変です。

ログファイルを開くのも大変ですし、どこがエラーになっているかを確認するのもしんどいですね。

ログをデータベースに保存するメリット

ログをデータベースに保存するメリットは以下の３つです。

• ・探したいログ情報をすぐに抽出できる
  ・その日のログを簡単に確認できる
  ・それぞれのイベントの処理時間を確認することができる

ちなみにそれぞれのメリットを最大限活かすにはSQL知識かBIツールのどちらかがあるといいですね。それでは細かく見ていきましょう。

探したいログ情報をすぐに抽出できる

テキストファイルだとログを開いて、今日のログをまとめて確認したり、エラーがないかをチェックしたりしますね。

SQLかBIツールが使えればログテーブルから一発でデータが表示されます。またエラーになっているものなどもすぐに表示できますね。ログ情報から特定の情報を抽出する手間は圧倒的に減るでしょう。

それぞれのイベントの処理時間を確認できる

さまざまなデータの連携をして、ジョブが増えると、管理にかかるコストが膨大になります。特に一連のイベントが一定時間で終わらなければ、各ジョブの時間を見直ししなければなりません。

そういった際にも普段からどのイベントがどれくらい時間がかかっているかを把握しておく必要があります。

テキストログからそれぞれの処理時間を把握するのは大変すぎます。Excelでいろいろ加工するのも大変です。でもテーブルにログを入れておけば、上図のようにガントチャートで情報を表現することが可能です。

上図のガントチャートは特定の日のイベントをまとめて表示しています。これをひとつのイベントに固定して、日付の推移を確認することもできますね。

もちろんSQLでもできますが、この場合はBIツールのガントチャートでわかりやすい表現ができるでしょう。

イベントの傾向を把握する

RDBとの接続でも設定次第ではタイムアウトエラーが起こります。私の体感だとSaaSの方がタイムアウトがはるかに多いです。しかもモノによっては要因が特定しにくいときもあります。

そのためにETLツールの中でさまざまな工夫を施します。下記のリンクはその中の例のひとつです。

Zenn

外部リンク

DataSpider（Dr.Sum Connect）でSalesforce API タイムアウトを乗り越える

これらの原因を特定するために、ログテーブルからの情報分析はとても重要です。

上図の例では曜日別にイベントをヒートマップに表しています。ここでわかるように特定のイベントは水曜と木曜にエラーとなる確率が多く、ここから仮説を立てることができます。

ログテーブルはどのような形式にするか

上で説明したようにログテーブルがあるとさまざまな分析ができ、業務効率をあげることが可能です。

それではここでは具体的にどのようなテーブルを用意すれば良いかを解説します。

用意するテーブルは２種類あります。それぞれを紹介しましょう。

ETL_Eventlogテーブル

まずひとつ目のテーブルは[ETL_Eventlog]です。このテーブルはイベントの件数＝レコードの件数となります。基本的な行動はUPDATEです。

ETL_Eventlogはこうやってつくる

CREATE文はこちらです。

CREATE TABLE ETL_EventLog (
  イベントID VARCHAR NOT NULL UNIQUE,
  イベント名 VARCHAR,
  ステータス VARCHAR,
  開始日時 VARCHAR,
  終了日時 VARCHAR,
  メッセージ VARCHAR);
ALTER TABLE ETL_EventLog MODIFY COMMENT 'DataSpiderの処理状況確認用（1:未処理 2:処理中 3:異常終了 4:正常終了）';

テーブルのコメントに記載しているステータスが重要です。

1:未処理 2:処理中 3:異常終了 4:正常終了

ここでエラーか否かをチェックできます。

重要なのは開始日時と終了日時が同じレコード内にあること

通常のデータベースの考え方では極力UPDATEよりもINSERTの方が効率が良いため、ステータスも主キーの中に入れ、正常終了と異常終了を別レコードに分けます。

しかしデータ分析の観点で見ると同レコードにあると時刻の引き算（＝イベントの処理時間）を簡単に算出することができます。いろいろなご意見があるとは思いますが、イベントログを作成する規模であればUPDATEにして、データ分析しやすい形をキープする方が望ましいのではないかと考えます。

イベント実行時にETL_Eventlogにする更新処理はこちら

UPDATE ETL_EventLog
SET
	ステータス = '2',
	開始日時 = '${タイムスタンプ}',
	メッセージ = '処理を開始します。'
WHERE
	イベントID = '${実行スクリプトID}'

正常終了時にETL_Eventlogにする更新処理はこちら

UPDATE ETL_EventLog
SET
	ステータス = '4',
	終了日時 = '${タイムスタンプ}',
	メッセージ = '正常終了しました。'
WHERE
	イベントID = '${実行スクリプトID}'

例外処理時にETL_Eventlogにする更新処理はこちら

UPDATE ETL_EventLog
SET
	ステータス = '3',
	終了日時 = '${タイムスタンプ}',
	メッセージ = '${エラーメッセージ}'
WHERE
	イベントID = '${実行スクリプトID}'

ETL_Eventlog_TRNテーブル

[ETL_Eventlog_TRN]テーブルは[ETL_Eventlog]テーブルと大きく構成は変わりませんが、以下の点が異なっています。

ETL_EventLogテーブルはUPDATE文をかけて、常に最新の情報のみを保持するテーブルです。かたやETL_EventLog_TRNテーブルはINSERTをしていく、情報を貯めるテーブルです。

日々のデータを蓄積するので、イベントごとの時系列推移を表すことが可能となります。

ETL_Eventlog_TRNはこうやってつくる

CREATE文はこちらです。

CREATE TABLE ETL_EventLog (
  イベントID VARCHAR NOT NULL UNIQUE,
  イベント名 VARCHAR,
  ステータス VARCHAR,
  開始日時 VARCHAR,
  終了日時 VARCHAR,
  メッセージ VARCHAR);
ALTER TABLE ETL_EventLog MODIFY COMMENT 'DataSpiderの処理状況確認用（1:未処理 2:処理中 3:異常終了 4:正常終了）';

ETL_Eventlog_TRNはイベント実行時にはテーブルに情報を書き込みません。ただ変数に処理開始時間のタイムスタンプは入れておきましょう。

正常終了時にETL_Eventlog_TRNにする更新処理はこちら

INSERT INTO ETL_EventLog_TRN (
	イベントID,
	イベント名,
	ステータス,
	開始日時,
	終了日時,
	メッセージ
) VALUES (
	'${実行スクリプトID}',
	'${実行スクリプト名}',
	'4',
	'${処理開始日時}',
	'${タイムスタンプ}',
	'正常終了しました。'
)

例外処理時にETL_Eventlog_TRNにする更新処理はこちら

以上のように２つのテーブルを作成しておくと、日々の管理が簡単になります。

さいごに

冒頭でお伝えしました通り、データ連携はテキストログでなくデータベースにログを書き込むようにすると、運用にかかるコストを大きく減らすことになります。

データ連携に携わる方々にぜひご覧になっていただけますと幸いです。

石井 亮介（りょうさん）

データパレード 代表取締役

TwitterContact

㈱データパレードの代表取締役で高田馬場の町中華のChief Data Officerをしています。 BIツールのセールスエンジニア・システムエンジニア・カスタマーサクセス歴10年以上経験、データエンジニア領域とコンサルティングが得意です。BIツール研究所・DMS Cubeなどデータ系コミュニティーのアンバサダーをしています