slite3に分析対象データを高速に登録したいときの注意点 #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件をまとめてあげるのがお勧めです。