PostgreSQL批量插入优化和错误处理

背景

最开始的原因呢,是想重写一下定向密码生成器,毕竟那个密码生成策略真是拍脑袋决定的,代码结构现在看来也写得很烂。目前网上能见到的分析大部分都比较浅显,无非是一些常用密码TOP,于是想到了对现有公开数据库的密码分析。目前具体的分析还在进行当中,后续会写博客说明。这里先讲讲如何从文本快速导入大量数据到数据库。

文本文件的结构大概如下:

1
2
3
zdg # 12344321 # zdg@csdn.net
LaoZheng # 670203313747 # chengming_zheng@163.com
fstao # 730413 # fstao@tom.com

操作思路

最开始我的思路是把文本文件用sed和awk处理拼接成SQL文件,然后导入到PostgreSQL中。也就是这样:

1
2
3
4
insert into tbname (a, b, c) values
("zdg", "12344321", "zdg@csdn.net"),
("huwolf", "2535263", "hujiye@263.net"),
("cadcjl", "KIC43dk6!", "ccedcjl@21cn.com");

于是我刷刷刷写了一条命令(为了阅读方便加了换行):

1
2
3
4
iconv -f LATIN1 -t UTF-8 file.txt |
gsed 's/\r//g' |
awk '{print "(\"" $1 "\", \"" $3 "\", \"" $5 "\")," }' |
gsed '$ s/.$/\;/; 1 iinsert into tbname (a, b, c) values' > exec.sql

先是转码,再是替换CRLF,然后字符串分割重组,接着把结尾的逗号变成分号,之后在第一行插入insert命令,最后输出文件。利用shell的管道,一气呵成,是不是美滋滋?

PostgreSQL中字符串的引号

结果把SQL一执行,第二行就报错了,一行都没插进去。

然后查了一下原因,原来标准的SQL中字符串用的是单引号,如果字符串本身也包括单引号,则使用两个单引号,也就是’’,不是”,也不是\’。(MySQL是支持双引号的,各家数据库有各自的方言)

那好吧,那就改成单引号吧,于是我改了一下命令:

1
2
3
4
iconv -f LATIN1 -t UTF-8 file.txt |
gsed "s/\r//g;s/'/''/g" |
awk '{print "(\'" $1 "\', \'" $3 "\', \'" $5 "\')," }' |
gsed '$ s/.$/\;/; 1 iinsert into tbname (a, b, c) values' > exec.sql

一气呵成,还不是美滋滋。

awk中的引号处理

结果这次还没生成完文件就报错了 - -。

原来当shell碰到第一个单引号时,它忽略掉其后直到下一个单引号的所有特殊字符。也就是说,原句中的awk '{print "(\'" $1 "\', \'" $3 "\', \'" $5 "\')," }'实际上忽略了'前的\。要让这句话执行,需要改成这样:

1
awk '{print "('\''" $1 "'\'', '\''" $3 "'\'', '\''" $5 "'\'')," }'

是不是引号看花眼(笑

实际上上面这句话可以这样理解:

1
2
3
4
5
6
7
8
9
10
11
12
13
awk '{print "('
\'
'" $1 "'
\'
', '
\'
'" $3 "'
\'
', '
\'
'" $5 "'
\'
')," }'

一组单引号内的字符是交给awk程序处理的,而\'是先由shell处理成'的。因为把\'用双引号包起来执行效果和原来一样:

1
2
3
4
5
6
7
8
9
10
11
12
13
awk '{print "('
"\'"
'" $1 "'
"\'"
', '
"\'"
'" $3 "'
"\'"
', '
"\'"
'" $5 "'
"\'"
')," }'

合并后:

1
awk '{print "('"\'"'" $1 "'"\'"', '"\'"'" $3 "'"\'"', '"\'"'" $5 "'"\'"')," }'

还有一种看的清楚一点的办法是用ASCII码:

1
awk '{print "(\x27" $1 "\x27, \x27" $3 "\x27, \x27" $5 "\x27)," }'

PostgreSQL的copy命令

费了一番功夫,终于把文本处理成SQL了,然后就美滋滋地把SQL扔到数据库里去执行了。结果没想到跑了半天没个结果,电脑却越来越烫,风扇呼呼呼,吓得我赶紧把进程杀了。直接批量insert消耗的资源太大,一条一条insert只会更慢,看来这条路是走不通了。

PostgreSQL专门为批量插入数据提供了一个copy命令,一个简单的例子如下:

1
copy tbname (a, b, c) from 'file.txt' delimiter '#';

意思是以#为分隔符分割file.txt的每一行,然后分别插入到tbname的abc字段中,看上去不错,是吧?

但是这里有一个大坑,那就是delimiter只能使用一个单字节的字符。当使用多个字符或者占用多个字节的字符时就会报如下错误:

1
ERROR: COPY delimiter must be a single one-byte character

而密码中可能包括所有可见字符,所以它们都不能用来当分隔符。首先我想到的是用空格或者tab(\t)来分割,结果发现有些人密码末尾是\\\把空格和tab给转义了。这就意味着直接这样分割是不行的了,需要有个办法让字符串不能发生转义。

查了文档和stackoverflow,发现可以用csv模式配合quote参数满足这个需求。quote参数只能用于csv模式,默认为双引号",改成单引号需要这样写quote ''''

最终我把文本处理为如下格式(两个单引号表示一个真实的单引号):

1
2
3
'z''dg','12344321','zdg@csdn.net'
'Lao''''Zheng','6702033,13747','chengming_zheng@163.com'
'fstao','730413\\\','fstao@tom.com'

插入数据的命令为:

1
copy tbname (a, b, c) from 'file.csv' delimiter ',' csv quote '''';

五百万条左右数据最后执行了4分钟左右,速度还是可以的。

插入后的结果为:

1
2
3
4
5
a | b | c
------------+---------------+-------------------------
z'dg | 12344321 | zdg@csdn.net
Lao''Zheng | 6702033,13747 | chengming_zheng@163.com
fstao | 730413\\\ | fstao@tom.com

在使用copy命令的时候,字符串是可以使用双引号标记的,但是也要和单引号一样,在字符串内的双引号"需要处理成两个双引号""

参考

https://my.oschina.net/davisqi/blog/318360
https://www.postgresql.org/docs/9.6/static/sql-copy.html
https://stackoverflow.com/questions/12856377/the-correct-copy-command-to-load-postgresql-data-from-csv-file-that-has-single-q
https://stackoverflow.com/questions/17808511/properly-escape-a-double-quote-in-csv
https://stackoverflow.com/questions/34189658/postgres-copy-multibyte-delimiter