exa.writeData: Write a data.frame into an EXASOL table fast.

In EXASOL/r-exasol: R Interface & SDK for the EXASOL Database

Write a data.frame into an EXASOL table fast.

Description

This function writes the given data frame to a database table.

The data are transfered to the database via a proprietary transfer channel which is optimized for high speed bulk transfer. To be more detailed, the IMPORT INTO ... FROM CSV AT ... statement is used internally. On the R-side, the data.frame is converted to csv format and written to a file connection, streaming the data to the database. You can also use arbitrary writers, processing the data frame according to your needs.

Usage

exa.writeData(
  channel,
  data,
  tableName,
  tableColumns = NA,
  encoding = tryCatch(strsplit(Sys.getlocale("LC_CTYPE"), ".", fixed = TRUE)[[1]][2],
    error = function(e) stop(paste("Cannot get system encoding." +
    " Please set manually.\n", e))),
  writer = function(data, conn, fileEncoding = encoding) {     write.table(data, file =
    conn, row.names = FALSE, col.names = FALSE, na = "", sep = ",", qmethod = "double",
    fileEncoding = fileEncoding) },
  server
)

Arguments

channel	The RODBC connection channel, typically created via odbcConnect.
data	The data frame to be written to the table specified in tableName. Please make sure that the column names and types of the data frame are consistent with the names and types in the EXASolution table.
tableName	Name of the table to write the data to. The table has to exist and the records will be appended.
tableColumns	If your data frame contains only a subset of the columns you can specify these here. The columns and types have to be specified as a vector of strings like: c("col1", "col2") Please take a look at the documentation of the cols parameter in the EXASolution User Manual sec. 2.2.2 'IMPORT', for details.
encoding	A string containing the source encoding (iconv format). By default, the encoding is being read from /codeSys.getlocale("LC_CTYPE"), which might fail on some OSes. In that case, an error is returned, asking to set this parameter manually (see example below).
writer	This parameter is for the rare cases where you want to customize the writer receiving the data frame and writing the data to the communication channel.
server	This parameter is only relevant in rare cases where you want to customize the address of the data channel. Per default, the data channel uses the same host and port as the RODBC connection.

Value

The function returns the value returned by the writer, or TRUE if there is none.

Author(s)

EXASOL AG <[e-mail]>

See Also

Other None-DBI-API: dbCurrentSchema(), dbEnd(), exa.createScript(), exa.readData()

Examples

## Not run: 

require(RODBC)
require(exasol)

# Connect via RODBC with configured DSN
C <- odbcConnect("exasolution")

# Generate example data frame with two groups
# of random values with different means.
valsMean0  <- rnorm(10, 0)
valsMean50 <- rnorm(10, 50)
twogroups <- data.frame(group = rep(1:2, each = 10),
                        value = c(valsMean0, valsMean50))

# Write example data to a table
odbcQuery(C, "CREATE SCHEMA test")
odbcQuery(C, "CREATE TABLE test.twogroups (groupid INT, val DOUBLE)")
exa.writeData(C, twogroups, tableName = "test.twogroups")

# setting the write encoding manually
# (See also EXASOL User Manual, Appendix D. "Supported Encodings")
exa.writeData(con, twogroups, "test.twogroups", encoding="1252")

## End(Not run)

EXASOL/r-exasol documentation built on Dec. 4, 2024, 10:22 p.m.