R批量处理txt文件并写入MySQL

填坑以及测试各种常见的方法的性能

Posted by Bruce Zhao on August 4, 2016

目录

开始

这篇博客主要记录的是批量读入txt文件并整合的常见方法及其速度比较，然后是解决中文不能写入到MySQL数据库小技巧。这里我首先比较的是readr&dplyr和data.table的读取和拼接速度；其次是比较RODBC和RMySQL这两种不同连接方式将数据写入MySQL的速度，顺便比较读取速度；最后再来简单总结一下。

批量读取txt及按行拼接

这里简单粗暴的方法就是写循环，但是不建议这样做，这里小赵给出了使用lapply的方法。读取的函数也不建议使用基础包里面的，小赵推荐有readr::read_tsv和data.table::fread。readr包据说比基础包快10倍及以上，而且较为灵活，可以设置各列的数据类型，更多的说明和用法请去github看看README; fread据说要比read_csv/tsv快2.5倍及以上，但是相对来说没有readr那么多的参数设置，适合快速读入规整的data frame。按行拼接dplyr的是bind_rows，data.table的是rbindlist。

数据说明：用于测试的是129个较为规整的txt文件，一共是16列，导入内存中总共约是14W行的样子，2M多。由于各文件中列的数据类型不太一样，为了方便后续的拼接所以全部设成字符串类型。

机器环境说明： i5-6200U, 8G RAM DDR4。

library(readr)
library(dplyr)
library(data.table)

> filelist <- list.files("test-data/", pattern = "\\.txt")  #列出文件夹下所有txt类型的文件

# 用microbenchmark包来进行速度测试
> io <- microbenchmark::microbenchmark(

  dt = lapply(filelist, function(x){
    fread(x, sep = "\t", header = F, encoding = "UTF-8",colClasses = rep("character",16))}) %>% 
    rbindlist(),

  readr = lapply(filelist, function(x){
    read_tsv(x, col_names = F, col_types = cols(.default = "c"))}) %>% do.call("bind_rows", .),

  times = 10L)  # 测试10次

> io
Unit: milliseconds
  expr       min         lq      mean     median        uq       max neval cld
    dt   76.7573   78.79884  117.4787   83.89957  106.2591  308.0055    10  a 
 readr 2138.4939 2152.19588 2242.2610 2179.00240 2257.3742 2688.8862    10   b

从测试的结果的中位数来看，data.table的速度约比readr快25倍的样子，但是fread的波动相对来说比read_tsv大很多。我发现一个有意思的现象，fread第一次读取稍微慢一点，到后面会越读越快。

测试图： 测试时间图

写入MySQL数据库

读入内存中的数据集都是UTF-8编码的，所以我将MySQL数据库的编码也设置成utf-8的编码，然后我就入坑了。之后，我不管是通过odbc还是DBI的方式写入数据库时都碰到了中文无法写入问题。最后，我通过反复测试发现原来是数据库编码的问题，这里需要将数据库的编码设置成gb18030/gbk的方式才行。打通接口后，下面来进行写入测试。

R连接数据库有两种常用方法：一种是通过ODBC的方式，通过RODBC包来实现，但是需要自己下载MySQL的odbc驱动然后通过控制面板里面的管理数据源来进行配置；另外一种方式是DBI，通过RMySQL包来实现连通。

library(RODBC)
library(RMySQL)

# 连接方式

conn <- dbConnect(RMySQL::MySQL(), username="root", password="****",
                  host="localhost", port=3306)
# host可以是云端的地址也可以是本地局域网IP地址的，例如"192.168.1.200"这样的。

odbc <- odbcConnect("配置的名称", uid = "", pwd = "", ...)
# 可以直接在管理数据源里面配置好用户名和密码，然后直接连接其名称就行。

# 写入MySQL速度测试

> io1 <- microbenchmark::microbenchmark(
  RMySQL=dbWriteTable(conn,"rmysql_speed",test_speed,append=T),
  RODBC=sqlSave(odbc,test_speed,"rodbc_speed",append = T),
  times = 2L
)
# 需要注意的是两种包写入数据库不同的写法；
# 由于RODBC的方式巨慢，所以只测两次，当然你也可以加大测试次数。

> io1
Unit: seconds
   expr        min         lq       mean     median        uq       max neval cld
 RMySQL   2.180623   2.180623   2.352721   2.352721   2.52482   2.52482     2  a 
  RODBC 322.617982 322.617982 406.036914 406.036914 489.45585 489.45585     2   b

根据上面的结果，发现RMySQL的写入速度完爆RODBC的写入速度，所以还是建议使用RMySQL这种连接方式。不过这个可能会暴露你的用户名及密码，当然你可以预先将用户名以及密码设置成变量保存为script然后通过source来调用就可以了。

读取速度测试

测试完写入速度，最后顺便来测试一下读取速度。

 > microbenchmark::microbenchmark(mysql=dbGetQuery(conn,"select * from rmysql_speed"),
+                                odbc = sqlQuery(odbc,"select * from rmysql_speed"),
+                                times = 10L)
Unit: milliseconds
  expr       min       lq     mean    median        uq       max neval cld
 mysql  66.04235  67.1841 280.9428  69.60182  72.41639 2183.3346    10   a
  odbc 331.52299 340.1690 425.7488 393.86295 525.25158  563.8237    10   a

测试10次，从均值来看RMySQL比RODBC 快2倍左右，中位数来看，RMySQL比RODBC快5倍以上，但是其最大值比RODBC大4倍的样子，只能说明RMySQL不是很稳定的样子，可能第一次读取慢点，但是后面就会变的很快。

插图解释ODBC的配置问题

管理数据源 配置MYSQL

总结

总体来看，RMySQL性能要比odbc的接口要好一些，而且不用下载驱动之类的东西，用起来十分的方便，我个人还是推荐这个。

最后忘了填补一个小坑：数据库中的编码为GB18030所以在读入到R中的时候回乱码，你需要这样几行命令：

#编码问题
dbSendQuery(conn,"set character_set_results=gb18030;")
# dbSendQuery(conn,'set character set gbK')
dbSendQuery(conn,'set character set gb18030')

#测试是否中文乱码
dbListTables(conn)
dbGetQuery(conn,"SELECT * FROM `table` LIMIT 6;")

然后你就不会出现中文乱码的问题了。Good Luck！ :blush:

Bruce Zhao / /  -  views
Published under(CC) BY-NC-SA 3.0 CN.

//评论数 //参与数                
友情链接