Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # Connect Via RJDBC -------------------------------------------------------
- library(RJDBC)
- drv <- JDBC("oracle.jdbc.OracleDriver", classPath="~/import/instantclient_12_2/ojdbc8.jar")
- con <- dbConnect(drv,"jdbc:oracle:thin:@server:1521:database",
- rstudioapi::askForSecret("Username"),
- rstudioapi::askForSecret("Database Password"))
- dbGetQuery(con,"select count(*) from schema.table")
- # Setup dplyr and dbplyr to work with RJDBC -------------------------------
- sql_translate_env.JDBCConnection <- dbplyr:::sql_translate_env.Oracle
- sql_select.JDBCConnection <- dbplyr:::sql_select.Oracle
- sql_subquery.JDBCConnection <- dbplyr:::sql_subquery.Oracle
- # Use dplyr and dbplyr ----------------------------------------------------
- library(dplyr)
- library(dbplyr)
- db_table <- tbl(con, in_schema("schema", "table"))
- db_table %>%
- filter(CURRENT_AGE >= 60) %>%
- mutate(age = case_when(
- CURRENT_AGE < 65 ~ "60 - 64",
- CURRENT_AGE < 70 ~ "65 - 79",
- CURRENT_AGE < 75 ~ "70 - 74",
- CURRENT_AGE < 80 ~ "75 - 79",
- CURRENT_AGE < 85 ~ "80 - 84",
- CURRENT_AGE >= 85 ~ "85+"
- )) %>%
- count(age, GENDER) %>%
- arrange(age, desc(n))
Add Comment
Please, Sign In to add comment