The link between knitr {sql} and tidyverse::glue

knitr has a great method of interacting with sql databases through sql chunks]( in a clean way with the SQL properly formatted in the body, but still able to have "some" r parameters passed to it via ?parameter. However, it doesn't allow the user (as far as I've found) to pass in vectors of values to mimic things like WHERE value IN (1,2,3,4) easily.

The glue package has great methods to deal with the situation above, but doesn't format nicely in print and decreases readability by using sprintf and paste-ish formats and requiring the query to be in a string.

Is there a method to allow knitr chunks to accept vectors like glue? e.g.

R chunk

values <- c(1,2,3)
table <- iris

SQL chunk

FROM {table}
WHERE value in {values*}

It should be possible to define a glue (and glue_sql) knitr engine.

1 Like

Thanks @jimhester, I'll look into doing that myself (though I suspect it might be a little beyond me). The package itself is very good, thanks for your work :slight_smile:

Ha, in looking at how a knitr engine works I think I've just found your pull request... XD

I have an implementation for a glue_sql engine at you can use. Until knitr#1468 is merged you will have to use either

{r engine = "glue_sql"}



To start the block. Also be sure you attach the glue package with library(glue) before trying to use the engine.


Fantastic, thank you very much! I'll try it out ASAP :slight_smile:

Afraid I'm having some trouble, I'm probably missing something.

If I install the new version of glue

devtools::install_github("tidyverse/glue", ref = "69bc72ce83ed997953511041e6b52d0f73b7e1dc")

I get a successful install message DONE (glue)

In a new notebook I have libraries loaded:


I also have an open ODBC connection that will run normal {sql}, but when it comes to a chunk with the header:

```{r engine = "glue_sql"}
FROM table
WHERE value IN ({values*})

And alternatively

FROM table
WHERE value IN ({values*})

I only get the following when running the chunk

/bin/sh: glue_sql: command not found


/bin/sh: gluesql: command not found

respectively. What have I missed?

Sorry for the hassle.

It doesn't work in a notebook, which likely needs extra tooling. But should work fine in a regular Rmd.

When I convert the yaml to output: html_document, I get this error on the first line that the {gluesql} or {r engine = "glue_sql"} chunk:

Error: All unamed arguments must be length 1 Execution halted

apologies :frowning:

It would be helpful to see exactly what code you are trying to run.

Here is a reproducible example

and the output on my machine

Fixed it! Totally a problem on my side, sorry.

TL;DR I assumed that install.github() would overwrite the cran install of the package, but it actually installed it in a secondary location, so both packages were available at different points in my .libpaths() list.

Your gist runs just as expected, and a slightly modified version runs on the Redshift cluster I am currently using.

Thank you very much for your work :slight_smile: