Vertica Blog
Jim Knicely

Jim Knicely

Vertica Principal Solution Architect

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 Jim on

Helpful Tips text with hand and marker over data center background

Define a Vertica Flattened Table Column as Having Both DEFAULT and SET USING Constraints

Columns in a flattened table can query other tables with constraints DEFAULT and SET USING . Vertica executes DEFAULT queries only on new rows when they are added to the flattened table, through load operations such as INSERT and COPY. Thereafter, changes in the original data sources have no effect on the flattened table. Vertica...
Helpful Tips in blue text with magnifying glass

Viewing Parquet Export Events More Easily

The EXPORT TO PARQUET command exports a table, columns from a table, or query results to files in the Parquet format. When you run EXPORT TO PARQUET information about the files created during the export is stored in the Vertica log. It's no fun combing through a Vertica log looking for those particular records. Good...
Hand writing the text: Helpful Tips

Clearing the Screen in VSQL

Lots of old school folks use VSQL for administrative tasks. As you enter commands, eventually the command prompt will end up at the bottom left of the screen making it a little difficult to read any additional output from your commands. You can clear the screen and move the prompt back up to the top...
Compass rose with true north pointing to "Helpful Tips" text

Which Database Mode Is My Database Using?

You can run Vertica in either Eon Mode or Enterprise Mode. The primary difference between Eon and Enterprise Modes is where they store data. Eon Mode stores data in a shared object store called communal storage, while Enterprise Mode stores data across the filesystems of the database nodes. If you are working with a new...
Tips and Tricks Orange Button

Moving a Table to a New Schema and Inheriting the New Schema Privileges

You can move a table from one schema to another schema with a simple ALTER TABLE command. But, you might be surprised that the table will not inherit the new schema’s privileges even if the new schema was created using the DEFAULT INCLUDE PRIVILEGES directive. After moving the table you will need to manually alter...
Construction site crane building a blue SQL 3D text.

Get the Row Count from an Outer Table Join

Aggregate functions summarize data over groups of rows from a query result set. When using an aggregate function like COUNT with an "*" or "1" parameter value, you may get a different result when the query implements a LEFT join verses an INNER join. If you want a row count of just the rows from...
Helpful Tips message on post-it note

DIRECT Is Now the Default Load Type

Write Optimized Store (WOS) is a memory-resident data structure for short-term data storage while Read Optimized Store (ROS) is a highly optimized, read-oriented, disk storage structure, organized by projection. Prior to Vertica 9.3, by default, Vertica initially loads data into WOS. For databases created in version 9.3 and later, Vertica now uses a default load...
Create Read Update Delete in red 3D letters surrounded by 3D ones and zeroes

Vertica 9.3 Supports UPDATE and DELETE Operations on Tables Having Live Aggregate Projections!

A live aggregate projection contains columns with values that are aggregated from columns in its anchor table. When you load data into the table, Vertica aggregates the data before loading it into the live aggregate projection. On subsequent loads (for example, through INSERT or COPY) Vertica recalculates aggregations with the new data and updates the...
Hand writing the text: Helpful Tips

Tracking Raw Schema Size Over Time

There is no way to go back in time to see how large a schema was a day/month/year ago. But you can use the AUDIT function to get a “raw” size of a schema and track growth over time by storing the results from a daily audit. Using cron, you can run this daily. Helpful...
Photo of actual roadrunner running on a road showing yellow stripe in the middle

Have VSQL Beep Upon Command Completion

There are a lot of cool command line options available to VSQL. One of my favorites is the –b option which causes Vertica to “beep” when a command completes. This is very useful if you are running a very long sequence of SQL commands via a script. You can go off and do other things...
hands sweeping into a dustpan

Easy Development Schema Cleanup (i.e., Have Vertica Drop a Bunch of Tables for You)

On a development database, I have a lot of tables in the PUBLIC schema having similar names starting with “test_”. I’d like to drop all of these tables quickly. To do that, I can have Vertica generate the DROP commands and then execute them for me! P.S. Be careful with this command! Make sure when...
Helpful Tips in blue text with magnifying glass

Investigate Data Collector Table Data Produced by Scrutinize

The diagnostics tool scrutinize collects a broad range of information from a Vertica cluster. Part of the output from scrutinize includes data collector table data. Once extracted, we can easily load the data from a data collector table file into a Vertica Flex Table for analysis! Let’s take a look at the data from the...