Running a .sql file from R

Question

I have a few .sql files which aggregate a number of tables/views in a SQL Database. I want to be able to direct R to a specific sql file and execute it and return the results in a dataframe.

Googling around it seems that I can only grab actual tables/views which are in the database or I have to rewrite the sql query and run that through the package RODBC.

In python this can be done with pd.read_sql_query

Inaugurate

You have to make a connection to your database via RODBC or another package before you can execute SQL commands against a database. If the data exists within R, you can use the library sqldf to run SQL queries against in-memory data. – Sihunn

Connecting is fine - even with sqldf it looks like I will need to paste in the query to run (which I can just do with RODBC). I don't want to paste in the query into R, instead I have the query code in a .sql file and I just need to get R to execute that sql file and return the data which is what pd.read_sql_query can do – Inaugurate

There's nothing stopping you from reading the .sql file into R as a single character string and then sending that to the db via your RODBC connection like you would any other query you typed in yourself. There's a package called squr (possibly only on github) that does that sort of thing (and probably a lot more than you want). – Beneath

And the important thing is that it needs to be a single character string, when you read in the raw file you need to be cautious of line breaks and comments. – Sihunn

Yes that's the problem, there's a few comments all around my sql code but if that's the only way I'll have to remove them – Inaugurate

It's not that hard at all. I have my own home grown system (forked from squr) where I have whole packages with dozens of sql files, many of them have comments. It's really not that big a deal. – Beneath

sql <- paste(readLines("myfile.sql"), collapse = "\n") . Now sql is an R variable holding the SQL string. – Leibowitz

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

@G.Grothendieck how does that deal with comments in the code? – Inaugurate

Answer 1

You can utilize the readr-package in conjunction with DBI and odbc

install.packages("readr")
library("DBI")
library("odbc") 
library("readr") 
df <- dbGetQuery(con, statement = read_file('Query.sql'))

Rebellion

Also, remember that when formatting SQL queries for R you might want to utilize SET NOCOUNT ON to prevent the sending of DONE_IN_PROC messages. – Rebellion

I cant edit the response above so I will add some insight here below. I you find that queries that work perfectly in SQL does not work with R using "SET NOCOUNT ON" might do it for you. You might also want to experiment with specifying a particular DB and not. – Rebellion