Vertica Blog

Vertica Blog

Quick Tips

Helpful Tips message on post-it note

Enable All Granted Roles on Login

James Knicely, Vertica Field Chief Technologist
June 20, 2019

You can automatically enable roles for users in two ways: Enable roles for individual users on login (either with the SET ROLE command or default roles). Enable all roles for all users on login (at the database level via the EnableAllRolesOnLogin parameter). The second option is great (and easy) if you want all users to...
Business card that says Expert Tips,

Generate DDL for Changed Configuration Parameters

James Knicely, Vertica Field Chief Technologist
June 13, 2019

The CONFIGURATION_CHANGES system table records the change history of system configuration parameters. If you want to apply all of the changes to a different Vertica database, you can generate the necessary DDL commands to do that! Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/CONFIGURATION_PARAMETERS.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/CONFIGURATION_CHANGES.htm Have fun!
Copy data

Generating Recursive COPY Commands

James Knicely, Vertica Field Chief Technologist
June 13, 2019

Recently a friend mentioned she wanted to load all of the files using the COPY command, where all the data files were located in many, many sub-directories. You can load all of the files in a single command by looping through each of the sub-directories. I have the following files in sub-directories under the parent...
Counting

Table Row Counts by Schema

James Knicely, Vertica Field Chief Technologist
June 13, 2019

I had a colleague ask today how she could generate a report showing the total table row counts by schema. This should be easy, right? After all, the PROJECTION_STORAGE system table has a ROW_COUNT column. But it turns out it’s a little complex considering there are segmented and unsegmented projections and delete vectors in Vertica....
Spread Daemon Timeout

Set the Spread Daemon Timeout Back to the Default Value

James Knicely, Vertica Field Chief Technologist
May 24, 2019

The default value for the Spread Daemon timeout is 8 seconds when the IP addresses of all cluster nodes start with the same two bytes. If all the IP addresses do not start with the same two bytes, then the default is 25 seconds. When you know your network or nodes may be unable to...
Spread Daemon Timeout

Monitor the Current Value of Node Spread Daemon Timeouts

James Knicely, Vertica Field Chief Technologist
May 24, 2019

When you know your network or nodes may be unable to respond for a specific amount of time due to network delays or temporary pauses of a VM, you can increase the spread timeout period to longer than this time using the SET_SPREAD_OPTION function (available starting in Vertica 9.2.1-0). You can view the current value...
Spread Daemon Timeout

Min and Max Values for Spread Daemon Timeout

James Knicely, Vertica Field Chief Technologist
May 24, 2019

When you know your network or nodes may be unable to respond for a specific amount of time due to network delays or temporary pauses of a VM, you can increase the spread timeout period to longer than this time using the SET_SPREAD_OPTION function (available starting in Vertica 9.2.1-0). You might be wondering what the...
Spread Daemon Timeout

Adjusting Spread Daemon Timeouts for Virtual Environments

James Knicely, Vertica Field Chief Technologist
May 24, 2019

If you are running Vertica in a virtual environment, you’ve probably seen nodes leave the cluster even though they are still running. If network delays or temporary pauses of a VM last longer than the spread timeout period, you may see up nodes leave the database. In these cases, you can increase the spread timeout...
Helpful Tips in blue text with magnifying glass

Include the ALTER and DROP Privileges in a GRANT ALL Statement

James Knicely, Vertica Field Chief Technologist
May 20, 2019

When you issue a GRANT ALL statement on a database object such as a table, the ALTER and DROP privileges are excluded. Staring in Vertica 9.2.1, you can include the ALTER and DROP privileges as part of a GRANT ALL statement by specifying the EXTEND clause. Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/GRANT/GRANTTable.htm Have fun!
Helpful Tips text with hand and marker over data center background

Watch Out for Data Induced “Cross” Joins

James Knicely, Vertica Field Chief Technologist
May 20, 2019

I recently worked with a client who reported that a query in Vertica would not return data. That is, the query ran forever. The query was pretty simple, joining two 100 billion+ tables together. After some investigation, we realized that the query could potentially return trillions and trillions of records! Why? Because the join keys...
Quick Tip - blue button

Direct Output to /dev/null in VSQL

James Knicely, Vertica Field Chief Technologist
May 16, 2019

When tuning a query, you often need to run it many times as you make adjustments. If the query returns a lot of rows, then this can make for a messy screen and long waits as VSQL formats the data. All you really want to know is how long it took to run the query....
Vintage businessman concept pointing on the wall wearing futuristic helmet at office

NULL Values Do Not Count Against Your Vertica License!

James Knicely, Vertica Field Chief Technologist
May 16, 2019

I am often asked if NULL values stored in table columns are counted against the raw data size of a Vertica license. The answer is NO. Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Licensing/CalculatingTheDatabaseSize.htm Have fun!