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

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...
Construction site crane building a blue SQL 3D text.

Load Data Enclosed By and Embedded With Double Quotes

Loading string data that is enclosed in double quotes where the string also contains double quotes can be problematic. We could alter the data so that it includes an escape character prior to each embedded double quote, but maybe that’s not possible. Another solution would be to use a FILLER to load the data then...
Cow peering around edge of frame with blue sky and clouds in shape of a comic book thought bubble.

Make a Cow Display Query Results in VSQL

I used to have fun with the amusing old Linux command Cowsay. It inserts any input into a word bubble and draws an ASCII cow to talk to you. You can make the cow display your query results in VSQL! Helpful Links: Have fun!
Business card that says Expert Tips,

Stop a Stubborn Query that Won’t Cancel

Sometimes a running SQL statement hangs and cannot be stopped using the CLOSE_SESSION or INTERRUPT_STATEMENT function. To stop it, first grab the culprit’s SESSION_ID, TRANSACTION_ID, and STATEMENT_ID from the QUERY_REQUESTS system table, then you can move it to a resource pool that has no resources! After the move and subsequent re-plan, the query will “usually”...
Compass rose with true north pointing to "Helpful Tips" text

Change Your User Password in VSQL with a Meta-Command

The \password VSQL meta-command can be used to change your database user’s password! The admin user can change the database password for any user: Helpful Link: Have fun!
Hand holding old-fashioned brass balance scale on a black background

View the History of Connection Load Balance Operations

The Data Collector table DC_LOAD_BALANCE_OPERATIONS tracks the history of connection load balance operations. Helpful Links: Have fun!