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:
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;
|
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.
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.
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
.
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.
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.