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

Swapping Schemas: Quick Tip

James Knicely, Vertica Field Chief Technologist
December 13, 2018

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

James Knicely, Vertica Field Chief Technologist
December 13, 2018

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...
Database Server Room

Sort Data from Within a View: Quick Tip

James Knicely, Vertica Field Chief Technologist
December 11, 2018

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

James Knicely, Vertica Field Chief Technologist
December 11, 2018

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

James Knicely, Vertica Field Chief Technologist
December 6, 2018

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

James Knicely, Vertica Field Chief Technologist
December 4, 2018

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

James Knicely, Vertica Field Chief Technologist
December 3, 2018

Vertica 9.2 now supports the Zstandard Compression Encoding Type which offers higher compression than the BZIP and GZIP Encoding Types! Helpful Link: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/encoding-type.htm Have fun!
Three 3D arrows, different colors pointing in different directions

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

James Knicely, Vertica Field Chief Technologist
November 28, 2018

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

James Knicely, Vertica Field Chief Technologist
November 28, 2018

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: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Profiling/LabelQueries.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATETABLE.htm Have fun!
Modern Database Analytics

Create a User Defined SQL Function to Calculate Fibonacci Numbers: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 26, 2018

The Fibonacci Sequence is the series of numbers (i.e. 0, 1, 1, 2, 3, 5, 8, 13, 21, 34, ...) where the next number in the sequence is found by taking the sum of the previous two numbers. Calculating these numbers in SQL would be a bit complicated if it were not for the “Golden...

Insert Spaces Into a Character String: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 20, 2018

In many SQL relational database you will have to use the RPAD function to insert spaces into a character string. That also works in Vertica. However, for a more robust solution, Vertica provides the built-in function SPACE which returns the specified number of blank spaces. Helpful Links: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/String/SPACE.htm https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/String/RPAD.htm Have fun!

Find the Version of Vertica that Created a Database: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 19, 2018

You can run the VERSION() function as one method of displaying the current version of Vertica. But what if you want to know the version of Vertica running when you created the current database? For that info you can query the VS_GLOBAL_SETTINGS table. Helpful Link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/AdministratorsGuide/Diagnostics/DeterminingYourVersionOfVertica.htm Have fun!