`select field as fieldname` not `select field as 'fieldname'` with odbc and glue

[dougfir]

When constructing a query using glue, the output is as a string. However, in SQL, at least my current SQL engine, the field alias is unquoted, so this is preventing my query from running. Example:

My r script:

library(tidyverse)
library(dbplyr)
library(DBI)
library(glue)
iris_db <- tbl_memdb(iris)
con <- src_memdb()$con

var1 <- "setosa"
query <- read_lines("example.sql") %>% 
  glue_collapse(sep = "\n") %>% 
  glue_sql(.con = con)
  
setosa <- dbGetQuery(con, query)
setosa %>% glimpse()
Observations: 50
Variables: 2
$ Sepal.Length     <dbl> 5.1, 4.9, 4.7, 4.6, 5.0, 5.4, 4.6, 5.0, 4.4, 4.9, 5.4, 4.8, 4.8, 4.3, 5.8, 5.7, 5.4, 5.1, 5.7, 5.1, 5.4, 5…
$ `Species-setosa` <chr> "setosa", "setosa", "setosa", "setosa", "setosa", "setosa", "setosa", "setosa", "setosa", "setosa", "setos…

The sql script being referenced, 'example.sql':

select
  `Sepal.Length`,
  Species as {glue('Species-{var1}')}
from iris
where species = {var1}

This runs when using memdb, however my actual connection is to Athena (Presto) using odbc.

Here's how the query renders:

query
<SQL> 
select
  `Sepal.Length`,
  Species as 'Species-setosa'
from iris
where species = 'setosa'

In particular Species as 'Species-setosa' is quoted. This prevents my query from running. Is there a way to render the sql like this:

query
<SQL> 
select
  `Sepal.Length`,
  Species as Species-setosa
from iris
where species = 'setosa'

This is the part of the sql file in question Species as {glue('Species-{var1}')}.

Backup option is to mess around with regex, but would prefer to avoid that if there's a cleaner, more 'prescribed' way of approaching this?

[jdb]

If Athena allows underscores in the alias, you can accomplish what you want using rlang::parse_exprs

New example.sql

select
  `Sepal.Length`,
  Species as {rlang::parse_exprs(glue('Species_{var1}'))}
from iris
where species = {var1}

Making that change turns query into

<SQL> select
  `Sepal.Length`,
  Species as Species_setosa
from iris
where species = 'setosa'

[dougfir]

@jdb

parse_exprs

Yep, this works, thanks a lot!