Есть ли более «чистый» способ конкатенации строки запроса?

Я передаю строку запроса в соединение, и результат должен выглядеть так:

select game_name, month, count(*) as count
  from device_metrics.mtu_events
 where YEAR = '2019' and month between '07' and '09'
 group by game_name, month
 order by game_name asc, month asc

Это отлично работает, если я передаю вышеуказанный блок как одну строку в DBI::dbGetQuery(con, myquery)

Но даты — это переменная в блестящем приложении, поэтому я попытался создать функцию для генерации строки запроса:

my_query <- function(start_date, end_date) {
        yr <- year(ymd(start_date))
        month_start <- month(ymd(start_date))
        month_end <- month(ymd(end_date))

        query <- paste0(
            "select game_name, month, count(*) as count
   from device_metrics.mtu_events
   where YEAR = ", yr, " and month between ", month_start, " and ", month_end, 
            " group by game_name, month
 order by game_name asc, month asc")

        return(query)
    }

Когда я вызываю эту функцию и пытаюсь запросить с ее помощью нашу базу данных, я получаю:

Произошла ошибка клиента AWS Athena. Ошибка Athena №: 372, код ответа HTTP: 1, сообщение об ошибке: SYNTAX_ERROR: строка 3:15: «=» не может применяться к varchar, целому числу

Есть ли «правильный» способ сделать это? Как я могу построить строку запроса с переменными и передать ее DBI::dbGetQuery()


r dbi dbplyr rodbc
person Doug Fir    schedule 25.01.2020    source источник
comment
Я думаю, вам нужно where YEAR = '", yr, "' and month between '", month_start, "' and '", month_end, "' group_by grame_name, month, потому что одинарные кавычки вокруг «год», «месяц_начало», «месяц_конец» не находятся в paste0   -  person akrun    schedule 25.01.2020
comment
Будет ли это эквивалентно, например. это дальше month_start <- month(ymd(start_date)) %>% str()   -  person Doug Fir    schedule 25.01.2020
comment
Я предполагаю, что month(Sys.Date()) возвращает числовое значение. Попробуйте my_query <- function(start_date, end_date) { yr <- year(ymd(start_date)) month_start <- month(ymd(start_date)) month_end <- month(ymd(end_date)) query <- paste0( "select game_name, month, count(*) as count from device_metrics.mtu_events where YEAR = '", yr, "' and month between '", month_start, "' and '", month_end, "' group by game_name, month order by game_name asc, month asc") return(query) }   -  person akrun    schedule 25.01.2020
comment
Вы смотрели на glue/stringr::str_glue?   -  person camille    schedule 25.01.2020
comment
Честно говоря, я бы предпочел использовать DBI::dbBind с параметризованными запросами, а не цитировать и тому подобное (bobby-tables.com). (Я не знаком с диалектом Афины и не уверен, насколько хорошо он поддерживается.)   -  person r2evans    schedule 25.01.2020
comment
Однако, если вам нужно выполнить подстановку строк, как это, я бы намного предпочел/рекомендовал бы использовать sQuote для строковых литералов, так как это обрабатывает кавычки в середине строки (тогда как использование литерала ' не делает) .   -  person r2evans    schedule 25.01.2020
comment
Некоторые идеи о параметризованных SQL-запросах здесь и здесь   -  person camille    schedule 25.01.2020

Ответы (2)


arrow_upward
3
arrow_downward

Вот два варианта, где мы можем указать (') ввод в виде строки, поскольку функции month, year возвращают числовые значения.

my_query <- function(start_date, end_date) {
        yr <- year(ymd(start_date))
        month_start <- month(ymd(start_date))
        month_end <- month(ymd(end_date))

        query <- paste0(
            "select game_name, month, count(*) as count
   from device_metrics.mtu_events
   where YEAR = '", yr, "' and month between '", month_start, "' and '", month_end, 
            "' group by game_name, month
 order by game_name asc, month asc")

        return(query)
    }

Использование sprintf

my_query <- function(start_date, end_date) {
            yr <- year(ymd(start_date))
            month_start <- month(ymd(start_date))
            month_end <- month(ymd(end_date))

            query <- sprintf("select game_name, month, count(*) as count
       from device_metrics.mtu_events
       where YEAR = '%d'  and month between '%02d' and '%02d' group by game_name, month
     order by game_name asc, month asc", yr, month_start, month_end)

            return(query)
        }
person akrun    schedule 25.01.2020
comment
Я бы не стал полагаться на ручные тики, sQuote был бы более устойчивым к искаженным параметрам (и sql-injection). - person r2evans; 25.01.2020
comment
@ r2evans Да, но, возможно, придется указать свою причудливую цитату как ЛОЖЬ sQuote(1) [1] "‘1’" - person akrun; 25.01.2020
comment
Конечно, но options(useFancyQuotes = FALSE) это исправляет. - person r2evans; 25.01.2020

arrow_upward
2
arrow_downward

Мы можем использовать fn$ из gsubfn для интерполяции строк. Мы будем использовать NULL вместо фактического соединения и fn$list вместо fn$dbGetQuery для воспроизводимости, но вы можете заменить оба. При этом не используйте символы подчеркивания или точки в именах.

library(gsubfn)

yr <- 2019
monthStart <- '07'
monthEnd <- '09'
con <- NULL

fn$list(con, "select game_name, month, count(*) as count
  from device_metrics.mtu_events
 where YEAR = '$yr' and month between '$monthStart' and '$monthEnd'
 group by game_name, month
 order by game_name asc, month asc")

Поочередно это можно сделать в два этапа:

query <- fn$c("select game_name, month, count(*) as count
  from device_metrics.mtu_events
 where YEAR = '$yr' and month between '$monthStart' and '$monthEnd'
 group by game_name, month
 order by game_name asc, month asc")
dbGetQuery(con, query)
person G. Grothendieck    schedule 25.01.2020