How to use WHEN CASE queires in AWS Athena

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

In AWS Athena, we can use the WHEN CASE expressions to build “switch” conditions that convert matching values into another value. Such a WHEN CASE expression consists of four parts:

  • CASE expression that produces the value that will be matched in the expression
  • WHEN value THEN result that checks whether the expression matches the value and returns the result if both are the same
  • ELSE default_result that returns the default value in the case of no matching found using the WHEN expressions. Note that if there is no ELSE default_result, the expression returns null as the default value.
  • ‘END’ that finishes the CASE WHEN block

We can use multiple WHEN expressions to build long “switch” statements, like this:

1
2
3
4
5
6
7
SELECT CASE column_to_be_matched
WHEN 'value_A' THEN 'the_replacement_of_value_A'
WHEN 'value_B' THEN 'the_replacement_of_value_B'
WHEN 'value_C' THEN 'the_replacement_of_value_C'
ELSE 'default'
END
FROM some_table

We can also use the CASE WHEN expressions to retrieve values from multiple columns and choose the correct one using the CASE WHEN statement:

1
2
3
4
SELECT client_id, CASE client_type
WHEN 'private' THEN last_name
WHEN 'company' THEN company_name
END from some_clients

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.