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

SergeB

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/vb64.py' 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');
vb64encode
--------------
VmVydGljYQ==
(1 row)
dbuser=> select vb64decode('VmVydGljYQ==');

vb64decode

Vertica
(1 row)