Can you explain the COPY command and how to optimize bulk loading into Redshift?

Question

Assisting AI · Accepted Answer

The COPY command in Amazon Redshift loads data from external sources (S3, EMR, DynamoDB, or other Redshift clusters) into a target table. It is highly parallelized and can ingest terabytes of data quickly.
Key syntax:
```sql
COPY target_table
FROM 's3://bucket/path/'
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRedshiftRole'
FORMAT AS CSV
DELIMITER ','
IGNOREHEADER 1
TIMEFORMAT 'auto'
COMPUPDATE ON
STATUPDATE ON;
```
**Optimization tips**:
1. **Use multiple files** – Redshift splits the load across slices; more files (up to 10 per node) increase parallelism.
2. **Compress data** – gzip or bzip2 reduces I/O; Redshift can decompress on the fly.
3. **Use the manifest file** – When data is split across many files, a manifest lists them, allowing COPY to skip missing files.
4. **Choose the right distribution style** – For large tables, use KEY or EVEN to balance data across slices; avoid ALL for big tables.
5. **Sort keys** – If you query by a column, create a sort key to speed up scans.
6. **Set MAXERROR** – Allow a small number of bad rows to avoid aborting the whole load.
7. **Use COPY with REGION** – Specify the region to avoid cross‑region data transfer.
8. **Parallel COPY** – Run multiple COPY commands on different tables or partitions concurrently.
The overall complexity is O(n) where n is the number of rows, but the actual throughput depends on the number of slices, file size, and network bandwidth.

Can you explain the COPY command and how to optimize bulk loading into Redshift?

💡 Model Answer

🎤 Get questions like this answered in real-time