Development

How to convert a string to a table in Vertica

1

Sometimes when working with BI tools you will need to pass a table of parameters to a Vertica query. One way to do this is to serialize the table into a text string, pass the text string as a parameter to Vertica, and then convert the text string back to a table inside of Vertica. This is accomplished in the TxtIndex.StringTokenizerDelim function that comes with Vertica 7.1 text indexing.

Here is the table that we want to create inside of Vertica from a text string parameter

KeyValue
1Bugs Bunny
2Daffy Duck
3Elmer Fudd

This can be converted to a string, using | to denote row endings and ^^ to denote columns within each row.

1^^Bugs Bunny|2^^Donald Duck|3^^Elmer Fudd

The string can be sent to Vertica, and then converted into a table using SQL.

The secret sauce is the TxtIndex.StringTokenizerDelim(,) function, which is a transform function that accepts two parameters. The first parameter is a string, and the second parameter is the delimiter to use to split the string into separate rows. It returns a table with two columns, words and input.

About the author / 

Doug Harmon

Doug is a Technical Consultant and author's his own blog at datadug.com. He has written the Vertical SQL Toolbelt that contains shell scripts and other helpful diagnostic queries.

1 Comment

  1. Filip Nowicki November 3, 2014 at 5:09 PM -  Reply

    Nice example 🙂

    Functions String Tokenizer and StringTokenizerDelim were introduced in String Package on Vertica Marketplace. Interesting is that functions in TxtIndex library are the same as were in String Package. Even the DDL is almost the same but some interesting functions are commented. One of these functions is GroupConcat that allows us revert Doug’s example:

    This query returns us 1^^Bugs Bunny| 2^^Donald Duck| 3^^Elmer Fudd which is the input for Doug’s example.

    Please note that String Package is only for versions 6 and 7. For versions 7.1.x package needs to be rebuild under new SDK.

Leave a Reply

Upcoming Events

  • No upcoming events
AEC v1.0.4

Subscribe to Blog via Email

Enter your email address to subscribe and receive notifications of new posts by email.

Read more use cases here.

Notice

This site is not affiliated, endorsed or associated with HPE Vertica. This site makes no claims on ownership of trademark rights. The author contributions on this site are licensed under CC BY-SA 3.0 with attribution required.
%d bloggers like this: