Base64 decode in redshift

Question

Is there a redshift/sql function to decode a base64 string ? If not please suggest me how to write a function in redshift to decode base64 ?

It is quite straightforward, I have a db where some data is stored in base64 encoded format and I need to show the results in decoded text format. — Paba, Jan 24 '17 at 08:49

score 3 · Answer 1 · edited Feb 01 '17 at 19:48

Thanks for the great suggestion. It worked. Here is my function. Only issue is that to create a function you have to be a superuser.

create function f_base64decode (a varchar)
  returns varchar
stable
as $$
  import base64
  return base64.b64decode(a)   
$$ language plpythonu;

We could check whether the programming language is trusted or not by querying the pg_language table. If it's not trusted, the lanpltrusted is FALSE

SELECT lanpltrusted 
FROM pg_language 
WHERE lanname LIKE 'plpythonu';

score 2 · Accepted Answer · edited May 23 '17 at 11:54

2

See this http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_FUNCTION.html, You can create a function in Redshift, and you can use python to code it.

Here are some possible ways to do it in python Python base64 data decode

edited May 23 '17 at 11:54

Community

answered Jan 24 '17 at 12:15

dinesh707

Thanks. But you have to be a super user to create functions in redshift. Any other suggestions ? – Paba Jan 24 '17 at 13:17

2 Answers2