CODEWITHSUNDEEP
amazon-s3amazon-redshift
rohitmb
rohitmb

Reputation: 151

PgSQL - Export select query data direct to amazon s3 with headers

I have this requirement where i need to export the report data directly to csv since getting the array/query response and then building the scv and again uploading the final csv to amazon takes time. Is there a way by which i can directly create the csv with the redshift postgresql. PgSQL - Export select query data direct to amazon s3 servers with headers here is my version of pgsql - Version PgSQL 8.0.2 on amazon redshift

Thanks

Upvotes: 0

Views: 1700

Answers (1)

Tomasz Tybulewicz
Tomasz Tybulewicz

Reputation: 8647

You can use UNLOAD statement to save results to a S3 bucket. Keep in mind that this will create multiple files (at least one per computing node).

You will have to download all the files, combine them locally, sort (if needed), then add column headers and upload result back to S3.

Using the EC2 instance shouldn't take a lot of time - connection between EC2 and S3 is quite good.

In my experience, the quickest method is to use shells' commands: 

# run query on the redshift
export PGPASSWORD='__your__redshift__pass__'
psql \
    -h __your__redshift__host__ \
    -p __your__redshift__port__ \
    -U __your__redshift__user__ \
    __your__redshift__database__name__ \
    -c "UNLOAD __rest__of__query__"

# download all the results
s3cmd get s3://path_to_files_on_s3/bucket/files_prefix*

# merge all the files into one
cat files_prefix* > files_prefix_merged

# sort merged file by a given column (if needed)
sort -n -k2 files_prefix_merged > files_prefix_sorted

# add column names to destination file
echo -e "column 1 name\tcolumn 2 name\tcolumn 3 name" > files_prefix_finished

# add merged and sorted file into destination file
cat files_prefix_sorted >> files_prefix_finished

# upload destination file to s3
s3cmd put files_prefix_finished s3://path_to_files_on_s3/bucket/...

# cleanup
s3cmd del s3://path_to_files_on_s3/bucket/files_prefix*
rm files_prefix* files_prefix_merged files_prefix_sorted files_prefix_finished

Upvotes: 2

Related Questions