Working With Databases and SQL in RStudio | R-bloggers

• Read original article here

% summarise(night_sum = sum(night), ocean_sum = sum(ocean), night_and_ocean_sum = sum(night_and_ocean)) # Source: lazy query [?? x 3] # Database: postgres [ivelasq3_demo_db_connection@db.bit.io:5432/ivelasq3] night_sum ocean_sum night_and_ocean_sum 1 11 36 4 Using the function collect(), we can then use our data with other functions or R packages such as ggplot2. library(ggplot2) tbl_ggplot % collect() %>% rowwise() %>% mutate(total_number = as.numeric(sum(c_across(where(is.numeric))))) %>% ggplot(aes(total_number)) + geom_histogram(fill = "#A4C689") + theme_minimal() + xlab("Number of elements by episode") R Markdown Would you rather write verbatim SQL code? You can run SQL code in an R Markdown document. Create a sql code chunk and specify your connection with the connection = con code chunk option. ```{sql, connection = con} SELECT episode, title FROM "ivelasq3/elements"."elements" LIMIT 3 ``` Table 1: 3 records episode title S01E01 “"A WALK IN THE WOODS"” S01E02 “"MT. MCKINLEY"” S01E03 “"EBONY SUNSET"” R Markdown provides options that simplify using SQL with R. For example, this post shows how you can use the cat engine to write the content of a chunk to a file. ```{cat, engine.opts = list(file = "select_tbl.sql", lang = "sql")} SELECT episode, title FROM "ivelasq3/elements"."elements" LIMIT 3 ``` You can read in the file using the code chunk option so you do not have to write out your SQL query. ```{sql, connection = con, code=readLines("select_tbl.sql")} ``` You can send the query output to an R data frame by defining output.var in the code chunk. Then you can reuse that data frame elsewhere in your code. ```{sql, connection = con, code=readLines("select_tbl.sql"), output.var = "dat"} ``` ```{r} print(dat) ``` episode title 1 S01E01 "\"A WALK IN THE WOODS\"" 2 S01E02 "\"MT. MCKINLEY\"" 3 S01E03 "\"EBONY SUNSET\"" These options make working with SQL in R Markdown even smoother. Learn More This blog post just touched on a few examples of how to work with databases and SQL in RStudio. Check out more resources below. Read how to use RStudio products and packages with databases on our website, https://db.rstudio.com/. This comprehensive website provides more information on working with databases in RStudio as well as examples of best practices. Learn more about RStudio’s SQL integration. Explore the powerful package dbplyr. Find out more about the SQL engine in R Markdown. Check out some great talks by Irene Steves, Ian Cook, and Edgar Ruiz." />
% summarise(night_sum = sum(night), ocean_sum = sum(ocean), night_and_ocean_sum = sum(night_and_ocean)) # Source: lazy query [?? x 3] # Database: postgres [ivelasq3_demo_db_connection@db.bit.io:5432/ivelasq3] night_sum ocean_sum night_and_ocean_sum 1 11 36 4 Using the function collect(), we can then use our data with other functions or R packages such as ggplot2. library(ggplot2) tbl_ggplot % collect() %>% rowwise() %>% mutate(total_number = as.numeric(sum(c_across(where(is.numeric))))) %>% ggplot(aes(total_number)) + geom_histogram(fill = "#A4C689") + theme_minimal() + xlab("Number of elements by episode") R Markdown Would you rather write verbatim SQL code? You can run SQL code in an R Markdown document. Create a sql code chunk and specify your connection with the connection = con code chunk option. ```{sql, connection = con} SELECT episode, title FROM "ivelasq3/elements"."elements" LIMIT 3 ``` Table 1: 3 records episode title S01E01 “"A WALK IN THE WOODS"” S01E02 “"MT. MCKINLEY"” S01E03 “"EBONY SUNSET"” R Markdown provides options that simplify using SQL with R. For example, this post shows how you can use the cat engine to write the content of a chunk to a file. ```{cat, engine.opts = list(file = "select_tbl.sql", lang = "sql")} SELECT episode, title FROM "ivelasq3/elements"."elements" LIMIT 3 ``` You can read in the file using the code chunk option so you do not have to write out your SQL query. ```{sql, connection = con, code=readLines("select_tbl.sql")} ``` You can send the query output to an R data frame by defining output.var in the code chunk. Then you can reuse that data frame elsewhere in your code. ```{sql, connection = con, code=readLines("select_tbl.sql"), output.var = "dat"} ``` ```{r} print(dat) ``` episode title 1 S01E01 "\"A WALK IN THE WOODS\"" 2 S01E02 "\"MT. MCKINLEY\"" 3 S01E03 "\"EBONY SUNSET\"" These options make working with SQL in R Markdown even smoother. Learn More This blog post just touched on a few examples of how to work with databases and SQL in RStudio. Check out more resources below. Read how to use RStudio products and packages with databases on our website, https://db.rstudio.com/. This comprehensive website provides more information on working with databases in RStudio as well as examples of best practices. Learn more about RStudio’s SQL integration. Explore the powerful package dbplyr. Find out more about the SQL engine in R Markdown. Check out some great talks by Irene Steves, Ian Cook, and Edgar Ruiz." />
% summarise(night_sum = sum(night), ocean_sum = sum(ocean), night_and_ocean_sum = sum(night_and_ocean)) # Source: lazy query [?? x 3] # Database: postgres [ivelasq3_demo_db_connection@db.bit.io:5432/ivelasq3] night_sum ocean_sum night_and_ocean_sum 1 11 36 4 Using the function collect(), we can then use our data with other functions or R packages such as ggplot2. library(ggplot2) tbl_ggplot % collect() %>% rowwise() %>% mutate(total_number = as.numeric(sum(c_across(where(is.numeric))))) %>% ggplot(aes(total_number)) + geom_histogram(fill = "#A4C689") + theme_minimal() + xlab("Number of elements by episode") R Markdown Would you rather write verbatim SQL code? You can run SQL code in an R Markdown document. Create a sql code chunk and specify your connection with the connection = con code chunk option. ```{sql, connection = con} SELECT episode, title FROM "ivelasq3/elements"."elements" LIMIT 3 ``` Table 1: 3 records episode title S01E01 “"A WALK IN THE WOODS"” S01E02 “"MT. MCKINLEY"” S01E03 “"EBONY SUNSET"” R Markdown provides options that simplify using SQL with R. For example, this post shows how you can use the cat engine to write the content of a chunk to a file. ```{cat, engine.opts = list(file = "select_tbl.sql", lang = "sql")} SELECT episode, title FROM "ivelasq3/elements"."elements" LIMIT 3 ``` You can read in the file using the code chunk option so you do not have to write out your SQL query. ```{sql, connection = con, code=readLines("select_tbl.sql")} ``` You can send the query output to an R data frame by defining output.var in the code chunk. Then you can reuse that data frame elsewhere in your code. ```{sql, connection = con, code=readLines("select_tbl.sql"), output.var = "dat"} ``` ```{r} print(dat) ``` episode title 1 S01E01 "\"A WALK IN THE WOODS\"" 2 S01E02 "\"MT. MCKINLEY\"" 3 S01E03 "\"EBONY SUNSET\"" These options make working with SQL in R Markdown even smoother. Learn More This blog post just touched on a few examples of how to work with databases and SQL in RStudio. Check out more resources below. Read how to use RStudio products and packages with databases on our website, https://db.rstudio.com/. This comprehensive website provides more information on working with databases in RStudio as well as examples of best practices. Learn more about RStudio’s SQL integration. Explore the powerful package dbplyr. Find out more about the SQL engine in R Markdown. Check out some great talks by Irene Steves, Ian Cook, and Edgar Ruiz." />
Photo by Conny Schneider on Unsplash Relational databases are a common way to store information, and SQL is a widely-used language for managing data held in these systems. RStudio provides several options to work with these crucial tools. Let’s explore using a PostgreSQL database that contains FiveThirtyEight’s data ...
" />
Photo by Conny Schneider on Unsplash Relational databases are a common way to store information, and SQL is a widely-used language for managing data held in these systems. RStudio provides several options to work with these crucial tools. Let’s explore using a PostgreSQL database that contains FiveThirtyEight’s data ...
">
% summarise(night_sum = sum(night), ocean_sum = sum(ocean), night_and_ocean_sum = sum(night_and_ocean)) # Source: lazy query [?? x 3] # Database: postgres [ [email protected] :5432/ivelasq3] night_sum ocean_sum night_and_ocean_sum 1 11 36 4 Using the function collect(), we can then use our data with other functions or R packages such as ggplot2. library(ggplot2) tbl_ggplot % collect() %>% rowwise() %>% mutate(total_number = as.numeric(sum(c_across(where(is.numeric))))) %>% ggplot(aes(total_number)) + geom_histogram(fill = "#A4C689") + theme_minimal() + xlab("Number of elements by episode") R Markdown Would you rather write verbatim SQL code? You can run SQL code in an R Markdown document. Create a sql code chunk and specify your connection with the connection = con code chunk option. ```{sql, connection = con} SELECT episode, title FROM "ivelasq3/elements"."elements" LIMIT 3 ``` Table 1: 3 records episode title S01E01 “"A WALK IN THE WOODS"” S01E02 “"MT. MCKINLEY"” S01E03 “"EBONY SUNSET"” R Markdown provides options that simplify using SQL with R. For example, this post shows how you can use the cat engine to write the content of a chunk to a file. ```{cat, engine.opts = list(file = "select_tbl.sql", lang = "sql")} SELECT episode, title FROM "ivelasq3/elements"."elements" LIMIT 3 ``` You can read in the file using the code chunk option so you do not have to write out your SQL query. ```{sql, connection = con, code=readLines("select_tbl.sql")} ``` You can send the query output to an R data frame by defining output.var in the code chunk. Then you can reuse that data frame elsewhere in your code. ```{sql, connection = con, code=readLines("select_tbl.sql"), output.var = "dat"} ``` ```{r} print(dat) ``` episode title 1 S01E01 "\\"A WALK IN THE WOODS\\"" 2 S01E02 "\\"MT. MCKINLEY\\"" 3 S01E03 "\\"EBONY SUNSET\\"" These options make working with SQL in R Markdown even smoother. Learn More This blog post just touched on a few examples of how to work with databases and SQL in RStudio. Check out more resources below. Read how to use RStudio products and packages with databases on our website, https://db.rstudio.com/. This comprehensive website provides more information on working with databases in RStudio as well as examples of best practices. Learn more about RStudio’s SQL integration. Explore the powerful package dbplyr. Find out more about the SQL engine in R Markdown. Check out some great talks by Irene Steves, Ian Cook, and Edgar Ruiz. " />