0

I am trying to pass a .sql file into DBI::dbGetQuery, but I run into problems whenever there is comment in the first line. For example:

runs_fine.sql

SELECT * FROM mytable

does_not_work.sql

-- Some comment
SELECT * FROM mytable

Then in R:

library(odbc)
library(tidyverse)

con <- dbConnect(odbc(), "my_connection")
dbGetQuery(con, read_file("sql/runs_fine.sql")) # success
dbGetQuery(con, read_file("sql/does_not_work.sql")) # fails

Motivation: I would like to leverage the convenient sql preview function from RStudio, but this requires the first line be the following for any .sql file:

-- !preview conn=con

...which causes the dbGetQuery to fail. I suppose a workaround would be to use some sort of read.file function that can skip lines or avoid comment lines.

NOTE: If a comment line is in the middle of a .sql file, dbGetQuery succeeds as expected and avoids the appropriate commented lines.

DBMS is CHD Impala, kerberized. The return value from read_file() begins with:

"-- !preview conn=con\r\n\r\nSELECT \r\n ..."

JasonAizkalns
  • 20,243
  • 8
  • 57
  • 116
  • 1
    What is the return from `read_file` here? If I test with (for instance) a SQLite connection, I can do `DBI::dbGetQuery(con0, "-- ignore\nselect 1 as a")` without error. (And I've tested with `odbc()` connections as well, not just SQLite.) – r2evans Jan 27 '20 at 17:40
  • 1
    Perhaps two better questions: to what DBMS are you connecting, and what is the error you get? I've tested with SQLite (`RSQLite`), Postgres (both `RPostgres` and `odbc`), and SQL Server (`odbc`). – r2evans Jan 27 '20 at 17:48
  • @r2evans Apologize, should have clarified DBMS is CDH -- Impala / Hive, Kerberized if that matters. – JasonAizkalns Jan 28 '20 at 13:15
  • @r2evans, I am able to get around this by chaining the `.sql` file into some string replacements: `file.sql %>% str_replace_all("--.*|\r|\n")` but this seems silly because when a comment is in the middle this is not required. It might have to do with how DBI is passing this to Impala. Likely via `impala-shell` but have not been able reproduce the error via the shell. – JasonAizkalns Jan 28 '20 at 13:44
  • That pattern can produce false positive matches, though some of them are admittedly contrived. Do you have access through any other interface in order to test if this is specific to this DBMS? Regardless, if you are really only concerned about "leading comments" (and mid-query comments are understood correctly), then perhaps `gsub("^\\s*--([^\n\r]*)[\n\r]", "", qry)` works? The `\\s*` matches zero or more whitespace, which includes `\n` and `\r` (if that's important ... I think it is) and then the two dashes, removing anything after the dashes until the first newline/linefeed. – r2evans Jan 28 '20 at 15:20

0 Answers0