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

In toaster: Big Data in-Database Analytics that Scales with Teradata Aster Distributed Platform

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

Example output

Loading required package: RODBC

toaster documentation built on May 30, 2017, 3:51 a.m.