
How do I do that? Like this…
Example:
dbadmin=> CREATE TABLE table_row_count(audit_date DATETIME, table_schema VARCHAR(128), table_name VARCHAR(128), row_count INT)
dbadmin-> ORDER BY table_schema, table_name, audit_date UNSEGMENTED ALL NODES;
CREATE TABLE
dbadmin=> \! vsql -Atc "SELECT 'SELECT sysdate || \$\$|' || table_schema || '|' || table_name || '|\$\$ || (SELECT COUNT(*) FROM \"' || table_schema || '\".\"' || table_name || '\")' || NVL2(LEAD(table_schema) OVER (ORDER BY table_schema), ' UNION', ';') FROM tables WHERE table_schema || '.' || table_name <> 'public.table_row_count';" | vsql -At | vsql -c "COPY table_row_count FROM STDIN DIRECT;"
dbadmin=> SELECT * FROM table_row_count ORDER BY row_count DESC;
audit_date | table_schema | table_name | row_count
----------------------------+--------------+-----------------+-----------
2019-02-14 08:19:22.406804 | Happy | Valentine's Day | 117440512
2019-02-14 08:19:22.406804 | public | table_b | 12288
2019-02-14 08:19:22.406804 | public | parts | 8
(3 rows)
Are the row counts accurate?Yup!
Now I can add my table row count job to CRONTAB so that it runs once a day at 11:59 PM.
[dbadmin@s18384357 ~]$ cat table_row_count.sh
. ~/.bashrc
/opt/vertica/bin/vsql -Atc "SELECT 'SELECT sysdate || \$\$|' || table_schema || '|' || table_name || '|\$\$ || (SELECT COUNT(*) FROM \"' || table_schema || '\".\"' || table_name || '\")' || NVL2(LEAD(table_schema) OVER (ORDER BY table_schema), ' UNION', ';') FROM tables WHERE table_schema || '.' || table_name <> 'public.table_row_count';" | /opt/vertica/bin/vsql -At | /opt/vertica/bin/vsql -c "COPY table_row_count FROM STDIN DIRECT;"
[dbadmin@s18384357 ~]$ crontab -l
# Minute Hour Day Month Day of Week Command
5 3 * * * /opt/vertica/oss/python/bin/python -m vertica.do_logrotate &> /dev/null
59 23 * * * /home/dbadmin/table_row_count.sh
In a few months I’ll have a pretty good history of how my tables are growing and/or shrinking!Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/COUNTAggregate.htm/br>
https://www.vertica.com/docs/latest/HTML/Content/Authoring/ConnectingToVertica/vsql/CommandLineOptions.htm/br>
https://www.vertica.com/docs/latest/HTML/Content/Authoring/ConnectingToVertica/vsql/CopyingDataUsingVsql.htm Have fun!