CODEWITHSUNDEEP
hadoophive
user1745713
user1745713

Reputation: 791

how to replace characters in hive?

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.

Upvotes: 34

Views: 214074

Answers (5)

Guest
Guest

Reputation: 91

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

Upvotes: 9

user2637464
user2637464

Reputation: 2356

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

Upvotes: 65

iggy
iggy

Reputation: 722

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

Upvotes: 4

ferics2
ferics2

Reputation: 5432

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
;

Upvotes: 12

Tariq
Tariq

Reputation: 34184

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).

Upvotes: 0

Related Questions