CODEWITHSUNDEEP
rteradata
Shalini
Shalini

Reputation: 85

Write dataframe to table in Teradata from R using RODBC sqlSave

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")

Upvotes: 3

Views: 1258

Answers (1)

Parfait
Parfait

Reputation: 107767

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)

Upvotes: 1

Related Questions