CODEWITHSUNDEEP
sqlbase64decodeamazon-redshift
Paba
Paba

Reputation: 1115

Base64 decode in redshift

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 ?

Upvotes: 5

Views: 6918

Answers (3)

Dmytro Bogatov
Dmytro Bogatov

Reputation: 796

Redshift now has VARBYTE type and functions. So you can now convert base64 string to VARBYTE with TO_VARBYTE(str, 'base64'). Then you can do stuff with this VARBYTE, like in my case I needed to see bit 3 of the first byte

SELECT GETBIT(SUBSTRING(TO_VARBYTE(str, 'base64'), 1, 1), 3) FROM ...

Upvotes: 1

Paba
Paba

Reputation: 1115

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';

Upvotes: 3

dinesh707
dinesh707

Reputation: 12592

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

Upvotes: 2

Related Questions