时间戳

首页 > 解决方案 > Amazon Redshift - 表列声明为 varchar(max) 但强制为 varchar(255)

r - Amazon Redshift - 表列声明为 varchar(max) 但强制为 varchar(255)

问题描述

我正在编写一个数据提取工具，以从 Google Search Console（从现在开始为 GSC）加载数据并将其存储在 Amazon Redshift（从现在开始为 AR）数据库中。我编写了一个函数来解析来自 GSC 的数据帧上的元素，以确定在 AR 上创建表时的字段结构。

这是我创建的 R 函数：

get_table_fields <- function (d) {
  r <- FALSE

  if (is.data.frame(d)) {
    r <- vector()
    t <- d[1,]
    c <- colnames(t)

    for (k in c) {
      v <- t[, k]

      if (is.character(v)) {
        r[k] <- "nvarchar(max)"
      } else if (!is.na(as.Date(as.character(v), format = c("%Y-%m-%d")))) {
        r[k] <- "date"
      } else if (is.numeric(v)) {
        r[k] <- ifelse(grepl(".", v, fixed = TRUE), "real", "integer")
      }
    }
  }

  return(r)
}

到目前为止，一切都很好。我传递了完整的数据框，函数从第一行提取所有相关信息，为我提供了在 AR 上创建表所需的结构。

这是我用来从 GSC 中提取数据并将其写入 AR 的代码：

# retrieve the table fields schema
s_fields <- get_table_fields(data)

# compose the table creation definition out of the fields schema
d_fields <- paste(toString(sapply(names(s_fields), function (x) {
  return(sprintf('"%s" %s', x, s_fields[x]))
})))

# compose the table creation query
c_query <- sprintf("CREATE TABLE IF NOT EXISTS %s (%s);", t_table_name, d_fields)

if (nrow(data) > 0) {
  # create the table if it doesn't exist
  dbSendUpdate(db, c_query)

  # delete previous saved records for the specified date
  dbSendUpdate(db, sprintf("DELETE FROM %s WHERE date = '%s' AND gsc_domain = '%s';", t_table_name, date_range[d], config.gsc.domain))

  # upload the Google Search Console (GSC) data to Amazon Redshift (AR)
  dbWriteTable(db, t_table_name, data, append = TRUE, row.names = FALSE)
}

db是数据库连接对象，声明如下：

# initialize the Amazon Redshift JDBC driver
driver <- JDBC("com.amazon.redshift.jdbc42.Driver", "drivers/RedshiftJDBC42-1.2.16.1027.jar", identifier.quote = "`")

# connect to the Amazon Redshift database instance
db <- dbConnect(driver, sprintf("jdbc:redshift://%s:%s/%s?user=%s&password=%s", config.ar.host, config.ar.port, config.ar.database, config.ar.user, config.ar.password))

t_table_name是一个串联字符串，在 GSC 提取定义中具有不同的维度，以gsc_by作为前缀并用下划线连接，因此，如果我们想提取日期、页面和设备，表名将是gsc_by_date_page_device

因此，基本上，这段代码所做的是从 GSC 收集数据帧，确保指定提取的表存在。如果没有，它会创建它。否则，它会删除任何现有数据（以防重新启动提取以不复制任何条目）并将其存储在 AR 中。

问题是似乎 AR 数据库或来自 Amazon Redshift 的 JDBC 驱动程序将我的列定义强制为 varchar(255) 而不是我正在尝试编写的 nvarchar(max) 或 varchar(max)。我尝试了不同的组合，但结果总是一样的：

<simpleError in .local(conn, statement, ...): execute JDBC update query failed in dbSendUpdate ([Amazon](500310) Invalid operation: Value too long for character type
Details:
-----------------------------------------------
error:  Value too long for character type
code:      8001
context:   Value too long for type character varying(255)
query:     116225
location:  funcs_string.hpp:395
process:   padbmaster [pid=29705]
-----------------------------------------------;)>

如果我c_query在发送查询之前打印变量（表创建查询），它会正确打印出来：

CREATE TABLE IF NOT EXISTS gsc_by_date_query_device ("date" date, "query" nvarchar(max), "device" nvarchar(max), "clicks" integer, "impressions" integer, "ctr" real, "position" integer, "gsc_domain" nvarchar(max));
CREATE TABLE IF NOT EXISTS gsc_by_date_query_country_device ("date" date, "query" nvarchar(max), "country" nvarchar(max), "device" nvarchar(max), "countryName" nvarchar(max), "clicks" integer, "impressions" integer, "ctr" real, "position" integer, "gsc_domain" nvarchar(max));
CREATE TABLE IF NOT EXISTS gsc_by_date_page_device ("date" date, "page" nvarchar(max), "device" nvarchar(max), "clicks" integer, "impressions" integer, "ctr" real, "position" real, "gsc_domain" nvarchar(max));

如果我在 SQLWorkbench/J（我用于检查的工具）上执行此操作，它会正确创建表，即使这样，失败的是数据插入。

你能告诉我我做错了什么吗？或者如何将文本列指定为大于 256 个字符？我为此做噩梦，我想我已经尽我所能。

标签： ramazon-redshiftrjdbcgoogle-search-console

解决方案

我写了一篇详尽的博文，解释了从 Amazon Redshift 读取/写入数据的许多细微差别：https ://auth0.com/blog/a-comprehensive-guide-for-connecting-with-r-to-redshift /

特别是，使用 R 读取数据的最佳方法是使用RPostgres库，并且我建议使用我创建的 R 包写入数据：https ://github.com/sicarul/redshiftTools

特别是，它没有您报告的问题，varchars 是使用函数 calculateCharSize 根据字符串的长度创建的：https ://github.com/sicarul/redshiftTools/blob/master/R/table_definition.R#L2

不过，作为最佳实践，我会说除非它是临时表或临时表，否则请尝试始终自己创建表，这样您就可以控制排序键、分配键和压缩，这些对于 Amazon Redshift 中的性能非常重要。

如果您已经创建了表，则可以执行以下操作：

rs_replace_table(data, dbcon=db, table_name=t_table_name, bucket="mybucket", split_files=4)

如果你还没有创建表，你可以做几乎相同的事情rs_create_table

您需要一个 S3 存储桶和 AWS 密钥来访问它，因为这个包上传到 S3，然后将 redshift 定向到该存储桶，这是批量上传数据的最快方式。

推荐阅读