Articles in this section

Load files in Redshift using user-defined COPY command

  • Updated
Follow

This flow bulk-loads data into Amazon Redshift using a COPY command you write yourself. It copies files from the source to S3 and then runs your COPY. Sources can be local or cloud storage, API responses, email attachments, or objects in a NoSQL database.

Compared to the auto-generated alternative: use the Bulk load files in S3 into Redshift flow when you want Etlworks to generate the COPY for you (and you want MERGE support). Use this flow when you need full control over the SQL.

How does this flow work?

  1. Copies files from the source to S3.
  2. Executes your user-defined COPY command against Redshift.
  3. Optionally cleans up staged files.

Note: Unlike the auto-generated bulk-load flow, this flow does not support automatic MERGE.

What do I need before I start?

  1. Redshift is up and reachable from your Etlworks instance.
  2. The Redshift user has INSERT on the target tables.
  3. An S3 bucket exists and Redshift can read it.

Step-by-step setup

  1. Create a source connection.
  2. Create and test a Redshift connection.
  3. Create an S3 connection. Set Archive file before copying to GZip.
  4. Open Flows, click Add flow, type Redshift, and select Copy files into Redshift.

    COPY into Redshift flow gallery

  5. Set FROM to the source connection + a filename or wildcard (e.g. *.csv).
  6. Set TO to the S3 connection + the base destination table name (no database or schema).

    FROM and TO

  7. Open the Connections tab and pick the Redshift connection.

    Flow Connections tab

  8. Click MAPPING, open Parameters, and fill in Copy SQL. See the Redshift COPY command reference for the full syntax.

    COPY SQL configuration

Optional parameters

Parameter What it does
Action Move (default): copy to S3 and delete from source. Copy: copy to S3 and keep the source file.
Add Suffix to the Destination File Name Adds a suffix to the staged filename. Default uuid appends a globally unique identifier so dest.csv becomes dest_uuid.csv.
Do not process files that have been already processed Skips files Etlworks has already loaded.
Maximum Simultaneous Operations How many file copy/move operations run in parallel.
Purge File(s) if Error On by default. Deletes staged files if COPY fails.
Name(s) of the File(s) to Purge Wildcard for files to delete on error. Defaults to the pattern used in COPY.
Before COPY SQL / Ignore errors SQL to run on Redshift before COPY, with optional error suppression.
After COPY SQL / Ignore errors Same, but after COPY.

Related articles