Specifies what method to use when inserting bulk data. By default DML mode is used.

Possible Values

DML, S3Staging, S3StagingWithIAM

Data Type

string

Default Value

"DML"

Remarks

When this is set to DML the driver uses Insert SQL statements to upload bulk data.

When this is set to S3Staging the driver uses the bulk upload API to upload data to the AWS S3 staging folder.

Bulk Insertion

InsertMode provides two options for executing a bulk insert operation by the driver.

1. InsertMode=DML This is the default option for sending multiple INSERT statements to the server by the driver in bulk. In this case the driver issues a multi-row INSERT statement with respective syntax supported by the server. For more information, see Use a multi-row insert - Amazon Redshift
2. InsertMode=S3Staging This option involves two steps. Initially, multiple rows are streamed to the Amazon S3 bucket in CSV format. A staging CSV file is then created in the bucket. In the second step, Redshift's command COPY FROM copies the staged CSV file to a database. See, for example: https://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html. The minimum set of policies required to connect AWS S3 by an IAM user, or a role assumed by it, are S3:ListBucket, S3:GetObject and S3:PutObject.
3. InsertMode=S3StagingWithIAM This option is similar to S3Staging, but it uses IAM role parameter for the COPY command. See AllowPreparedStatement property.

To decide which insert mode you should use, compare the performance of these two options. How well each option performs depends on your environment, so you can use this comparison to make your decision.