工具如下:
1.mysql-connector-odbc
2.putty
3.RODBC包
步骤如下:
1.安装工具mysql-connector-odbc,然后在控制面板(win+x,p)中打开“系统和安全”,“管理工具",ODBC数据源(根据系统位数选择)
2.添加ODBC数据源,MySQL ODBC Unicode Driver,配置相关参数
其中ip一定要填本地地址或者localhost,而非数据库地址,端口也必须是本地一个不用的端口,也非数据库端口,先不要点OK。
3.打开并配置putty。
在session界面输入ssh的ip以及相应端口,然后保存session名称,点击save,参数按我这个图配置就行了。
4.在connection界面打开SSH,Tunnels界面
在Source Port端口填写刚才在DSN填写的本地不常用的端口,然后在Destination填写数据库的IP和端口,用英文冒号隔开。
然后点击Add,再点击Open,然后在putty的黑色窗口填写SSH账户名,回车,填写密码。
然后再在DSN界面点击test进行测试,会提示连接成功,然后保存关闭DSN。
5.在R终端(一般用Rstudio比较方便)加载RODBC包(如果没有安装,先install.packages("RODBC")),然后定义连接,如下:
library(RODBC);
conn<-odbcConnect("test");#"test"#为刚才设置的DSN连接名,一般设置为数据库名
然后就可以进行sqlQuery等操作了,如查询表的某些数据sqlQuery(conn,"select * from tablename limit 10;")
当然也可以保存相应的数据(必须是dataframe形式的数据)上传到数据库,在没有定义表主键时,很简单
sqlSave(conn,dataframe,tablename,rownames=F,append=T);
但假如数据库相应表中数据包含主键时,再往里边穿数据,则会报错。
我想到的一种方法是先通过sqlQuery找到该主键的最大值,然后再给数据库的rownames加上这个值再上传。如下:
rownum<-as.numeric(sqlQuery(conn,"select max(id) from tablename"));#因返回的是数据框,需转换为数值型
rownames(dataframe)<-as.numeric(rownames(dataframe))+rownum;#rownames为字符串型,需转换,然后加上
最后通过如下方法:
sqlSave(conn,dataframe,tablename,rownames="主键colname",append=T,addPK=T);
这里的“主键colname”,也就是主键的列名,一般为"id",然后就可以看到数据在往远程数据库中上传了。只是速度相比RMySQL包的dbWritetable方法慢,不知道是不是SSH通道的影响。