SqlActivity & RedshiftCopyActivity fails ? Use shellCommandActivity instead

Written by mannem on . Posted in Data Pipelines

There are several limitations of SQLActivity and RedshiftCopyActivity. If the psql/sql commands are too complex, these activities fail to prepare the statements correctly and will throw out some errors which cannot be easily rectified. So, you always have the option to use shellCommandActivity to run your complex script.

This article guides you to create a shell Script and corresponding Data-Pipeline template to run your complex script directly(part 1)or when present in S3(Part 2 ). As the true purpose of Data-Pipelines is automation, The script can also take arguments that you can reference using placeholders.


The following shell script takes arguments referenced in ScriptArguments object of the ShellCommandActivity. Its runs COPY command to copy files from S3 to PostgreRDS. Another example shows a copy from S3 to Redshift.

Run a PSQL command to copy from S3 to PostgreRDS

Run a PSQL command to copy from S3 to Redshift

A sample Pipeline template to copy from s3 to RDSPostgres

You may use a similar definition to copy from S3 to Redshift.


If your script is in S3 and you wanna pass arguments to your script:

Ex: insert into :v1 select * from source where source.id = :v2; -> s3://YourBucket/event_data_yyyymm.sql

$1 -> S3File location
$2 -> Password for Redshift
$3 -> Table Name
$4 -> condition value

A snippet of Definition for ShellCommandActivity can be :

Tags: , , , , , , , , ,

Trackback from your site.

Comments (5)

  • Jeff'


    Thank you so much for this post. Mos specifically I was looking for an answer to the problem of not being able to use apt-get so i got your yum example and solved

    Thank u!


  • Tom



    I use this example “Run a PSQL command to copy from S3 to Redshift” but I get error from Data pipline: “s3:/xxx/cust.manifest: No such file or directory”


  • tom


    This is still very much relevant in 2017. With no real good way to do specialized UNLOAD calls to move data out of redshift into s3, these psql ShellCommandActivity seem to be the way to go.


  • Amali


    How to use this same concept for Redshift unload activity?? Can you provide an example code


Leave a comment