exa.readData: Execute a SQL query on an EXASolution database and read...

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

Execute a SQL query on an EXASolution database and read results fast.

Description

This function executes the given SQL query using a given RODBC connection and returns the results as a data frame.

The results are transfered via a proprietary high speed channel from the database optimized for bulk transfer. The EXPORT ... INTO CSV AT ... statement is used internally to transfer the results as a csv. This is significantly faster than RODBC.

On the R-side, the results are parsed per default via read.csv. You can also use arbitrary readers, processing the incoming csv records according to your needs.

Usage

exa.readData(
  channel,
  query,
  encoding = "UTF-8",
  reader = function(x, ..., enc = encoding) {     read.csv(x, ..., stringsAsFactors =
    FALSE, encoding = enc, blank.lines.skip = FALSE, numerals = "no.loss") },
  server = NA,
  ...
)

Arguments

channel	The RODBC connection channel, typically created via odbcConnect.
query	A string with the SQL query to be executed on EXASolution.
encoding	A string containing the DB encoding. By default "UTF-8". There should be no need to change this as the DB will convert the result set before sending if necessary.
reader	This is a function reading and processing the results coming from the database. The read.csv function is used per default. The input for the reader is an R file connection object (as returned by the file function) containing the CSV input, as it is returned by the internal call of EXPORT TO CSV. The following custom reader function would simply read and return the CSV formatted text: myReader <- function(fileConnection) {readLines(fileConnection)}
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.
...	Other parameters passed on to the reader (read.csv).

Value

The return value is the return value of the reader function. If the default read.csv is used, the result will be a dataframe as it is returned read.csv.

Author(s)

EXASOL AG <[e-mail]>

See Also

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

Examples

## Not run: 

require(RODBC)
require(exasol)

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

# Read results
tables <- exa.readData(C, "SELECT * FROM EXA_ALL_TABLES")

# Work with the data frame returned (examples)
print(nrow(tables))      # print number of rows
print(colnames(tables))  # print names of columns
print(tables[1,])        # print first row
print(tables$TABLE_NAME[1])  # print first value of specified column


## End(Not run)

EXASOL/r-exasol documentation built on Aug. 28, 2023, 2:32 a.m.