dbplyr
数据库底层操作 & SQL代码转译
前言
dbplyr相比R语言中传统数据库操作的优势:
可使用dplyr语句操作数据库中的表
可直接将dplyr语句转换为SQL语句
基于数据库底层的虚拟tbl类操作,几乎不占用本地计算资源
github 项目地址:
https://github.com/tidyverse/dbplyr
01
加载必要包
> pacman::p_load(RPostgreSQL,
dplyr,
dbplyr,
stringr)
#RPostgreSQL与stringr是非必要加载
#实际使用时要根据数据库类型选择要加载的包
#pacman包是一个安装包管理包,也可library
02
连接数据库
> pgdriver<-dbDriver("PostgreSQL")
> con <- dbConnect(pgdriver,
host="数据库ip地址",
port="访问端口",
dbname="数据库名称",
user="用户名",
password="密码")
#这里以Postgresql为例
03
查看该数据库中表名
> dbListTables(con)
......
[109] "stock20191227" "dbplyr_data"
#这里获取到我预先写入的表:"dbplyr_data"
04
将表"dbplyr_data"定义为数据库底层操作tbl类型
> data <- tbl(con, "dbplyr_data")
> class(data)
[1] "tbl_PostgreSQLConnection" "tbl_dbi"
[3] "tbl_sql" "tbl_lazy"
[5] "tbl"
#这里可以看到data已经变成tbl格式,模式为PostgreSQLConnection
#接着我们print一下data看输出
> print(data)
# Source: table<dbplyr_data> [?? x 12]
# Database: postgres 10.0.11
# [postgres@106.54.32.120:5432/item_info_base]
row.names id total_sales_pri~ total_sales_qua~ click_count
<chr> <chr> <int> <int> <int>
1 1 2152 25464 2 191
2 2 2157 26278 1 42
3 3 1582 0 0 17
4 4 725 0 0 17
5 5 1610 0 0 16
6 6 2117x 0 0 15
7 7 984 0 0 15
8 8 2151 0 0 13
9 9 2051 0 0 13
10 10 1774 0 0 13
# ... with more rows, and 7 more variables:
# visitor_count <int>, conversion_rate <dbl>,
# unit_price <int>, order_count <int>, review_points <dbl>,
# review_count <int>, date <date>
#这是一个tibble的相似类型
#同时对于data.frame的统计函数对其不再适用,该类型只接受dplyr函数
05
与data.frame类型操作做对比
查看变量名:
> names(data)
[1] "src" "ops"
可以用:
> data$ops$vars
[1] "row.names" "id" "total_sales_price"
[4] "total_sales_quantity" "click_count" "visitor_count"
[7] "conversion_rate" "unit_price" "order_count"
[10] "review_points" "review_count" "date"
06
使用dplyr函数操作进行数据清洗
> data %>%
group_by(id) %>%
filter(!str_detect(id, "ss_")) %>%
summarise(sale_n = sum(order_count, na.rm = T)) %>%
arrange(desc(sale_n)) -> info
释义:
1.以“id”列进行分组
2.筛选“id”列不包含“ss_”字符的行
3.计算各“id”组下“order_count”之和,计算结果传入新建列“sale_n”,并忽略缺失值
4.根据“sale_n”的值倒序排序
5.最后赋值到info
#输出查看info中数据&类型
> print(info)
# Source: lazy query [?? x 2]
# Database: postgres 10.0.11
# [postgres@106.54.32.120:5432/item_info_base]
# Ordered by: desc(sale_n)
id sale_n
<chr> <dbl>
1 2053 75
2 2152 53
3 2151 53
4 1698 45
5 1582 38
6 1660 29
7 1314 28
8 2157 25
9 907 24
10 2104 22
# ... with more rows
> class(info)
[1] "tbl_PostgreSQLConnection" "tbl_dbi"
[3] "tbl_sql" "tbl_lazy"
[5] "tbl"
#返回数据类型仍然为tbl不变
07
dplyr代码转译为SQL代码
> sql_code <- sql_render(info)
> print(sql_code)
<SQL> SELECT "id", SUM("order_count") AS "sale_n"
FROM "dbplyr_data"
WHERE (NOT(STRPOS("id", 'ss_') > 0))
GROUP BY "id"
ORDER BY "sale_n" DESC
08
用转译后的SQL代码提取数据(方法一)
#由于上述SQL代码已经赋值在变量sql_code内,可以直接使用dbGetQuery函数将数据从数据库导入到R内存中
> real_data <- dbGetQuery(con, sql_code)
> head(real_data)
id sale_n
1 2053 75
2 2151 53
3 2152 53
4 1698 45
5 1582 38
6 1660 29
> class(real_data)
[1] "data.frame"
#我们看到得到的是实际数据,类型为data.frame
09
利用info变量提取数据(方法二)
real_data <- collect(info)
10
底层操作tbl虚拟类型&实际data.frame占用资源比较
#将实际data.frame下载下来与tbl类虚拟数据框data比较
> real_frame <- dbGetQuery(con, "select * from dbplyr_data")
> object.size(real_frame) #实际数据大小
9244240 bytes
> object.size(data) #虚拟类大小
4464 bytes
#利用虚拟tbl类型可以几乎不占用本地计算资源进行数据操作
最后
相信写到这里大家已经基本理解了dbplyr包的功能与用法,在应用到大数据源的实际生产中,可以为本地节省海量的计算资源。同时,转译SQL代码功能简直是神来一笔,这让对SQL不熟的R用户降低了与外界的沟通成本。
·END·
R语言与数据分析
生产力干货
微信号:RforData