Vertica Blog

Vertica Blog

Vertica Blog

Understanding the APPROXIMATE_COUNT_DISTINCT Functions

Soniya Shah, Information Developer
February 28, 2018

This blog post was authored by Curtis Bennett. The exact computation of the number of distinct values of an expression X on a multi-node architecture requires bringing all distinct values of X (within the specified group if a GROUP BY was specified) to the same node, and then counting the number of distinct values on...
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...

Aggregate Projections

Soniya Shah, Information Developer
February 27, 2018

This blog post was authored by Curtis Bennett. Vertica stores physical data for tables in objects known as projections. Unlike traditional RDBMS's, Vertica does not rely on indexes for performance. Instead, Vertica stores the physical data (either all or some of the columns) in whatever sort order is required for optimal query processing. This can...

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

Improve the Efficiency of Mergeout on Wide Tables

Soniya Shah, Information Developer
February 20, 2018

This blog post was co-authored by Xiao Ling and Jim Kelley. Introduction When resource pools were first introduced to Vertica, the average computer had a lot less memory than it does today. The default memory size for the Tuple Mover resource pool, 200 MB, reflects the more limited resources of that period. As hardware and...

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

North East Database Day Conference

Vertica Technical Team
February 15, 2018

This blog post was authored by Eden Zik. On January 19th, Vertica engineers joined the North East database community for the North East Database Day conference organized annually at MIT, sponsored by Facebook and Microsoft and featuring Turing award winner Michael Stonebraker. The full conference program can be found here: http://mitdbg.github.io/nedbday/2018/ This year Styliani Pantela...

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!