By using AWS re:Post, you agree to the Terms of Use

generate unload command for multiple tables

0

select 'UNLOAD ('select * from '||schemaname||'.'||tablename||'') TO 's3://bucket/schema/'||schemaname||'.'||tablename||'' iam_role 'arn:aws:iam::619481062323:role/sam-redshift-role-for-s3' region 'us-east-1' allowoverwrite parallel off ; ' from pg_tables where schemaname = 'schemaname'

ERROR: syntax error at or near "select" LINE 1: select 'UNLOAD ('select * from '||schemaname||'.'||tablename... ^

is the any way to generate unload script for all tables in schema like as statement

thanks

  • Hi,

    The tricky part here is dealing with all the single quotes - some you need in the output text while others delimit different parts of the query.

    I think the below does the trick - see what you think:

    select 'UNLOAD ('||''''||'select * from '||schemaname||'.'||tablename||''''||') TO '||''''||'s3://bucket/schema/'||schemaname||'.'||tablename||''''||' iam_role '||''''||'arn:aws:iam::dontpostthis:role/sam-redshift-role-for-s3'||''''||' region '||''''||'us-east-1'||''''||' allowoverwrite parallel off ; ' from pg_tables

    The key part is the use of ||''''|| to output a single quote character in the output.

    You probably don't want to post the full ARN by the way here - I've removed it in my query above - so you'll need to swap that back.

    Cheers

asked a month ago61 views
1 Answers
0

Hi, Yes, the key part is to get single quote while we are generating the dynamic sqls. You can follow the solution provided by Flag or use '' twice single quote to get single quote in dynamic query.

Rewritten query and sample output:

rsdb=# select 'UNLOAD (''select * from '||schemaname||'.'||tablename||''') TO ''s3://bucket/schema/'||schemaname||'.'||tablename||''' iam_role ''arn:aws:iam::account_id:role/yourbucketrole'' region ''us-east-1'' allowoverwrite parallel off ; '
from pg_tables where schemaname='temp1';
                                                                                  ?column?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 UNLOAD ('select * from temp1.t2') TO 's3://bucket/schema/temp1.t2' iam_role 'arn:aws:iam::account_id:role/yourbucketrole' region 'us-east-1' allowoverwrite parallel off ;
(1 row)
SUPPORT ENGINEER
answered 24 days ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions