Agenda


  • connect to databases from R
  • display database information
  • list tables
  • create table
  • read data into R
  • export data to database
  • remove tables
  • query data using dplyr
  • disconnect from database

Libraries


library(DBI)
library(dbplyr)
library(dplyr)

Data


ecom <- read_csv('https://raw.githubusercontent.com/rsquaredacademy/datasets/master/web.csv')
## # A tibble: 1,000 x 11
##       id referrer device bouncers n_visit n_pages duration country       
##    <int> <chr>    <chr>  <chr>      <int>   <dbl>    <dbl> <chr>         
##  1     1 google   laptop true          10       1      693 Czech Republic
##  2     2 yahoo    tablet true           9       1      459 Yemen         
##  3     3 direct   laptop true           0       1      996 Brazil        
##  4     4 bing     tablet false          3      18      468 China         
##  5     5 yahoo    mobile true           9       1      955 Poland        
##  6     6 yahoo    laptop false          5       5      135 South Africa  
##  7     7 yahoo    mobile true          10       1       75 Bangladesh    
##  8     8 direct   mobile true          10       1      908 Indonesia     
##  9     9 bing     mobile false          3      19      209 Netherlands   
## 10    10 google   mobile true           6       1      208 Czech Republic
## # ... with 990 more rows, and 3 more variables: purchase <chr>,
## #   order_items <dbl>, order_value <dbl>

Data Dictionary


  • id: row id
  • referrer: referrer website/search engine
  • os: operating system
  • browser: browser
  • device: device used to visit the website
  • n_pages: number of pages visited
  • duration: time spent on the website (in seconds)
  • repeat: frequency of visits
  • country: country of origin
  • purchase: whether visitor purchased
  • order_value: order value of visitor (in dollars)

Connect


con <- DBI::dbConnect(RSQLite::SQLite(), 
                      dbname = "mydatabase.db")

Connection Summary


summary(con)
##           Length            Class             Mode 
##                1 SQLiteConnection               S4

List Tables


dbListTables(con)
## [1] "COMPANY"    "DEPARTMENT" "ecom"

List Fields


DBI::dbListFields(con, "ecom")
##  [1] "id"          "referrer"    "device"      "bouncers"    "n_visit"    
##  [6] "n_pages"     "duration"    "country"     "purchase"    "order_items"
## [11] "order_value"

Querying Data


  • read entire table
  • read few rows
  • read data in batches

Entire Table


