javaからsqliteに素早く書き込みたい

概要

以前ツイートデータ収集をpythonを使ってsqliteに保存していたが、
javaでマルチスレッディングしながら高速でやりたいと思って色々調べたメモ。

ライブラリ

sqliteを扱う際は
sqlite-jdbcを使うと楽チンなのでmavenから取ってきて使う。

ハマったこと

当初はHDDにデータを収集していたのですが、

とにかく遅い。

なんでこんなに遅いのかってぐらい一件一件に時間がかかって収集よりもDB追加で待つ時間が多かった。

解決法

原因を探った。

1. データを追加する際に一つ一つexecuteUpdateで追加していた
2. データ(ツイート)の重複を避けるためにINSERT OR UPDATEしていた
3. デフォルト設定ではdb-jornalファイルに書き出してからDBファイルに追加するので余計なディスクアクセスが発生する。また、DBとの同期フラグが入っているので遅い

一つずつ解決していく

1. 該当ライブラリにはprepareStatementで予めどのようなものをデータとして挿入するか予約することが出来る
2. これに関してはぐぐってみたところUPDATEがかなり時間がかかる処理のようなのでINSERT OR IGNOREに変更する
3. pragmaのモードをいじっていく。具体的にはjournal_modeをMEMORYにしてsync_modeをOFFにする

実装例

最初に言ってたツイートを保存する場合の例

DataBase.java

    /**
     * ドライバの登録
     */
    public static void dbInit() {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    /**
     * pragmaの設定
     * ここで3.のjornal_modeとsync_modeを設定している
     */
    public static Properties getProperties() {
        Properties prop = new Properties();
        prop.put("journal_mode", "MEMORY");
        prop.put("sync_mode", "OFF");
        return prop;
    }
    /**
     * データベースに追加する処理
     * synchronizedを入れないとマルチスレッドでガンガン書き込んだときにdeadlockを起こす
     */
    public static synchronized void putTweet2SQL(File dbFile, List<Status> tweet) {
        Statement stmt;
        String dbHeader = "jdbc:sqlite:" + dbFile.getAbsolutePath();
        PreparedStatement pstmt;
        dbInit();
        try (Connection conn = DriverManager.getConnection(dbHeader, getProperties())) { //try-with-resources
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            //データベースが無かったら作成
            stmt.executeUpdate("CREATE TABLE IF NOT EXISTS tweets (tweet_id INTEGER PRIMARY KEY, user_id INTEGER, user_screen_name TEXT,tweet_text TEXT)");
            //ツイートID，ユーザID，スクリーン名，ツイートテキスト
            //2.一括でデータを追加するための準備
            pstmt = conn.prepareStatement("INSERT OR IGNORE INTO tweets VALUES (?, ?, ?, ?)");
            for (Status status : tweet) {
                place = status.getPlace().getFullName();
                pstmt.setLong(1, status.getId());
                pstmt.setLong(2, status.getUser().getId());
                pstmt.setString(3, status.getUser().getScreenName());
                pstmt.setString(4, status.getText());
                pstmt.addBatch();//1.処理に追加
            }
            pstmt.executeBatch();//1.実際のデータベース追加処理はここで行われる
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

おまけ

DBから読み出すときもsetFetchSizeすると指定した数一括で呼び出してくれるのである程度快適になる。

DataBase.java

    /**
     * ツイートの取得
     * 巨大データを引っ張ってくるとヒープ不足になるのでその場合は適当に書き換える
     * @return ツイートリスト
     */
    public static List<Status> getTweetsFromSQL(File dbFile) {
        String dbHeader = "jdbc:sqlite:" + dbFile.getAbsolutePath();
        PreparedStatement pstmt;
        List<Status> userDetails = new ArrayList<>();//適当なリスト
        dbInit();
        try (Connection conn = DriverManager.getConnection(dbHeader, getProperties())) {
            pstmt = conn.prepareStatement("SELECT * FROM tweets");
            pstmt.setFetchSize(1000);//5000兆とかセットすれば一回で全部取得する
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {//ツイートID，ユーザID，スクリーン名，ツイートテキスト
                Status status = new status();
                status.setId(rs.getLong(1));
                status.setUserId(rs.getLong(2));
                status.setScreenName(rs.getString(3));
                status.setTweetText(rs.getString(4));
                
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userDetails;
    }

所感

だいぶ速くなって、更にSSDが買えたので幸せになりました