how to replace characters in hive?

Question

I have a string column description in a hive table which may contain tab characters '\t', these characters are however messing some views when connecting hive to an external application. is there a simple way to get rid of all tab characters in that column?. I could run a simple python program to do it, but I want to find a better solution for this.

Answer 1

Custom SerDe might be a way to do it. Or you could use some kind of mediation process with regex_replace:

create table tableB as 
select 
    columnA
    regexp_replace(description, '\\t', '') as description
from tableA
;

Answer 2

regexp_replace UDF performs my task. Below is the definition and usage from apache Wiki.

regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT):

This returns the string resulting from replacing all substrings in INITIAL_STRING that match the java regular expression syntax defined in PATTERN with instances of REPLACEMENT,

e.g.: regexp_replace("foobar", "oo|ar", "") returns fb

Answer 3

select translate(description,'\\t','') from myTable;

Translates the input string by replacing the characters present in the from string with the corresponding characters in the to string. This is similar to the translate function in PostgreSQL. If any of the parameters to this UDF are NULL, the result is NULL as well. (Available as of Hive 0.10.0, for string types)

Char/varchar support added as of Hive 0.14.0

Answer 4

There is no OOTB feature at this moment which allows this. One way to achieve that could be to write a custom InputFormat and/or SerDe that will do this for you. You might this JIRA useful : https://issues.apache.org/jira/browse/HIVE-3751. (not related directly to your problem though).

Answer 5

You can also use translate(). If the third argument is too short, the corresponding characters from the second argument are deleted. Unlike regexp_replace() you don't need to worry about special characters. Source code.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-StringFunctions