Read a Query from .sql File
A minimal example showing how to read the contents of an .sql file and execute the query from R
So, I typically write SQL queries directly in R as strings because I often want to pass function arguments to them, e.g.
<- function(year) {
make_query <- paste("SELECT * FROM my_table WHERE year = " year)
ret return(ret)
}
But sometimes I find it easier to just write a canned query in a .sql
file, often when I’m using that query in another non-R program, or when I need one “true” query that gets passed around to various other applications. If we need to read this into R, we just need a combination of paste()
and readLines()
.
Imagine we have the following query in my_query.sql
SELECT *
FROM my_table
And then we want to read that query in and execute it in R. We would just do:
library(DBI)
<- dbConnect(
con #specify arguments here to connect to your DB
)
<- paste(readLines("my_query.sql"), collapse = "\n")
qry
<- dbGetQuery(con, qry) res
And that will do it!