If your target table is in a database, the db_tbl() function is a handy way of accessing it. This function simplifies the process of getting a tbl_dbi object, which usually involves a combination of building a connection to a database and using the dplyr::tbl() function with the connection and the table name (or a reference to a table in a schema). You can use db_tbl() as the basis for obtaining a database table for the read_fn parameter in create_agent() or create_informant(). This can be done by using a leading ~ before the db_tbl() call (e.g,. read_fn = ~db_tbl(...)). Another great option is supplying a table-prep formula involving db_tbl() to tbl_store() so that you have access to database tables though single names via a table store.

The username and password are supplied though environment variables. If desired, these can be supplied directly by enclosing those values in I().

db_tbl(
  table,
  dbname,
  dbtype,
  host = NULL,
  port = NULL,
  user = NULL,
  password = NULL
)

Arguments

table

The name of the table, or, a reference to a table in a schema (two-element vector with the names of schema and table). Alternatively, this can be supplied as a data table to copy into an in-memory database connection. This only works if: (1) the db is either "sqlite" or "duckdb", (2) the dbname was chosen as ":memory:", and (3) the data_tbl is a data frame or a tibble object.

dbname

The database name.

dbtype

Either an appropriate driver function (e.g., RPostgres::Postgres()) or a shortname for the database type. Valid names are: "postgresql", "postgres", or "pgsql" (PostgreSQL, using the RPostgres::Postgres() driver function); "mysql" (MySQL, using RMySQL::MySQL()); "duckdb" (DuckDB, using duckdb::duckdb()); and "sqlite" (SQLite, using RSQLite::SQLite()).

host, port

The database host and optional port number.

user, password

The environment variables used to access the username and password for the database.

Value

A tbl_dbi object.

Function ID

1-6

See also

Examples

# You can use an in-memory database # table and supply an in-memory table # to it too: small_table_duckdb <- db_tbl( table = small_table, dbname = ":memory:", dbtype = "duckdb" ) if (interactive()) { # It's also possible to obtain a remote # file and shove it into an in-memory # database; use the all-powerful # `file_tbl()` + `db_tbl()` combo all_revenue_large_duckdb <- db_tbl( table = file_tbl( file = from_github( file = "all_revenue_large.rds", repo = "rich-iannone/intendo", subdir = "data-large" ) ), dbname = ":memory:", dbtype = "duckdb" ) # For remote databases, it's much the # same; here's an example that accesses # the `rna` table (in the RNA Central # public database) using `db_tbl()` rna_db_tbl <- db_tbl( table = "rna", dbname = "pfmegrnargs", dbtype = "postgres", host = "hh-pgsql-public.ebi.ac.uk", port = 5432, user = I("reader"), password = I("NWDMCE5xdipIjRrp") ) # Using `I()` for the user name and # password means that you're passing in # the actual values but, normally, you # would want use the names of environment # variables (envvars) to securely access # the appropriate username and password # values when connecting to a DB: example_db_tbl <- db_tbl( table = "<table_name>", dbname = "<database_name>", dbtype = "<database_type_shortname>", host = "<connection_url>", port = "<connection_port>", user = "<DB_USER_NAME>", password = "<DB_PASSWORD>" ) # Environment variables can be created # by editing the user `.Renviron` file and # the `usethis::edit_r_environ()` function # makes this pretty easy to do # Storing table-prep formulas in a table # store makes it easier to work with DB # tables in pointblank; here's how to # generate a table store with two named # entries for table preparations tbls <- tbl_store( small_table_duck ~ db_tbl( table = pointblank::small_table, dbname = ":memory:", dbtype = "duckdb" ), small_high_duck ~ db_tbl( table = pointblank::small_table, dbname = ":memory:", dbtype = "duckdb" ) %>% dplyr::filter(f == "high") ) # Now it's easy to access either of these # tables (the second is a mutated version) # via the `tbl_get()` function tbl_get("small_table_duck", store = tbls) tbl_get("small_high_duck", store = tbls) # The table-prep formulas in `tbls` # could also be used in functions with # the `read_fn` argument; this is thanks # to the `tbl_source()` function agent <- create_agent( read_fn = ~ tbl_source( "small_table_duck", store = tbls ) ) informant <- create_informant( read_fn = ~ tbl_source( "small_high_duck", store = tbls ) ) }