Redshift unload script

Question

I have a script that has multiple redshift unload statements

UNLOAD('SELECT * FROM test.products WHERE product_name LIKE \'%20160912%\'') TO 's3://test-bucket/products/20160912/prod_' CREDENTIALS 'XXXX' DELIMITER AS '|' parallel off;

There are multiple such statements. I want to parameterize the date and pass in the date when I run this script. How can I do this?

The script would be like this

UNLOAD('SELECT * FROM test.products WHERE product_name LIKE \'%${DATE}%\'') TO 's3://test-bucket/products/${DATE}/prod_' CREDENTIALS 'XXXX' DELIMITER AS '|' parallel off;

Is there a way to replace ${DATE} when I run the script. I know there is PREPARE in Redshift but my thought is it cannot work with the Unload statement. Another option is to write a shell script and have the shell script call this SQL script. But is there an easier way to just pass in the parameter when running the SQL script from the command line.

Thanks in advance for the help!

John Rotenstein · Accepted Answer · 2016-09-17 04:04:29Z

There is no ability to pass parameters to an UNLOAD command.

The PREPARE statement works only for SELECT, INSERT, UPDATE or DELETE.

You will need to insert the desired parameters into the command prior to sending it to Redshift. This could be done from a bash script like this:

psql ... "UNLOAD (SELECT * FROM ... WHERE date = '`date +%Y%m%d`')..."

This would substitute the current date (date +%Y%m%d) as part of the query.

Utsav Jha · Accepted Answer · 2017-12-13 09:37:39Z

You can pass arguments to an unload statement using a bash script. Call 'psql' for your Redshift instance from an edge node accessing the Redshift database cluster and use the '-v' argument.

For example: In a Bash Script that is calling the SQL Script:

$> curr_date=$(date +%F) $> select_of_unload_stmt='SELECT * from myTable where column like \'$curr_date\'' $> psql -h <HostName> -p <Port_Number> -d <Database_Name> -U <User> -v RS_Script_Unload_Stmt="$select_of_unload_stmt" -v date_var="$curr_date" -f <Script_Containing_RS_Unload_Query.SQL>

Within the SQL Script, you can reference the passed variable as :RS_Script_Unload_Stmt. So you can create the Unload in your SQL Script as:

UNLOAD (:RS_Script_Unload_Stmt) TO 's3://test-bucket/products/:date_var/prod_' CREDENTIALS 'XXXX' DELIMITER AS '|' parallel off;

Yusuf Hassan · Accepted Answer · 2017-12-15 07:41:32Z

Try making the necessary changes in the script itself rather than to include something in unload statement.

What I did is:

aws s3 mv s3://[bucket_name]/"$file_name".csv000 s3://[bucket_name]/"$file_name$from_date".csv

What it does is:

Populate the bucket on s3 with the file that your unload statement generates.
Rename (move) the file appending the date/timestamp to the file name.

Here, $from_date is a variable, initialised as date --date='1 days ago' +'%Y-%m-%d';

Collectives™ on Stack Overflow

Redshift unload script

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related