使用R将DataFrame上传至Redshift的public.revman表时遇错误求助

针对你使用RPostgreSQL将data.frame上传至Redshift的public.revman表失败的问题，结合代码和Redshift常见上传问题，整理以下排查方向和解决方案：

一、最常见原因：数据类型/列名不匹配 #

Redshift对数据类型的兼容性要求严格，是上传失败的核心诱因：

• 时间戳类型不兼容：Sys.time()生成的POSIXct类型直接上传可能报错，Redshift需要标准字符串格式的时间戳
• 数值类型不匹配：co_skey/cust_nbr用as.numeric()转换后可能包含非整数值，若Redshift表列定义为INT会报错
• 列名大小写敏感：Redshift默认列名为小写，若upload_data的列名是驼峰/大写，会导致列匹配失败

解决方案： #

1. 先查询Redshift表结构，确认列类型与列名：

   SELECT column_name, data_type FROM information_schema.columns 
   WHERE table_schema = 'public' AND table_name = 'revman';
   

2. 修正upload_data的类型与列名：

   upload_data <- data %>%
     anti_join(existing_cust %>% rename(co_cust_id = co_cust_id)) %>% 
     mutate(initiative_nm = "Hispanic Pricing: New Hispanic Customers", 
            co_skey = as.integer(`site ID`), # 转为INT匹配Redshift整数列
            cust_nbr = as.integer(`customer ID`),
            updt_user = "sraz2759",
            updt_dttm = format(Sys.time(), "%Y-%m-%d %H:%M:%S"), # 转为Redshift兼容的时间字符串
            supc_pz_id = as.integer(0)) %>%
     select(initiative_nm, co_skey, cust_nbr, co_cust_id, updt_user, updt_dttm, supc_pz_id) %>%
     distinct() %>%
     replace_na(list(co_skey = 0, cust_nbr = 0)) %>% # 处理空值
     rename_with(tolower) # 统一转为小写列名，匹配Redshift默认规则
   

二、RPostgreSQL对Redshift的兼容性问题 #

RPostgreSQL是为原生PostgreSQL开发的工具，对Redshift的支持存在局限性，建议替换为更适配的RPostgres包：

解决方案： #

1. 安装并替换连接库：

   # 安装包（首次使用）
   install.packages("RPostgres")
   # 替换RPostgreSQL为RPostgres
   library(RPostgres)
   

2. 修改数据库连接代码：

   myconn <- dbConnect(Postgres()
                      , host = read.xlsx("~/sqlp.xlsx", colNames = F)[5,9]
                      , port = as.integer(read.xlsx("~/sqlp.xlsx", colNames = F)[6,9]) # 确保端口为整数
                      , dbname = read.xlsx("~/sqlp.xlsx", colNames = F)[2,9]
                      , user = read.xlsx("~/sqlp.xlsx", colNames = F)[3,9]
                      , password = read.xlsx("~/sqlp.xlsx", colNames = F)[4,9]
   )
   

三、上传参数配置错误 #

dbWriteTable的默认参数可能导致列数不匹配或权限问题：

• row.names默认被作为列上传：导致列数多于Redshift表的列数
• append模式下列数不匹配：upload_data的列数与目标表必须完全一致

解决方案： #

上传时明确指定参数：

dbWriteTable(myconn, "public.revman", upload_data, 
             append = TRUE, 
             row.names = FALSE, # 禁止上传行名作为列
             overwrite = FALSE)

四、其他排查方向 #

• 权限问题：确认当前Redshift用户拥有public.revman表的INSERT权限，可联系管理员执行：

  GRANT INSERT ON public.revman TO your_username;
  

• 特殊字符/空值问题：清理数据中的换行符、引号等特殊字符：

  upload_data <- upload_data %>% 
    mutate(across(where(is.character), ~str_remove_all(., "[\\n\\r\\']")))
  

完整修正后的代码示例 #

#############Import required libraries#############
library(tidyverse)
library(lubridate)
library(stringr)
library(openxlsx)
library(RPostgres)
library(data.table)

#################### Clear environment & set Working Directory #################
rm(list=ls())
setwd(dirname(rstudioapi::getActiveDocumentContext()$path))

data <- fread("Input Files/FW42CombinedExceptionUpload Template.csv") %>%
  mutate(`site ID` = str_pad(`site ID`, pad = 0, width = 3),
         `customer ID` = str_pad(`customer ID`, pad = 0, width = 6),
         co_cust_id = paste0(`site ID`,'-',`customer ID`))

# 连接Redshift
myconn <- dbConnect(Postgres()
                   , host = read.xlsx("~/sqlp.xlsx", colNames = F)[5,9]
                   , port = as.integer(read.xlsx("~/sqlp.xlsx", colNames = F)[6,9])
                   , dbname = read.xlsx("~/sqlp.xlsx", colNames = F)[2,9]
                   , user = read.xlsx("~/sqlp.xlsx", colNames = F)[3,9]
                   , password = read.xlsx("~/sqlp.xlsx", colNames = F)[4,9]
)

existing_cust <- dbGetQuery(myconn, paste0("SELECT co_cust_id FROM public.revman_initiative_scope WHERE co_cust_id IN ('", paste(unique(data$co_cust_id), collapse = "', '"), "')"))

dbDisconnect(myconn)
gc()

upload_data <- data %>%
  anti_join(existing_cust %>% rename(co_cust_id = co_cust_id)) %>% 
  mutate(initiative_nm = "Hispanic Pricing: New Hispanic Customers", 
         co_skey = as.integer(`site ID`),
         cust_nbr = as.integer(`customer ID`),
         updt_user = "sraz2759",
         updt_dttm = format(Sys.time(), "%Y-%m-%d %H:%M:%S"),
         supc_pz_id = as.integer(0)) %>%
  select(initiative_nm, co_skey, cust_nbr, co_cust_id, updt_user, updt_dttm, supc_pz_id) %>%
  distinct() %>%
  replace_na(list(co_skey = 0, cust_nbr = 0)) %>%
  rename_with(tolower)

## Write the updated data frame to a new CSV file
fwrite(upload_data, paste0("Results/upload_data_",today(),".csv")) 

# 重新连接上传
myconn <- dbConnect(Postgres()
                   , host = read.xlsx("~/sqlp.xlsx", colNames = F)[5,9]
                   , port = as.integer(read.xlsx("~/sqlp.xlsx", colNames = F)[6,9])
                   , dbname = read.xlsx("~/sqlp.xlsx", colNames = F)[2,9]
                   , user = read.xlsx("~/sqlp.xlsx", colNames = F)[3,9]
                   , password = read.xlsx("~/sqlp.xlsx", colNames = F)[4,9]
)

dbWriteTable(myconn, "public.revman", upload_data, append = TRUE, row.names = FALSE)

dbDisconnect(myconn)
gc()

内容的提问来源于stack exchange，提问作者Shobi