Basic Bulk Insert

In this example we will insert 1000 rows of two integer values into table test.t1. The full code for this can be found in the example/Basic_bulk_insert.java file in the mcsapi codebase.

You will need the following table in the test database to execute this:

example/basic_bulk_insert.sql
1
2
3
4
CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=Columnstore;
example/Basic_bulk_insert.java
23
import com.mariadb.columnstore.api.*;

We need to import all classes from the package com.mariadb.columnstore.api to use mcsapi.

example/Basic_bulk_insert.java
25
26
27
28
29
public class Basic_bulk_insert {

    public static void main(String[] args) {
        try {
            ColumnStoreDriver d = new ColumnStoreDriver();

A new instance of ColumnStoreDriver is created which will attempt to find the Columnstore.xml configuration file by first searching for the environment variable COLUMNSTORE_INSTALL_DIR and then the default path of /usr/local/mariadb/columnstore/etc/Columnstore.xml. Alternatively we could provide a path as a parameter to ColumnStoreDriver.

example/Basic_bulk_insert.java
30
            ColumnStoreBulkInsert b = d.createBulkInsert("test", "t1", (short)0, 0);

Once we have the ColumnStore installation’s configuration in the driver we use this to initiate a bulk insert using ColumnStoreDriver.createBulkInsert. We are using the test database and the t1 table. The remaining two parameters are unused for now and set to 0.

example/Basic_bulk_insert.java
31
32
33
34
35
            for (int i=0; i<1000; i++){
                b.setColumn(0, i);
                b.setColumn(1, 1000-i);
                b.writeRow();
            }

A “for” loop is used to loop over 1000 arbitrary inserts in this example. We use ColumnStoreBulkInsert.setColumn to specify that column 0 (column a) should be set to the integer from the “for” loop and column 1 (column b) is set to 1000 minus the integer from the “for” loop.

When we have added something to every column ColumnStoreBulkInsert.writeRow is used to indicate we are finished with the row. The library won’t necessarily write the row at this stage, it buffers up to 100,000 rows by default.

example/Basic_bulk_insert.java
36
37
            b.commit();
        }

At the end of the loop we execute ColumnStoreBulkInsert.commit which will send any final rows and initiate the commit of the data. If we do not do this the transaction will be implicitly rolled back instead.

example/Basic_bulk_insert.java
38
39
40
41
        catch (ColumnStoreException e) {
            System.err.println("Error caught: " + e.getMessage());
        }
    }

If anything fails then we should catch ColumnStoreException to handle it.