Using `glue_sql()`s `*` collapse with table name identifiers

tidyverse
1

I like to use glue_sql() for easy creation of MERGE statements, specifically generating the column names in the INSERT/VALUES section. But to do this, I need to be able to prefix a column identifier with a table name identifier, eg "table1"."col1".

I don't show the variations I've tried to try and make this work, but I've tried everything I can think of and just can't seem to get it right. Instead I show my use case, and what I'm wanting to get. Do you have any advice? Is this something glue can currently do? Essentially I feel like I need to iterate in parallel over a table_name variable and a column_name variable, doing identifier quoting and placing a period between them as I go.

You can ignore most of the code below and just look at the SQL output at the bottom if you want.

(I was planning on submitting this as an issue to glue but felt it more appropriate to post here instead unless we determine this is something glue does not yet support)

library(glue)
library(tibble)
library(DBI)
library(odbc)

# imagine this is already in the db
target_tbl <- cars

# these are the new rows we want to append if they are actually new data
# imagine that I ran a copy_to() on this and named it ##temp_source
source_tbl <- tibble(speed = 25, `dist spaces` = 3)

target_table  <- c("target_tbl")
source_table  <- c("##temp_source")

# Mock my S4 sql server connection
setClass("Microsoft SQL Server", representation(quote = "character"), contains = "OdbcConnection")
conn <- new("Microsoft SQL Server", quote = "\"")

# really these are colnames(tbl(con, target_table))
target_names <- colnames(target_tbl)
source_names <- colnames(source_tbl)

sql_merge <- glue_sql(
  '
  MERGE INTO {`target_table`} WITH (HOLDLOCK) AS target
  USING {`source_table`} as source
  ON target.date = source.date
  WHEN NOT MATCHED BY TARGET THEN
  INSERT ({`target_names`*})
  VALUES ({`source_names`*});
  ', 
  .con = conn
)

# This gives me this, which is right, but I need to specify the table
# as well
sql_merge
#> <SQL> MERGE INTO "target_tbl" WITH (HOLDLOCK) AS target
#> USING "##temp_source" as source
#> ON target.date = source.date
#> WHEN NOT MATCHED BY TARGET THEN
#> INSERT ("speed", "dist")
#> VALUES ("speed", "dist spaces");

# I somehow need this:

# <SQL> MERGE INTO "target_tbl" WITH (HOLDLOCK) AS target
# USING "##temp_source" as source
# ON target.date = source.date
# WHEN NOT MATCHED BY TARGET THEN
# INSERT ("speed", "dist")
# VALUES ("source"."speed", "source"."dist spaces");

Created on 2018-07-27 by the reprex package (v0.2.0).

1 Like
2

You need to use DBI::Id() to construct the identifiers. Unfortunately glue_sql() does not handle lists of these objects by default (it probably should), so you currently need to do the quoting manually with DBI::dbQuoteIdentifier().

library(glue)
library(tibble)
library(DBI)
library(odbc)

target_tbl <- cars

source_tbl <- tibble(speed = 25, `dist spaces` = 3)

target_table  <- c("target_tbl")
source_table  <- c("##temp_source")

setClass("Microsoft SQL Server", representation(quote = "character"), contains = "OdbcConnection")
conn <- new("Microsoft SQL Server", quote = "\"")

target_names <- colnames(target_tbl)
source_names <- lapply(colnames(source_tbl), function(nme) DBI::Id(table = "source", column = nme))
source_names2 <- lapply(source_names, DBI::dbQuoteIdentifier, conn = conn)

sql_merge <- glue_sql(
  '
  MERGE INTO {`target_table`} WITH (HOLDLOCK) AS target
  USING {`source_table`} as source
  ON target.date = source.date
  WHEN NOT MATCHED BY TARGET THEN
  INSERT ({`target_names`*})
  VALUES ({source_names2*});
  ',
  .con = conn
)

sql_merge
#> <SQL> MERGE INTO "target_tbl" WITH (HOLDLOCK) AS target
#> USING "##temp_source" as source
#> ON target.date = source.date
#> WHEN NOT MATCHED BY TARGET THEN
#> INSERT ("speed", "dist")
#> VALUES ("source"."speed", "source"."dist spaces");

Created on 2018-07-27 by the reprex package (v0.2.0).

4 Likes
3

Woah! I figured it might require manual use of dbQuoteIdentifier() but had no idea about Id(). Super useful thanks so much!

4

As of glue@ea29e09 you can use

{`source_names`*}

and it will do the unquoting of the list of Id() objects without needing to call DBI::dbQuoteIdentifier() yourself.

5 Likes