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

darkbaby123 for 上海巧议网络科技有限公司 · February 13, 2017 · Last by nouse replied at February 17, 2017 · 15378 hits
Topic has been selected as the excellent topic by the admin.

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

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

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

huacnlee mark as excellent topic. 13 Feb 13:57

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

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

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

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

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

#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;
12 Floor has deleted

数据库导入导出 pg 有 fdw

hooopo in Bulk Upsert for MySQL & PostgreSQL mention this topic. 03 Mar 01:51
You need to Sign in before reply, if you don't have an account, please Sign up first.