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?

Hey, were u able to get it working ? I'm also facing a similar issue where in I want to pass multiple arguments to a sql file. If it's solved for you, please let me know the format of arguments and way to access it in sql file. — abhishekmahawar, Feb 15 '16 at 12:14
@marnun How did you get it to work finally? I am trying to do the same thing and can't get it to work. — Chirag Agrawal, Sep 05 '17 at 23:34

score 5 · Answer 1 · answered Mar 02 '16 at 23:39

This is the script that works fine from psql shell :

insert into tempsdf select * from source where source.id = '123';

Here are some of my tests on SqlActivity using Data-Pipelines :

Test 1 : Using ?'s

insert into mytable select * from source where source.id = ?; - works fine if used via both 'script' and 'scriptURI' option on SqlActivity object.

where "ScriptArgument" : "123"

here ? can replace the value of the condition, but not the condition itself.

Test 2 : Using parameters works when command is specified using 'script' option only

insert into #{myTable} select * from source where source.id = ?; - Works fine if used via 'script' option only

insert into #{myTable} select * from source where source.id = #{myId};

works fine if used via 'script' option only

where #{myTable} , #{myId} are Parameters whose value can be declared in template.

http://docs.aws.amazon.com/datapipeline/latest/DeveloperGuide/dp-custom-templates.html

(when you are using only parameters, make sure you delete an unused scriptArguments - otherwise it will still throw and error)

FAILED TESTS and inferences:

insert into ? select * from source where source.id = ?;

insert into ? select * from source where source.id = '123';

Both the above commands does not work because

Table names cannot be used for placeholders for script arguments. '?''s can only be used to pass values for a comparison condition and column values.

insert into #{myTable} select * from source where source.id = #{myId}; - doesn't work if used as 'SciptURI'

insert into tempsdf select * from source where source.id = #{myId}; - does not work when used with 'ScriptURI'

Above 2 commands does not work because

Parameters cannot be evaluated if script is stored in S3.

insert into tempsdf select * from source where source.id = $1 ; - doesnt work with 'scriptURI'

insert into tempsdf values ($1,$2,$3); - does not work.

using $'s - doesn't not work in any combination

Other tests :

"ScriptArgument" : "123" "ScriptArgument" : "456" "ScriptArgument" : "789"

insert into tempsdf values (?,?,?); - works as both scriptURI , script and translates to insert into tempsdf values ('123','456','789');

scriptArguments will follow the order you insert and replaces "?" in the script.

I found this post to be more accurate than the accepted one. — Steve, Jul 09 '18 at 12:24

score 1 · Answer 2 · answered Dec 23 '14 at 08:34

1

in shellcommand activity we specify two scriptArguments to acces using $1 $2 in shell script(.sh)

"scriptArgument" : "'s3://location_of_unload/#format(minusDays(@scheduledStartTime,1),'YYYY/MM/dd/hhmm/')}'", # can be accesed using $1 "scriptArgument" : "'aws_access_key_id=????;aws_secret_access_key=*******'" # can be accesed using $2

I dont know will this work for you.

answered Dec 23 '14 at 08:34

santhoshc

29
5

It will, since it's solution as the first answer – marnun Dec 24 '14 at 14:20

score 0 · Accepted Answer · answered Dec 16 '14 at 01:23

0

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

answered Dec 16 '14 at 01:23

AravindR

677
4
11

Can you please show some example code of how this was achieved for the the unload command in redshift ? – Richard Vella Mar 25 '15 at 20:20
I used `to $1` and I'm getting `The column index is out of range: 1, number of columns: 0.` – soup_boy Nov 29 '16 at 07:00
this is not working for me. I also get The column index is out of range: 1, number of columns: 0. – hellomichibye Feb 27 '17 at 10:12
@aravindr do you have any working examples of an unload script? I am getting the error `The column index is out of range: 1, number of columns: 0` when I try to refer to an argument using $1 or ? – Chirag Agrawal Sep 05 '17 at 23:23
The only way I was able to get this work was using the `?` within the sql script itself. Also, I was only able to get parameters to work inside of `SELECT, INSERT, UPDATE, and DELETE` statements. I couldn't get the `unload` statement to accept a parameter. – Steve Jul 09 '18 at 12:22

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

3 Answers3

Linked