6

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

zx8754
  • 52,746
  • 12
  • 114
  • 209
user33484
  • 740
  • 2
  • 9
  • 36
  • 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. – Mako212 Dec 05 '18 at 17:36
  • 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 – user33484 Dec 05 '18 at 17:38
  • 2
    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). – joran Dec 05 '18 at 17:39
  • 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. – Mako212 Dec 05 '18 at 17:41
  • 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 – user33484 Dec 05 '18 at 17:43
  • 1
    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. – joran Dec 05 '18 at 17:44
  • 2
    `sql <- paste(readLines("myfile.sql"), collapse = "\n")` . Now `sql` is an R variable holding the SQL string. – G. Grothendieck Dec 05 '18 at 18:54
  • 1
    Possible duplicate of [How to read the contents of an .sql file into an R script to run a query?](https://stackoverflow.com/questions/44853322/how-to-read-the-contents-of-an-sql-file-into-an-r-script-to-run-a-query) – ismirsehregal Dec 05 '18 at 19:27
  • @G.Grothendieck how does that deal with comments in the code? – user33484 Dec 05 '18 at 20:49

1 Answers1

11

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'))
Steffen Moritz
  • 7,277
  • 11
  • 36
  • 55
Igelkatt
  • 111
  • 1
  • 5
  • 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. – Igelkatt Apr 25 '21 at 11:35
  • 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. – Igelkatt Aug 30 '21 at 06:21