Vertica Blog

Vertica Quick Tip: My SQL History

This blog post was authored by Jim Knicely. In vsql you can use the \s meta-command to view your command line history: Example: dbadmin=> CREATE TABLE some_table (c1 INT, c2 VARCHAR(10)); CREATE TABLE dbadmin=> INSERT INTO some_table SELECT 1, ‘TEST1’; OUTPUT ——– 1 (1 row) dbadmin=> ALTER TABLE some_table ADD COLUMN c3 int; ALTER TABLE […]

Introducing the Parallel Streaming Transformation Loader (PSTL) Solution

This blog post was authored by Soniya Shah. At Vertica, we understand how important it is that our customers can make decisions in near real time. Being able to do this not only requires the massive parallel processing that Vertica offers, but the ability to transform and ingest your data into Vertica as quickly as […]

Vertica Quick Tip: Getting a Word Count

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. Example: dbadmin=> CREATE OR REPLACE FUNCTION get_word_count(x varchar) RETURN INT dbadmin-> AS dbadmin-> […]

Vertica Quick Tip: Parsing a String as Rows

This blog post was authored by Jim Knicely. A simple SQL trick makes it easy to expand an entire string into separate rows. Example: dbadmin=> SELECT substr(‘Vertica is Awesome!’, x, 1) forward_string_parse dbadmin-> FROM (SELECT row_number() OVER () x FROM tables) foo dbadmin-> WHERE substr(‘Vertica is Awesome!’, x, 1) ”; forward_string_parse ———————- V e r […]

Vertica Quick Tip: Repeating

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 repeat(‘TesT’, 5); repeat ———————- TesTTesTTesTTesTTesT (1 row) dbadmin=> SELECT ‘1’ || repeat(‘0’, 100) AS “One Googolplex”; One Googolplex ——————————————————————————————————- 10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 (1 row) This […]

Vertica Quick Tip: Splitting a String into Rows

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. Example: dbadmin=> SELECT * FROM test; c1 | c2 —-+——- 1 | A|B|C 2 | D|E|F (2 rows) dbadmin=> SELECT c1, words […]

Monitoring Login Failures

This blog post was authored by Curtis Bennett. Security is an ever-present topic on the minds of database administrators. Certainly Vertica has a number of security features like Kerberos, and SSL. This blog isn’t about those! Instead, let’s take a look at a much more simplistic aspect of security which Vertica tracks – login failures. […]

Blog Post Series: Using Vertica to Track Commercial Aircraft in near Real-Time

Part Five: Apache KafkaThis blog post was authored by Mark Whalley. As discussed in an earlier blog post, we have a continuous stream of messages from aircraft transponders captured and decoded using the DUMP1090 application. We now want to load this data into a series of Vertica tables. As with the streaming nature of the […]

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

This blog post was authored by Jim Knicely. You can use the Vertica MOD function to grab every nth row from a table. Example: dbadmin=> SELECT * FROM nth_row ORDER BY 1; id | some_data —-+———– 1 | TEST1 2 | TEST2 3 | TEST3 4 | TEST4 5 | TEST5 6 | TEST6 7 […]

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

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 […]