Vertica Blog
Avatar photo

Phil Molea

Sr. Information Developer, Vertica

Phil developed technical documentation in the areas of security and diagnostics for the Vertica Analytics Platform that enabled companies to extract value from their data at the speed and scale they need to thrive in today’s economy.

(Sadly, Phil passed away recently. He will be missed.)

Connect With Phil on

Conveniently Display Key Projection Info: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 22, 2018

Jim Knicely authored this tip. The Vertica GET_PROJECTION_STATUS function can be called to quickly display key projection facts such as segmentation columns, verified fault tolerance, and statistics status. Examples: Helpful Link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/GET_PROJECTION_STATUS.htm Have fun!

Checking the Validity of a Table Audit: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 18, 2018

Jim Knicely authored this tip. The Vertica AUDIT function can be used to estimate the raw data size of a database, schema, or table. Ever wonder if it’s accurate? A simple test shows that it is 100 percent accurate if you specify an error tolerance of 0 and a confidence level of 100. First let’s...

Generate an Auto-Incrementing Alphabetical Series Using SQL: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 17, 2018

Jim Knicely authored this tip. One of the very first Vertica questions I was asked from a client was how to generate an auto-incrementing alphabetical series using SQL for use as a primary key in a table. For example, the user wanted to generate this result set: We figured out that this was easily done...

Display Null Query Result With an Alternate Text: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 16, 2018

Jim Knicely authored this post. In vsql, the /pset meta-command is used to set options that control how Vertica formats query result output. One of my favorites is the null option which allows you to specify an alternative value for null in the result set of a query. That didn’t work! Make sure to use...

Special Date/Time Formatting: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 15, 2018

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

Phil Molea, Sr. Information Developer, Vertica
October 11, 2018

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

Phil Molea, Sr. Information Developer, Vertica
October 10, 2018

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

Phil Molea, Sr. Information Developer, Vertica
October 9, 2018

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

Phil Molea, Sr. Information Developer, Vertica
October 8, 2018

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!

DbVisualizer Free for Vertica Distribution Updates

Phil Molea, Sr. Information Developer, Vertica
October 8, 2018

Stephen Crossman authored this post Recently, there have been some changes in how DbVisualizer Free for Vertica is distributed. Previously, there were standard DbVisualizer Free and Pro Edition distributions available on the DbVisualizer web site, and there was a special DbVisualizer Free for Vertica distribution available on the Vertica Marketplace. Now, in an effort to...

Display Last SQL Run Time: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 4, 2018

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

Phil Molea, Sr. Information Developer, Vertica
October 3, 2018

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!