用R语言做数据分析时，常常需要从多种数据源取数据，其中数据库是非常常见的数据源。RClickhouse包，可以在R语言中对Clickhouse数据库进行增删改查的操作。
安装：install.packages(“RClickhouse”)
查看一下：
packageVersion("RClickhouse")
> packageVersion("RClickhouse")
[1] ‘0.5.2’
创建DBI Connection:
con <- DBI::dbConnect(RClickhouse::clickhouse(), host="192.168.8.145",db ="test")
注意：请注意，{RClickhouse}不使用HTTP接口与Clickhouse通信。因此，您可以使用本机接口端口（默认为9000）而不是HTTP接口（8123）。
将数据写入数据库:DBI::dbWriteTable(con, "mtcars", mtcars) DBI::dbListTables(con) DBI::dbListFields(con, "mtcars")
使用dplyr查询数据库：
tbl(con, "mtcars") %>% group_by(cyl) %>%summarise(smpg=sum(mpg)) tbl(con, "mtcars") %>% filter(cyl == 8, vs == 0) %>% group_by(am) %>% summarise(mean(qsec)) # Close the connection DBI::dbDisconnect(con)使用带有DBI的SQL样式命令查询数据库：:dbGetQuery:
DBI::dbGetQuery(con, "SELECt vs ,COUNT(*) AS 'number of cases',AVG(qsec) AS 'average qsec'FROM mtcars GROUP BY vs") # Save results of querying: DBI::dbGetQuery(con, "SELECt (*) FROM mtcars WHERe am = 1") # Or save the whole set of data (only useful for smaller datasets, for better performance and for larger datasets always use remote servers): mtcars <- dbReadTable(con, mtcars) # Close the connection dbDisconnect(con)使用ClickHouse函数查询数据库
# Get the names of all the avaliable databases
DBI::dbGetQuery(con, "SHOW DATAbaseS")
# 查看表
DBI::dbGetQuery(con, "SHOW TABLES")
# Get information about the variable names and types
DBI::dbGetQuery(con, "DESCRIBE TABLE mtcars")
# Compact CASE - WHEN - THEN conditionals
DBI::dbGetQuery(con, "SELECt multiIf(am='1', 'automatic', 'manual') AS 'transmission',multiIf(vs='1', 'straight', 'V-shaped') AS 'engine'FROM mtcars")
# 写入数据,跟Rmysql 语法差不多
diagram<-data.frame(id=4:5,name=c("周璐老师","宋雪老师"))
DBI::dbWriteTable(conn,"test01",diagram,append=T,row.names=F)
# Close the connection
dbDisconnect(con)
