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!
About the Author
James Knicely
Vertica Field Chief Technologist
I've had the privilege of working with many database technologies in my career. But after being introduced to Vertica in May of 2011 as a client, I was hooked on the new technology after witnessing a query run in milliseconds that had previously ran for hours on the legacy database we had in place. It was then that I knew I wanted to eventually join the Vertica team, and 4 years later I did! I am currently a Vertica evangelist and am ready to help you get on board! Please feel free to reach out to me with any questions you have about Vertica and make sure to follow my Vertica Quick Tips!
