dbi.table-package {dbi.table} | R Documentation |
DBI Table
Description
A dbi.table is a data structure that describes a SQL query (called the
dbi.table's underlying SQL query). This query can be manipulated
using data.table
's [i, j, by]
syntax.
Usage
dbi.table(conn, id, check.names = FALSE, key = NULL, stringsAsFactors = FALSE)
## S3 method for class 'dbi.table'
x[i, j, by, keyby, nomatch = NA, on = NULL]
Arguments
conn |
A |
id |
An |
check.names |
Just as |
key |
A character vector of one or more column names to set as the resulting
|
stringsAsFactors |
A logical value (default is |
x |
A |
i |
A logical expression of the columns of When When When |
j |
A list of expressions, a literal character vector of column names of
|
by |
A list of expressions, a literal character vector of column names of
|
keyby |
Same as |
nomatch |
Either |
on |
|
Value
A dbi.table
.
Keys
A key marks a dbi.table
as sorted with an attribute "sorted"
.
The sorted columns are the key. The key can be any number of columns.
Unlike data.table
, the underlying data are not physically sorted, so
there is no performance improvement. However, there remain benefits to
using keys:
The key provides a default order for window queries so that functions like
shift
andcumsum
give reproducible output.-
dbi.table
'smerge
method uses adbi.table
's key to determin the default columns to merge on in the same way thatdata.table
's merge method does. Note: if adbi.table
has a foreign key relationship, that will be used to determin the default columns to merge on before thedbi.table
's key is considered.
A table's primary key is used as the default key
when it can be
determined.
Differences vs. data.table
Keys
There are a few key differences between dbi.table
keys and
data.table
keys.
In
data.table
,NA
s are always first. Some databases (e.g., PostgreSQL) sortNULL
s last by default and some databases (e.g., SQLite) sort them first.as.data.frame
does not change the order of the result set returned by the database. Note thatas.data.table
uses thedbi.table
's key so that the resultingdata.table
is sorted in the usualdata.table
way.The sort is not stable: the order of ties may change on subsequent evaluations of the
dbi.table
's underlying SQL query.
Strict Processing of Keys
By default, when previewing data (dbi.table
's print
method), the key is not included in the underlying SQL query's ORDER BY
clause. However, the result set is sorted locally to resepct the key. This
behavior is referred to as a non-strict evaluation of the key and
the printed output labels the key (non-strict)
. To override the
default behavior for a single preview, call print
explicitly and
provide the optional argument strict = TRUE
. To change the default
behavior, set the option dbitable.print.strict
to TRUE
.
Non-strict evaluation of keys reduces the time taken to retrieve the preview.
See Also
-
as.data.frame
to retrieve the results set as adata.frame
, -
csql
to see the underlying SQL query.
Examples
# open a connection to the Chinook example database using duckdb
duck <- chinook.duckdb()
# create a dbi.table corresponding to the Album table on duck
Album <- dbi.table(duck, DBI::Id(table_name = "Album"))
# the print method displays a 5 row preview
# print(Album)
Album
# 'id' can also be 'SQL'; use the same DBI connection as Album
Genre <- dbi.table(Album, DBI::SQL("chinook_duckdb.main.Genre"))
# use the extract ([...]) method to subset the dbi.table
Album[AlbumId < 5, .(Title, nchar = paste(nchar(Title), "characters"))]
# use csql to see the underlying SQL query
csql(Album[AlbumId < 5, #WHERE
.(Title, #SELECT
nchar = paste(nchar(Title), "characters"))])