Sample ingestion snippet - transformSpec 'if, then, else' and 'like'

Introduction

This article gives an example for transformSpec for ingestion, with  if-then-else  expression.

For the example, let us consider a datasource with a dimension called countryIsoCode. The following are the transformations to be completed:

1. If countryIsoCode begins with U, transform it to XY
2. Else If countryIsoCode begins with K, transform it to YX
3. Else create a new field countryIsoCodeNew and initialize it with the value of countryIsoCode

Sample Ingestion Spec

The following snippet of the ingestion spec would do the transformation described above.

"transformSpec": {
 "transforms": [{
 "type": "expression",
 "name": "countryIsoCodeNew",
 "expression": "if(like(\"countryIsoCode\", 'U%'), 'XY', if(like(\"countryIsoCode\", 'K%'), 'YX', \"countryIsoCode\" ))"
 }]
 }

To verify transformation, post ingestion, use a tool like dsql to check by running a query similar to the following:

select distinct countryIsoCode, countryIsoCodeNew from wikipedia 
where countryIsoCode like 'U%' or countryIsoCode like 'K%' ;

countryIsoCode           countryIsoCodeNew

KE                                  YX
KR                                  YX
KW                                  YX
KZ                                  YX

UA                                  XY
US                                  XY
UY                                  XY
UZ                                  XY

Country codes starting with letters except "U" and "K" do not get modified:

select distinct countryIsoCode, countryIsoCodeNew from wikipedia 
where countryIsoCode like 'V%' or countryIsoCode like 'Z%' ;

countryIsoCode           countryIsoCodeNew

VE                                 VE

VN                                 VN

ZA                                 ZA

References:

Druid Documentation: transformSpec for Ingestion

Druid Tutorial: Transforming Input Data

Druid Documentation: Druid Expressions