Redshift COPY csv array field to separate rows

Question

I have a relatively large MongoDB collection that I'm migrating into Redshift. It's ~600mm documents, so I want the copy to be as efficient as possible. The problem is, I have an array field in my Mongo collection, but I'd like to insert each value from the array into separate rows in Redshift.

Mongo:

{
  id: 123,
  names: ["market", "fashion", "food"]
}

In Redshift, I want columns for "id" and "names", where the primary key is (id, name). So I should get 3 new Redshift rows from that one mongo document.

Is it possible to do that with a Redshift COPY command? I can export my data as either a csv or json into s3, but I don't want to have to do any additional processing on the data due to how long it takes to do that many documents.

Dmitri Dmitri · Accepted Answer · 2016-03-09T17:22:06

You can probably do it on COPY with triggers, but it'd be quite awkward and the performance would be miserable (since you can't just transform the row and would need to do INSERTs from the trigger function).

It's a trivial transform, though, why not just pass it through any scripting language on export?

You can also import as-is, and transform afterwards (should be pretty fast on Redshift):

CREATE TABLE mydata_load (
  id    int4,
  names text[]
);

do the copy

CREATE TABLE mydata AS SELECT id, unnest(names) as name FROM mydata_load;

Redshift COPY csv array field to separate rows

2 Answers