Vertica Blog

Vertica Blog

DBadmin

Analyze Statistics at the Schema Level (Part 2): Quick Tip

Phil Molea, Sr. Information Developer, Vertica
June 19, 2018

Jim Knicely authored this tip. The ANALYZE_STATISTICS function only accepts a table/projection/column name as input. In yesterday’s Vertica Quick Tip we learned how to get Vertica to generate and execute ANALYZE_STATISTICS SQL statements, one for each table in a given schema. It was an okay solution, but not very convenient. A better option would be...

Analyze Statistics at the Schema Level (Part 1): Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
June 18, 2018

Jim Knicely wrote this tip. The ANALYZE_STATISTICS function collects and aggregates data samples and storage information from all nodes that store projections associated with the specified table. The function accepts a table/projection/column name as input. What if you wanted to get stats for all of the tables in a schema? One option is to have...

What Version of Vertica am I Running?

Sarah Lemaire, Manager, Vertica Documentation
June 14, 2018

Jim Knicely authored this tip. The built-in VERSION function returns a VARCHAR that contains your Vertica node's version information. The Vertica version is formatted as X.Y.Z-R, where… • X.Y is the two-digit Vertica major release number, e.g., 8.1, 9.0 and 9.1 • Z is the service pack number, e.g., 7.2.3, 8.1.1 and 9.0.1 • R...
Three 3D arrows, different colors pointing in different directions

Which One of these Nodes Doesn’t Belong? A Vertica Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
June 13, 2018

Jim Knicely wrote this tip. We strongly recommend that you use a homogeneous hardware configuration for your Vertica cluster: Each node of the cluster should be similar in CPU, clock speed, number of cores, memory, and operating system version. We learned in the last Vertica Quick Tip that the V_CATALOG.HOST_RESOURCES system table provides a snapshot...

Getting your Node Hardware Specs: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
June 12, 2018

Jim Knicely wrote this tip. At some point in the future, you might not remember the number of CPU sockets/processor cores and the amount of memory on each node of your Vertica cluster. Instead of having to issue several Linux commands on each node, Vertica provides the V_CATALOG.HOST_RESOURCES system table, which provides a snapshot of...
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...