Calculate a Year’s Chinese Zodiac Animal: Quick Tip

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

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

Viewing External Table Definitions: Quick Tip

An alternative to importing data into Vertica is to query it in place. For that we use external tables. Once an external table is created you can view its definition via the TABLES system table. Helpful Links: Have fun!

Generate a Random Integer Within a Range: Quick Tip

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: Have fun!

Seeding a Random Integer: Quick Tip

The RANDOM function returns a uniformly-distributed random number x, where 0 <= x < 1 and the RANDOMINT functions returns one of the n integers from 0 through n – 1. In we learned that we can use the SEEDED_RANDOM function to re-use generated numbers. There is not a SEEDED_RANDOMINT function, but that’s not a...

Seeding a Random Number: Quick Tip

The RANDOM function returns a uniformly-distributed random number x, where 0 <= x < 1. Notice above that each time I called the RANDOM function I got a new random value. But what if I need to generate a random number and use it in multiple places in my query? For this case, you can...

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

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

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

View Vertica Features Recently Used: Quick Tip

Curious if Vertica features are being used in your database? If so, the data collector table DC_FEATURES_USED stores recently used Vertica features (i.e. commands, meta-functions and UDXs). Helpful Links: Have fun!

Monitoring COPY Statement Events (Part 2): Quick Tip

A COPY command is typically composed of two phases. The second phase, if using the DIRECT parameter, includes sorting the data and writing the data in Disk. You can monitor the progress of this phase of the COPY command via the data collector table DC_MERGE_EVENTS. Helpful Links: Have fun!

Monitoring COPY Statement Events (Part 1): Quick Tip

A COPY command is typically composed of two phases. The first phase includes reading the data from the source, parsing the data, segmenting the data and sending it to the nodes after it is segmented. You can monitor the load events of this phase of the COPY command via the data collector table DC_LOAD_EVENTS. Helpful...

Nested Boolean Operators: Quick Tip

Vertica supports the following Boolean operators: • AND • OR • NOT To nest Boolean operators, use parenthesis! Helpful Link: Have fun!