プログラミング

pythonでsqlite3を使う




現場で、大量のnginxやinfo・Errorログを集計したり検索することがあります。
件数は数万件~数百万件くらいです。

が。

  • ログ集計を行う環境にDBやら集計用のツールが入っていない
  • 追加もできない

という制限があるため、Linuxに標準で用意されているもので何とかするしかありません。
AWSなのでAthenaが使えればいいのですが、他社管理のサーバーなので、それもできず。。。

仕方なくShellで検索するわけですが、単純に1ファイル内から1レコードを検索×100件やるとすると、100回ファイルを読み込むことになる(しかもファイルサイズがでかいので時間がかかる)ので、本当に非効率です。
DBが使えれば、もっと早く必要な情報が取得できるのに。。。

そんなこんなで数か月、無駄な検索時間をかけて頑張ってきたのですが。
先日pythonだと標準ライブラリにsqlite3が含まれていることを知ったので、ファイルを読み込んでInsert→selectできるようにしてみました。

サンプルファイル

ここに置きました。

  • sqlite.py:sqlite3を扱うための処理
  • users.csv:ユーザーデータサンプル
  • RentalList.csv.gz:本の貸し出し履歴を書いたつもり(途中で力尽きたので適当なデータ)

sqlite.py

DBの作成

sqlite3は1つのファイルで1DBを管理します。
connectで指定したファイルが存在しなければ新規作成、存在すれば作成済みのデータを読み込みます。

テーブルの作成

テーブルを作成するためのクエリ
対象テーブルが未作成の時だけ作成するよう、「if not exists」を付けます。

これを毎回書いてもいいのですが、書き間違いが減るようにテーブル名とkeyを渡してクエリを自動生成することにしました。

ファイルからデータを読み込む

ファイルからデータをインポートします。
クエリは、?とタプルを渡す方法もありますが、辞書化したほうがよさそうだったので以下のようになりました。

このクエリも、関数の中で作成するようにします。

対象のファイルは.logと.gzがあるので、gzファイルを未解凍のまま読み込めるように処理を分けます。
非圧縮のサンプルファイルは.csvにしていますが、.logでも.txtでもOKです。

RentalListはprimary keyがオートインクリメント以外設定していないので、実行した回数分データが追加されます。

select文で情報を取得する

selectのクエリは関数化すると自由度が下がってしまうので、テキストで直接渡すようにしました。

Table CreateとInsertはDBを渡していましたが、selectの場合はcursorを渡します。
(cursorが何者なのかはよくわかってません。。。)

tableを2つ作成したので、JOINも試してみます。

実行結果

最後にDBを閉じる

最後にDBを閉じておしまい。
withを使うとcloseの書き忘れを防げる、とのことでしたが、今回はopenの処理を分けるために使いませんでした。

sqliteはどれくらいのデータ数まで耐えられるの?

処理はできたわけですが、今回必要としている「数百万件のデータ管理」に耐えられないと意味がありません。

SQLiteにpythonで数億行いれてみた

を見る限り、数億行でも問題なさそうですが、レコードの内容やPCのスペックにもよりますし、試してみないとですね。
今度現場で試験してみようと思います。

-プログラミング
-

© 2024 そんなこと猫でもできる Powered by AFFINGER5