src_scidb: src_scidb

Description

Connect to the scidb server on Amazon Web Services.

Usage

src_scidb(dbname, ...)
dbConnect_scidb(dbname, ...)
mysql_scidb(dbname, ...)

Value

For src_scidb(), a dbplyr::src_dbi object

For dbConnect_scidb(), a RMariaDB::MariaDBConnection object

For mysql_scidb(), a character vector of length 1 to be used as an engine.ops argument, or on the command line.

Arguments

dbname: the name of the database to which you want to connect
...: arguments passed to dbplyr::src_dbi() or DBI::dbConnect()

Details

This is a public, read-only account. Any abuse will be considered a hostile act.

The MariaDB server accessible via these functions is a db.t3.micro RDS instance hosted by Amazon Web Services. It is NOT a powerful server, having only 2 CPUs, 1 GB of RAM, and 20 GB of disk space. It is useful for quick, efficient and no-stress setup, but not useful for any kind of serious computing.

The airlines database on the server contains complete flight records for the three years between 2013 and 2015, which contains about 6 million rows annually. Thus, the flights table contains approximately 18 million rows. The flights table has several indexes, including an indices on year, origin, dest, carrier, and tailnum. There is also a composite index on the date (across year, month, and day). Please use these indexes to improve query response times.

There are two databases on this server:

airlines: The structure of the database is similar to what you find in the nycflights13 and nycflights23 packages. See their documentation at nycflights13::flights and nycflights23::airports, for example.
imdb: These data were retrieved from an old dump of the Internet Movie Database, circa 2016. Please see this ER diagram for relationships between the tables.

Examples

Run this code

# \donttest{
# Connect to the database instance via `dplyr`
db_air <- src_scidb("airlines")
db_air
# }
# \donttest{
# Connect to the database instance via `DBI` (recommended)
db_air <- dbConnect_scidb("airlines")
db_air

# Get more information...
if (require(DBI)) {

  # About the database instance
  dbGetInfo(db_air)
  
  # About the available tables
  dbListTables(db_air)
  
  # About the variables in a particular table
  dbListFields(db_air, "flights")
  
  # About the indexes (using raw SQL)
  dbGetQuery(db_air, "SHOW KEYS FROM flights")
}
# }

if (require(knitr)) {
  opts_chunk$set(engine.opts = mysql_scidb("airlines"))
}