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:

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

SELECT client_id, CASE client_type
WHEN 'private' THEN last_name
WHEN 'company' THEN company_name
END from some_clients
Older post

How to decode base64 to text in AWS Athena

How to use from_base64 in AWS Athena

Newer post

How to check whether a YARN application has finished

How to use Airflow PythonSensor to check whether a YARN application finished running