Vertica Blog

Vertica Blog

SQL

Database Server Room

Assign All Users’ Granted Roles as Default Roles

James Knicely, Vertica Field Chief Technologist
February 26, 2019

By default, no roles (other than the default PUBLIC Role) are enabled at the start of a user session. You have to assign one or more of a user’s granted roles as default roles so that they are enabled when a user logs on. You do this with the ALTER USER … DEFAULT ROLE command....

Tracking Table Row Counts Over Time: Quick Tip

James Knicely, Vertica Field Chief Technologist
February 15, 2019

I would like to track the row counts from all of my database tables over time. Doing so will help me predict future growth. How do I do that? Like this… Are the row counts accurate? Yup! Now I can add my table row count job to CRONTAB so that it runs once a day...

Auditing Database DDL Operations: Quick Tip

James Knicely, Vertica Field Chief Technologist
February 14, 2019

Starting with Vertica 9.2, you can now track all DDL operations (i.e. CREATE, ALTER, TRUNCATE, etc.) that are being executed in your database! The LOG_QUERIES system table provides summary information about those operations. Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/Security/DatabaseAuditing.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/LOG_QUERIES.htm Have fun!
Three 3D arrows, different colors pointing in different directions

Another Way to De-Duplicate Table Rows: Quick Tip

James Knicely, Vertica Field Chief Technologist
February 8, 2019

To remove duplicate table rows it is typically better (i.e. faster) to create a temporary table that contains all of the distinct rows from a table, drop the original table, then rename the temp table to the original table’s name. The issue with that solution is that you’ll need to be sure that the original...

Combining Functions to Extract Discrete Data From a String: Quick Tip

James Knicely, Vertica Field Chief Technologist
February 7, 2019

Vertica has 1000s of built-in functions including String Functions, which perform conversion, extraction, or manipulation operations on strings or return information about strings. Regular Expression Functions let you perform pattern matching on strings of characters. There is real power in combing Vertica functions to accomplish almost any task in SQL. I want to extract the...
Programmer

Calculate a Year’s Chinese Zodiac Animal: Quick Tip

James Knicely, Vertica Field Chief Technologist
February 5, 2019

The Chinese Zodiac (Sheng Xiao) is based on a twelve-year cycle where each year is represented by an animal sign. The twelve animal signs in order are the rat, ox, tiger, rabbit, dragon, snake, horse, sheep, monkey, rooster, dog and pig. The animal sign is calculated according to the Chinese lunar calendar. I can never...

Finding All Columns Storing Some Value: Quick Tip

James Knicely, Vertica Field Chief Technologist
February 4, 2019

A long time ago a database developer asked me how she could produce a list of all table columns in the database which store a given value. Note that she was in the process of normalizing tables and wanted to find every fact table that referenced a particular string value to replace it with a...
Database Server Room

Generate a Random Integer Within a Range: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 31, 2019

The RANDOMINT functions returns one of n integers from 0 through n – 1. If you need to generate a random integer that falls within a range of integers between X and Y, inclusive, use this simple formula: Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/RANDOM.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/RANDOMINT.htm Have fun!

Seeding a Random Integer: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 30, 2019

The RANDOM function returns a uniformly-distributed random number x, where 0

Seeding a Random Number: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 29, 2019

The RANDOM function returns a uniformly-distributed random number x, where 0

Limiting the Amount of Memory Available to a Query: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 28, 2019

Every Vertica DBA has seen a user execute some crazy, memory intensive query that starves every other user request forcing them to queue. To avoid that situation, you can use the MAXQUERYMEMORYSIZE resource pool parameter, which sets the maximum amount of memory that the pool can allocate at runtime to process a query. User JIM...

Display a Rounded Number With a Specific Scale: Quick Tip

James Knicely, Vertica Field Chief Technologist
January 25, 2019

The Vertica ROUND function rounds a value to a specified number of decimal places, retaining the original precision and scale. Fractions greater than or equal to .5 are rounded up. Fractions less than .5 are rounded down (truncated). If you’d prefer not to retain the original scale and have the scale of the result be...