SQLDataFrame {SQLDataFrame} | R Documentation |
SQLDataFrame
constructor, slot getters, show
method and coercion methods to DataFrame
and
data.frame
objects.
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, ...)
conn |
a valid |
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 |
col.names |
A character vector specifying the column names you
want to read into the |
x |
An |
value |
The column name to be used as |
object |
An |
row.names |
|
optional |
logical. If |
... |
additional arguments to be passed. |
A SQLDataFrame
object.
## 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)