I have created a Kinesis data stream and I'm feeding records into it that have format like this:
{
"ufo": 5,
"unicorn": 7,
"something else": 9,
}
I'm trying to write a transformation lambda that will transform above record and insert three records into Redshift. Redshift table schema is like this:
CREATE TABLE my_table(
timestamp DATETIME,
name TEXT,
count INT
) SORTKEY(timestamp);
So in essence - from single Kinesis record I need to produce three rows in Redshift.
Documentation of transformation lambda does not explain if what I am trying to achieve is possible. I already tried to wrap all payload within array before encoding but this results in processing error on Redshift end. I also tried to return multiple records with the same record-id - this results in error too.
I came across this bit of documentation which shows that multiple JSON objects can be stored within a single JSON file that is fed by COPY command. According documentation there can be more than one JSON object within the file and only white characters should be placed between JSON objects.
So I dropped objects like this:
{"timestamp": "2020/07/17 00:00:00", "name": "ufo", "count": 5}
{"timestamp": "2020/07/17 00:00:00", "name": "unicorn", "count": 7}
{"timestamp": "2020/07/17 00:00:00", "name": "something else", "count": 9}
It's quite confusing to debug this since AWS is retrying failed records - I added new line between objects (which is what is being suggested within documentation) and at first I thought I'm getting errors but errors were for retried previous attempts.