AWS

How to use WHEN CASE queires in AWS Athena

Using conditions in AWS Athena queries

Bartosz Mikulski 08 Dec 2020 – 1 min read

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

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

MLOps AWS

How to deploy a Transformer-based model with custom preprocessing code to Sagemaker Endpoints using BentoML

Deploy a machine learning model with custom inference code to a Sagemaker Endpoint using BentoML
Published on: 01 Oct 2021

AWS MLOps

Multimodel deployment in Sagemaker Endpoints

How to deploy multiple models in a single Sagemaker Endpoint?
Published on: 28 May 2021

AWS

How to deploy a REST API AWS Lambda using Chalice and AWS Code Pipeline

How to create a REST API Endpoint using AWS Lambda, Chalice, and AWS Code Pipeline
Published on: 16 Apr 2021