How to Read the Contents of an .SQL File into an R Script to Run a Query

How to read the contents of an .sql file into an R script to run a query?

I've had trouble with reading sql files myself, and have found that often times the syntax gets broken if there are any single line comments in the sql. Since in R you store the sql statement as a single line string, if there are any double dashes in the sql it will essentially comment out any code after the double dash.

This is a function that I typically use whenever I am reading in a .sql file to be used in R.

getSQL <- function(filepath){
  con = file(filepath, "r")
  sql.string <- ""

  while (TRUE){
    line <- readLines(con, n = 1)

    if ( length(line) == 0 ){
      break
    }

    line <- gsub("\\t", " ", line)

    if(grepl("--",line) == TRUE){
      line <- paste(sub("--","/*",line),"*/")
    }

    sql.string <- paste(sql.string, line)
  }

  close(con)
  return(sql.string)
}

SQL query with comments import into R from file

Are you sure you can't just use it as is? This works despite taking up multiple lines and having a comment:

> library(sqldf)
> sql <- "select * -- my select statement
+ from BOD
+ "
> sqldf(sql)
  Time demand
1    1    8.3
2    2   10.3
3    3   19.0
4    4   16.0
5    5   15.6
6    7   19.8

This works too:

> sql2 <- c("select * -- my select statement", "from BOD")
> sql2.paste <- paste(sql2, collapse = "\n")
> sqldf(sql2.paste)
  Time demand
1    1    8.3
2    2   10.3
3    3   19.0
4    4   16.0
5    5   15.6
6    7   19.8

Run SQL script from R with variables defined in R

There are several options, but my preferred is "bound parameters".

If, for instance, your 'Query.sql' looks something like

select ...
from MyTable
where CreatedDateTime > ?

The ? is a place-holder for a binding.

Then you can do

con <- dbConnect(...) # from DBI
df = dbGetQuery(con, statement = read_file('Query.sql'), params = list(dt))

With more parameters, add more ?s and more objects to the list, as in

qry <- "select ... where a > ? and b < ?"
newdat <- dbGetQuery(con, qry, params = list(var1, var2))

If you need a SQL IN clause, it gets a little dicey, since it doesn't bind things precisely like we want.

candidate_values <- c(2020, 1997, 1996, 1901)
qry <- paste("select ... where a > ? and b in (", paste(rep("?", length(candidate_values)), collapse=","), ")")
qry
# [1] "select ... where a > ? and b in ( ?,?,?,? )"
df <- dbGetQuery(con, qry, params = c(list(avar), as.list(candidate_values)))

R: read contents of text file as a query?

Scan does the job, but the function for this purpose is actually readLines().

query <- readLines("biglongquery.sql")

This gives you a vector with the lines. To combine them to one single variable, you can use the paste function, e.g.

one.variable <- paste(query,collapse="\n")

---

Related Topics