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

Rejected Data Table Row Number: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 15, 2018

When running a COPY command, using the REJECTED DATA parameter with the AS TABLE clause saves rejected data into a table. The rejected data table includes an informative column called ROW_NUMBER where its value indicates the rejected row number from the input file. Be aware that when a COPY encounters an empty line while loading...
Database Server Room

Reload Data from a Rejected Data Table: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 14, 2018

When running a COPY command, using the REJECTED DATA parameter with the AS TABLE clause, will save rejected data into a table. If you realize there is a modification to the COPY command that will allow those rejected records to load successfully, you can re-run the updated COPY command against the rejected data in the...

Handling Cast Conversion Load Errors: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 13, 2018

The nifty cast ::! returns all cast failures as NULL instead of generating an error if a the data type cannot be coerced. This cast feature, combined with the FILLER option of the COPY command, is very useful for loading data when data types aren’t playing nice. Vertica cannot implicitly cast the NULL value to...

Return All Cast Failures as NULL: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 12, 2018

When you invoke data type coercion (casting) by an explicit cast and the cast fails, the result returns either an error or NULL. Cast failures commonly occur when you attempt to cast conflicting conversions, such as trying to convert a varchar expression that contains letters to an integer. However, using the nifty cast ::! instead...

Display Canceled Queries: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 8, 2018

We can cancel a long running query in vsql by typing CTRL+C. The data collector table DC_CANCELS tracks queries that were stopped in this manner. Now I will run a simple SQL statement and hit CTRL+C after waiting for 2 seconds: Helpful Link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/Glossary/DataCollector.htm Have fun!

Calculate Request Queue Length: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 7, 2018

The RESOURCE_ACQUISITIONS system table retains information about resources (memory, open file handles, threads) acquired by each running request. Each request is uniquely identified by its transaction and statement IDs within a given session. From this system table, you can calculate how long a request was queued in a resource pool before acquiring the resources it...

Concatenate non-NULL Values from a Group: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 6, 2018

Vertica 9.1.1-4 introduces an extremely useful aggregate function named LISTAGG, which returns a string with concatenated non-NULL values from a group. Have fun!

Simplify String Literals with Dollar-Quoted String Literals: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 6, 2018

The standard syntax for specifying string literals can be difficult to understand. To allow more readable queries in such situations, Vertica SQL provides dollar quoting. Dollar quoting is not part of the SQL standard, but it is often a more convenient way to write complicated string literals than the standard-compliant single quote syntax. Helpful Link:...

Re-Compute a Table Column’s Default Value Immediately: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 1, 2018

Vertica evaluates the DEFAULT expression and sets the column on load operations, if the operation omits a value for the column. That DEFAULT expression can be derived from another column in the same table! When you update the value in a base column, you will need to re-compute the value in your derived column (the...
Three 3D arrows, different colors pointing in different directions

Exiting a DbVisualizer Script Following an Error: Quick Tip

James Knicely, Vertica Field Chief Technologist
September 19, 2018

After reading yesterday’s Vertica Quick Tip “Exiting a vsql Script Following an Error”, a client asked if the ON_ERROR_STOP variable is available in the popular third party Vertica client tool DbVisualizer. The answer to that is no, as ON_ERROR_STOP is a Vertica vsql client specific setting. However, many clients, including DbVisualizer, have a similar feature!...

Database and Node Uptime: Quick Tip

James Knicely, Vertica Field Chief Technologist
September 10, 2018

You can query the DATABASES system table to find out the last time your Vertica database started and you can get the cluster node up times by querying the NODE_STATES system table. dbadmin=> SELECT node_name, MAX(event_timestamp) node_uptime dbadmin-> FROM node_states dbadmin-> WHERE node_state = 'UP' dbadmin-> GROUP BY node_name dbadmin-> ORDER BY node_name; node_name |...

Mimicking Enumerated Types: Quick Tip

James Knicely, Vertica Field Chief Technologist
September 6, 2018

I used to work a lot with MySQL. It had a cool data type called "Enumerated Types". Example in MySQL: How do we do this in Vertica? With a Check Constraint! Example in Vertica: Helpful link: https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Constraints/ConstraintTypes/CheckConstraints.htm Have fun!