DBI::dbReadTable(con, 'ecom')
##        id referrer device bouncers n_visit n_pages duration
## 1       1   google laptop     true      10       1      693
## 2       2    yahoo tablet     true       9       1      459
## 3       3   direct laptop     true       0       1      996
## 4       4     bing tablet    false       3      18      468
## 5       5    yahoo mobile     true       9       1      955
## 6       6    yahoo laptop    false       5       5      135
## 7       7    yahoo mobile     true      10       1       75
## 8       8   direct mobile     true      10       1      908
## 9       9     bing mobile    false       3      19      209
## 10     10   google mobile     true       6       1      208
## 11     11   direct laptop     true       9       1      738
## 12     12   direct tablet    false       6      12      132
## 13     13   direct mobile    false       9      14      406
## 14     14    yahoo tablet    false       5       8       80
## 15     15    yahoo mobile    false       7       1       19
## 16     16     bing laptop     true       1       1      995
## 17     17     bing tablet    false       5      16      368
## 18     18   google tablet     true       7       1      406
## 19     19   social tablet    false       7      10      290
## 20     20   social tablet    false       2       1       28
## 21     21   direct laptop     true       2       1      384
## 22     22   google mobile     true       5       1      147
## 23     23     bing mobile    false       0       7      196
## 24     24   social laptop     true       4       1      850
## 25     25    yahoo laptop     true       1       1      779
## 26     26   social laptop     true       7       1      574
## 27     27   direct tablet    false       2      19      342
## 28     28   google laptop     true       9       1      126
## 29     29   google mobile     true      10       1      338
## 30     30    yahoo mobile    false       8       9      225
## 31     31   social tablet     true       2       1      795
## 32     32   direct mobile     true       2       1      501
## 33     33   direct laptop     true       8       1      658
## 34     34   social tablet    false       9      20      420
## 35     35   direct mobile     true       6       1      404
## 36     36     bing mobile    false       1       1       25
## 37     37     bing laptop     true       0       1      897
## 38     38    yahoo mobile     true       3       1      700
## 39     39   google mobile     true       0       1      796
## 40     40   google mobile     true      10       1      479
## 41     41     bing mobile    false       4      20      440
## 42     42   direct mobile    false       1      13      234
## 43     43     bing laptop     true       0       1      456
## 44     44    yahoo tablet    false       7      11      264
## 45     45     bing laptop     true       7       1      867
## 46     46    yahoo tablet     true       7       1      799
## 47     47     bing mobile     true       6       1      843
## 48     48    yahoo laptop     true       4       1      781
## 49     49   social mobile    false       1       2       44
## 50     50     bing tablet     true       5       1      831
## 51     51   direct mobile     true       9       1      898
## 52     52     bing mobile     true       7       1      569
## 53     53   social tablet    false       3      12      324
## 54     54   social tablet     true       4       1       53
## 55     55   direct mobile     true       0       1      566
## 56     56   google tablet    false       1      19      304
## 57     57   google laptop    false       1       8      136
## 58     58   google tablet    false       9       3       57
## 59     59    yahoo tablet     true       9       1      706
## 60     60     bing laptop     true       4       1      748
## 61     61   google laptop     true       0       1       75
## 62     62   direct tablet     true       8       1      814
## 63     63   direct mobile    false      10       1       17
## 64     64    yahoo tablet    false       3       5      140
## 65     65    yahoo laptop    false       5       8      136
## 66     66   google tablet    false       6       6      114
## 67     67    yahoo tablet     true       3       1      332
## 68     68   direct mobile     true       7       1      912
## 69     69     bing tablet     true       1       1      406
## 70     70     bing mobile     true       5       1      190
## 71     71   social laptop     true       0       1      874
## 72     72   social laptop    false       5      16      176
## 73     73   google tablet     true       4       1      565
## 74     74   social tablet     true       5       1      934
## 75     75   direct tablet     true       9       1       47
## 76     76    yahoo tablet    false       5       3       78
## 77     77    yahoo laptop     true       4       1      928
## 78     78   social laptop    false       4      19      399
## 79     79     bing mobile     true       9       1      823
## 80     80   social laptop    false       8      12      276
## 81     81   social laptop     true       8       1      581
## 82     82   direct tablet     true       7       1      219
## 83     83   social mobile     true       5       1      613
## 84     84    yahoo tablet     true       4       1      804
## 85     85   google tablet     true       3       1      652
## 86     86    yahoo tablet     true       0       1      824
## 87     87   social tablet    false       9      16      432
## 88     88    yahoo tablet    false       3      11      176
## 89     89   direct mobile    false       4       8      144
## 90     90     bing mobile    false       7       1       22
## 91     91   social laptop     true       3       1      365
## 92     92   google mobile    false       5       8      192
## 93     93   google laptop     true       9       1      869
## 94     94    yahoo tablet    false       2      16      480
## 95     95   social tablet     true       1       1      566
## 96     96   direct tablet    false      10       3       57
## 97     97     bing tablet     true       2       1      292
## 98     98     bing mobile    false       3      18      288
## 99     99    yahoo mobile     true       1       1       73
## 100   100   google laptop     true       5       1      751
## 101   101    yahoo tablet    false       2      14      364
## 102   102   google mobile    false       2       3       69
## 103   103   direct laptop    false       0       7      196
## 104   104    yahoo tablet    false       6      18      432
## 105   105   direct mobile     true       1       1      423
## 106   106   direct mobile     true       6       1      227
## 107   107   social mobile     true       2       1      267
## 108   108   google laptop     true       9       1      174
## 109   109   google tablet     true       9       1      382
## 110   110   direct mobile    false       5       5       55
## 111   111   social tablet     true       9       1      717
## 112   112   social mobile    false      10      11      242
## 113   113    yahoo tablet    false       7      16      224
## 114   114     bing tablet    false       0      14      280
## 115   115   google laptop    false       1       6       72
## 116   116   direct mobile     true      10       1      675
## 117   117     bing tablet    false       6      16      176
## 118   118   direct laptop    false       2      16      400
## 119   119     bing laptop     true       1       1      938
## 120   120    yahoo mobile    false       2       5       95
## 121   121   direct laptop     true       4       1      468
## 122   122   direct laptop     true       3       1      768
## 123   123     bing mobile     true       5       1      751
## 124   124   social mobile     true       0       1      345
## 125   125    yahoo mobile    false       6      14      322
## 126   126   direct mobile     true       6       1      981
## 127   127   social tablet     true       2       1      576
## 128   128     bing tablet    false       9       2       50
## 129   129   google tablet     true       8       1      249
## 130   130   direct laptop     true       6       1      178
## 131   131     bing tablet    false       3      15      195
## 132   132     bing tablet    false       0       3       42
## 133   133   direct mobile    false       2       4       76
## 134   134   google mobile    false       1      18      252
## 135   135   social mobile    false       0       3       78
## 136   136    yahoo tablet    false       0      20      200
## 137   137    yahoo mobile    false       5       3       30
## 138   138    yahoo tablet     true       6       1      297
## 139   139    yahoo laptop    false       8       6       60
## 140   140   social laptop     true       5       1      286
## 141   141    yahoo tablet     true       1       1      949
## 142   142   google laptop    false       1       7      126
## 143   143   social mobile    false       7      16      352
## 144   144   social tablet     true       3       1      225
## 145   145   google laptop    false       5       9      261
## 146   146   google laptop     true       7       1      792
## 147   147   direct tablet    false       4       5       55
## 148   148    yahoo laptop     true       5       1      739
## 149   149     bing tablet    false       6       3       54
## 150   150     bing laptop    false       5      10      190
## 151   151   social tablet     true       6       1      133
## 152   152    yahoo mobile    false       6       3       39
## 153   153   google tablet     true      10       1       86
## 154   154   social laptop     true       0       1      720
## 155   155   google mobile    false       8      10      240
## 156   156   direct mobile    false       4      18      324
## 157   157   social laptop    false       9       6       66
## 158   158   google tablet    false       7      12      324
## 159   159   social laptop    false       2      16      288
## 160   160    yahoo tablet     true       6       1       67
## 161   161   google laptop     true       5       1      806
## 162   162   social tablet     true       8       1      502
## 163   163   social laptop     true       1       1       35
## 164   164   social mobile     true      10       1      479
## 165   165   social laptop    false       5       3       90
## 166   166   direct tablet    false       6      20      580
## 167   167   social mobile    false       2       2       36
## 168   168   direct tablet    false       7      15      240
## 169   169   direct laptop    false       6       6       96
## 170   170     bing tablet     true       0       1      985
## 171   171   direct laptop    false       7       5       95
## 172   172    yahoo laptop     true       8       1      109
## 173   173   google mobile    false       2       9      162
## 174   174     bing mobile    false       9       1       22
## 175   175    yahoo mobile    false       4      13      364
## 176   176    yahoo laptop     true       2       1      506
## 177   177     bing laptop    false       5       1       20
## 178   178   direct mobile    false       9      15      210
## 179   179   google laptop     true       2       1      773
## 180   180    yahoo tablet     true       6       1      680
## 181   181     bing mobile     true       9       1      650
## 182   182     bing mobile    false       9       2       42
## 183   183     bing mobile    false       3      15      450
## 184   184   social laptop     true       5       1      544
## 185   185   google mobile    false       8       2       44
## 186   186   google tablet     true       5       1      169
## 187   187     bing laptop    false       1       2       22
## 188   188    yahoo tablet     true       3       1      664
## 189   189   direct mobile     true       2       1      338
## 190   190   social tablet    false       4       8       80
## 191   191     bing mobile     true       4       1      366
## 192   192     bing laptop     true       1       1      989
## 193   193    yahoo tablet    false       1      12      156
## 194   194   google laptop     true       3       1      978
## 195   195   direct laptop     true       3       1      510
## 196   196   direct laptop    false       5      18      540
## 197   197     bing mobile     true       1       1       67
## 198   198     bing tablet     true       0       1      352
## 199   199    yahoo laptop    false      10      11      110
## 200   200   google tablet     true      10       1      905
## 201   201   social laptop    false       7      15      210
## 202   202    yahoo tablet    false       9      13      182
## 203   203   direct tablet     true       6       1       54
## 204   204   direct tablet     true       5       1       32
## 205   205    yahoo tablet     true       0       1       28
## 206   206   google mobile     true       7       1      241
## 207   207   google tablet    false      10       8      200
## 208   208     bing tablet    false       0       2       52
## 209   209    yahoo laptop     true       3       1      373
## 210   210   social mobile     true      10       1      102
## 211   211     bing tablet     true       4       1      493
## 212   212   social tablet     true       4       1      578
## 213   213     bing laptop     true       7       1      811
## 214   214   direct laptop    false       8      10      210
## 215   215    yahoo mobile     true      10       1      594
## 216   216    yahoo laptop     true       3       1      812
## 217   217   google mobile    false       9       9      135
## 218   218   social laptop     true       5       1      553
## 219   219   social mobile    false       1      20      520
## 220   220   social tablet    false       6       6      108
## 221   221   direct tablet    false       6      19      304
## 222   222   direct laptop    false       8       5      150
## 223   223   social tablet    false       1       8      128
## 224   224   social mobile    false       5       7      140
## 225   225   direct tablet    false       3      12      324
## 226   226   google laptop     true       7       1      928
## 227   227    yahoo mobile    false       0      13      351
## 228   228   direct tablet     true      10       1      497
## 229   229   google mobile     true      10       1      948
## 230   230     bing tablet    false       8       6      180
## 231   231     bing tablet     true       0       1      697
## 232   232   social laptop    false       8       2       60
## 233   233   google tablet    false       3       3       78
## 234   234   social tablet     true      10       1      674
## 235   235    yahoo laptop     true       7       1      300
## 236   236    yahoo tablet     true       5       1       10
## 237   237   social laptop     true       8       1      152
## 238   238    yahoo mobile     true       1       1      257
## 239   239   social mobile    false       2      10      190
## 240   240   direct laptop    false       6       4      104
## 241   241    yahoo mobile    false      10      11      209
## 242   242     bing mobile    false      10      13      221
## 243   243     bing tablet    false       6       1       22
## 244   244   google mobile    false       5      13      390
## 245   245   social laptop     true       9       1      164
## 246   246   direct mobile     true       9       1      207
## 247   247     bing laptop     true       1       1      148
## 248   248     bing tablet     true       5       1      151
## 249   249   google mobile    false      10       8       88
## 250   250     bing laptop     true       3       1      422
## 251   251   social tablet    false       3       5       95
## 252   252     bing tablet     true       3       1      798
## 253   253   google mobile    false       8      20      300
## 254   254    yahoo laptop    false       8      13      364
## 255   255   google tablet     true       6       1      216
## 256   256   social laptop    false      10      10      180
## 257   257     bing laptop    false       5      13      286
## 258   258   google mobile    false       3      17      510
## 259   259   google mobile     true       3       1      553
## 260   260    yahoo laptop    false      10      10      290
## 261   261   social laptop     true       0       1      581
## 262   262   google mobile    false       9      17      221
## 263   263   social mobile     true      10       1      432
## 264   264    yahoo laptop    false       7       2       30
## 265   265     bing laptop     true      10       1      777
## 266   266   social laptop     true       8       1      735
## 267   267    yahoo laptop     true       5       1      994
## 268   268    yahoo tablet     true       6       1      669
## 269   269    yahoo laptop    false       9       9      135
## 270   270    yahoo mobile    false       4       9      189
## 271   271   social laptop    false       2       5      150
## 272   272   direct mobile    false       6      12      324
## 273   273   social laptop     true       4       1      794
## 274   274    yahoo mobile    false      10      18      270
## 275   275     bing laptop     true       8       1      484
## 276   276   social laptop    false       4      20      200
## 277   277   google tablet    false       8       1       22
## 278   278    yahoo laptop    false       5      11      143
## 279   279    yahoo laptop    false       7      15      150
## 280   280    yahoo mobile     true       2       1       90
## 281   281   social laptop     true      10       1       81
## 282   282   direct laptop     true       4       1      314
## 283   283   social mobile     true       5       1       22
## 284   284   social mobile     true       9       1      803
## 285   285   direct mobile    false       8      16      464
## 286   286   google tablet     true       4       1       86
## 287   287    yahoo mobile     true       7       1      520
## 288   288     bing tablet    false       5       6      138
## 289   289    yahoo mobile     true       1       1      376
## 290   290     bing mobile    false       4      18      414
## 291   291    yahoo laptop     true       5       1      611
## 292   292    yahoo tablet    false       0      13      390
## 293   293     bing mobile     true      10       1      480
## 294   294    yahoo laptop    false       0       4       52
## 295   295     bing laptop    false      10      16      304
## 296   296   google mobile    false       5       7      133
## 297   297     bing mobile    false      10       8      152
## 298   298     bing laptop    false       5      16      192
## 299   299    yahoo laptop    false      10      18      180
## 300   300   direct laptop     true       9       1      296
## 301   301    yahoo tablet    false       7      10      240
## 302   302   google laptop    false       6       9      135
## 303   303     bing mobile     true       4       1      979
## 304   304   social laptop     true       7       1      185
## 305   305     bing tablet    false       2      11      220
## 306   306   direct mobile    false       7       7       98
## 307   307   google laptop    false       3       8      192
## 308   308    yahoo tablet    false       8      14      322
## 309   309   google mobile     true       0       1      327
## 310   310   social laptop    false       6       5       50
## 311   311   direct tablet     true       2       1      891
## 312   312    yahoo tablet    false       1      18      270
## 313   313    yahoo laptop    false       1      16      400
## 314   314    yahoo mobile    false       3      20      480
## 315   315     bing tablet    false       2       1       23
## 316   316   direct laptop     true       2       1      149
## 317   317   google mobile    false       3      18      270
## 318   318   direct laptop     true       8       1      298
## 319   319     bing laptop    false      10       6       90
## 320   320     bing laptop    false       4       3       48
## 321   321     bing tablet    false       1      17      170
## 322   322     bing tablet     true       3       1      192
## 323   323   google mobile     true       1       1      417
## 324   324   direct laptop    false       9      17      425
## 325   325     bing tablet     true       9       1      281
## 326   326    yahoo mobile     true       4       1      464
## 327   327     bing laptop     true