如何在Amazon RDS上将SQL输出直接导出到CSV [英] How to export SQL Output directly to CSV on Amazon RDS

问题描述

Amazon不会直接授予对RDS Server的访问权限(它们仅通过服务RDS公开访问权限)，因此，"select into outfile"不起作用. 甚至主用户也没有FILE特权.

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..

当然，一种方法可能是使用MYSql Workbench之类的工具->执行查询->导出为CSV.输出具有数千行.

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.

推荐答案

只需将查询写入文件a.sql. SQL应该采用以下格式:

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

输出将存在于deepak.csv文件中

Output will be there in file deepak.csv

这篇关于如何在Amazon RDS上将SQL输出直接导出到CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！