How to use WHEN CASE queires in AWS Athena
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 expressionthat produces the value that will be matched in the expression
WHEN value THEN resultthat checks whether the
valueand returns the
resultif both are the same
ELSE default_resultthat 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
nullas the default value.
- ‘END’ that finishes the
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!
You may also like
- 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