toaster (version 0.5.5)

getTableCounts: Counts number of rows and columns in the database tables.

Description

Counts number of rows and columns in the database tables.

Usage

getTableCounts(channel, schema = NULL, tableType = "TABLE", pattern = NULL, columns = FALSE, where = NULL, tables = NULL, test = FALSE, parallel = FALSE)

Arguments

channel

object as returned by odbcConnect.

schema

character vector with schemas to restric tables to one or more schemas. If NULL table search performed across whole database. Including schema restricts it to the specified schemas only.

tableType

can specify zero or more types in separate elements of a character vector (one or more of "TABLE", "VIEW", "SYSTEM TABLE", "ALIAS", "SYNONYM").

pattern

character string containing regular expression to match table names (without schema).

columns

logical directs to include a column count for each table in the result.

where

specifies criteria to satisfy by the table rows before applying computation. The creteria are expressed in the form of SQL predicates (inside WHERE clause).

tables

optional pre-built list of tables (data frame returned by sqlTables).

test

logical: if TRUE show what would be done, only (similar to parameter test in RODBC functions like sqlQuery and sqlSave).

parallel

logical: enable parallel calls to Aster database. This option requires parallel backend enabled and registered (see in examples). Parallel execution requires ODBC channel obtained without explicit password: either with odbcConnect(dsn) or odbcDriverConnect calls, but not with odbcConnect(dsn, user, password).

Value

a data frame returned by sqlTables augmented with rowcount (number of rows) and optinal colcount (number of columns) columns for each table.

Examples

if (interactive()) {

# initialize connection to Dallas database in Aster 
conn = odbcDriverConnect(connection="driver={Aster ODBC Driver};
                         server=<dbhost>;port=2406;database=<dbname>;uid=<user>;pwd=<pw>")

table_counts = getTableCounts(conn, 'public')

library(reshape2)
library(ggplot2)
library(ggthemes)

data = melt(table_counts, id.vars='TABLE_NAME', measure.vars=c('rowcount','colcount'))
ggplot(data) +
  geom_bar(aes(TABLE_NAME, rowcount, fill=TABLE_NAME), stat='identity') +
  facet_wrap(~variable, scales = "free_y", ncol=1) +
  theme_tufte(ticks=FALSE) +
  theme(axis.text.x=element_text(size=12, angle=315, hjust=0),
        legend.position="none")
}