Analyze Table By Batch

#!/bin/bash

#################################################################################
# do_analyze_by_batch.sh
#
# Using this query as the source...
#
# SELECT database, schema || '.' || "table" AS "table", stats_off
# FROM svv_table_info
# WHERE stats_off > 5
# ORDER BY 3 DESC, 2;
#
# ...which displays all tables and their stats_off percentage in descending order.
#
#
# Then this file is processed like this...
#
# ALLTABS=`cat tables.txt | cut -f2 -d'|'`
#
# for TAB in ${ALLTABS}
# do
#       echo "ANALYZE ${TAB};"
# done
#
# ...which reads in the raw listing, splits on the bar, and creates a huge .sql file
# with all the ANALYZE commands (if a table is underneat the standard threshhold for
# Redshift, ANALYZE will skip it).
#
# Finally, the huge .sql file is split into groups of XX statements (in the case below, 10)
#
# split -l 10 analyze.sql --additional-suffix=".sql" pass
#
# Then THIS script reads all those passXX.sql files in, in order, and executes them with a
# 3 minute wait between each group.
#
# v1.00    2018-09-30     rlbyrd     Initial creation
#
# TODO: Utilize a sourced bash file with usernames and passwords, as with other scripts
# in the library.
#
#################################################################################

# CONSTANTS
PGUSER="xxxxxxxxxxxx"
PGPASS="xxxxxxxxxxxx"

countdown() {
  secs=$1
  shift
  msg=$@
  while [ $secs -gt 0 ]
  do
    printf "\r\033[KWaiting %.d seconds $msg" $((secs--))
    sleep 1
  done
  echo
}


ALLTABS=`ls -1 pass*.sql|sort`

for TAB in ${ALLTABS}
do
        echo "Analyzing tables in ${TAB} ..."
        echo "------------------------------------------------"
        cat ${TAB}
        echo "------------------------------------------------"
        time PGPASSWORD=${PGPASS} psql -h  warehouse.vacasa.services -p 5439 -U${PGUSER} -dwarehouse < ${TAB}

        countdown 180 "to process next batch; ctrl-c to abort."
done

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: