#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"
Like this:
Like Loading...
Related