Checking the Validity of a Table Audit: Quick Tip

Posted October 18, 2018 by Phil Molea, Sr. Information Developer, Vertica

Jim Knicely authored this tip.

The Vertica AUDIT function can be used to estimate the raw data size of a database, schema, or table.

Ever wonder if it’s accurate? A simple test shows that it is 100 percent accurate if you specify an error tolerance of 0 and a confidence level of 100.

Example: First let’s audit the table BIG_TABLE. [dbadmin@s18384357 ~]$ /opt/vertica/bin/vsql -c "SELECT audit('public.big_table', 0, 100);" audit ---------- 10696205 (1 row) Next extract the data from the table to a file, eliminating all field separators and record terminators as Vertica does not include those bytes in the audit. [dbadmin@s18384357 ~]$ stat -c %s /home/dbadmin/big_table.out 10696206 Wait a second! Why is the output file size 1 byte bigger than the result of the Vertica AUDIT function? It’s because there is a trailing new line character in the file. Let’s get rid of it! [dbadmin@s18384357 ~]$ printf %s "$(< /home/dbadmin/big_table.out)" > /home/dbadmin/big_table_no_trailing_nl.out [dbadmin@s18384357 ~]$ stat -c %s /home/dbadmin/big_table_no_trailing_nl.out 10696205 Now we are 100% accurate!

Helpful Links:

Have fun!