Skip to content

Extended Bulk Copy for Azure DW

Jump to bottom
Peter Bae edited this page May 29, 2020 · 3 revisions

Preview version v8.3.1 adds a new connection property, sendTemporalDataTypesAsStringForBulkCopy. This boolean property is TRUE by default.

This connection property, when set to FALSE, will send DATE, DATETIME, DATIMETIME2 DATETIMEOFFSET, SMALLDATETIME, and TIME datatypes (aka temporal datatypes) as their respective types instead of sending them as String.

Sending the temporal datatypes as their respective types allows the user to send data into those columns for Azure DW, which was not possible before due to the driver converting the data into String. Sending String data into temporal columns works for SQL Server because SQL Server would perform implicit conversion for us, but it is not the same with Azure DW.

Additionally, even without setting this connection string to FALSE, from v8.3.1 and onward, MONEY and SMALLMONEY datatypes will be sent as MONEY / SMALLMONEY datatypes instead of DECIMAL after these changes, which also allows those datatypes to be bulk copied into Azure DW.

Limitations

There are currently two limitations:

  1. With this connection property set to FALSE, the driver will only accept the default string literal format of each temporal datatype, for example:

DATE: YYYY-MM-DD

DATETIME: YYYY-MM-DD hh:mm:ss[.nnn]

DATETIME2: YYYY-MM-DD hh:mm:ss[.nnnnnnn]

DATETIMEOFFSET: YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]

SMALLDATETIME:YYYY-MM-DD hh:mm:ss

TIME: hh:mm:ss[.nnnnnnn]

  1. With this connection property set to FALSE, the column type specified for bulk copy has to respect the data type mapping chart: https://docs.microsoft.com/en-us/sql/connect/jdbc/using-basic-data-types?view=sql-server-2017

For example, previously users could specify java.sql.Types.TIMESTAMP to bulk copy data into a DATE column, but with this feature enabled, they must specify java.sql.Types.DATE to perform the same.

Clone this wiki locally