How to decode base64 to text in AWS Athena

This article is a part of my "100 data engineering tutorials in 100 days" challenge. (75/100)

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:

1
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.

Did you enjoy reading this article?
Would you like to learn more about software craft in data engineering and MLOps?

Subscribe to the newsletter or add this blog to your RSS reader (does anyone still use them?) to get a notification when I publish a new essay!

Newsletter

Do you enjoy reading my articles?
Subscribe to the newsletter if you don't want to miss the new content, business offers, and free training materials.

Bartosz Mikulski

Bartosz Mikulski

  • Data/MLOps engineer by day
  • DevRel/copywriter by night
  • Python and data engineering trainer
  • Conference speaker
  • Contributed a chapter to the book "97 Things Every Data Engineer Should Know"
  • Twitter: @mikulskibartosz
Newsletter

Do you enjoy reading my articles?
Subscribe to the newsletter if you don't want to miss the new content, business offers, and free training materials.