PythonからSqlite3に大量データをinsertしてみた（その2）

PythonからSqliteに大量データをinsertする際に1件ずつinsertを行うのでは無く、Bulkinsertを行ったら、どの程度高速化されるかを確認しました。Pythonに組み込まれているsqlite3クラスにはexecutemany()というメソッドが実装されていて簡単でした。

事前にsqliteでtestdbというDBを作っておいて、
sqlite> CREATE TABLE hash ( id INTEGER PRIMARY KEY , value TEXT );
でhashというテーブルを作成しておきます。
その後、neta.csvというファイルの中身をhashテーブルにinsertしていきます。

------------------------------------------------------------------------------------------

#!/usr/bin/env python

import csv

import sqlite3

import time

if __name__ == '__main__':

    start = time.time()

    c = sqlite3.connect('./testdb')

    i = 0

    l = []

    reader = csv.reader(open("./neta.csv"))

    for row in reader:

        id = row[0]

        value = row[1]

        t = (id,value)

        l.append(t)

        i += 1

        if i % 200000 == 0:

            c.executemany('insert into hash (id,value) values(?,?)',l)

            c.commit()

            l = []

    c.commit()

    c.close()

    print  time.time()-start

------------------------------------------------------------------------------------------

上記を実行したところ、13sで100万件のinsertが完了しました。

以前、1件ずつやった場合だと、27sだったので約2倍の高速化につながりました。

JavaでSqlite3に大量データをinsertしてみた

今回はJavaからsqlite3に大量データをinsertしてみました。
以前と同じですが、条件は再掲します。

まず、sqlite3でテーブルを作ります。
sqlite> CREATE TABLE hash ( id INTEGER PRIMARY KEY , value TEXT );

insertするネタはcsvファイルで
1,356a192b7913b04c54574d18c28d46e6395428ab
2,da4b9237bacccdf19c0760cab7aec4a8359010b0
3,77de68daecd823babbb58edb1c8e14d7106e83bb
…
1列目は数字で2列目は数字のsha1値です。
これを100万行分用意します。(neta.csv)

次にsqlite3のJDBCを探します。複数ありそうですが、今回はココにあったやつをダウンロードしました。HP上の記載を見るとsqliteは3.6.14がベースとの事です。

次に以前作成したpostgresqlへのinsertするjavaアプリを適度にsqlite用に数行修正しました。変更点は以下、赤字です。

import java.sql.*;
import java.io.*;
import java.util.Date.*;

public class sqliteins {
    public static void main (String args[]) {
        Connection db = null;
        PreparedStatement ps = null;
        String sql = "insert into hash values(?,?)";
        String url = "jdbc:sqlite:testdb";
        BufferedReader br = null;
        int i = 0;
        try {
            Class.forName("org.sqlite.JDBC");
            db = DriverManager.getConnection(url);
            db.setAutoCommit(false);
            ps = db.prepareStatement(sql);
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        java.util.Date d1 = new java.util.Date();
        System.out.println(d1);

        try{
            br = new BufferedReader(new InputStreamReader(new FileInputStream("neta.csv")));
            String line;
            while( (line = br.readLine()) != null ){
                i++;
                String[] col = line.split(",");
                int id = new Integer(col[0]);
                String value = col[1];
                    ps.setInt(1,id);
                ps.setString(2,value);
                ps.executeUpdate();
                if( i % 10000 == 0) {
                    System.out.println(i);
                    db.commit();
                }
                }
             // データベース切断
            db.commit();
            ps.close();
            db.close();
            br.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        java.util.Date d2 = new java.util.Date();
        System.out.println(d2);
    }
 }

CLASSPATHを通して実行します。

100万件のinsertの実行時間は39sでした。

因みに、

commitを10万回に1回にした場合→21s

commitを50万回に1回にした場合→19s

とさらに早くなりました。

全く同じコードでもPostgreSQLよりも断然早いです。そもそもPostgreSQLやその他DBMSとSqliteは用途が若干異なる為、比較する意味はあまり無いかもしれませんが、Sqliteはかなりいい感じ。。。ですね。

PythonでSqlite3に大量のデータをInsertしてみた

次はPython2.xでSqlite3に対して大量のデータをinsertしてみました。
尚、Sqlite3は少なくとも最新のPython2.xでは内部に組み込まれている為、特に何かをインストールする必要はありません。

これもPythonのDocumentを参照して以下のようなコードを作成して実行しました。

#!/usr/bin/env python
import csv
import sqlite3
import time

if __name__ == '__main__':
    start = time.time()

 　 c = sqlite3.connect('./testdb')
    i = 0
    reader = csv.reader(open("./neta.csv"))
    for row in reader:
        id = row[0]
        value = row[1]
        t = (id,value,)
        c.execute('insert into hash (id,value) values(?,?)',t)
        i += 1
        if i % 100000 == 0:
            c.commit()
            print "commit! %d" % i
    c.commit()
    c.close()
    end = time.time()
    print end-start

前回同様、neta.csvというファイルを1件ずつ読み込み、testdbのhashテーブルに対してデータをinsertしてます。100万件のデータをinsertするのにかかった時間は27.1sでした。

・上記の例では10万件でcommit()するようにしましたが、30万件程度に1回のcommit()とした場合、若干パフォーマンスがあがりました。
・pythonのsqlite3モジュールはデフォルトではAutoCommitではないようで明示的にCommitしなければ、データが格納されません。因みに、Connectの際に以下のように変更する事でAutoCommitと出来るようです。

c = sqlite3.connect('./testdb',isolation_level=None)

やってみると、我慢出来ない位、遅くなります。