Decoding base64 in AWS Athena requires two steps. First, we have to use the from_base64 function to get a binary representation of the decoded content. We don’t get text automatically because we can use base64 to encode as a string any binary data, for example, a picture.

Therefore, Athena does not know that the content is a string. Because of that, we have to use the from_utf8 function to convert the binary data into text.

The complete SQL looks like this:

select from_utf8( from_base64(base64_encoded_column) ) FROM some_table

One more thing. Never, ever say that base64 is encryption. It is not. That is just a method of representing binary data as a string, so you can send it to/from a REST API as a JSON object or include it in an URL.

Older post

How to combine two DataFrames with no common columns in Apache Spark

Use full outer join to combine two Apache Spark DataFrames with no common columns

Newer post

How to use WHEN CASE queires in AWS Athena

Using conditions in AWS Athena queries