In DyfanJones/connectR: Database backend for DBI, dplyr and dbplyr

Quick Overview of connectR Syntax

Connect to Databases Using src_connectR

library(tidyverse)
library(connectR)

src_connectR("PostgreSQL35W")->post

Push R Dataframes into TeraData Using copy_to

mtcars->mtcars1
mtcars1$car<-rownames(mtcars1)
rownames(mtcars1)<-NULL

Note. copy_to defaults temporary=FALSE

copy_to(post,mtcars1,"drj_mtcars",indexes = list(c("mpg","hp")),overwrite=T)

Select Columns Using select

drj_mtcars<-tbl(post,"drj_mtcars") %>% collect() 
drj_mtcars %>% select(car, contains('a'), ends_with('s')) %>% head

drj_mtcars %>% select(car, contains('a'), ends_with('s'))->drj_knitr1
knitr::kable(head(drj_knitr1))

Extract Distinct Rows Using distinct

drj_mtcars %>% select(am) %>% distinct

Add New Columns Using mutate

drj_mtcars %>% select(-mpg,-wt,hp) %>% 
  mutate(gear_no=ifelse(gear<=5,"high","low"),
         car_type=ifelse(grepl("merc",tolower(car)),"nice","ordinary"),
         cyl_sq=cyl^2,
         row_no=row_number(drat)) %>% head

drj_mtcars %>% select(-mpg,-wt,hp) %>% 
  mutate(gear_no=ifelse(gear<=5,"high","low"),
         car_type=ifelse(grepl("merc",tolower(car)),"nice","ordinary"),
         cyl_sq=cyl^2,
         row_no=row_number(drat))->drj_knitr2
knitr::kable(head(drj_knitr2))

Order Data Using arrange

drj_mtcars %>% arrange(mpg)
drj_mtcars %>% arrange(cyl, desc(mpg))

knitr::kable(head(drj_mtcars %>% arrange(cyl, desc(mpg))))

Aggregate Rows Using summarise

drj_mtcars %>% summarise(mpg_mean = mean(mpg),
                         max_disp = max(disp))

knitr::kable(drj_mtcars %>% summarise(mpg_mean = mean(mpg),
                         max_disp = max(disp)))

Group Operations Using group_by

drj_mtcars %>% group_by(cyl) %>%
  summarise(mpg_mean = mean(mpg),
            max_disp = max(disp))

knitr::kable(drj_mtcars %>% group_by(cyl) %>%
  summarise(mpg_mean = mean(mpg),
            max_disp = max(disp)))

drj_mtcars %>% group_by(cyl) %>% 
  mutate(row_number = row_number(car)) %>% 
  filter(row_number <= 3) %>% 
  select(cyl, row_number) %>% 
  arrange(cyl, row_number)

knitr::kable(drj_mtcars %>% group_by(cyl) %>% 
  mutate(row_number = row_number(car)) %>% 
  filter(row_number <= 3) %>% 
  select(cyl, row_number) %>% 
  arrange(cyl, row_number) %>% head)

Drop Data from database using db_drop_table

db_drop_table(post$con,"drj_mtcars")

Sending queries to the databse using db_send_query

db_send_query(post,"create table kaggle_test as
                        (Select
                        a.name,
                        a.style,
                        a.abv,
                        b.brewery_name,
                        b.city
                        from kaggle_beers a
                        inner join kaggle_breweries b
                        on a.brewery_id = b.brewery_id) with no data;")

#Tidying up the database
db_send_query(post,"drop table kaggle_test")

DyfanJones/connectR documentation built on May 23, 2019, 10:32 p.m.