Vertica Blog

Vertica Blog

Best Practices

Special Date/Time Formatting: Quick Tip

Jim Knicely authored this tip. Vertica supports several special date/time values for our convenience. All of these values need to be written in single quotes when used as constants in SQL statements. My favorite is ALLBALLS which is named so because the time digits look like balls (I had to Google that). Helpful link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Expressions/DateTimeExpressions.htm...
Three 3D arrows, different colors pointing in different directions

Change the VSQL Result Set Border: Quick Tip

Jim Knicely authored this tip. vsql is a character-based, interactive, front-end utility that lets you type SQL statements and see the results. The results from vsql are formatted nicely with a border between columns. You are probably used to viewing border style 1, the default, but you may prefer border styles 0 or 2! Helpful...

Find Which System Tables Have a Particular Column Name: Quick Tip

Jim Knicely authored this tip. There are over 180 built-in Vertica system tables in the V_CATALOG and V_MONITOR schemas. Trying to remember which system tables contain a certain column can be a daunting task. Instead, query the V_CATALOG.SYSTEM_COLUMNS system table! Helpful link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/CATALOG/SYSTEM_COLUMNS.htm Have fun!
Programmer

Find Which Tables Have a Particular Column Name: Quick Tip

Jim Knicely authored this tip. Your Vertica database probably contains hundreds, if not thousands, of tables. Trying to remember which tables contain a certain column can be a daunting task. Instead, query the V_CATALOG.COLUMNS system table! Which tables have a column named “DateKey”? Which tables have a column with a name that contains the text...

Database Storage – Raw and Compressed Size: Quick Tip

Jim Knicely authored this post. One of the files generated by the /opt/vertica/scripts/collect_diag_dump.sh script details the raw and compressed size for every table in the Vertica database. At the bottom of the report there is a “total” line that shows the database raw and compressed size! Example Helpful link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/AdministratorsGuide/Diagnostics/ExportingProfilingData.htm Have fun!

Display Last SQL Run Time: Quick Tip

Jim Knicely authored this post. When the \timing meta-command in vsql is set to “on”, Vertica will return how long (in milliseconds) each SQL statement runs. That’s great! But what if you forgot to turn it on prior to executing a long running query? Instead of turning it on then re-executing that long running query,...

Displaying the Administration Tools Process ID: Quick Tip

Jim Knicely authored this post. From within vsql you can run the Linux ps command to display the Administration Tools process on the local node. However, it’s a lot easier to use the GETPID function. Helpful link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/AdministratorsGuide/Monitoring/Vertica/MonitoringProcessStatusPs.htm Have fun!

Rotating Column Data Using SQL Analytics: Quick Tip

Jim Knicely authored this post. Vertica analytics are SQL functions based on the ANSI 99 standard. These functions handle complex analysis, reporting tasks, and allow for some cool data manipulation. I have the following table of names where each name has a unique ID. I want to rotate the values in column NAME up by...

Return Zero Instead of NULL: Quick Tip

Jim Knicely authored this post. The built-in Vertica function ZEROIFNULL evaluates to 0 for NULL values in a table’s column. Whoa! What happened in that last statement? Since NULL by itself does not have a data type, you’ll need to cast it to a valid data type for the function! Helpful link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Null/ZEROIFNULL.htm Have fun!

Exiting a vsql Script Following an Error: Quick Tip

Jim Knicely authored this tip. By default, if a vsql script command results in an error, for example, because of a malformed command or invalid data format, processing continues. If you set ON_ERROR_STOP to “on” in a vsql script and an error occurs during processing, the script terminates immediately. dbadmin=> CREATE TABLE tbl1_temp (pkid INT);...

Using admintools to List Nodes for a Particular Database: Quick Tip

Jim Knicely authored this post. The Vertica Administration tools allow you to easily perform administrative tasks, such as quickly viewing information and statuses of all database nodes via the list_allnodes tool. If I only want to see the IP addresses and states of the nodes from a particular database, I could combine the Linux grep...

Faster Data Loads with Apportioned Load: Quick Tip

Jim Knicely authored this tip. Vertica can divide the work of loading data, taking advantage of parallelism to speed up the operation. One supported type of parallelism is called apportioned load. An apportioned load divides a single large file or other single source into segments (portions), which are assigned to several nodes to be loaded...