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)
Relevant questions
Unload command added more decimal places to float4 field
Accepted Answerasked 3 years agogenerate unload command for multiple tables
asked a month agoRedshift Unload to S3 End of Line Encoding Type
asked 5 months agoUnload with timestamp filter
Accepted Answerasked 4 years agoRedshift UNLOAD questions
Accepted Answerasked 6 years agoCan not select multiple S3 bucket from Security & permissions page
asked 2 years agoRedshift UNLOAD parquet file size
Accepted Answerasked 3 years agoS3 Select vs Athena
Accepted Answerasked 2 years agoCan I use the 'WITH' clause in Athena UNLOAD
asked a month agoUnload is broken when a column contains certain characters
Accepted Answerasked 3 years ago
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