R: Write ffdf data to a database table by using a ODBC...
write.odbc.ffdf
R Documentation
Write ffdf data to a database table by using a ODBC connection.
Description
Write ffdf data to a database table by using a ODBC connection.
This can for example be used to store large ffdf datasets from R in
Oracle, SQLite, MySQL, PostgreSQL, Hive or other SQL databases.
Mark that for very large datasets, these SQL databases might have tools to speed up by bulk loading.
You might also consider that as an alternative to using this procedure.
character string with the name of the table to store the data in. Passed on to sqlSave.
odbcConnect.args
a list of arguments to pass to ODBC's odbcConnect (like dsn, uid, pwd). See the examples.
RECORDBYTES
optional integer scalar representing the bytes needed to process a single row of the ffdf
BATCHBYTES
integer: bytes allowed for the size of the data.frame storing the result of reading one chunk.
See documentation in read.table.ffdf for more details.
by
integer passed on to chunk indicating to write to the database in chunks of this size. Overwrites
the behaviour of BATCHBYTES and RECORDBYTES.
VERBOSE
logical: TRUE to verbose timings for each processed chunk (default FALSE).
...
optional parameters passed on to sqlSave
Details
Opens up the ODBC connection using RODBC::odbcConnect, writes data to the SQL table
using RODBC::sqlSave by extracting the data in batches from the ffdf
and appending them to the table.
Value
invisible()
See Also
sqlSave, chunk
Examples
##
## Using the sqlite database (smalldb.sqlite3) in the /inst folder of the package
## set up the sqlite ODBC driver (www.stats.ox.ac.uk/pub/bdr/RODBC-manual.pd)
## and call it 'smalltestsqlitedb'
##
## Not run:
require(RODBC)
x <- read.odbc.ffdf(
query = "select * from testdata limit 10000",
odbcConnect.args = list(
dsn="smalltestsqlitedb", uid = "", pwd = "",
believeNRows = FALSE, rows_at_time = 1),
nrows = -1,
first.rows = 100, next.rows = 1000, VERBOSE = TRUE)
write.odbc.ffdf(x = x, tablename = "testdata", rownames = FALSE, append = TRUE,
odbcConnect.args = list(
dsn="smalltestsqlitedb", uid = "", pwd = "",
believeNRows = FALSE, rows_at_time = 1),
by = 1000, VERBOSE=TRUE)
## End(Not run)