Summarizing large tables with SQL

Henry Luan

Overview

The core helper function get_summary_codes in the sql.mechanic
package takes in a string that specifies a table’s database name, schema
name, and table name. It outputs an SQL query that summarizes the
table’s column statistics.

If you don’t have much time to read or just want a quick solution, jump
to Example
2.

Intended User

If you work with tables inside databases hosted on powerful servers
(usually on-premise) but have limited compute resources (e.g. RAM,
GPU, CPU) for advanced BI (e.g. Python, R, SAS, etc.)
If it’s much cheaper for you to use database servers than analytics
servers (e.g. those for Python, R, etc.) either on-premise or on the
cloud.

Limitations

You probably want to understand how Example
2affect
the CPU and disk usage of your database server, to avoid bad surprises
on your server’s resource usage.
If you use the setup of Example
2on a
cloud database, you MUST do some testing, to understand how the
example affects the CPU and disk usage of your cloud resource. Please
avoid potentially expensive mistakes.
Currently, the function only works with Microsoft SQL Server and
Netezza databases. Feel free to contribute to the codes, if
interested.

Credits

Specail credits: the R codes in this package are built on top of Gordon
S. Linoff’s book Data Analysis Using SQL and Excel, 2nd Edition. His
work has been a tremendous inspiration for the creation of this package.

Example 1: Generate SQL queries and execute them in DMBS

Step 1: Install packages

You can install the library from my GitHub. If you have concerns
regarding the package’s security, you can download, check, and use the
“get_summary_codes.R” file directly.

# Install package
library(devtools)
install_github("casualcomputer/sql.mechanic",quiet=TRUE)
# Alternative: use the 'get_summary_codes.R' file only
  # source("get_summary_codes.R")

Step 2: Generate SQL quires

The following codes 1) generate the SQL queries you need to summarize a
table, and 2) copy (Ctrl+C) the codes to your clipboard. All you have to
do is paste it to your SQL editor and execute the queries.

library(sql.mechanic)
#SQL codes for basic summary, Netezza database 
    sql_basic_netezza = get_summary_codes("DB_NAME.SCHEMA_NAME.TABLE_NAME", type="basic", dbtype="Netezza")   
#SQL codes for advanced summary, Netezza database 
    sql_advanced_netezza = get_summary_codes("DB_NAME.SCHEMA_NAME.TABLE_NAME", type="advanced", dbtype="Netezza")   
#SQL codes for basic summary, Microsoft SQL Server 
    sql_basic_mssql = get_summary_codes("DB_NAME.SCHEMA_NAME.TABLE_NAME", type="basic", dbtype="MSSQL")  
#SQL codes for advanced summary, Microsoft SQL Server
    sql_advanced_mssql = get_summary_codes("DB_NAME.SCHEMA_NAME.TABLE_NAME", type="advanced", dbtype="MSSQL")  
#copy some of the sql queries to the clipboard    
    writeClipboard(sql_basic_netezza)

Step 3: Paste the codes in your clipboard and run it in SQL

In case you are curious, the SQL copied to your clipboard looks like
this.

SELECT REPLACE(REPLACE(REPLACE('<start> SELECT ''<col>'' as colname,
                               COUNT(*) as numvalues,
                               MAX(freqnull) as freqnull,
                               CAST(MIN(minval) as CHAR(100)) as minval,
                               SUM(CASE WHEN <col> = minval THEN freq ELSE 0 END) as numminvals,
                               CAST(MAX(maxval) as CHAR(100)) as maxval,
                               SUM(CASE WHEN <col> = maxval THEN freq ELSE 0 END) as nummaxvals,
                               SUM(CASE WHEN freq =1 THEN 1 ELSE 0 END) as numuniques
                               FROM (SELECT <col>, COUNT(*) as freq
                               FROM SCHEMA_NAME.<tab> GROUP BY <col>) osum
                                                   CROSS JOIN (SELECT MIN(<col>) as minval, MAX(<col>) as maxval, SUM(CASE WHEN <col> IS NULL THEN 1 ELSE 0 END) as freqnull
                                                   FROM (SELECT <col> FROM SCHEMA_NAME.<tab>) osum
                                                   ) summary',
                               '<col>', column_name),
                               '<tab>', 'TABLE_NAME'),
                               '<start>',
                               (CASE WHEN ordinal_position = 1 THEN ''
                               ELSE 'UNION ALL' END)) as codes_data_summary
                               FROM (SELECT table_name, case when regexp_like(column_name,'[a-z.]|GROUP')  then '"'||column_name||'"'
                                                             else column_name end as column_name  , ordinal_position
                               FROM information_schema.columns
                               WHERE table_name ='TABLE_NAME') a;

Step 4: Copy, paste and execute the query results from Step 3.

Example 2: Automatically summarize tables in your databases

This example shows you how you can summarize tables as you did in
Example 1, with only a few lines of R codes.

# Install package
library(devtools)
install_github("casualcomputer/sql.mechanic",quiet=TRUE)
# Alternative: use the 'get_summary_codes.R' file only
  # source("get_summary_codes.R")
# Load packages  
library(sql.mechanic)
library(odbc)
library(DBI)
# Connect to database(s)
## Method 1: prompting user (you need to make some changes here)
  con <- dbConnect(odbc(),
                   Driver = "SQL Server",
                   Server = "mysqlhost",
                   Database = "mydbname",
                   UID = "myuser",
                   PWD = "Database password",
                   Port = 1433, encoding = 'windows-1252') #'windows-1252' allows French to display properly
## Alternative Method: Using a DSN
  #con <- dbConnect(odbc::odbc(), "DNS_NAMES", encoding = 'windows-1252')
sql_query = get_summary_codes("DB_NAME.SCHEMA_NAME.TABLE_NAME", type="basic", dbtype="Netezza") 
res = dbSendQuery(con, sql_query) # part of Step 3 in "Example 1"
sql_query_mod = dbFetch(res) # part of Step 3 in "Example 1"
res = dbSendQuery(con, sql_query_mod) # part of Step 4 in "Example 1"
output_table = dbFetch(res) # part of Step 4 in "Example 1"
print(output) #desired summary table
dbDisconnect(con) #close database connection