Vertica Blog
Jim Knicely

Jim Knicely

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

Keep Data Collector Information for a Set Interval: Quick Tip

The Data Collector retains history of important system activities and records essential performance and resource utilization counters. You probably know that you can set a size restraint (memory and disk space in kilobytes) for the specified Data Collector table on all nodes. But if you are looking to keep records for some period of time,...

Turn Off Row Counts in VSQL: Quick Tip

he SET NOCOUNT command in SQL Server will stop the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results. You can accomplish the same thing in Vertica by shutting off the footer. Helpful Links: Have fun!
Programmer

Generate a Notice Not an Error When Adding a Duplicate Column to a Table: Quick Tip

When adding a column to a table in Vertica 9.2, the optional clause IF NOT EXISTS generates an informational message if the column already exists under the specified name. If you omit this option and the column exists, Vertica generates a ROLLBACK error message. dbadmin=> ALTER TABLE test ADD COLUMN c2 INT; ROLLBACK 3145: Duplicate...

Swapping Schemas: Quick Tip

Renaming schemas is useful for swapping schemas without actually moving data. To facilitate the swap, enter a non-existent, temporary placeholder schema. But be careful with hardcoded schema names in SQL code like views! They won’t be swapped. dbadmin=> create schema schema2; CREATE SCHEMA dbadmin=> create table schema1.test1 (c int); CREATE TABLE dbadmin=> create table schema2.test1...

Confirming if a Data Load was Apportioned: Quick Tip

Jim Knicely authored this tip. When running a COPY command, a parser can use more than one database node to load a single input source in parallel. This approach is referred to as apportioned load and is often much faster than loading from a single node. One way of confirming that a data load took...

Sort Data from Within a View: Quick Tip

If a view definition includes an ORDER BY clause, Vertica ignores it. But you can force Vertica to sort the view data on the ORDER BY columns by adding a LIMIT clause. If you don’t know how many records to limit, simply limit by some arbitrary large value! dbadmin=> CREATE VIEW some_view AS dbadmin-> SELECT...

Copy Table History: Quick Tip

The COPY_TABLE function copies one table to another… FAST! Down the road I might want to know if a table was created as a copy using the COPY_TABLE function. To find out, I can query the data collector table DC_COPY_TABLES. dbadmin=> \timing on Timing is on. dbadmin=> SELECT copy_table('big_fact', 'big_fact_copy'); copy_table ---------------------------------------------------------------------- Created table big_fact_copy....

Restarting a Downed Node: Quick Tip

I’m a big fan of scripting with admintools which provides us with many database tools. One of those awesome tools, that I just became familiar with, is command_start. It allows us to start a downed node, with one caveat: you have to run the command on the node that is down. Here I’ll use the...

Handling Expression Errors in COPY Statements: Quick Tip

We can transform data as it is loaded via a COPY command using a FILLER column and a COPY expression. In the example below, a COPY expression uses the LOWER function to convert uppercase text to lowercase as it is loaded. dbadmin=> \! cat /home/dbadmin/test.txt 1|TEST1 2| 3|TEST3 A|TEST4 dbadmin=> COPY test (a, b) dbadmin->...

Encode Projection Columns with Zstandard Compression: Quick Tip

Vertica 9.2 now supports the Zstandard Compression Encoding Type which offers higher compression than the BZIP and GZIP Encoding Types! Helpful Link: Have fun!
Three 3D arrows, different colors pointing in different directions

Calculate the Catalog Size in Memory on Each Node: Quick Tip

The RESOURCE_POOL_STATUS system table provides the current state of built-in and user-defined resource pools on each node, including memory usage. The METADATA built-in pool tracks memory allocated for catalog data and storage data structures. Knowing those two facts, we can calculate catalog memory usage on all nodes by querying the system table RESOURCE_POOL_STATUS for the...

Labeling Queries: Quick Tip

To quickly identify queries for profiling and debugging purposes, include the LABEL hint. Later you can search the QUERY_REQUESTS and QUERY_PROFILES systems table for the label! Helpful Links: Have fun!