Write dataframe to table in Teradata from R using RODBC sqlSave

Question

This is a legacy R code that is running for months. I was able to read/drop a table in Teradata from R but not able to write data into the table from data frame.

I have tried by dropping table and recreating and writing different dataframe.

sqlSave(ch, df, tablename = paste("scenario.table_storetype"),rownames=F)

I was getting the following error

Error in sqlColumns(channel, tablename)[4L][, 1L]: incorrect number of dimensions Traceback:
1. sqlSave(ch, df, tablename = paste("scenario.table_storetype"), . rownames = F, safer = FALSE, append = T)
2. sqlwrite(channel, tablename, dat, verbose = verbose, fast = fast, . test = test, nastring = nastring)

dput(head(df))

Output: structure(list(forecast = c(36659805.75, 28117111.75, 27005618.75, 33650734.4166667, 27243750.75, 26907919.0833333), actual = c(38293943, 29892143, 27016674, 33524728, 27252399, 26521098), BC = c("Prepared Salad, Fruit & Veg", "Prepared Salad, Fruit & Veg", "Prepared Salad, Fruit & Veg", "Prepared Salad, Fruit & Veg", "Prepared Salad, Fruit & Veg", "Prepared Salad, Fruit & Veg"), period = 201904:201909, how = c("a_tslm_mape", "a_tslm_mape", "a_tslm_mape", "a_tslm_mape", "a_tslm_mape", "a_tslm_mape" )), .Names = c("forecast", "actual", "BC", "period", "how"), row.names = c(NA, 6L), class = "data.frame")

dput(head(df))

After rounding off - Output: structure(list(forecast = c(36659805.75, 28117111.75, 27005618.75, 33650734.42, 27243750.75, 26907919.08), actual = c(38293943, 29892143, 27016674, 33524728, 27252399, 26521098), BC = c("Prepared Salad, Fruit & Veg", "Prepared Salad, Fruit & Veg", "Prepared Salad, Fruit & Veg", "Prepared Salad, Fruit & Veg", "Prepared Salad, Fruit & Veg", "Prepared Salad, Fruit & Veg"), period = 201904:201909, how = c("a_tslm_mape", "a_tslm_mape", "a_tslm_mape", "a_tslm_mape", "a_tslm_mape", "a_tslm_mape" )), .Names = c("forecast", "actual", "BC", "period", "how"), row.names = c(NA, 6L), class = "data.frame")

How large is your df in columns and rows: dim(df)? Try reducing rows and test. ODBC driver might be imposing limits. — Parfait
@Parfait I tried writing single row but it doesn't help. I was able to insert a row using sqlQuery command manually giving values but sqlSave is not working — Shalini
Can you answer my first question? And by reducing rows, I meant saving first few rows: sqlSave(ch, head(df, 10), tablename = paste("scenario.table_storetype"),rownames=F). Your issue may be a size restriction. — Parfait
Do you have nested objects inside data.frame like lists? Please post the dput(head(df)) so we can see its properties/dimensions. — Parfait

Parfait Parfait · Accepted Answer · 2019-06-25T18:32:19

Apparently, you have very large numbers with varying degrees of precision. When creating the table dynamically, the numeric fields may map over as DECIMAL(n, m) types where the scale of 2 may be set by the first value, 36659805.75 but this later conflicts with 33650734.4166667 with scale of 7.

Consider using the varTypes argument of sqlSave to explicitly define the data types. Per the docs:

varTypes: an optional named character vector giving the DBMSs datatypes to be used for some (or all) of the columns if a table is to be created

Additionally, round accordingly in R to adhere to precision needs of the Teradata column types

df$forecast <- round(df$forecast, 4)           # PRECISION OF 4

max(nchar(df$BC))                              # FIND MAX CHARS
max(nchar(df$how))                             # FIND MAX CHARS

tbl_types = c(forecast = "DECIMAL(12, 4)",
              actual = "DECIMAL(12, 4)",
              BC = "VARCHAR(50)",
              period = "INTEGER",
              how = "VARCHAR(20)")

sqlSave(ch, df, tablename = paste("scenario.table_storetype"), 
        varTypes = tbl_types, rownames = FALSE)

Write dataframe to table in Teradata from R using RODBC sqlSave

1 Answers