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

发布时间: 2017-06-27 阅读数: 240

开始

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

批量读取txt及按行拼接

这里简单粗暴的方法就是写循环,但是不建议这样做,这里小赵给出了使用lapply的方法。读取的函数也不建议使用基础包里面的,小赵推荐有readr::read_tsvdata.table::freadreadr包据说比基础包快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的配置问题


总结

总体来看,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的博客,原文标题和链接为R批量处理txt文件并写入MySQL,转载请注明出处,雪晴数据网欢迎大家投稿。

分享到:
热门文章

REmap发布,用R绘制百度迁徙图

2015年07月23日更新 18785次阅读

学习R语言,一篇文章让你从懵圈到入

2016年05月09日更新 13023次阅读

清华大学教授:大数据时代 统计学依

2015年08月07日更新 10828次阅读

在R中填充缺失数据—mice包

2015年12月31日更新 8653次阅读

非统计学专业的人该如何学习 R 语

2015年07月24日更新 7551次阅读

精通 R plot—第一部分:颜色

2016年01月20日更新 6978次阅读
热门课程

R语言初级课程

2015年02月20日发布 2994名学员

R语言高效数据清理工具包dplyr

2015年08月30日发布 1572名学员

R语言数据分析入门

2016年05月10日发布 1390名学员

如何用R做数据预处理

2016年06月03日发布 1104名学员

全栈数据工程师养成攻略

2016年11月08日发布 953名学员

R语言大规模数据分析实战

2015年12月17日发布 920名学员
登录 注册