Upsert to Redshift within transaction

Question

I am very new to the whole Lambda, AWS, step functions and Redshift. But I think I've pinpointed a problem that was given to me to investigate.

The step function invokes a lambda node js code to do a copy from S3 into Redshift.

Relevant step definitions are as shown below

"States": {
...
            "CopyFiles": {
                "Type": "Task",
                "Resource": "ARN:activity:CopyFiles",
                "ResultPath": "...",
                "Retry": [
                    {
                        "ErrorEquals": ["Error"],
                        "MaxAttempts": 0
                    },
                    {
                        "ErrorEquals": [
                            "States.ALL"
                        ],
                        "IntervalSeconds": 60,
                        "BackoffRate": 2.0,
                        "MaxAttempts": 3
                    }
                ],
                "Catch": [
                    {
                        "ErrorEquals": [
                            "States.ALL"
                        ],
                        "ResultPath": "$.errorPath",
                        "Next": "ErrorStateHandler"
                    }
                ],
                "Next": "SuccessStep"
            },
            "SuccessStep": {
                "Type": "Task",
                "Resource": "ARN....",
                "ResultPath": null,
                "Retry": [
                    {
                        "ErrorEquals": ["Error"],
                        "MaxAttempts": 0
                    },
                    {
                        "ErrorEquals": [
                            "States.ALL"
                        ],
                        "IntervalSeconds": 60,
                        "BackoffRate": 2.0,
                        "MaxAttempts": 3
                    }
                ],
                "End": true
            },

The SQL statements (used in CopyFiles activity) are wrapped in a transaction by

"BEGIN;
CREATE TABLE "tempTable_datetimestamp_here" (LIKE real_table);
COPY tempTable_datetimestamp_here from 's3://bucket/key...' IGNOREHEADER 1 COMPUPDATE OFF STATUPDATE OFF';
DELETE FROM toTable
    USING tempTable_datetimestamp_here
    WHERE toTable.index = tempTable_datetimestamp_here.index;
INSERT INTO toTable SELECT * FROM tempTable_datetimestamp_here;

END;

when I put through multiple files (50) at the same time, all the step functions hang (keep running until I abort), please see screenshot . If I put a single file through then it works fine.

select pid, trim(starttime) as start,
duration, trim(user_name) as user,
query as querytxt
from stv_recents
where status = 'Running';

returns nothing anymore. However, the step functions are still showing as "Running".

Anyone please show me what I need to do to get this working? Thanks Tim

Can you include your step functions in your question? – Noel Llevares Jan 15 '18 at 10:35 — Noel Llevares, Jan 15 '18 at 10:35

score 0 · Answer 1 · answered Jan 15 '18 at 14:27

This approach (50 concurrent smallish commits) would work OK on an OLTP (small pinpoint queries) database (e.g. Postgres, MySQL).

But the process outlined creates multiple competing commits that block or bottleneck each other in Redshift.

Redshift is designed for OLAP (large analytic queries) and commits in Redshift are relatively expensive because they have to be confirmed by all compute nodes before they can be returned.

I would suggest a 2 phase process:

Use Lambda to create a manifest (JSON file) listing the currently available files for load (e.g. 50 files).
Use COPY with the manifest to load all available files in parallel and process the transaction once.

https://docs.aws.amazon.com/redshift/latest/dg/loading-data-files-using-manifest.html

Upsert to Redshift within transaction

1 Answers1