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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[hadoop] how to replace characters in hive?

Examples related to hadoop

Examples related to hive