Vertica Blog

Vertica Blog

Quick Tips

Modern Database Analytics

Vertica Quick Tip: Keeping Track of Data Load File Sources

Soniya Shah, Information Developer
March 6, 2018

This blog post was authored by Jim Knicely. If you are like most companies you will be loading data into a Vertica table from many different data files. And if you are like me, you’ll forget later which file a table record arrived in. Fortunately the CURRENT_LOAD_SOURCE function returns the file name used when executing...

Vertica Quick Tip: What’s the first day of the month?

Soniya Shah, Information Developer
March 1, 2018

This blog post was authored by Jim Knicely. In the last Vertica Quick Tip we learned about the Vertica built-in LAST_DAY function. So where’s the FIRST_DAY function? You’ll have to create your own. So how do you compute the first day of the month for a given date? There are many ways, each involving a...
Database Server Room

Vertica Quick Tip: What’s the last day of the month?

Soniya Shah, Information Developer
February 28, 2018

This blog post was authored by Jim Knicely. The Vertica built-in LAST_DAY function returns the last day of the month for a specified date. This function comes in handy for leap years. In one of my previous positions, an accountant asked how to compute the last day of the previous month. That’s not a problem...

Vertica Quick Tip: Checking User Role Membership

Soniya Shah, Information Developer
February 27, 2018

This blog post was authored by Jim Knicely. The HAS_ROLE function returns a Boolean value that indicates whether a role has been assigned to a user. To create a read only user and role, do the following: Later, I can verify that I made that grant by querying the V_CATALOG.GRANTS system table: But it’s a...

Vertica Quick Tip: Increasing the Performance of a Rebalance

Soniya Shah, Information Developer
February 26, 2018

This blog post was authored by Jim Knicely. Before performing a rebalance, Vertica by default will query system tables to compute the size of all projections involved in the rebalance task. This query can add significant overhead to the rebalance operation! To disable this query, set the configuration parameter RebalanceQueryStorageContainers to 0. Have Fun!

Vertica Quick Tip: Expiring a User’s Password

Soniya Shah, Information Developer
February 22, 2018

This blog post was authored by Jim Knicely. You can expire a user's password immediately using the ALTER USER statement's PASSWORD EXPIRE parameter. By expiring a password, you can: • Force users to comply with a change to password policy. • Set a new password when a user forgets the old password. This feature also...

Vertica Quick Tip: VSQL Shortcuts to Move Faster on the Command Line

Soniya Shah, Information Developer
February 22, 2018

This blog post was authored by Jim Knicely. vsql is Vertica’s character-based, interactive, front-end utility that lets you type SQL statements and see the results. If you’ve typed a particularly long query in vsql then realize that you’d have a typo way back at the beginning of your code (i.e. you wrote SEELECT), instead of...

Vertica Quick Tip: Renaming a View

Soniya Shah, Information Developer
February 20, 2018

This blog post was authored by Jim Knicely. You are probably aware that you can rename a table using the ALTER TABLE … RENAME command. But you might not know that you can just as easily rename a database view using a very similar command! Have fun!

Vertica Quick Tip: Viewing Query Error Information

Soniya Shah, Information Developer
February 15, 2018

This blog post was authored by Jim Knicely. The V_MONITOR.ERROR_MESSAGES system table tracks error and warning messages encountered while processing queries. If you need a bit more info, like the cursor position of a syntax error, you can query the data collector table DC_ERRORS. Have fun!
Modern Database Analytics

Vertica Quick Tip: Setting a Client Connection Label

Soniya Shah, Information Developer
February 14, 2018

This blog post was authored by Jim Knicely. When you connect to a Vertica database you can set a client connection label to help you later identify the connection. Have fun!

Vertica Quick Tip: Analyzing Table Statistics by Column

Soniya Shah, Information Developer
February 13, 2018

This blog post was authored by Jim Knicely. The ANALYZE_STATISTICS function collects and aggregates data samples and storage information from all nodes that store projections associated with the specified table. On a very large wide table it will take a significant amount of time to gather those statistics. In many situations only a few columns...

Vertica Tip: The System Table for System Tables

Soniya Shah, Information Developer
February 12, 2018

This blog post was authored by Sarah Lemaire. Most of you probably know that Vertica provides system tables that allow you to monitor • System resources • Background processes • Workload • Performance • Catalog size These tables help you to profile, diagnose, and view historical data equivalent to load streams, query profiles, Tuple Mover...