InsertMode

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 add-in uses Insert SQL statements to upload bulk data.

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

Bulk Insertion

InsertMode provides the following options for executing a bulk insert operation by the driver:

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. See Use a multi-row insert in the Amazon Redshift documentation.
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 COPY FROM command copies the staged CSV file to a database. See COPY in the Amazon Redshift documentation for more information.
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
- S3:PutObject.
Note that this mode uses your AWSAccessKey and AWSSecretKey in COPY commands that are sent to the server. This could present a security concern. If you want to hide sensitive information, use the S3StagingWithIAM mode instead.
S3StagingWithIAM: This option is similar to S3Staging, but it uses an IAM role parameter for the COPY command.
The AWSAccessKey and AWSSecretKey are still required to connect in this mode, but they are not be included in the COPY commands sent to Redshift.

NOTE:If you choose the S3Staging or S3StagingWithIAM insert mode, and if you set the UseUIDForStaging connection property to True, you may want to define an AWS S3 policy on the bucket to remove staging files that are automatically stored in the bucket.