Vertica Blog

Vertica Blog

Quick Tips

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

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...
Three 3D arrows, different colors pointing in different directions

Exiting a DbVisualizer Script Following an Error: Quick Tip

James Knicely, Vertica Field Chief Technologist
September 19, 2018

After reading yesterday’s Vertica Quick Tip “Exiting a vsql Script Following an Error”, a client asked if the ON_ERROR_STOP variable is available in the popular third party Vertica client tool DbVisualizer. The answer to that is no, as ON_ERROR_STOP is a Vertica vsql client specific setting. However, many clients, including DbVisualizer, have a similar feature!...

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

Self-Descriptive Constraint Names: Quick Tip

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

Jim Knicely authored this tip. Constraints set rules on what data is allowed in table columns and help maintain data integrity. PRIMARY KEY, REFERENCES (foreign key), CHECK, and UNIQUE constraint must be named. If you omit assigning a name, Vertica automatically assigns one. Those Vertica-created constraint names aren’t very descriptive. It’s a good idea to...

Database and Node Uptime: Quick Tip

James Knicely, Vertica Field Chief Technologist
September 10, 2018

You can query the DATABASES system table to find out the last time your Vertica database started and you can get the cluster node up times by querying the NODE_STATES system table. dbadmin=> SELECT node_name, MAX(event_timestamp) node_uptime dbadmin-> FROM node_states dbadmin-> WHERE node_state = 'UP' dbadmin-> GROUP BY node_name dbadmin-> ORDER BY node_name; node_name |...