SQLDataFrame-class: SQLDataFrame class

In SQLDataFrame: Representation of SQL database in DataFrame metaphor

Description

SQLDataFrame constructor, slot getters, show method and coercion methods to DataFrame and data.frame objects.

Usage

SQLDataFrame(
  conn,
  host,
  user,
  dbname,
  password = NULL,
  billing = character(0),
  type = c("SQLite", "MySQL", "BigQuery"),
  dbtable = character(0),
  dbkey = character(0),
  col.names = NULL
)

## S4 method for signature 'SQLDataFrame'
tblData(x)

## S4 method for signature 'SQLDataFrame'
dbtable(x)

## S4 method for signature 'SQLDataFrame'
dbkey(x)

## S4 replacement method for signature 'SQLDataFrame'
dbkey(x) <- value

## S4 method for signature 'SQLDataFrame'
dbconcatKey(x)

## S4 method for signature 'SQLDataFrame'
dbnrows(x)

## S4 method for signature 'SQLDataFrame'
ROWNAMES(x)

## S4 method for signature 'SQLDataFrame'
show(object)

## S4 method for signature 'SQLDataFrame'
as.data.frame(x, row.names = NULL, optional = NULL, ...)

Arguments

conn	a valid DBIConnection from SQLite, MySQL or BigQuery. If provided, arguments of 'user', 'host', 'dbname', 'password' will be ignored.
host	host name for MySQL database or project name for BigQuery.
user	user name for SQL database.
dbname	database name for SQL connection.
password	password for SQL database connection.
billing	the Google Cloud project name with authorized billing information.
type	The SQL database type, supports "SQLite", "MySQL" and "BigQuery".
dbtable	A character string for the table name in that database. If not provided and there is only one table available, it will be read in by default.
dbkey	A character vector for the name of key columns that could uniquely identify each row of the database table. Will be ignored for BigQueryConnection.
col.names	A character vector specifying the column names you want to read into the SQLDataFrame.
x	An SQLDataFrame object
value	The column name to be used as dbkey(x)
object	An SQLDataFrame object.
row.names	NULL or a character vector giving the row names for the data frame. Only including this argument for the as.data.frame generic. Does not apply to SQLDataFrame. See base::as.data.frame for details.
optional	logical. If TRUE, setting row names and converting column names is optional. Only including this argument for the as.data.frame generic. Does not apply to SQLDataFrame. See base::as.data.frame for details.
...	additional arguments to be passed.

Value

A SQLDataFrame object.

Examples

## SQLDataFrame construction
dbname <- system.file("extdata/test.db", package = "SQLDataFrame")
conn <- DBI::dbConnect(DBI::dbDriver("SQLite"), dbname = dbname)
obj <- SQLDataFrame(conn = conn, dbtable = "state",
                    dbkey = "state")
obj1 <- SQLDataFrame(conn = conn, dbtable = "state",
                     dbkey = c("region", "population"))
obj1

### construction from database credentials
obj2 <- SQLDataFrame(dbname = dbname, type = "SQLite",
                     dbtable = "state", dbkey = "state")
all.equal(obj, obj2)  ## [1] TRUE

## slot accessors
connSQLDataFrame(obj)
dbtable(obj)
dbkey(obj)
dbkey(obj1)

dbconcatKey(obj)
dbconcatKey(obj1)

## slot accessors (for internal use only)
tblData(obj)
dbnrows(obj)

## ROWNAMES
ROWNAMES(obj[sample(10, 5), ])
ROWNAMES(obj1[sample(10, 5), ])

## coercion
as.data.frame(obj)
as(obj, "DataFrame")


## dbkey replacement
dbkey(obj) <- c("region", "population")
obj

## construction from MySQL 
## Not run: 
mysqlConn <- DBI::dbConnect(dbDriver("MySQL"),
                            host = "",
                            user = "",
                            password = "",  ## required if need further
                                            ## aggregation operations such as
                                            ## join, union, rbind, etc. 
                            dbname = "")
sdf <- SQLDataFrame(conn = mysqlConn, dbtable = "", dbkey = "")

## Or pass credentials directly into constructor
objensb <- SQLDataFrame(user = "genome",
                        host = "genome-mysql.soe.ucsc.edu",
                        dbname = "xenTro9",
                        type = "MySQL",
                        dbtable = "xenoRefGene",
                        dbkey = c("name", "txStart"))


## End(Not run)

## construction from BigQuery
## Not run: 
con <- DBI::dbConnect(bigquery(),  ## equivalent dbDriver("bigquery")
                      project = "bigquery-public-data",
                      dataset = "human_variant_annotation",
                      billing = "")  ## your project name that linked to
                                     ## Google Cloud with billing information.
sdf <- SQLDataFrame(conn = con, dbtable = "ncbi_clinvar_hg38_20180701")

## Or pass credentials directly into constructor
sdf1 <- SQLDataFrame(host = "bigquery-public-data",
                     dbname = "human_variant_annotation",
                     billing = "",
                     type = "BigQuery",
                     dbtable = "ncbi_clinvar_hg38_20180701")


## End(Not run)

SQLDataFrame documentation built on Nov. 29, 2020, 2:01 a.m.