从R到Redshift写入数据框时出错 [英] Error in Dataframe writing from R to Redshift
问题描述
我在R中有一个数据框,其中包含各种不同的数据类型。从R将数据帧写入redshift服务器时,仅字符和时间戳值出现错误。我在下面添加了R代码段,以使您对该问题有更多的了解。
I have a dataframe in R with various different data types. While writing the dataframe from R to redshift server, I am getting error only with character and timestamp values. I am adding R code snippet below to give you more idea about the issue.
library(lubridate)
library(dplyr)
dat <- data.frame(id = letters[1:2], x = 2:3, date = now())
dat
str(dat)
drv <- dbDriver("PostgreSQL")
conn <- dbConnect(drv, host="redshift.amazonaws.com", port="5439", dbname="abcd", user="xyz", password="abc")
DBI::dbGetQuery(conn, "DROP TABLE test21;")
DBI::dbGetQuery(conn, "CREATE TABLE test21 ( id VARCHAR(255), x INT, date timestamp);")
chunksize = 100
for (i in 1:ceiling(nrow(dat)/chunksize)) {
query = paste0('INSERT INTO test21 (',paste0(colnames(dat),collapse = ','),') VALUES ')
vals = NULL
for (j in 1:chunksize) {
k = (i-1)*chunksize+j
if (k <= nrow(dat)) {
vals[j] = paste0('(', paste0(dat[k,],collapse = ','), ')')
}
}
query = paste0(query, paste0(vals,collapse=','))
DBI::dbExecute(conn, query)
}
在运行最后一部分时,我得到了出现以下错误:
While running the last part, I am getting the below error:
RS-DBI driver: (could not Retrieve the result : ERROR: column "date" is of type timestamp without time zone but expression is of type numeric
HINT: You will need to rewrite or cast the expression.
当我在redshift表中手动输入值时,就如预期的那样。
When I manually entered the values into the redshift table, it came as expected.
DBI :: dbGetQuery(conn, INSERT INTO test21(id,x,date)values('a','2','2019-02-08 15:21:08'),('b','3','2019-02-08 15:21:08'))
我感觉到此问题是由某些程序错误引起的。
I am sensing that this issue is coming from some programmatic error. requesting your advise on the same where I am doing wrong with the code.
推荐答案
在数据框的日期字段中,尝试替换
In the date field of your dataframe, try replacing
now()
with
substr(now(),1,19)
这篇关于从R到Redshift写入数据框时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!