How to enable Bulk Copy API for batch insert operations

Using Bulk Copy API for batch insert operations is supported starting from JDBC driver preview release 6.5.4. This feature allows the users to utilize Bulk Copy API underneath when executing batch insert operations against Azure Data Warehouses, and improves the performance significantly. This feature aims to achieve the performance improvement while inserting the same data as it would have with regular batch insert operation. This page shows various use cases with batch insert using Bulk Copy API, as well as its limitations.

Prerequisites

There are two prerequisites to enable Bulk Copy API for batch insert.

1. The server must be Azure Data Warehouse.
2. The query must be an insert query (the query may contain comments, but the query must start with the INSERT keyword for this feature to come into effect).

Enabling Bulk Copy API for batch insert

There are two ways to enable Bulk Copy API for batch insert.

1. Enabling with connection property:

Adding useBulkCopyForBatchInsert=true; to the connection string enables this feature.

connection = DriverManager.getConnection("jdbc:sqlserver://localhost;userName=user;password=password;database=test;useBulkCopyForBatchInsert=true;");

2. Enabling with setUseBulkCopyForBatchInsert() method from SQLServerConnection object:

Calling SQLServerConnection.setUseBulkCopyForBatchInsert(true) enables this feature.

SQLServerConnection.getUseBulkCopyForBatchInsert() retrieves the current value for **useBulkCopyForBatchInsert **connection property.

Limitations

There are currently these limitations that apply to this feature.

1. The insert query cannot contain INSERT-SELECT queries (e.g. INSERT INTO TABLE SELECT * FROM TABLE2).
2. The insert query cannot contain multiple VALUE expressions (e.g. INSERT INTO TABLE VALUES (1, 2) (3, 4))
3. The insert query cannot be followed by the OPTION clause, or be joined with other queries that are not part of the INSERT query.

If the query fails due to non-SQL server related errors, the driver will log the error message and fallback to the original logic for batch insert.