NDJSON を SQL に変換

イベントログやアプリケーションの出力を、顧客テーブルと突き合わせたり、時間帯ごとに集計したりしたい場面があります。表計算ソフトでは面倒な集計も、SQL に入れてしまえば簡単に済むことがあります。

このページは NDJSON の各行を読み取り、対応する INSERT 文を1つずつ書き出します。

空でない1行につき、INSERT 文が1つ生成されます。40万行のログファイルからは、40万個の INSERT 文が出力されます。CREATE TABLE 文やトランザクションの区切りは含まれません。

したがって、読み込み側の行数とログの行数が一致していれば、読み込みが完全に終わったことを確認できます。

列の一覧はファイル全体に出てくるキーの和集合として決まります。最後の行にしか現れないフィールドがあっても、それは列として扱われます。そのため、ファイルを最後まで読み終えるまで、1文も書き出せません。

この性質のため、変換全体がブラウザーのメモリー上で行われます。数十メガバイト程度は問題なく処理できますが、非常に大きなファイルは事前に分割しておくほうが確実です。

リクエスト、エラー、ジョブ完了など、異なる種類のイベントが1つのログファイルに混在している場合、それぞれのキーの和集合がそのまま列になります。ある種類のイベントにしか存在しないキーは、他の行では NULL として埋められます。

一時的な分析であれば問題になりませんが、長く使うテーブルにするなら、種類ごとにファイルを分けてから変換するほうが、扱いやすいテーブルになります。

このページが生成するのは INSERT 文だけです。JSON からは、ある値が数値であることは分かっても、それが整数なのか小数を含む数値なのか、NULL を許すのかまでは分かりません。テーブルの定義は、変換した結果全体を見たうえで自分で書く必要があります。

列名を確認する際は、ファイル全体を変換した結果から拾うようにしてください。一部の行だけから作った列一覧では、まれにしか出てこないフィールドが漏れることがあります。

構造化されたログレコードにはネストがよくあります。request オブジェクトの中の method のようなフィールドは、request_method のような1つの列名にまとめられます。

1段か2段のネストであれば読みやすい列名になりますが、深くネストした構造体をそのまま変換すると、列名が非常に長くなります。必要な部分だけを事前に取り出しておくほうが、読みやすいテーブルになります。

数値はそのまま、真偽値は TRUE と FALSE、null は NULL として書き出されます。文字列は一重引用符で囲まれ、内部の一重引用符は二重に重ねて書かれます。この方式はどのデータベースでも読み取れます。

バックスラッシュはそのまま書き出されます。これは SQL 標準としては正しい扱いですが、初期設定の MySQL ではバックスラッシュをエスケープ文字として解釈するため、Windows のパスや正規表現を含む値では注意が必要です。

生成された文をそのまま1件ずつ実行すると、1文ごとに独立したトランザクションになり、非常に時間がかかります。ファイル全体を BEGIN と COMMIT で囲むだけで、読み込み時間は大きく改善します。

さらにインデックスを一時的に外してから読み込み、あとで作り直すことでも速度が上がります。件数が百万行を超えるような規模では、INSERT 文の代わりに一括読み込みの仕組みを使うほうが向いています。

いずれかの行が正しい JSON でない場合、変換は失敗し、該当する行番号が示されます。中途半端な結果は生成されません。

ログの途中が壊れているのは、たいてい書き込み中にプロセスが終了したことが原因です。行番号が分かれば、末尾を切り捨てて残りを確実に読み込むことができます。

変換に使われるのはこのページに読み込まれた JavaScript だけです。ログファイルはどこにも送信されません。

イベントログには IP アドレスやセッション識別子、時にはトークンが含まれることがあります。障害対応のさなかに、そうしたファイルを外部サービスへ送るのは避けたい場面のはずです。