Checking the Validity of a Table Audit: Quick Tip

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:

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/LicenseManagement/AUDIT.htm

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/AdministratorsGuide/Licensing/CalculatingTheDatabaseSize.htm

Have fun!