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.

PART 1:

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

#!/usr/bin/env bash

s3_prefix=$1

db_host=$2

db_name=$3

db_username=$4

db_password=$5

db_tablename=$6

db_port=$7

dir=temp

export PGPASSWORD=$5

# install postgres in AmazonLinux

sudo yum install-ypostgresql94

# Copy from S3 to PostrgreSQL RDS.

mkdir-p$dir

rm-rf$dir/*

aws s3 cp$s3_prefix$dir/--recursive

IFS=$'\n';forfin$(find$dir-name'*');

do

if[[-f"$f"]];then

echo"Processing $f file..."

psql-h$2-d$3-U$4-p$7-c"\COPY $db_tablename FROM '$f' CSV"

fi

done

Run a PSQL command to copy from S3 to Redshift

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

#!/usr/bin/env bash

manifest=$1

db_host=$2

db_name=$3

db_username=$4

db_password=$5

db_tablename=$6

db_port=$7

dir=temp

export PGPASSWORD=$5

# install postgres in AmazonLinux

sudo yum install-ypostgresql94

# Copy from S3 to Redshift.

# Using manifest file provided in ShellCommandAcvtivity's scriptArgument 1 of of the Data-Pipeline