Amazon Data Pipeline: How to use a script argument in a SqlActivity?

Question

When trying to use a Script Argument in the sqlActivity:

 {
"id" : "ActivityId_3zboU",
  "schedule" : { "ref" : "DefaultSchedule" },
  "scriptUri" : "s3://location_of_script/unload.sql",
  "name" : "unload",
  "runsOn" : { "ref" : "Ec2Instance" },
  "scriptArgument" : [ "'s3://location_of_unload/#format(minusDays(@scheduledStartTime,1),'YYYY/MM/dd/hhmm/')}'", "'aws_access_key_id=????;aws_secret_access_key=*******'" ],
  "type" : "SqlActivity",
  "dependsOn" : { "ref" : "ActivityId_YY69k" },
  "database" : { "ref" : "RedshiftCluster" }
}

where the unload.sql script contains:

 unload ('
    select *
    from tbl1 
 ')  
 to ?
 credentials  ?
 delimiter ',' GZIP;

or :

 unload ('
    select *
    from tbl1 
 ')  
 to ?::VARCHAR(255)
 credentials  ?::VARCHAR(255) 
 delimiter ',' GZIP;

process fails:

syntax error at or near "$1" Position

Any idea what i'm doing wrong?

Answer 1

I believe you are using this sql activity for Redshift. Can you modify your sql script to refer to parameters using their positional notation. To refer to the parameters in the sql statement itself, use $1, $2, etc.

See http://www.postgresql.org/docs/9.1/static/sql-prepare.html