Vertica Blog
Jim Knicely with a big teddy bear

James

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!

Connect With James on

Data Definition Language in red 3D text in a field of white 3D ones and zeros

Export CREATE OR REPLACE DDL for Database Views

James Knicely, Vertica Field Chief Technologist
July 19, 2019

The EXPORT_OBJECTS Catalog Management Function generates a SQL script you can use to recreate non-virtual catalog objects on another cluster. When you export a database view, Vertica includes the CREATE VIEW DDL in the output. But what if you want the DDL to be CREATE OR REPLACE VIEW? You can make that change with the...
Vintage businessman concept pointing on the wall wearing futuristic helmet at office

Hash Function Result Depends on Data Type and Sometimes Size

James Knicely, Vertica Field Chief Technologist
July 18, 2019

The Vertica HASH function calculates a hash value over the function arguments, producing a value in the range 0
Helpful Tips in blue text with magnifying glass

Loading Data Stored in Scientific Notation to an Integer Column

James Knicely, Vertica Field Chief Technologist
July 17, 2019

Scientific notation is a way of expressing numbers that are too big or too small to be conveniently written in decimal form. Vertica can display numbers in scientific notation in decimal form with ease. That is, unless the value is a string! You first have to convert the VARCHAR to a NUMERIC and then to...
Red and blue square dots fly from scattered into two neat rows arranged by color.

Auto-Projection Column Sort Order

James Knicely, Vertica Field Chief Technologist
July 8, 2019

Auto-projections are superprojections that Vertica automatically generates for tables, both temporary and persistent. Vertica uses the current value of the MaxAutoSortColumns configuration parameter to determine which table columns are included in the auto-projection sort order. Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Projections/AutoProjections.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/ConfiguringTheDB/ProjectionParameters.htm Have fun!
Hand with suit cuff flips shiny gold coin

Coin Flip User Defined SQL Function

James Knicely, Vertica Field Chief Technologist
July 2, 2019

In the past when my wife and I couldn’t decide which of two restaurants to go to grab dinner, we opened the Coin Flip app on one of our phones to let it decide. Now I let Vertica choose for us via a simple User Defined SQL Function! dbadmin=> SELECT coin_flip, COUNT(*) FROM (SELECT coin_flip()...
Quick Tip - blue button

Non-DBADMIN Access to System Tables (Table by Table)

James Knicely, Vertica Field Chief Technologist
July 1, 2019

The DBADMIN can assign a delegate the SYSMONITOR role to grant full access to system tables without granting full DBADMIN access. The SYSMONITOR role applies to all tables that have the flag IS_MONITORABLE set to true in the SYSTEM_TABLES system table. What if I want a non-DBADMIN user to have full access to only a...
Compass rose with true north pointing to "Helpful Tips" text

Tracking VBR Invocations

James Knicely, Vertica Field Chief Technologist
June 28, 2019

The Vertica Backup and Recovery tool (vbr) allows you to back up and restore either the full database, or one or more schema and table objects of interest. You can also copy a cluster and list backups you created previously. You can view vbr invocations by querying the DC_VBR_INVOCATIONS data collector table! Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/BackupRestore/VBRUtilityReference.htm...
Vintage businessman concept wearing futuristic helmet at office

Track License Usage Over Time

James Knicely, Vertica Field Chief Technologist
June 28, 2019

The LICENSE_AUDITS system table lists the results of Vertica's license automatic compliance audits. You can query it to track your Vertica license usage over time! Looks like I’m going to need add some new Vertica license soon! Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/LICENSE_AUDITS.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Licensing/CalculatingTheDatabaseSize.htm Have fun!
Tips and Tricks Orange Button

A Quick Way to View the Current and Ancient History Mark Epochs

James Knicely, Vertica Field Chief Technologist
June 20, 2019

The MANAGE_EPOCH function can be called to quickly display the Current Epoch (CE) and Ancient History Mark (AHM) Epochs! Helpful Links: https://www.vertica.com/kb/Understanding-Vertica-Epochs/Content/BestPractices/Understanding-Vertica-Epochs.htm Have fun!
Helpful Tips message on post-it note

Enable All Granted Roles on Login

James Knicely, Vertica Field Chief Technologist
June 20, 2019

You can automatically enable roles for users in two ways: Enable roles for individual users on login (either with the SET ROLE command or default roles). Enable all roles for all users on login (at the database level via the EnableAllRolesOnLogin parameter). The second option is great (and easy) if you want all users to...
Business card that says Expert Tips,

Generate DDL for Changed Configuration Parameters

James Knicely, Vertica Field Chief Technologist
June 13, 2019

The CONFIGURATION_CHANGES system table records the change history of system configuration parameters. If you want to apply all of the changes to a different Vertica database, you can generate the necessary DDL commands to do that! Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/CONFIGURATION_PARAMETERS.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/CONFIGURATION_CHANGES.htm Have fun!
Copy data

Generating Recursive COPY Commands

James Knicely, Vertica Field Chief Technologist
June 13, 2019

Recently a friend mentioned she wanted to load all of the files using the COPY command, where all the data files were located in many, many sub-directories. You can load all of the files in a single command by looping through each of the sub-directories. I have the following files in sub-directories under the parent...