Checking the Validity of a Table Audit

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

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.

[[email protected] ~]$ /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.

[[email protected] ~]$ /opt/vertica/bin/vsql -o /home/dbadmin/big_table.out -F '' -R '' -At -c "SELECT * FROM public.big_table;" -q

Here is the exported file size:

[[email protected] ~]$ 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 training new line character in the file. Let’s get rid of it!

[[email protected] ~]$ printf %s "$(< /home/dbadmin/big_table.out)" > /home/dbadmin/big_table_no_trailing_nl.out

[[email protected] ~]$ stat -c %s /home/dbadmin/big_table_no_trailing_nl.out
10696205

Now we are 100% accurate!

Helpful Links:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/LicenseManagement/AUDIT.htm
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Licensing/CalculatingTheDatabaseSize.htm

Have fun!

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    Fyi... Instead of having to create the output file as I did in in the example above, you can stream the output from VSQL to WC to get the output size in bytes:

    Example:

    [[email protected] ~]$ /opt/vertica/bin/vsql -c "SELECT audit('public.big_table2', 0, 100);"
       audit
    ------------
    5916352340
    (1 row)
    
    [[email protected] ~]$ /opt/vertica/bin/vsql -F '' -R '' -At -c "SELECT * FROM public.big_table2;" -q | wc -c
    5916352341
    

    Thanks!

Sign In or Register to comment.