slite3に分析対象データを高速に登録したいときの注意点

3086 ワード
SQLite3 Python Python テキストリンク

概要

  • データ分析で元データをテキストファイルのまま扱っていたが、テキストファイルだと処理のたびに読み込み時間がかかり嫌になってきた。
  • dbに入れてしまえば集計も簡単なのにと思い始めた。
  • MySQLやPostgreSQLを立てるのも面倒なので、sqlite3に入れることにした
  • pythonを使ってINSERTの時間が短くなるように工夫した。740,746件のデータを1分以内にINSERTできた。

create table

  • 処理対象データ(テキストファイル)を項目に分けて格納することにした。
  • 集計しやすくするため、年月日のカラムも作成した。
  • ハンドリングしやすくするため項番も入れた。
  • データが増えたら、create tableしなおす予定で進めた。
  • そのために、insert時間はトイタイム(10分前後)以内にしたい

import sqlite3

db_file = 'db.sqlite3'

drop_table = 'drop table logs'
create_table = 'create table logs("index" integaer, log text, log_type text, date text,ipaddress text, ...)'

db = sqlite3.connect(db_file)
dbc = db.cursor()
#dbc.executr(drop_table)
dbc.executr(create_table)
db.commit()
dbc.close()
db.close()

insert(execute板)

  • 処理対象データは、740,746件ある。
  • テストで1,000件登録してみた
  • 約90秒かかった
  • ざっくり740倍すると、66,600秒。3,600秒で割ると約18時間。待ってられない。

import sqlite3

logs = getLogLines('./log')
db_file = 'db.sqlite3'

insert_sub = 'insert into logs ("index", log, log_type, date, ipaddress, ...)'
select_sub = 'select log from logs where date=? and ipaddress=?'

db = sqlite3.connect(db_file)
dbc = db.cursor()

max = len(logs)
for index, log in enumerate(logs):
    dbc.execute(select_sub, [ log[1], log[2] ])
    data = dbc.fetchall()
    if 0 == len(data):
        insert_list = createInsertList(log)
        dbc.executr(insert_sub,insert_list)
        print( index, '/', max)

db.commit()
dbc.close()
db.close()

insert(executemany板)

  • 調べた結果、まとめてinsertするexecutemanyが早いと判った。
  • 10,000件まとめて実行するプログラムを書いて時間を計ってみた。
  • 入っているかの確認も今回は除いてみた。
  • 結果、12秒くらいで740,746件がINSERT出来た。5,000倍くらい早い。
  • 有無判定を入れてもトイレタイムで終わりそう。

import sqlite3

logs = getLogLines('./log')
db_file = 'db.sqlite3'

insert_sub = 'insert into logs ("index", log, log_type, date, ipaddress, ...)'
select_sub = 'select log from logs where date=? and ipaddress=?'

db = sqlite3.connect(db_file)
dbc = db.cursor()

multiple_insert_list = {]}
for index, log in enumerate(logs):
    insert_list = createInsertList(log)
    multiple_insert_list.append(insert_list)
    mod = index % 10000
    if 0 == mod:
        if 0 != len(multiple_insert_list):
            dbc.executr(insert_sub, multiple_insert_list)
            multiple_insert_list = []

dbc.executr(insert_sub, multiple_insert_list)

db.commit()
dbc.close()
db.close()

まとめ

  • pythonでsqlite3にまとめてINSERTするときは、executemanyで1,000～10,000件をまとめてあげるのがお勧めです。
微信公衆プラットフォームの返信メッセージの改行方法
pythonのsetUp,setUpClass,およびsetUpModuleの違い