TileDB provides the Universal Data Engine that can be accessed in a variety of ways. Users sometimes wonder how to transfer data from existing databases. This short vignettes shows an example relying on the DBI package for R. It offers a powerful and convenient abstraction layer on top a number of database backends with connection packages that adhere to, and utilise, the DBI framework. Some examples are the packages (listed in alphabetical order) duckdb, RClickhouse, RGreenplum, RJDBC, RMariaDB, RMySQL, ROracle, RPostgres, RPostgreSQL, RPresto, RRedshiftSQL, RSQLite, and many more as seen via the CRAN page.
We provide a simple example using RPostgreSQL and an existing database of historical stockmarket price data.
The basic setup is straightforward. We load the required package RPostgreSQL which in turn imports DBI as well as tiledb. We use data.table for its print method, the tibble package offers an alternative):
This step uses the DBI abstraction. A compliant backend driver can be
loaded via dbDriver
, and a connection can be established
via dbConnect
using appropriate arguments
dbname
, user
, password
,
host
, and port
, as needed, with proper
dispatching the implementation provided by the driver. The details
depend on the chosen backend, this can be as simple as
con <- dbConnect(RSQLite::SQLite(), ":memory:")
in the
case of RSQLite
and an in-memory (and likely transient) database.
In the next step we fetch the data—and for simplicity issue just one
select
statement returning a single data.frame
(or here a data.table
variant). In larger-than-memory
settings the SQL query could easily bucket by symbols, or date range, or
…
Having read the data into memory we can use the TileDB R function
fromDataFrame
. It has numerous option to configure, as well
as sensible defaults (to for example enable ZSTD compression). Here we
select the first two columns for symbol and data as dimensions. Symbols,
being text, do not set a domain set. For the date we set two ‘safe’
outer values for the range.
storeDataTDB <- function(dat, uri) {
fromDataFrame(dat, uri,
col_index=1:2,
tile_domain=list(date=c(as.numeric(as.Date("1985-01-01")),
as.numeric(as.Date("2030-12-31")))))
}
The mode="append"
argument of fromDataFrame
can be used to append to an existing array to support chunked
operation.
Reading data from TileDB is a very standard operation of opening the URI, possibly specifying the return type and possibly subsetting by dimension values, or attributes. Here, for simplicity, we just read everything.
getDataTDB <- function(uri) {
set_allocation_size_preference(1e7) # larger than local default value
arr <- tiledb_array(uri, return_as="data.frame")
res <- arr[]
res
}
uri <- "/tmp/tiledb/beancounter"
res <- getDataFromSQL(con)
storeData(dat, uri)
chk <- getDataTDB(uri)
print(dim(chk))
cat("Done!\n")
The vignette TileDB MariaDB Examples shows to use MariaDB via the MyTile integration of TileDB as a direct backend.
The TileDB R Tutorial at useR! 2021 contained a worked example of writing much larger data set in chunks. The process is very similar to the simple example we showed here – and in addition requires a suffient domain range for the dimension along with a (sequential or parallel) loop of reading chunks and writing them to TileDB.
This vignette provides a commented walk-through of a worked example of a SQL-to-TileDB data ingestion.