将表从 AmazonRDS 导出到 CSV 文件

我有一个在 亚马逊 RDS中运行的 MySQL 数据库，我想知道如何将整个表导出为 CSV 格式。

我目前使用 Windows 上的 MySQL 服务器来查询 Amazon 数据库，但是当我尝试运行导出时，我得到了一个错误，可能是因为没有专用于 Amazon RDS 的文件服务器。有解决办法吗？

67990 次浏览
小开
最佳答案

可以推测，您正试图通过 SELECT ... INTO OUTFILE查询从 亚马逊 RDS数据库导出，这会产生这个常见的问题，参见 输出数据库到 CSV。相应的 AWS 小组反应证实了您的假设，即缺乏服务器访问阻止了这样的导出，并建议了一种替代方法，以及通过导出 通过选择 MySQL 命令行客户端中的数据并通过管道输出将数据重新格式化为 CSV 格式，从而实现 CSV 格式的数据，如下所示:

mysql -u username -p --database=dbname --host=rdshostname --port=rdsport --batch
-e "select * from yourtable"
| sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > yourlocalfilename

用户 fpalero 提供了另一种选择和据说更简单的方法，如果你知道并指定前面的字段:

mysql -uroot -ppassword --database=dbtest
-e "select concat(field1,',',field2,',',field3) FROM tabletest" > tabletest.csv
小开

我正在使用 EC2上的 耶框架连接到 RDS MySQL。关键是使用 fputcsv ()。下面的代码无论在我的本地主机上还是在生产环境中都可以很好地工作。

$file = 'path/to/filename.csv';
$export_csv = "SELECT * FROM table";


$qry = Yii::app()->db->createCommand($export_csv)->queryAll();


$fh = fopen($file, "w+");
foreach ($qry as $row) {
fputcsv($fh, $row, ',' , '"');
}
fclose($fh);
小开

首先，Steffen 的回答在大多数情况下工作。

最近，我遇到了一些更大、更复杂的输出，其中“ sed”是不够的，因此决定提出一个简单的实用程序来完成这项工作。

我构建了一个名为 sql2csv 的模块，它可以解析 MySQL CLI 的输出:

$ mysql my_db -e "SELECT * FROM some_mysql_table"


+----+----------+-------------+---------------------+
| id | some_int | some_str    | some_date           |
+----+----------+-------------+---------------------+
|  1 |       12 | hello world | 2018-12-01 12:23:12 |
|  2 |       15 | hello       | 2018-12-05 12:18:12 |
|  3 |       18 | world       | 2018-12-08 12:17:12 |
+----+----------+-------------+---------------------+


$ mysql my_db -e "SELECT * FROM some_mysql_table" | sql2csv
 

id,some_int,some_str,some_date
1,12,hello world,2018-12-01 12:23:12
2,15,hello,2018-12-05 12:18:12
3,18,world,2018-12-08 12:17:12

您还可以使用 CLI 中内置的:

sql2csv -u root -p "secret" -d my_db --query "SELECT * FROM some_mysql_table;"


1,12,hello world,2018-12-01 12:23:12
2,15,hello,2018-12-05 12:18:12
3,18,world,2018-12-08 12:17:12

更多关于 < a href = “ https://github.com/gabfl/sql2csv”rel = “ nofollow norefrer”> sql2csv (GitHub)的信息。

小开

假设在 RDS 中使用 MySQL，另一种方法是使用批处理模式，该模式输出制表符分隔值并转义换行符、制表符和其他特殊字符。我还没有找到一个 CSV 导入工具，它不能处理 TAB 分离的数据。例如:

$ mysql -h myhost.rds.amazonaws.com -u user -D my_database -p --batch --quick -e "SELECT * FROM my_table" > output.csv

作为 注意到 Halfgaar，--quick选项立即刷新，因此可以避免大型表的内存不足错误。要引用字符串(推荐使用) ，您需要在查询中做一些额外的工作:

SELECT id, CONCAT('"', REPLACE(text_column, '"', '""'), '"'), float_column
FROM my_table

REPLACE转义 text_column值中的任何双引号字符。我还建议对日期时间字段使用 iso8601字符串，因此:

SELECT CONCAT('"', DATE_FORMAT(datetime_column, '%Y%m%dT%T'), '"') FROM my_table

请注意，如果您有一个 NULL 列值，CONCAT 将返回 NULL。

我已经在一些性能合理的大型表上运行了该程序。当在与 RDS 实例相同的 VPC中运行 MySQL 命令时，600M 行和23GB 数据需要约30分钟。

小开

AWS 有一个新的方法来做这件事，就是使用他们的 DMS (数据库迁移服务)。

下面是关于如何将表导出到 S3存储器上的文件的文档: 使用 Amazon S3作为 AWS 数据库迁移服务的目标-AWS 数据库迁移服务

您将有可能导出两种格式: CSV或 镶木地板。

小开

如果使用 斯蒂芬 · 欧宝的解决方案，您会注意到它生成了一个包含“ concat”字符串文本的头。显然这不是你想要的。您很可能需要数据的对应头部。

除了替换列名和表名之外，这个查询不需要任何修改就可以工作:

mysql -h xxx.xxx.us-east-2.rds.amazonaws.com
--database=mydb -u admin -p
-e "SELECT 'column1','column2'
UNION ALL SELECT column1,column2
FROM table_name WHERE condition = value" > dataset.csv

我刚刚在 数字 OS X 应用程序中打开了结果，输出看起来非常完美。

小开

对于一个非常大的表(约500m 行) ，即使使用 --quick，也没有向导出文件写入任何内容，这个过程从未完成(+ 6小时)。为了解决这个问题，我编写了以下 bash 脚本。另一个好处是，当每个批处理文件写入时，您都可以看到进度指示。

只要有某种类型的顺序列，例如自动递增整数 PK 或日期列，这种解决方案就可以很好地工作。如果有大量数据，请确保将日期列编入索引！

#!bin/bash


# Maximum number of rows to export/total rows in table, set a bit higher if live data being written
MAX=500000000
# Size of each export batch
STEP=1000000


for (( c=0; c<= $MAX; c = c + $STEP ))
do
mysql --port 3306 --protocol=TCP -h <rdshostname> -u <username> -p<password> --quick --database=<db> -e "select column1, column2, column3 <table> order by <timestamp> ASC limit $STEP offset $c" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > export$c.csv
done

一个稍微不同的方法可能会更快，这取决于你有适当的索引，是逐月逐步通过数据:

#!bin/bash
START_YEAR=2000
END_YEAR=2022
for (( YEAR=$START_YEAR; YEAR<=$END_YEAR; YEAR++ ))
do
for (( MONTH=1; MONTH<=12; MONTH++ ))
do


NEXT_MONTH=1
let NEXT_YEAR=$YEAR+1
if [ $MONTH -lt 12 ]
then
let NEXT_MONTH=$MONTH+1
NEXT_YEAR=$YEAR
fi
            

mysql --port 3306 --protocol=TCP -h <rdshostname> -u app -p<password> --quick --database=<database> -e "select column1, column2, column3 from <table> where <dateColumn> >= '$YEAR-$MONTH-01 00:00:00' and <dateColumn> < '$NEXT_YEAR-$NEXT_MONTH-01 00:00:00' order by <dateColumn> ASC" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > export-$YEAR-$MONTH-to-$NEXT_YEAR-$NEXT_MONTH.csv
done
done

希望这能帮到别人


提交答案