Bash script to gather statistics (at 100%) on the tables of a Vertica database. It will stop starting new statistics collections after 2 hours here.

vertica_gather_full_statistics.sh

#!/bin/bash
# Gathering full statistics at 100% on all tables in Vertica.
# The tables with no row will always appear to have null statistics but that's ok.
# When a statistics ends it checks if the script has been running for more than
# 2 hours. If it's the case it stops.
#
# Prerequisites:
#   a credentials file that looks like this:
#
# VERTICA_USER=MyUser
# VERTICA_PWD='MyPassword'
# VERTICA_DB=mydb
#

# Change that if your credentials file is setup elsewhere
source ~/.credentials/vertica
# Change that to the path of the vsql command
VSQL=/usr/local/vertica/bin/vsql

# If you want to change the time after which the job don't gather stats for other tables than the current one.
STOP_AFTER=$(date +'%Y%m%d%H%M' --date='+2 hour')

${VSQL} -h localhost -U ${VERTICA_USER} -w ${VERTICA_PWD} -d ${VERTICA_DB} -t -c "select 'SELECT ANALYZE_HISTOGRAM('''|| c.TABLE_SCHEMA || '.' || c.table_name ||''', 100); -- Last stats run: '|| NVL(min(p.statistics_updated_timestamp), '1900-01-01') from COLUMNS c LEFT OUTER JOIN projection_columns p on c.TABLE_ID = p.TABLE_ID AND c.COLUMN_ID = p.TABLE_COLUMN_ID group by c.TABLE_SCHEMA, c.table_name order by min(p.statistics_updated_timestamp);" | while read row ; do
  if [[ -n "${row}" ]]; then
    echo "Executing: ${row}"
    ${VSQL} -h localhost -U ${VERTICA_USER} -w ${VERTICA_PWD} -d ${VERTICA_DB} -t -c "${row}"
  fi

  # Condition to stop
  CURRENT_DATE=$(date +'%Y%m%d%H%M')
  if [ ${CURRENT_DATE} -ge ${STOP_AFTER} ]
  then
    echo 'Maximum time reached. Leaving the script'
    exit 0
  fi
done