在工作中进行数据挖掘，机器学习，数据分析等等，需要使用到数据库。但是如何链接数据库，可能统计学的学生在学校接触并不多。R基本上所有的数据库的链接都支持：
RODBC - R中ODBC数据库范围. 官网：http://cran.r-project.org/web/packages/RODBC/
DBI - 在R和数据库管理系统之间定义一个公共的接口. 官网：https://github.com/rstats-db/DBI
elastic - Elasticsearch HTTP API的包装器. 官网：https://github.com/ropensci/elastic
mongolite - R中Mongo客户端. 官网：https://github.com/jeroenooms/mongolite
RMySQL - R语言的MySQL数据库接口. 官网：http://cran.r-project.org/web/packages/RMySQL/
ROracle - R中Oracle数据库的接口. 官网：http://cran.r-project.org/web/packages/ROracle/index.html
RPostgreSQL - R语言的PostgreSQL数据库系统接口. 官网：https://code.google.com/p/rpostgresql/
RSQLite - R语言SQLite数据库接口. 官网：http://cran.r-project.org/web/packages/RSQLite/
RJDBC - 通过JDBC接口访问数据库. 官网：http://cran.r-project.org/web/packages/RJDBC/
rmongodb - R中MongoDB驱动. 官网：https://github.com/mongosoup/rmongodb
rredis - R中Redis驱动. 官网：http://cran.r-project.org/web/packages/rredis/
RCassandra -Apache Cassanda直接接口(不是JAVA)，提供了最多的基本功能. 官网：http://cran.r-project.org/web/packages/RCassandra/index.html
RHive - 通过Apache Hive的R扩展促进分布式计算. 官网：https://github.com/nexr/RHive
RNeo4j - Neo4j图形数据库驱动. 官网：https://github.com/nicolewhite/Rneo4j
链接redshift
链接redshift数据库的方法有很多，一一列举：
- RJDBC
install.packages("RJDBC")
library(RJDBC)
# download Amazon Redshift JDBC driver
download.file('http://s3.amazonaws.com/redshift-downloads/drivers/RedshiftJDBC41-1.1.9.1009.jar','RedshiftJDBC41-1.1.9.1009.jar')
# connect to Amazon Redshift
driver <- JDBC("com.amazon.redshift.jdbc41.Driver", "RedshiftJDBC41-1.1.9.1009.jar", identifier.quote="`")
# url <- "<JDBCURL>:<PORT>/<DBNAME>?user=<USER>&password=<PW>
url <- "jdbc:redshift://demo.ckffhmu2rolb.eu-west-1.redshift.amazonaws.com
:5439/demo?user=XXX&password=XXX"
conn <- dbConnect(driver, url)
library(RJDBC)
# download Amazon Redshift JDBC driver
download.file('http://s3.amazonaws.com/redshift-downloads/drivers/RedshiftJDBC41-1.1.9.1009.jar','RedshiftJDBC41-1.1.9.1009.jar')
# connect to Amazon Redshift
driver <- JDBC("com.amazon.redshift.jdbc41.Driver", "RedshiftJDBC41-1.1.9.1009.jar", identifier.quote="`")
# url <- "<JDBCURL>:<PORT>/<DBNAME>?user=<USER>&password=<PW>
url <- "jdbc:redshift://jiayundatapro.cls0csjdlwvj.us-west-2.redshift.amazonaws.com:5439/jiayundata?user=ds_report&password=DataAs1234"
conn <- dbConnect(driver, url)
显示数据库中的表格
# list tables
dbGetTables(conn)
dbGetQuery(conn, "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'")
# get some data from the Redshift table
dbGetQuery(conn, "select count(*) from flights")
# this is not a good idea – the table has more than 100 Mio. rows”
# fligths <- dbReadTable(conn, "flights")
# close connection
dbDisconnect(conn)
- dplyr
这个包是数据分析的包，但是也可以用来链接数据库
# now run analyses with the dplyr package on Amazon Redshift
install.packages("dplyr")
library(dplyr)
library(RPostgreSQL)
#myRedshift <- src_postgres("<DBNAME>",
# host = "<ENDPOINT>,
# port = <PORT>,
# user = "<USER<",
# password = "<PW>")
myRedshift <- src_postgres('demo',
host = 'redshiftdemo.ckffhmu2rolb.eu-west-1.redshift.amazonaws.com',
port = 5439,
user = "markus",
password = "XXX")
获取某一个表
# create table reference
flights <- tbl(myRedshift, "flights")
# 或者
dplyr::tbl(con, dbplyr::in_schema("abc", "mytable1"))
# 或者
remote_df = dplyr::tbl(sc,from = "db.financer_tbl") # 定义数据源表
# 或者
remote_df = dplyr::tbl(sc,from = dplyr::sql("select * from db.financer_tbl limit 10")) #
# simple and default R commands analyzing data frames
dim(flights)
colnames(flights)
head(flights)
#the summarize command reduces grouped data to a single row.
summarize(flights, avgdelay=mean(arrdelay))
summarize(flights, avgdelay=max(arrdelay))
链接好数据库，获取数据，然后就可以进行各种数据操作了。
3.再添加几种方式
require(redshift)
conn <- redshift.connect('jdbc:postgresql://jiayundatapro.cls0csjdlwvj.us-west-2.redshift.amazonaws.com:5439/jiayundata', "xxx", "xxxx")
tables <- redshift.tables(conn)
mydata <- dbGetQuery(conn, paste('select * from milin.clicktime'))
连外附上连接mysql的方式
my_db <- src_mysql(dbname = "rmysql",
host = localhost,
port = 3306,
user = "rmysql",
password = "rmysql")
