dbplyr basics

diamonds_db <- tbl(con, "diamonds")

diamonds_db

## # Source:   table<diamonds> [?? x 10]
## # Database: DuckDB v1.0.0 [unknown@Linux 6.5.0-1023-azure:R 4.4.1/:memory:]
##    carat cut       color clarity depth table price     x     y     z
##    <dbl> <fct>     <fct> <fct>   <dbl> <dbl> <int> <dbl> <dbl> <dbl>
##  1  0.23 Ideal     E     SI2      61.5    55   326  3.95  3.98  2.43
##  2  0.21 Premium   E     SI1      59.8    61   326  3.89  3.84  2.31
##  3  0.23 Good      E     VS1      56.9    65   327  4.05  4.07  2.31
##  4  0.29 Premium   I     VS2      62.4    58   334  4.2   4.23  2.63
##  5  0.31 Good      J     SI2      63.3    58   335  4.34  4.35  2.75
##  6  0.24 Very Good J     VVS2     62.8    57   336  3.94  3.96  2.48
##  7  0.24 Very Good I     VVS1     62.3    57   336  3.95  3.98  2.47
##  8  0.26 Very Good H     SI1      61.9    55   337  4.07  4.11  2.53
##  9  0.22 Fair      E     VS2      65.1    61   337  3.87  3.78  2.49
## 10  0.23 Very Good H     VS1      59.4    61   338  4     4.05  2.39
## # ℹ more rows

big_diamonds_db <- diamonds_db |> 
  filter(price > 15000) |> 
  select(carat:clarity, price)

big_diamonds_db

## # Source:   SQL [?? x 5]
## # Database: DuckDB v1.0.0 [unknown@Linux 6.5.0-1023-azure:R 4.4.1/:memory:]
##    carat cut       color clarity price
##    <dbl> <fct>     <fct> <fct>   <int>
##  1  1.54 Premium   E     VS2     15002
##  2  1.19 Ideal     F     VVS1    15005
##  3  2.1  Premium   I     SI1     15007
##  4  1.69 Ideal     D     SI1     15011
##  5  1.5  Very Good G     VVS2    15013
##  6  1.73 Very Good G     VS1     15014
##  7  2.02 Premium   G     SI2     15014
##  8  2.05 Very Good F     SI2     15017
##  9  1.5  Very Good F     VS1     15022
## 10  1.82 Very Good G     SI1     15025
## # ℹ more rows

big_diamonds_db |>
  show_query()

## <SQL>
## SELECT carat, cut, color, clarity, price
## FROM diamonds
## WHERE (price > 15000.0)

• collect() moves data into R

big_diamonds <- big_diamonds_db |> 
  collect()
big_diamonds

## # A tibble: 1,655 × 5
##    carat cut       color clarity price
##    <dbl> <fct>     <fct> <fct>   <int>
##  1  1.54 Premium   E     VS2     15002
##  2  1.19 Ideal     F     VVS1    15005
##  3  2.1  Premium   I     SI1     15007
##  4  1.69 Ideal     D     SI1     15011
##  5  1.5  Very Good G     VVS2    15013
##  6  1.73 Very Good G     VS1     15014
##  7  2.02 Premium   G     SI2     15014
##  8  2.05 Very Good F     SI2     15017
##  9  1.5  Very Good F     VS1     15022
## 10  1.82 Very Good G     SI1     15025
## # ℹ 1,645 more rows