Amazon Redshift - столбцы таблицы, объявленные как varchar(max), но заданные как varchar(255)

Question

Я пишу инструмент извлечения данных для загрузки данных из 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 или драйвер JDBC от Amazon Redshift заставляют мои столбцы определять как 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 символов? У меня кошмар с этим, и я думаю, что перепробовал все, что мог.

Answer 1

Я написал обширный блог, объясняющий множество нюансов чтения / записи данных в / из Amazon Redshift: https://auth0.com/blog/a-comprehensive-guide-for-connecting-with-r-to-redshift/

В частности, лучший способ чтения данных с помощью R - это использование библиотеки RPostgres, а для записи данных я рекомендую использовать созданный мной пакет R: https://github.com/sicarul/redshiftTools

В частности, у него нет проблемы, о которой вы сообщаете, varchars создаются на основе длины строк с использованием функции calcCharSize: 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, а затем направляет красное смещение в эту корзину, это самый быстрый способ массовой загрузки данных.