Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
1
Help us understand the problem. What is going on with this article?
@ezmscrap

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

More than 1 year has passed since last update.

概要

  • データ分析で元データをテキストファイルのまま扱っていたが、テキストファイルだと処理のたびに読み込み時間がかかり嫌になってきた。
  • 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件をまとめてあげるのがお勧めです。
1
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ezmscrap

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
1
Help us understand the problem. What is going on with this article?