【R】dbplyr - 数据库底层操作 SQL代码转译

dbplyr

数据库底层操作 & SQL代码转译

前言

dbplyr相比R语言中传统数据库操作的优势：

1. 可使用dplyr语句操作数据库中的表

2. 可直接将dplyr语句转换为SQL语句

3. 基于数据库底层的虚拟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