dbplyr преобразовать символ в формат даты во временной таблице

Я извлек данные во временную таблицу в SQL Server, используя DBI::dbGetQuery.

Несмотря на то, что в реальном запросе (а не в запросе воспроизведения ниже), I select convert(date, date_value) as date_value, даты по-прежнему сохраняются как символы.

Затем я пытаюсь изменить символ, представляющий дату, с помощью lubridate::ymd, однако получаю сообщение о том, что

date_value не найден

Я также пробовал convert(date, date_value) и as.Date, но безрезультатно.

require(dplyr)
if (dbExistsTable(con, "##temp", catalog_name = "tempdb")){
  dbRemoveTable(con, "##temp")
}
DBI::dbGetQuery(con, paste(              
"select 
    convert(date, '2013-05-25') as date_value
into ##temp
"))

tbl(con, "##temp")

# Error - date_value not found
tbl(con, "##temp") %>%   mutate(date_value= lubridate::ymd(date_value))

# this works
tbl(con, "##temp") %>%   mutate(temp= date_value) 

# this doesn't work - date value not found
tbl(con, "##temp") %>%   mutate(temp= lubridate::ymd(date_value))

Как я могу использовать это поле как дату?

Примечание: когда я пишу следующее в SQL Server, date_value отображается как тип даты

select 
convert(date, '2013-05-25') as date_value
into #hello

select *
from #hello

exec tempdb..sp_help #hello

в ответ на комментарий @Kevin Arseneau на следующем изображении показаны результаты выполнения show_query() сообщение об ошибке


r dbplyr r-dbi
person user1420372    schedule 07.02.2018    source источник
comment
Не могли бы вы добавить show_query в ваши dplyr каналы, чтобы мы могли видеть, что происходит с SQL?   -  person Kevin Arseneau    schedule 07.02.2018
comment
только что немного поигрались с реальными данными. Я могу группировать_по году (date_value), но не могу использовать функции R / Tidyverse. Изначально я вытащил данные в R, но по мере их роста это заняло больше времени, поэтому я перешел на использование временной таблицы и dbplyr (в отличие от локальных данных с dplyr). Это означало, что мне пришлось изменить stringr :: str_c на concat. Должны ли функции tidyverse работать с dbplyr? Я также только что изменил тег вопроса с dplyr на dbplyr! Не заметил тонкого изменения в именах пакетов ...   -  person user1420372    schedule 07.02.2018
comment
Не все tidyverse глаголы совместимы с DBI, вам может потребоваться сначала collect, а затем mutate. Вы можете попробовать tbl(con, "##temp") %>% collect %>% mutate(temp = lubridate::ymd(date_value)).   -  person Kevin Arseneau    schedule 07.02.2018
comment
Спасибо .. должно быть проблема. Мне не удалось найти какую-либо документацию, которая соответствует требованиям, а какая нет - сталкивались ли вы с этой документацией? Я хотел избежать сбора данных до тех пор, пока не подведу итоги (разве это не лучше для скорости с точки зрения большего количества строк?). Я решил проблему, используя функции года и конкатенации; хотя я только в начале своего анализа ...   -  person user1420372    schedule 07.02.2018
comment
Поскольку соответствие основано на серверной части DBI и драйвере db, насколько мне известно, универсального ресурса не существует. Вы можете прочитать здесь и здесь для получения дополнительной информации.   -  person Kevin Arseneau    schedule 07.02.2018
comment
@ user1420372 Вы когда-нибудь придумали решение этого вопроса? Я застрял на одном и том же.   -  person epi_n00b    schedule 05.08.2020
comment
@ epi_n00b. Я считаю, что придерживался обходного пути, согласно комментарию Кевина Арсено выше (17 февраля 2018 г.). Также см. Комментарий ниже к предлагаемому решению gavg712 - кажется, что поле все еще можно использовать для фильтрации, и проблема была только при сборе. Я больше не использую SQL-сервер, поэтому не могу продолжить тестирование.   -  person user1420372    schedule 05.08.2020
comment
То же самое и здесь с использованием sql-сервера @KevinArseneau. Разве проблема не в том, что драйвер в первую очередь не извлекает данные как дату (как описано в схеме базы данных)? Пользователь не хочет использовать здесь какой-либо возможный метод, не совместимый с DBI, но стремится просто сохранить тип базы данных при использовании dplyr / dbi / dbplyr. Есть ли билет на github по проблеме?   -  person Holger Brandl    schedule 04.09.2020

Ответы (1)


arrow_upward
5
arrow_downward

Несколько месяцев назад я безуспешно искал решение для использования lubridate функций + dplyr на PostgreSQL. Случайно я нашел простое решение, использующее функции СУБД непосредственно при dbplyr кодировании.

Извините, я буду использовать пример PostgreSQL, потому что я не знаю о функциях сервера SQL. В этом примере я создам темпоральную таблицу в СУБД PostgreSQL, а затем вычислю новый столбец с помощью функции to_date(), предоставленной PostgreSQL. В результате получилась дата, которую искали:

# Easy example on postgreSQL
library(tidyverse)
library(dbplyr)
library(RPostgreSQL)

con <- dbConnect(PostgreSQL(), 
                 dbname="postgre",
                 host="localhost",
                 port=5432,
                 user="user",
                 password="pass")

date <- data_frame(date_str = c("20180212", "20180213"))

dbWriteTable(con, "tmp", date, temporary = TRUE)

tbl(con, "tmp") %>% 
# The DBMS function is used here
  mutate(date = to_date(date_str, "YYYYMMDD")) %>% 
# Finally, I collect the data from database to R session
  collect()

#># A tibble: 2 x 3
#>  row.names date_str date      
#>* <chr>     <chr>    <date>    
#>1 1         20180212 2018-02-12
#>2 2         20180213 2018-02-13

Вы можете попробовать с настройками для SQL Server, и функция CAST() может преобразовать ваши строки в текущую дату, как объясняется в этом ответе. Надеюсь, это вам поможет.

Я надеюсь, что когда-нибудь dplyr/dbplyr сможет преобразовать lubridate функции в SQL запросы.

person gavg712    schedule 13.02.2018
comment
Функции преобразования и преобразования СУБД, похоже, не работают для SQL-сервера (оба возвращают ошибки), поскольку дата по-прежнему правильно хранится в таблице temp, может использоваться для фильтрации и извлечения месяцев, лет и т. Д .; только в коллекции дата отображается в виде символа. tbl (con, ## temp)% ›% mutate (date = datefromparts (год (date_value), month (date_value), day (date_value)))%›% collect ()% ›% # date и date_value возвращаются как character mutate (date = ymd (date)) # здесь можно преобразовать по мере необходимости - person user1420372; 16.02.2018
comment
Что вы получите, попробовав tbl(con, "##temp") %>% head ()? Вы видели столбец date_value? Если он появится, то какой это класс? - person gavg712; 18.02.2018