The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Base64 decode/encode functions in Vertica (via Python UDX-SDK)

SergeBSergeB Employee
edited January 2020 in Tips from the Team

A customer recently asked us how to perform base 64 encoding/decoding in a Vertica SQL statement.
While Vertica provides an impressive array of string functions, it doesn't provide Base64 string decode/encode functions.
A quick way to implement those functions is by using Vertica's Python UDx SDK. Base64 is a basic Python module that ships with Vertica and it's very simple to adapt one of the Python UDx SDK examples shipping with Vertica (/opt/vertica/sdk/examples/python ).
Attached is a Python script that implements base64 decode/encode functions.
To load the library:

CREATE LIBRARY pylib as '/path_to/' LANGUAGE 'Python';

To register the base64 decode and encode USER functions:

CREATE FUNCTION vb64encode AS LANGUAGE 'Python' NAME 'vb64encode_factory' LIBRARY pylib fenced;
CREATE FUNCTION vb64decode AS LANGUAGE 'Python' NAME 'vb64decode_factory' LIBRARY pylib fenced;

To use these two functions:

dbuser=> select vb64encode('Vertica');
(1 row)
dbuser=> select vb64decode('VmVydGljYQ==');


(1 row)

Sign In or Register to comment.