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

Database Server Room

Rotating Column Data Using SQL Analytics: Quick Tip

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

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

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

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!

Viewing Previously Generated Explain Plans: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
September 27, 2018

Jim Knicely authored this post. The EXPLAIN command returns a formatted description of the Vertica optimizer's plan for executing the specified statement. dbadmin=> EXPLAIN SELECT * FROM fact JOIN dim USING (c2); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN DESCRIPTION: ------------------------------ EXPLAIN SELECT * FROM fact JOIN dim USING (c2); Access Path: +-JOIN MERGEJOIN(inputs presorted) [Cost: 25,...

Display the Current Statement Number Within the Current Transaction: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
September 26, 2018

Jim Knicely authored this post. A Transaction in Vertica is one or more operations that are executed as a unit of work. At the user level, transactions occur in the current session by a user or script running one or more SQL statements. A transaction has a unique Transaction ID and the “units of work”...

My Intern Experience @ Vertica

Phil Molea, Sr. Information Developer, Vertica
September 26, 2018

Zherui Cao authored this post. Hello, I am Zherui Cao. I am a currently studying for my Masters Degree in computing and information technology at the University of Pittsburgh. A kind Pitt alumni informed me of a summer internship opportunity at Vertica. With database development experience, I quickly became a good candidate for an internship....

Changing the Owner of a View: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
September 25, 2018

Jim Knicely authored this tip. We can change the owner of a database view with the ALTER VIEW … OWNER TO command. dbadmin=> INSERT INTO test SELECT 1; OUTPUT -------- 1 (1 row) dbadmin=> CREATE VIEW test_vw AS SELECT c1 FROM test; CREATE VIEW dbadmin=> SELECT table_schema, table_name, owner_name dbadmin-> FROM views dbadmin-> WHERE table_name...

Setting Variables on the VSQL Command Line: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
September 24, 2018

Jim Knicely authored this post. The vsql client provides variable substitution features similar to common Linux command shells. Variables are name/value pairs, where the value can be a string of any length. The -v, --variable, and --set options can be used to set a variables value from the command line. Example: The following simple SQL...

Enabling Inter-Node Spread Encryption: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
September 20, 2018

Jim Knicely authored this tip Internode SSL allows you to use SSL to secure communication between nodes within a Vertica cluster. It is important to secure communications between Vertica nodes if you do not trust the network between the nodes. The EncryptSpreadComm configuration parameter enables encryption on the control channel. dbadmin=> SELECT SET_CONFIG_PARAMETER('EncryptSpreadComm', 'vertica'); SET_CONFIG_PARAMETER...

Exiting a vsql Script Following an Error: Quick Tip

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

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);...

The Parts of a Session ID: Quick Tip

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

Jim Knicely authored this post. A Vertica Session is an occurrence of a user interacting with a database through the use of SQL statements. Each Session has an associated Session Identifier stored in the SESSION_ID column of many of the Vertica system tables. The Session ID is a unique identifier within the cluster at any...

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

Phil Molea, Sr. Information Developer, Vertica
September 13, 2018

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

Phil Molea, Sr. Information Developer, Vertica
September 13, 2018

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...