Advanced Bulk Insert

In this example we will insert 2 rows in a more complex table. This will demonstrate using different kinds of data types, chanined methods and getting the summary information at the end of a transaction.

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

example/advanced_bulk_insert.sql
1
2
3
4
5
6
7
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(40) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `added` datetime DEFAULT NULL,
  `salary` decimal(9,2) DEFAULT NULL
) ENGINE=Columnstore;
example/Advanced_bulk_insert.java
23
24
25
26
27
28
29
30
import com.mariadb.columnstore.api.*;

public class Advanced_bulk_insert {

    public static void main(String[] args) {
        try {
            ColumnStoreDriver d = new ColumnStoreDriver();
            ColumnStoreBulkInsert b = d.createBulkInsert("test", "t2", (short)0, 0);

As with the basic example we create an instance of the driver and use it to create a bulk insert instance.

example/Advanced_bulk_insert.java
31
32
33
34
35
36
            b.setColumn(0, 1);
            b.setColumn(1, "Andrew");
            b.setColumn(2, "1936-12-24");
            b.setColumn(3, "2017-07-07 15:14:12");
            b.setColumn(4, "15239.45");
            b.writeRow();

This demonstrates setting several different data types using strings of data. The second column (column 1) is a VARCHAR(40) and is set to “Andrew”. The third column is a DATE column and the API will automatically convert this into a binary date format before transmitting it to ColumnStore. The fourth is a DATETIME and the fifth a DECIMAL column which again the API will convert from the strings into the binary format.

example/Advanced_bulk_insert.java
37
            b.setColumn(0, 2).setColumn(1, "David").setColumn(2, new ColumnStoreDateTime("1972-05-23", "%Y-%m-%d")).setColumn(3, new ColumnStoreDateTime("2017-07-07 15:20:18", "%Y-%m-%d %H:%M:%S")).setColumn(4, new ColumnStoreDecimal(2347623, (short)2)).writeRow();

Many of the ColumnStoreBulkInsert methods return a pointer to the class and a return status which means multiple calls can be chained together in a similar way to ORM APIs. Here we use additional datatypes ColumnStoreDateTime and ColumnStoreDecimal.

ColumnStoreDateTime is used to create an entry for a DATE or DATETIME column. It can be used to define custom formats for dates and times using the strptime format.

A decimal is created using the ColumnStoreDecimal class. It can be set using a string, double or a pair of integers. The first integer is the precision and the second integer is the scale. So this number becomes 23476.23.

example/Advanced_bulk_insert.java
38
39
40
41
42
43
44
45
            b.commit();
            ColumnStoreSummary summary = b.getSummary();
            System.out.println("Execution time: " + summary.getExecutionTime());
            System.out.println("Rows inserted: " + summary.getRowsInsertedCount());
            System.out.println("Truncation count: " + summary.getTruncationCount());
            System.out.println("Saturated count: " + summary.getSaturatedCount());
            System.out.println("Invalid count: " + summary.getInvalidCount());
        }

After a commit or rollback we can obtain summary information from the bulk insert class. This is done using the ColumnStoreBulkInsert.getSummary method which will return a reference ColumnStoreSummary class. In this example we get the number of rows inserted (or would be inserted if there was a rollback) and the execution time from the moment the bulk insert class is created until the commit or rollback is complete.

example/Advanced_bulk_insert.java
46
47
48
49
50
        catch (ColumnStoreException e) {
            System.err.println("Error caught: " + e.getMessage());
        }
    }
}

At the end we clean up in the same was as the basic bulk insert example.