Is a "Load DATA" without a file (i.e., in memory) possible for MySQL and Java?

Question

I'm in the process of optimizing an import of ~10TB of Data into a MySQL database. Currently, I can import 2.9GB (+0.8GB index) in about 14 minutes on a current laptop. The process includes reading a data file (Oracle ".dat" export), parsing the data, writing the data into a CSV file and executing the "LOAD DATA LOCAL" sql command on it.

Is it possible to increase the import speed (without hardware changes)? Is there a way to remove the step of writing a file to the file system and letting MySQL read it again. Is it possible to stream the data in memory directly to MySQL (e.g., via the JDBC driver)?

Many thanks in advance, Joerg.

Ron Klein · Accepted Answer · 2015-12-06 12:44:30Z

7

It seems that from MySQL Connector/J JDBC driver version 5.1.3 onwards, you can hook up an InputStream reference, using com.mysql.jdbc.Statement.setLocalInfileInputStream() method, internally within your Java code, to 'pipe' your in-memory formatted string/text to the 'LOAD DATA INFILE' call. This means you do not have to write out and re-read a temporary file back from memory. Please refer to:

http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-implementation-notes.html (bottom of page)

The process is also outlined in this post:

http://jeffrick.com/2010/03/23/bulk-insert-into-a-mysql-database

O'reilly produced a PDF covering MySQL/JDBC performance gems, which refers to this.

There is also mention of it's usage with Hadoop (advanced Java topic).

Hope this all helps.

Cheers

Rich

edited Dec 6, 2015 at 12:44

Ron Klein

9,52010 gold badges59 silver badges91 bronze badges

answered May 6, 2011 at 16:24

Big Rich

6,0751 gold badge43 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex R · Accepted Answer · 2016-09-17 00:34:26Z

Actual working code for this was hard to come by, so here's some:

@Test
public void bulkInsert() throws SQLException {
    try(com.mysql.jdbc.Connection conn = (com.mysql.jdbc.Connection) dao.getDataSource().getConnection()) {

        conn.setAllowLoadLocalInfile(true);

        try(com.mysql.jdbc.Statement stmt = (com.mysql.jdbc.Statement) conn.createStatement()) {

            stmt.execute("create temporary table BasicDbTest_1 (phone integer)");

            String data = "8675309\n";
            stmt.setLocalInfileInputStream(new ByteArrayInputStream(data.getBytes()));

            stmt.execute("load data local infile '' into table BasicDbTest_1");

            try(ResultSet rs = stmt.executeQuery("select phone from BasicDbTest_1")) {
                Assert.assertTrue(rs.next());
                Assert.assertEquals(rs.getInt(1), 8675309);                 
            }
        }
    }
}

Collectives™ on Stack Overflow

Is a "Load DATA" without a file (i.e., in memory) possible for MySQL and Java?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related