How to export SQL Output directly to CSV on Amazon RDS

Question

Amazon doesn't give Access to RDS Server directly ( they expose it only through service RDS) hence, "select into outfile" doesn't work.. Even the master user does not have privileges of FILE.

I created ticket with Amazon; talked at length with them.. They suggested few work-around like using Data Pipeline etc.. but all are too complicated..

Surely one of the way could be to use tool like MYSql Workbench --> execute query --> Export to CSV.. Only problem with this approach is that you need to execute same query twice on server and is problematic if your output is having thousands of rows.

Answer 1

Just write the query in a file a.sql. The SQL Should be in this format:

select concat( '"',Product_id,'","',    Subcategory,'","',  ifnull(Product_type,''),'","',     ifnull(End_Date,''), '"') as data from tablename

mysql -h xyz.abc7zdltfa3r.ap-southeast-1.rds.amazonaws.com -u query -pxyz < a.sql > deepak.csv

Output will be there in file deepak.csv