JavaでCSVを高速でDBに取り込む方法

こんにちわ。hokanのおかもとです。 JavaでCSVを高速でDBファイルを作成するのにいろいろ試行錯誤したのでご紹介します。

はじめに

動機

私がまだ前職のソフトウェア会社でユーザーからの問い合わせで調査用のユーザデータ(CSVファイル)を受け取り調査していたころ、そのCSVファイルに対してSQLを実行できれば、調査が捗ると思ったことがきっかけです。

何を使う?

言語Java 8フレームワークJavaFXDBSQLite

最終的には、GUIにしたかったので、JavaFXを利用しています。

取り込むデータ

要約にはCSVファイルとありますが、正確には、CSV形式のデータ郡を束ねたファイルを取り込みます。便宜上CSVファイルと呼びます。 こんな感じです。

テーブル名1
カラム1,カラム2,カラム3,カラム4
：テーブル名2
カラム1,カラム2,カラム3,カラム4,カラム5,カラム6,カラム7,カラム8
：

50テーブル、平均40カラム、100レコードほどのデータを取り込みます。

製作過程

まずは普通に

なにも考えずにCSVファイルを一行一行取り込んで見る。

CSVParser cp = new CSVParser();
    StringBuilder sb = new StringBuilder();
    sb.append("INSERT INTO ").append(tableName);
    String[] values = cp.parseLine(line);
    for (String value : values) {
        sb.append("'" + value + "',");
    }
    sb.deleteCharAt(sb.length() - 1).append(")");;
    con.createStatement().executeUpdate(sb.toString());

これにかかった時間は、約10分でした。 これでは、CSVをEXCELとかで開いて少しずつでも絞り込んだほうが早そうです。

マルチスレッド化

とりあえず高速化といえば、マルチスレッドって認識なので マルチスレッドにして取り込んでみます。

final int MAX_THREADS = 8;
    executor = Executors.newFixedThreadPool(MAX_THREADS);
    List<Future<?>> list = new ArrayList<Future<?>>();
    for (ArrayList<String> data : dataList) {
        executor.submit(new RunnableDBBuilder(data, callback));
    }

RunnableDBBuilderの中でSQLを生成、実行しています。 これで実行した結果は5 分ほどでした。 早くはなっていますが、まだまだ実用できではありません。 またスレッド数を増やしても速度が極端に変化するということもありませんでした。

マルチテーブル・インサートSQL

SQLの実行回数が原因と踏み、複数行のデータを同時に取り込むマルチテーブル・インサートSQLを導入しました。

通常のSQLであれば以下のような形式になります。

INSERT INTO MY_TABLE COLUMN_A, COLUMN_B  VALUES ( DATA_A1, DATA_B1 );
INSERT INTO MY_TABLE COLUMN_A, COLUMN_B  VALUES ( DATA_A2, DATA_B2 );
INSERT INTO MY_TABLE COLUMN_A, COLUMN_B  VALUES ( DATA_A3, DATA_B3 );
：

SQLiteにおけるマルチテーブル・インサートSQLでは、以下のような形式になります。

INSERT INTO MY_TABLE
                SELECT DATA_A1 AS COLUMN_A, DATA_B1 AS COLUMN_B
    UNION  ALL  SELECT DATA_A2 , DATA_B2
    UNION  ALL  SELECT DATA_A3 , DATA_B3;

すべてのSQLをこれに置き換えた結果、全データを取り込むのに10秒ほどしかかからなくなりました。

コネクション・プーリング

以下の設定でコネクションプーリングを作成して実行します。

結果は、ほぼ変わらずといった感じです。マルチテーブル・インサートSQLを導入してからとほぼ変わらずといった感じです。

まとめ

いろいろな手法を組み合わせて見ましたが、マルチテーブル・インサートSQLがかなり効果的でした。

‘+