Loading Data into Redshift: COPY and Best Practices

Getting data into Amazon Redshift is a different activity from querying it, and the obvious method is the wrong one. Row-by-row INSERT statements work, slowly. The intended route is COPY, which loads files in parallel from Amazon S3 and other sources, and which the developer guide describes as loading large amounts of data much more efficiently than INSERT statements and storing the data more effectively as well (Redshift docs: use COPY).

Whether a COPY is fast depends less on the command than on the files it is pointed at.

How COPY loads in parallel

A Redshift cluster's compute nodes are divided into slices, and each slice can read a file, or part of one, at the same time as the others. The work can only be shared out if the input can be divided (Redshift docs: loading data files):

For files that cannot be split, the guide recommends dividing the data into multiple files of similar size, from 1 MB to 1 GB after compression, with the number of files a multiple of the number of slices. One large gzip file is the classic slow load: a single slice reads it while the rest wait.

Two further rules:

• Use one COPY command for all the files. Several concurrent COPY commands into the same table force a serialized load, which the guide calls much slower (Redshift docs: single COPY command).
• Keep the bucket in the same Region as the cluster; for Parquet and ORC this is a requirement.

Preparing files: a worked split

We took one month of New York yellow taxi trips, 2,964,624 rows, and prepared it the way the guide recommends for compressed text: eight files with equal row counts, gzipped, plus a manifest listing them. Eight stands in for the slice count of a small cluster. This ran on a local disk; nothing was uploaded.

import json, os
import pyarrow as pa
import pyarrow.csv as pacsv
import pyarrow.parquet as pq

SLICES = 8                                  # make the file count a multiple of the cluster's slices
PREFIX = "s3://amzn-s3-demo-bucket/load/trips/2024-01/"

