sql - Wanted to unload redshift table data to S3 with appending date on the file name -


unload ('select * cn.test') 's3://bucket/archive/stage/test-#{format(@scheduledstarttime,'yyyy-mm-dd-hh')}.csv' credentials 'aws_access_key_id=###;aws_secret_access_key=###' delimiter '|' addquotes parallel off allowoverwrite 

i tried above command unload s3. getting error : amazon invalid operation: syntax error @ or near "yyyy"; 1 statement failed.

if give escape character near date, file name going test-#{format(@scheduledstarttime,'yyyy-mm-dd-hh')}.csv instead of actual sysdate.

could can me

a known issue. had similar requirements. resolution, wrote shell script unload statement , when executed successfully, fired mv command rename file on s3 bucket.

script:

psql -u$user -h hostname -p port -c "unload (...) 's3://bucket-name' credentials 'aws_access_key_id; aws_secret_access_key' [switch1][switch2]..."      if [ "$?" != "0" ]                         echo "\ns3 error: error in unloading csv s3"                 echo "\ns3 error: error in unloading csv s3" >> $log_file          else    aws s3 mv s3://bucket-name/"$file_name".csv000 s3://bucket-name/"$file_name$from_date".csv                 echo "$file_name$from_date copied s3" >> $status_file                 return 0         fi 

Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -