Home > Enterprise >  how to change dbplyr's show_query() quote covention for snowflake SQL
how to change dbplyr's show_query() quote covention for snowflake SQL

Time:07-29

I am using dbplyr's package to translate my dplyr's query to SQL and it works really well, however when I copy and paste my translated SQL statement it wont run in snowflake because the quotes the columns with ` (the key above tab), whereas my snowflake SQL will only run if its columns are quoted with either " (double quote) ' (single quote) or no quote at all (if there are no breaks).

Is there a way to change the dbplyr::show_query() argument so that the outcome is in double quotes or single quotes instead of backtick? there is a con argument which I've set to simulate_snowflake() however that doesn't change anything.

The error I get is: SQL compilation error: error line 2 at position 0 invalid identifier '"COL_NAME"'

#This will not work in my snowflake SQL


SELECT 
`COL_NAME`
FROM
TABLENAME

#This will work though:
SELECT 
"COL_NAME"
FROM
TABLENAME

CodePudding user response:

One possibility would be to use sql_render(), convert to a character string, and use any regex replace process that you like to change the quotes. For example:

<pipeline> %>%
  sql_render() %>%
  as.character() %>%
  str_replace_all(pattern="`",replacement = "\\\"")
  • Related