TRANSLATE

Translates the given value using a given dictionary.

Syntax

TRANSLATE(dictionary, keepValuesWithoutTranslation, emptyAsNull, input)

Arguments

Name	Type	Description	Default Value
dictionary	string
keepValuesWithoutTranslation	boolean	Whether to keep values that have that are not mapped to a value in the feature
emptyAsNull	boolean	If set, empty values will be treated as null
input	string/numeric

Returns

Returns a string value.

---

Examples

dictionary	keepValuesWithoutTranslation	emptyAsNull	input	Output
'a,Antman
b,Batman
d,'	false	false	'a'	Antman
'a,Antman
b,Batman
d,'	false	false	'b'	Batman
'a,Antman
b,Batman
d,'	false	false	'c'	null
'a,Antman
b,Batman
d,'	true	false	'c'	c
'a,Antman
b,Batman
d,'	true	true	'd'	null
'a,Antman
b,Batman
d,'	true	false	'd'	``
'1234.0,good'	false	false	1234	good
'1234.0,good'	false	false	1234	good
'0.0,good'	false	false	0	good
'-0.0,good'	false	false	0	good
'-0.0,good'	false	false	0	good
'1.23456e17,good'	false	false	123456000000000000	good
'6.000000000000001,good'	false	false	6	null

Transformation job example

SQL

CREATE JOB function_operator_example
    ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data 
  MAP_COLUMNS_BY_NAME
    SELECT dictionary, keepValuesWithoutTranslation, emptyAsNull, input,
        TRANSLATE('a,Antman
b,Batman
d,', false, false, input) AS Output
    FROM default_glue_catalog.upsolver_samples.orders_raw_data
    LET dictionary = 'a,Antman
b,Batman
d,',
        keepValuesWithoutTranslation = false,
        emptyAsNull = false,
        input = 'a'
    WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
    LIMIT 1;

Query result

dictionary	keepValuesWithoutTranslation	emptyAsNull	input	Output
'a,Antman
b,Batman
d,'	false	false	'a'	Antman