table = pq.read_table("yellow_tripdata_2024-01.parquet")
rows_per_file = -(-table.num_rows // SLICES)        # ceiling division
os.makedirs("parts", exist_ok=True)

entries = []
for i in range(SLICES):
    part = table.slice(i * rows_per_file, rows_per_file)
    name = f"trips_{i:03d}.csv.gz"
    with pa.CompressedOutputStream(os.path.join("parts", name), "gzip") as out:
        pacsv.write_csv(part, out, pacsv.WriteOptions(include_header=False))
    size = os.path.getsize(os.path.join("parts", name))
    print(f"{name}  {part.num_rows:8,} rows  {size / 2**20:5.2f} MB")
    entries.append({"url": PREFIX + name, "mandatory": True})

sizes = [os.path.getsize(os.path.join("parts", f)) for f in sorted(os.listdir("parts"))]
print(f"{len(sizes)} files, {sum(sizes) / 2**20:.1f} MB in total, "
      f"largest is {max(sizes) / min(sizes):.2f} times the smallest")

with open("trips.manifest", "w", newline="\n") as f:
    json.dump({"entries": entries}, f, indent=2)
print()
print(json.dumps({"entries": entries[:2]}, indent=2), "... and", len(entries) - 2, "more entries")

trips_000.csv.gz   370,578 rows   6.50 MB
trips_001.csv.gz   370,578 rows   6.42 MB
trips_002.csv.gz   370,578 rows   6.42 MB
trips_003.csv.gz   370,578 rows   6.43 MB
trips_004.csv.gz   370,578 rows   6.36 MB
trips_005.csv.gz   370,578 rows   6.41 MB
trips_006.csv.gz   370,578 rows   6.40 MB
trips_007.csv.gz   370,578 rows   6.57 MB
8 files, 51.5 MB in total, largest is 1.03 times the smallest

{
  "entries": [
    {
      "url": "s3://amzn-s3-demo-bucket/load/trips/2024-01/trips_000.csv.gz",
      "mandatory": true
    },
    {
      "url": "s3://amzn-s3-demo-bucket/load/trips/2024-01/trips_001.csv.gz",
      "mandatory": true
    }
  ]
} ... and 6 more entries

The eight parts are within three percent of each other in size, which is what "similar size" asks for: no slice finishes long after the rest.

The manifest

COPY can take an S3 prefix and load everything under it. That is convenient and slightly dangerous: a stray file under the prefix is loaded too, and a missing one goes unnoticed. A manifest removes both risks. It is a JSON file naming each object, and the guide describes its purpose as making sure COPY loads all of the required files, and only the required files (Redshift docs: manifest). With mandatory set to true, a missing file fails the load instead of shrinking it silently.

The commands

Written from the documentation and not run; names are placeholders:

-- Gzipped CSV parts listed in a manifest
COPY trips
FROM 's3://amzn-s3-demo-bucket/load/trips/2024-01/trips.manifest'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoadRole'
MANIFEST
CSV
GZIP
TIMEFORMAT 'auto';

-- Parquet files under a prefix
COPY trips
FROM 's3://amzn-s3-demo-bucket/curated/trips/2024-01/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoadRole'
FORMAT AS PARQUET;

The cluster reads S3 through an IAM role attached to it, so no access keys appear in SQL.

Parquet loads are simpler and stricter. The guide lists the differences (Redshift docs: COPY from columnar formats): only a handful of parameters are supported, MAXERROR is not among them, so any error fails the whole command, and values are placed into the table's columns in file order, so the number and order of columns must match.

Loading changes, not only new rows

COPY appends. It does not update existing rows or skip duplicates, so running the same load twice doubles the data. For incremental loads the documented pattern is a staging table (Redshift docs: updating and inserting):

CREATE TEMP TABLE trips_stage (LIKE trips);

COPY trips_stage
FROM 's3://amzn-s3-demo-bucket/load/trips/2024-02/trips.manifest'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoadRole'
MANIFEST CSV GZIP;

MERGE INTO trips
USING trips_stage AS s
ON trips.trip_id = s.trip_id
WHEN MATCHED THEN UPDATE SET fare_amount = s.fare_amount, payment_type = s.payment_type
WHEN NOT MATCHED THEN INSERT (trip_id, pickup_at, payment_type, fare_amount)
     VALUES (s.trip_id, s.pickup_at, s.payment_type, s.fare_amount);

When every column is being replaced, the guide notes that deleting the matching rows and inserting from the staging table is the fastest method. Either way the load becomes safe to rerun, which is the property idempotency in data pipelines explains.

Checking the load

The guide's advice is to verify before and after. Before: confirm the bucket holds the right files and only those. After: query the load history.

-- Files committed by the most recent COPY in this session
SELECT filename, lines_scanned
FROM stl_load_commits
WHERE query = pg_last_copy_id();

-- Why rows were rejected
SELECT starttime, filename, line_number, colname, err_reason
FROM stl_load_errors
ORDER BY starttime DESC
LIMIT 20;

On Redshift Serverless the equivalent information is in the SYS_LOAD_DETAIL and SYS_LOAD_ERROR_DETAIL views. Compare the row count loaded with the count in the source files; that single reconciliation catches most load faults.

Details that affect speed and storage

• Compression encodings. ENCODE AUTO is the table default, and Redshift manages column encodings for you. The guide's position is that automatic compression produces the best results in most cases.
• Sort order. If each load follows the existing rows in sort key order, as daily loads on a timestamp key do, the data stays sorted and needs less vacuuming.
• Column sizes and types. Use date and timestamp types for dates, and size character columns to the data.
• Automatic loading. An auto-copy job, set up through an S3 event integration, lets Redshift detect new files under a path and run COPY for them, tracking which files have been loaded.

A loading checklist

1. Land files in S3 in the cluster's Region.
2. Prefer Parquet, or split compressed text into even parts in a multiple of the slice count.
3. Use one COPY per table, with a manifest for anything important.
4. Load into a staging table and merge when rows can change or loads can repeat.
5. Reconcile row counts and check the load error view.
6. Review sort and distribution settings once volumes grow; see Redshift distribution and sort keys.

For the warehouse these loads feed, see Amazon Redshift explained.

Amazon Redshift, S3 and ETL pipelines on AWS are covered in the Data Engineering course.

Sources

• Use a COPY command to load data (Amazon Redshift Database Developer Guide) — AWS Documentation
• Loading data files (Amazon Redshift Database Developer Guide) — AWS Documentation
• Use a single COPY command to load from multiple files (Amazon Redshift Database Developer Guide) — AWS Documentation
• Using a manifest to specify data files (Amazon Redshift Database Developer Guide) — AWS Documentation
• COPY from columnar data formats (Amazon Redshift Database Developer Guide) — AWS Documentation
• Updating and inserting new data (Amazon Redshift Database Developer Guide) — AWS Documentation