Vertica Blog

Vertica Blog

Quick Tips

Programmer

Find the Number of Days Passed and Remaining in the Relative Year

Sarah Lemaire, Manager, Vertica Documentation
August 9, 2018

Jim Knicely authored this post. Although there aren’t any specific functions that will return the number of days that have passed and that are remaining in a given year, you can combine a few of Vertica’s built-in date functions to find these numbers. You can encapsulate the date logic above into several user-defined functions that...

Displaying the Current Schema

James Knicely, Vertica Field Chief Technologist
August 8, 2018

Each user session has a search path of schemas. Vertica uses this search path to find tables and user-defined functions (UDFs) that are unqualified by their schema name. You can use the CURRENT_SCHEMA function to display the name of the current schema (i.e., the first “valid” schema in the user’s search path). dbadmin=> SHOW search_path;...

Permanently Attach a Comment to a Query: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
August 2, 2018

Jim Knicely authored this tip. There are several system tables like QUERY_REQUESTS that store the queries executed in the database. To help understand why a query was executed (i.e., for debugging purposes), you might want to add a comment to the SQL code. dbadmin=> SELECT request FROM query_requests WHERE transaction_id = current_trans_id() AND statement_id =...

Comparing Two Times: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
July 30, 2018

Jim Knicely authored this tip. There are several cool built-in functions that you can use to compare two times. Have fun!

Avoid a Division by Zero Error with the NULLIF and NULLIFZERO Functions: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
July 30, 2018

Jim Knicely authored this tip. The NULLIF built-in Vertica function compares two expressions. If the expressions are not equal, the function returns the first expression. If the expressions are equal, the function returns NULL. The NULLIFZERO built-in Vertica function evaluates to NULL if the value in the column is 0. Both come in handy when...
Three 3D arrows, different colors pointing in different directions

Determining Candidate Segmentation Keys: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
July 26, 2018

Jim Knicely authored this tip. Hash segmentation allows you to segment a projection based on a built-in hash function that provides even distribution of data across multiple nodes, resulting in optimal query execution. In a projection, the data to be hashed consists of one or more column values, each having a large number of unique...
Modern Database Analytics

Comparing Two Dates: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
July 25, 2018

Jim Knicely authored this post. There are several cool built-in functions that you can use to compare two dates. Have fun!

Table Last Accessed

Sarah Lemaire, Manager, Vertica Documentation
July 24, 2018

Jim Knicely authored this post. You can query the data collector table DC_PROJECTIONS_USED to ascertain when a table was last accessed and by whom. Helpful Links: PROJECTION_USAGE Querying Data Collector Tables Have fun!

Determining the Per-Node Storage of a Segmented Table

Sarah Lemaire, Manager, Vertica Documentation
July 23, 2018

Jim Knicely authored this blog. You can join a few system tables together to find the disk space used and the row counts by node for segmented tables: Skew in the data distribution across the nodes is an indicator of a poor choice of the table's segmentation key. Have fun!

Changing the Default Database Time Zone

Sarah Lemaire, Manager, Vertica Documentation
July 18, 2018

Jim Knicely authored this tip. When you first install Vertica, the default database time zone is controlled by the TZ environment variable. If TZ is undefined, Vertica uses the operating system time zone. You can change the default database time zone later by altering the value of the TZ environment variable or the operating system...

Vertica Quick Tip: Keepalive settings for AWS Network Load Balancer

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

Serge Bonte authored this post. Network Load Balancers are one of the three types of load balancers supported by Amazon’s Elastic Load Balancing. See: https://docs.aws.amazon.com/elasticloadbalancing/latest/network/introduction.html Since load balancers act as a proxy between clients (such as JDBC) and Vertica servers, it is important to understand how AWS’s NLB handle idle timeouts for connections. The idle...

Counting Days, Week Days and Weekend Days Across the Years: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
July 17, 2018

Jim Knicely authored this post. By combing Vertica’s built-in date formatting functions and the TIMESERIES clause, it’s easy to find the total number of days, total number of week days (Monday - Friday), and the total number of weekend days (Saturday - Sunday) that occur within a multi-year range (i.e., 2016 - 2018), including Leap...