PREHOOK: query: DESCRIBE FUNCTION translate PREHOOK: type: DESCFUNCTION POSTHOOK: query: DESCRIBE FUNCTION translate POSTHOOK: type: DESCFUNCTION translate(input, from, to) - translates the input string by replacing the characters present in the from string with the corresponding characters in the to string PREHOOK: query: DESCRIBE FUNCTION EXTENDED translate PREHOOK: type: DESCFUNCTION POSTHOOK: query: DESCRIBE FUNCTION EXTENDED translate POSTHOOK: type: DESCFUNCTION translate(input, from, to) - translates the input string by replacing the characters present in the from string with the corresponding characters in the to string translate(string input, string from, string to) is an equivalent function to translate in PostGreSQL. It works on a character by character basis on the input string (first parameter). A character in the input is checked for presence in the from string (second parameter). If a match happens, the character from to string (third parameter) which appears at the same index as the character in from string is obtained. This character is emitted in the output string instead of the original character from the input string. If the to string is shorter than the from string, there may not be a character present at the same index in the to string. In such a case, nothing is emitted for the original character and it's deleted from the output string. For example, translate('abcdef', 'adc', '19') returns '1b9ef' replacing 'a' with '1', 'd' with '9' and removing 'c' from the input string translate('a b c d', ' ', '') return 'abcd' removing all spaces from the input string If the same character is present multiple times in the input string, the first occurence of the character is the one that's considered for matching. However, it is not recommended to have the same character more than once in the from string since it's not required and adds to confusion. For example, translate('abcdef', 'ada', '192') returns '1bc9ef' replaces 'a' with '1' and 'd' with '9' ignoring the second occurence of 'a' in the from string mapping it to '2' PREHOOK: query: -- Create some tables to serve some input data CREATE TABLE table_input(input STRING) PREHOOK: type: CREATETABLE POSTHOOK: query: -- Create some tables to serve some input data CREATE TABLE table_input(input STRING) POSTHOOK: type: CREATETABLE POSTHOOK: Output: default@table_input PREHOOK: query: CREATE TABLE table_translate(input_string STRING, from_string STRING, to_string STRING) PREHOOK: type: CREATETABLE POSTHOOK: query: CREATE TABLE table_translate(input_string STRING, from_string STRING, to_string STRING) POSTHOOK: type: CREATETABLE POSTHOOK: Output: default@table_translate PREHOOK: query: FROM src INSERT OVERWRITE TABLE table_input SELECT 'abcd' WHERE src.key = 86 PREHOOK: type: QUERY PREHOOK: Input: default@src PREHOOK: Output: default@table_input POSTHOOK: query: FROM src INSERT OVERWRITE TABLE table_input SELECT 'abcd' WHERE src.key = 86 POSTHOOK: type: QUERY POSTHOOK: Input: default@src POSTHOOK: Output: default@table_input POSTHOOK: Lineage: table_input.input SIMPLE [] PREHOOK: query: FROM src INSERT OVERWRITE TABLE table_translate SELECT 'abcd', 'ahd', '12' WHERE src.key = 86 PREHOOK: type: QUERY PREHOOK: Input: default@src PREHOOK: Output: default@table_translate POSTHOOK: query: FROM src INSERT OVERWRITE TABLE table_translate SELECT 'abcd', 'ahd', '12' WHERE src.key = 86 POSTHOOK: type: QUERY POSTHOOK: Input: default@src POSTHOOK: Output: default@table_translate POSTHOOK: Lineage: table_input.input SIMPLE [] POSTHOOK: Lineage: table_translate.from_string SIMPLE [] POSTHOOK: Lineage: table_translate.input_string SIMPLE [] POSTHOOK: Lineage: table_translate.to_string SIMPLE [] PREHOOK: query: -- Run some queries on constant input parameters SELECT translate('abcd', 'ab', '12'), translate('abcd', 'abc', '12') FROM src LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src #### A masked pattern was here #### POSTHOOK: query: -- Run some queries on constant input parameters SELECT translate('abcd', 'ab', '12'), translate('abcd', 'abc', '12') FROM src LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src #### A masked pattern was here #### POSTHOOK: Lineage: table_input.input SIMPLE [] POSTHOOK: Lineage: table_translate.from_string SIMPLE [] POSTHOOK: Lineage: table_translate.input_string SIMPLE [] POSTHOOK: Lineage: table_translate.to_string SIMPLE [] 12cd 12d PREHOOK: query: -- Run some queries where first parameter being a table column while the other two being constants SELECT translate(table_input.input, 'ab', '12'), translate(table_input.input, 'abc', '12') FROM table_input LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@table_input #### A masked pattern was here #### POSTHOOK: query: -- Run some queries where first parameter being a table column while the other two being constants SELECT translate(table_input.input, 'ab', '12'), translate(table_input.input, 'abc', '12') FROM table_input LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@table_input #### A masked pattern was here #### POSTHOOK: Lineage: table_input.input SIMPLE [] POSTHOOK: Lineage: table_translate.from_string SIMPLE [] POSTHOOK: Lineage: table_translate.input_string SIMPLE [] POSTHOOK: Lineage: table_translate.to_string SIMPLE [] 12cd 12d PREHOOK: query: -- Run some queries where all parameters are coming from table columns SELECT translate(input_string, from_string, to_string) FROM table_translate LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@table_translate #### A masked pattern was here #### POSTHOOK: query: -- Run some queries where all parameters are coming from table columns SELECT translate(input_string, from_string, to_string) FROM table_translate LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@table_translate #### A masked pattern was here #### POSTHOOK: Lineage: table_input.input SIMPLE [] POSTHOOK: Lineage: table_translate.from_string SIMPLE [] POSTHOOK: Lineage: table_translate.input_string SIMPLE [] POSTHOOK: Lineage: table_translate.to_string SIMPLE [] 1bc PREHOOK: query: -- Run some queries where some parameters are NULL SELECT translate(NULL, 'ab', '12'), translate('abcd', NULL, '12'), translate('abcd', 'ab', NULL), translate(NULL, NULL, NULL) FROM src LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src #### A masked pattern was here #### POSTHOOK: query: -- Run some queries where some parameters are NULL SELECT translate(NULL, 'ab', '12'), translate('abcd', NULL, '12'), translate('abcd', 'ab', NULL), translate(NULL, NULL, NULL) FROM src LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src #### A masked pattern was here #### POSTHOOK: Lineage: table_input.input SIMPLE [] POSTHOOK: Lineage: table_translate.from_string SIMPLE [] POSTHOOK: Lineage: table_translate.input_string SIMPLE [] POSTHOOK: Lineage: table_translate.to_string SIMPLE [] NULL NULL NULL NULL PREHOOK: query: -- Run some queries where the same character appears several times in the from string (2nd argument) of the UDF SELECT translate('abcd', 'aba', '123'), translate('abcd', 'aba', '12') FROM src LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src #### A masked pattern was here #### POSTHOOK: query: -- Run some queries where the same character appears several times in the from string (2nd argument) of the UDF SELECT translate('abcd', 'aba', '123'), translate('abcd', 'aba', '12') FROM src LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src #### A masked pattern was here #### POSTHOOK: Lineage: table_input.input SIMPLE [] POSTHOOK: Lineage: table_translate.from_string SIMPLE [] POSTHOOK: Lineage: table_translate.input_string SIMPLE [] POSTHOOK: Lineage: table_translate.to_string SIMPLE [] 12cd 12cd PREHOOK: query: -- Run some queries for the ignorant case when the 3rd parameter has more characters than the second one SELECT translate('abcd', 'abc', '1234') FROM src LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src #### A masked pattern was here #### POSTHOOK: query: -- Run some queries for the ignorant case when the 3rd parameter has more characters than the second one SELECT translate('abcd', 'abc', '1234') FROM src LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src #### A masked pattern was here #### POSTHOOK: Lineage: table_input.input SIMPLE [] POSTHOOK: Lineage: table_translate.from_string SIMPLE [] POSTHOOK: Lineage: table_translate.input_string SIMPLE [] POSTHOOK: Lineage: table_translate.to_string SIMPLE [] 123d PREHOOK: query: -- Test proper function over UTF-8 characters SELECT translate('Àbcd', 'À', 'Ã') FROM src LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src #### A masked pattern was here #### POSTHOOK: query: -- Test proper function over UTF-8 characters SELECT translate('Àbcd', 'À', 'Ã') FROM src LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src #### A masked pattern was here #### POSTHOOK: Lineage: table_input.input SIMPLE [] POSTHOOK: Lineage: table_translate.from_string SIMPLE [] POSTHOOK: Lineage: table_translate.input_string SIMPLE [] POSTHOOK: Lineage: table_translate.to_string SIMPLE [] Ãbcd