使用JDBC连接数据库更通用，只要有对应数据库的JDBC driver，有Java环境，基本可以连上所有常见的数据库。所以我倾向于用客户端 Squirrel + jdbc 连接数据库进行一般的数据查询和处理。 R 中连接只需要以下几步。
R连接到Postgresql
查询用 dbGetQuery, DML语句用 dbSendQuery, DDL语句用 dbSendUpdate
library(RJDBC)
pDriver <- JDBC(driverClass=”org.postgresql.Driver”, classPath=”C:/Squirrel3.0/lib/postgresql-9.4.1211.jre6.jar”)
#pDriver <- JDBC(driverClass=”org.postgresql.util.PGJDBCMain”, classPath=”C:\\Squirrel3.0\\lib\\postgresql-9.4.1211.jre6.jar”)
pconn <- dbConnect(pDriver, “jdbc:postgresql://postgresql_svr_name_ip:5432/databasename?autosave=always”, “dbuser”, “password”)
input.data <- dbGetQuery(pconn, “select * from pg_tables limit 10”)
dbDisconnect(pconn) #可有可无
========================
R连接到Vertica
library(RJDBC)
vDriver <- JDBC(driverClass=”com.vertica.jdbc.Driver”, classPath=”C:/Squirrel3.0/lib/vertica-jdbc-7.0.1-0.jar”)
vconn <- dbConnect(vDriver, “jdbc:vertica://vertica_svr_name_ip:5433/databasename”, “dbuser”, “password”)
input.data <- dbGetQuery(vconn, “select * from tables limit 10”)
========================
R连接到Oracle
library(RJDBC)
oDriver <- JDBC(driverClass=”oracle.jdbc.OracleDriver”, classPath=”C:\\Squirrel3.0\\lib\\ojdbc6.jar”)
oconn <- dbConnect(oDriver, “jdbc:oracle:thin:@oracle_svr_name_ip:1521:databasename”, “dbuser”, “password”)
input.data <- dbGetQuery(oconn,”select * from all_tables limit 10″)
========================
R连接到Mysql
mDriver <- JDBC(driverClass=”com.mysql.jdbc.Driver”, classPath=”C:/Squirrel3.0/lib/mysql-connector-java-5.1.46.jar”)
mconn <- dbConnect(mDriver, “jdbc:mysql://mysql_svr_name_ip:3306/databasename”, “dbuser”, “password”)
input.data <- dbGetQuery(mconn, “select * from information_schema.tables limit 10”)
========================
R连接到Microsoft Sqlserver
兼容mssql 2003之前的版本
mssDriver <- JDBC(driverClass=”com.microsoft.sqlserver.jdbc.SQLServerDriver”, classPath=”C:/Squirrel3.0/lib/mssqljdbc41.jar”)
mssconn <- dbConnect(mssDriver , “jdbc:sqlserver://mssql_svr_name_ip:1433;databaseName=databasename”, “dbuser”, “password”)
input.data <- dbGetQuery(mssconn , “select * from sysobjects limit 10”)
========================
R连接到Teradata
tDriver<- JDBC(driverClass=”com.teradata.jdbc.TeraDriver”, classPath=”C:/Squirrel3.0/lib/terajdbc4.jar;C:/Squirrel3.0/lib/tdgssconfig.jar”)
tconn <- dbConnect(tDriver, “jdbc:teradata://Tera_svr_name_ip/TMODE=TERA”, “username”, “password”)
input.data <- dbGetQuery(tconn,”select top 10 * from dbc.dbcinfo”)
所用到的jar包都在Squerriel/lib目录下。 下载 [小松鼠] Squirrel SQL Client Portable: https://1drv.ms/u/s!AtVaUU1SN60KhTXulW5JdrTNGF0E
