数据库 用 PostgreSQL 的 COPY 导入导出 CSV

darkbaby123 for 上海巧议网络科技有限公司 · 发布于 2017年02月13日 · 最后由 nouse 回复于 2017年02月17日 · 2617 次阅读
2575
本帖已被设为精华帖!

TL;DR

原文 来自我的博客。

无意中看到了一篇讲 数据批量导入 的文章,才注意到 PostgreSQL 的 COPY 命令。简而言之,它用来在文件和数据库之间复制数据,效率非常高,并且支持 CSV 。

导出 CSV

以前做类似的事情都是用程序语言写,比如用程序读取数据库的数据,然后用 CSV 模块写入文件,当数据量大的时候还要控制不要一次读太多,比如一次读 5000 条,处理完再读 5000 条之类。

PostgreSQL 的 COPY TO 直接可以干这个事情,而且导出速度是非常快的。下面例子是把 products 表导出成 CSV :

COPY products
TO '/path/to/output.csv'
WITH csv;

可以导出指定的属性:

COPY products (name, price)
TO '/path/to/output.csv'
WITH csv;

也可以配合查询语句,比如最常见的 SELECT

COPY (
  SELECT name, category_name
  FROM products
  LEFT JOIN categories ON categories.id = products.category_id
)
TO '/path/to/output.csv'
WITH csv;

导入 CSV

跟上面的导出差不多,只是把 TO 换成 FROM ,举例:

COPY products
FROM '/path/to/input.csv'
WITH csv;

这个命令做导入是非常高效的,在开头那篇博客作者的测试中,COPY 只花了 INSERT 方案 1/3 的时间,而后者还用 prepare statement 优化过。

总结

COPY 还有一些其他配置,比如把输入输出源指定成 STDIN/STDOUT 和 shell 命令,或者指定 CSV 的 header 等等。这里不再赘述。数据库也有很多细节可挖,有些简单却非常实用。合理使用能大大提高效率。

参考资料

Friends Don’t Let Friends Use Loops

PostgreSQL: COPY

共收到 11 条回复
3

👍 我所遇到的大多数需要使用 CSV 的场景,通常数据源不是从其他数据库导出,而是从 Excel 导出的数据。

8

之前写过一个etl工具,就是用csv做中间格式,来快速导出和插入的。

De6df3 huacnlee 将本帖设为了精华贴 02月13日 13:57
2575

@lgn21st Excel 也是很蛋疼的东西 😭 @hooopo CSV 做数据库互导还是很方便的。

26054

这么好用的东西好赞 👍 但是项目不用pg,不知道mysql有没有什么类似的工具。 好像可以实现,但是语法没这个好用,不知mysql这方面效率如何。

3

#4楼 @darkbaby123 如果是 MySQL <=> MySQL 或者 Pg <=> Pg,那么内置的 dump 工具不是更适合数据库互相导入?如果是异构数据库迁移,恐怕 CSV 还得配上些许处理脚本以解决异构数据库之间的差异?

8

#6楼 @lgn21st 内置dump工具更适合备份与恢复。但如果需要额外处理就麻烦很多,比如生产库有信用卡信息,导入到staging环境需要把信用卡信息替换成空。除了异构迁移,还有一类需求是ETL,比如生产系统是MySQL,分析系统用Pg。

2575

@lgn21st 同类型数据库肯定是用 dump 工具方便。实际上 pg_dump 如果导出成 SQL 的话,也是默认生成 COPY 。跨数据库才需要一个双方都能交流的格式。

162

#5楼 @themadeknight

mysql有select into,和copy命令类似,能支持各种选项,导出csv的例子:

SELECT * INTO OUTFILE '/tmp/result.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM table;
3

#8楼 @hooopo 学习了。

12楼 已删除
775

数据库导入导出pg有fdw

8 hooopo Bulk Upsert for MySQL & PostgreSQL 中提及了此贴 03月03日 01:51
需要 登录 后方可回复, 如果你还没有账号请点击这里 注册