Generate Overall Rowcount Tallies In Redshift

#SCHEMA="${2}"

STARTIME=`date`

echo "Starting rowcount update at $STARTIME"

# fivetran database
DBASE="fivetran"
SCHEMALIST="greenhouse hubspot jira mandrill marketo"
# Truncate rollup table
echo "Deleting from rollup table for ${DBASE}  schema..."
PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c "TRUNCATE TABLE admin.overall_rowcount;"


# Iterate through schemata
for SCHEMA in ${SCHEMALIST}
do

	TABLES=`PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c "select table_name from information_schema.tables where table_schema='${SCHEMA}' and table_type='BASE TABLE' ORDER BY table_name;"`

	for TAB in $TABLES
	do
		echo ">>> Working on ${DBASE}.${SCHEMA}.${TAB}..."

		TABLINE=`PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -n -q -d${DBASE} -c "insert into admin.overall_rowcount select '${SCHEMA}','$TAB',count(*) from ${SCHEMA}.${TAB}"`
	done
done
echo "*** All done with database ${DBASE}."



# eng_metrics database
DBASE="eng_metrics"
SCHEMALIST="jira"
# Truncate rollup table
echo "Deleting from rollup table for ${DBASE}  schema..."
PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c "TRUNCATE TABLE admin.overall_rowcount;"

# Iterate through schemata
for SCHEMA in ${SCHEMALIST}
do
	TABLES=`PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c "select table_name from information_schema.tables where table_schema='${SCHEMA}' and table_type='BASE TABLE' ORDER BY table_name;"`

	for TAB in $TABLES
	do
		echo ">>> Working on ${DBASE}.${SCHEMA}.${TAB}..."

		TABLINE=`PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -n -q -d${DBASE} -c "insert into admin.overall_rowcount select '${SCHEMA}','$TAB',count(*) from ${SCHEMA}.${TAB}"`
	done
done
echo "*** All done with database ${DBASE}."




# warehouse database
DBASE="warehouse"

SCHEMALIST="adwords_master adwords_owner aircall analysis_ready bing_ads common data_staging etl_testing facebook_ads finance google_adwords google_analytics high_risk housekeeping hubspot looker mailing marketo owner_portal_prod phones property_nexus prospect public rates reporting reporting_prod s3_files secure secure_scrubbed segment segment_marketo split_io_production splitio vacasa vacasa_gps_service vacasa_prod vacasa_units_svc_prod"
# Truncate rollup table
echo "Deleting from rollup table for ${DBASE}  schema..."
PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c "TRUNCATE TABLE admin.overall_rowcount;"

# Iterate through schemata
for SCHEMA in ${SCHEMALIST}
do

	TABLES=`PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -d${DBASE} -n -q -c "select table_name from information_schema.tables where table_schema='${SCHEMA}' and table_type='BASE TABLE' ORDER BY table_name;"`

	for TAB in $TABLES
	do
		echo ">>> Working on ${DBASE}.${SCHEMA}.${TAB}..."

		TABLINE=`PGPASSWORD=xxxxxxxxxx psql -t -h  warehouse.vacasa.services -p 5439 -Uvacasaroot -n -q -d${DBASE} -c "insert into admin.overall_rowcount select '${SCHEMA}','$TAB',count(*) from ${SCHEMA}.${TAB}"`
	done
done
echo "*** All done with database ${DBASE}."

ENDTIME=`date`

echo "All done with every damned thing."
echo "Startime was: $STARTIME"
echo "Endtime was : $ENDTIME"