Vertica Blog

Vertica Blog

Quick Tips

Programmer

SQL Reserved Words: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
June 11, 2018

Jim Knicely authored this tip. Keywords are words that have a specific meaning in the SQL language. Every SQL statement contains one or more keywords. Many keywords are also reserved words. Vertica recommends that you not use reserved words as names for objects, or as identifiers. Including reserved words can make your SQL statements confusing....
Database Server Room

Loading Negative (BC) Dates: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
June 7, 2018

Jim Knicely authored this tip. I was asked recently how to load a negative date (one representing a BC date) into Vertica. Although negative dates are not valid in Vertica, the FILLER parameter of the COPY command can be used to load them. dbadmin=> \! cat /home/dbadmin/date.txt -4712-01-01 00:00:00 dbadmin=> COPY public.test FROM '/home/dbadmin/date.txt' REJECTED...

Superfast Table Copy (Revisited): Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
June 6, 2018

Jim Knicely authored this tip. You learned from a Vertica Quick Tip back on 02/07/2018 that Vertica has the lightweight, in-memory COPY_TABLE function. This awesome function lets us copy huge tables in less than a second. I wanted to revisit this awesome feature to point out that when using the COPY_TABLE command, if the target...

Handling OLE DB Square Brackets: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
June 5, 2018

Jim Knicely authored this blog. Many Microsoft products, including SSAS, can connect to Vertica using the Vertica OLE DB driver for Windows which is installed as part of the Client Drivers and Tools for Windows. Unfortunately tools like MS SSAS can include square brackets to qualify identifiers in the queries it generates to run in...

Vertica Quick Tip: Monitoring Changes to Configuration Parameters

Sarah Lemaire, Manager, Vertica Documentation
June 4, 2018

Jim Knicely authored this tip. The CONFIGURATION_CHANGES system table records the change history of system configuration parameters. This information is useful for identifying: • Who changed the configuration parameter value • When the configuration parameter was changed • Whether nonstandard settings were in effect in the past dbadmin=> SELECT event_timestamp, user_name, parameter, value FROM configuration_changes...
Modern Database Analytics

Vertica Quick Tip: Replacing an Empty String

Soniya Shah, Information Developer
May 31, 2018

This blog post was authored by Jim Knicely. In Vertica an empty string is not treated as a NULL value, so an empty string does equal an empty string. The function REPLACE replaces all occurrences of characters in a string with another set of characters. The REPLACE function appears to work well with an empty...
Programmer

Vertica Quick Tip: Empty String Vs. NULL

James Knicely, Vertica Field Chief Technologist
May 30, 2018

An empty string ('') is treated as a NULL value in Oracle, while in Vertica an empty string is not treated as a NULL value. So when using Vertica, if you want to indicate that a column value is unknown, be sure to use NULL and not an empty string! In Oracle: Are they the...

Vertica Quick Tip: Which Column in my Flattened Table caused the “Subquery used as an expression returned more than one row” Error?

Soniya Shah, Information Developer
May 29, 2018

This blog post was authored by Jim Knicely. A flattened table contains columns that are derived from query-expressions that must return only one row and column value, or none. If a column’s query-expression does return more than one row you will get a “Subquery used as an expression returned more than one row” error. If...
Database Server Room

Vertica Quick Tip: Tuple Mover (TM) Assigned More Resources in Vertica 9.1

Soniya Shah, Information Developer
May 24, 2018

This blog post was authored by Jim Knicely. In version 9.1 and later, Vertica increases the default amount of memory assigned to the Tuple Mover (TM) resource pool from 200MB to 5% of available memory to improve performance merging out wide tables. If your General resource pool is defined by a percentage, the Tuple Mover...

Vertica Quick Tip: Monitoring the Ancient History Mark

Soniya Shah, Information Developer
May 23, 2018

This blog post was authored by Jim Knicely. Also known as AHM, the ancient history mark is the oldest epoch whose data is accessible to historical queries. Any data that precedes the AHM is eligible to be purged. If a cluster nodes is down or if a database contains projections that need to be refreshed,...

Vertica Quick Tip: Determining Table Columns that Contain NULL Values

Soniya Shah, Information Developer
May 22, 2018

This blog post was authored by Jim Knicely. A client recently asked if we had a Vertica function that could return a list of columns from a table which contain NULL values. There aren’t any built-in functions for that, but there is always a fast solution available in Vertica! Here’s one! dbadmin=> CREATE OR REPLACE...

Vertica Quick Tip: Getting Better Performance via External Table Row Counts

Soniya Shah, Information Developer
May 21, 2018

This blog post was authored by Jim Knicely. The Vertica optimizer uses its own internal logic to determine whether to join one table to another as an inner or outer input. When joining external tables, you can help out the optimizer immensely by letting it know the exact row counts of those external tables! For...