Vertica Blog

Vertica Blog

Quick Tips

Three 3D arrows, different colors pointing in different directions

Vertica Quick Tip: Case Insensitive Session Queries

Soniya Shah, Information Developer
March 27, 2018

This blog post was authored by Jim Knicely. By default Vertica is case sensitive when it comes to comparing strings. You can change this behavior by setting the session locale to LEN_S1. Note that you will see a pretty hefty performance hit when using the LEN_S1 locale. A better option would be to store your...

Vertica Quick Tip: Automatically Close an Idle Session

Soniya Shah, Information Developer
March 26, 2018

This blog post was authored by Jim Knicely. The IDLESESSIONTIMEOUT parameter can be used to close a session that has been idle for a period of time. An idle session is one that has no queries running. dbadmin=> \c - jim You are now connected as user "jim". dbadmin=> SELECT current_session(); current_session ------------------------------- v_test2_node0001-3667:0x2a19e (1...

Vertica Quick Tip: EXPLAIN Plan in JSON Format

Soniya Shah, Information Developer
March 22, 2018

This blog post was authored by Jim Knicely. The EXPLAIN command returns the optimizer's query plan for executing a specified query. ------------------------------ QUERY PLAN DESCRIPTION: ------------------------------ EXPLAIN SELECT the_date FROM big_date_table WHERE the_date_as_date BETWEEN '09-19-2017' AND '09-19-2018'; Access Path: +-STORAGE ACCESS for big_date_table [Cost: 5M, Rows: 25M] (PATH ID: 1) | Projection: public.big_date_table_super | Materialize:...

Vertica Quick Tip: On the Fly Date Dimension

Soniya Shah, Information Developer
March 21, 2018

This blog post was authored by Jim Knicely. The Vertica TIMESERIES Clause is an important component of time series analytics computation. It performs gap filling and interpolation (GFI) to generate time slices missing from the input records. I like to use it to create a dynamic date dimension for my data warehouse. Have Fun!

Vertica Quick Tip: Enable \timing from the vsql Command Line

Soniya Shah, Information Developer
March 20, 2018

This blog post was authored by Jim Knicely. The vsql \timing meta-command reports, in milliseconds, the length of time each SQL statement runs. dbadmin=> SELECT COUNT(*) FROM big_varchar_table WHERE pk BETWEEN 1000000 AND 2000000; COUNT --------- 1000001 (1 row) Time: First fetch (1 row): 179.609 ms. All rows formatted: 179.691 ms You can also enable...
Database Server Room

Vertica Quick Tip: My SQL History

Soniya Shah, Information Developer
March 19, 2018

This blog post was authored by Jim Knicely. In vsql you can use the \s meta-command to view your command line history: If using some other Vertica SQL client, i.e. DbVisualizer, you can also get a history of your current session’s SQL commands like this: Have Fun!

Vertica Quick Tip: Getting a Word Count

Sarah Lemaire, Manager, Vertica Documentation
March 15, 2018

This blog post was authored by Jim Knicely. The Vertica REGEXP_COUNT function returns the number times a regular expression matches a string. You can use it to create your own user-defined SQL function that counts the number of words in a string. dbadmin=> SELECT get_word_count('The Vertica Analytics Platform is purpose built from the very first...
quick sql tips for rows

Vertica Quick Tip: Parsing a String as Rows

Sarah Lemaire, Manager, Vertica Documentation
March 14, 2018

This blog post was authored by Jim Knicely. A simple SQL trick makes it easy to expand an entire string into separate rows. i s A w e s o m e ! (19 rows) Now that you’ve expanded the string into rows, you can perform various actions if you encounter certain conditions… i s...

Vertica Quick Tip: Repeating

Soniya Shah, Information Developer
March 13, 2018

This blog post was authored by Jim Knicely. The Vertica REPEAT function replicates a string the specified number of times, and concatenates the replicated values as a single string. Examples: dbadmin=> SELECT '1' || repeat('0', 100) AS "One Googolplex"; One Googolplex ------------------------------------------------------------------------------------------------------- 10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 (1 row) This function is also very useful if you plan on...

Vertica Quick Tip: Splitting a String into Rows

Soniya Shah, Information Developer
March 12, 2018

This blog post was authored by Jim Knicely. The Vertica Text Search feature includes a handy function named StringTokenizerDelim that you can use to split a string into rows by a given delimiter. Have Fun!
Three 3D arrows, different colors pointing in different directions

Vertica Quick Tip: Getting Every n-th Row from a Table

Soniya Shah, Information Developer
March 8, 2018

This blog post was authored by Jim Knicely. You can use the Vertica MOD function to grab every nth row from a table. Say I want every 2nd row from the table above. I can run the following query to get them: Or for every 3nd row, I’d run this query: Have Fun!

Vertica Quick Tip: What’s the Distance Between Two Points

Soniya Shah, Information Developer
March 7, 2018

This blog post was authored by Jim Knicely. Planning a road trip to Disney? How far is it? Vertica has a built-in function called DISTANCE, which returns the distance (in kilometers) between two points. You specify the latitude and longitude of both the starting point and the ending point. You can also specify the radius...