Get a table from a database

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

Other Planning and Prep: action_levels(), create_agent(), create_informant(), draft_validation(), file_tbl(), scan_data(), tbl_get(), tbl_source(), tbl_store(), validate_rmd()

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
    )
  )

}