Hive string manipulation [go]

1. String length function: Length

Syntax: Length (string A)

return value: int

Description: Returns the length of string a

Example:

Hive> Select Length (' ABCEDFG ') from lxw_dual;

7

2. String reversal function: Reverse

Syntax: Reverse (string A)

return value: String

Description: Returns the reversal result of string a

Example:

Hive> Select reverse (ABCEDFG ') from lxw_dual;

Gfdecba

3. String connection function: Concat

Syntax: concat (String A, String B ...)

return value: String

Description: Returns the result of an input string connection, supporting any input string

Example:

Hive> Select Concat (' abc ', ' Def ', ' GH ') from lxw_dual;

Abcdefgh

4. Delimited string connection function: Concat_ws

Syntax: Concat_ws (String SEP, String A, String B ...)

return value: String

Description: Returns the result of the input string concatenated, and Sep represents the delimiter between each string

Example:

Hive> Select Concat_ws (', ', ' abc ', ' Def ', ' GH ') from lxw_dual;

Abc,def,gh

5. String intercept function: substr,substring

Syntax: substr (string A, int start), substring (string a, int start)

return value: String

Description: Returns a string from the start position to the end of string a

Example:

Hive> Select substr (' ABCDE ', 3) from lxw_dual;

Cde

hive> Select substring (' ABCDE ', 3) from lxw_dual;

Cde

Hive> selectsubstr (' ABCDE ', -1) from Lxw_dual; (Same as Oracle)

E

6. String intercept function: substr,substring

Syntax: substr (string A, int start, int len), substring (string A, intstart, int len)

return value: String

Description: Returns string A from start position, len-length string

Example:

Hive> Select substr (' ABCDE ', 3,2) from Lxw_dual;

Cd

hive> Select substring (' ABCDE ', 3,2) from Lxw_dual;

Cd

Hive>select substring (' ABCDE ', -2,2) from Lxw_dual;

De

7. String-To-uppercase function: Upper,ucase

Syntax: Upper (String a) UCase (string a)

return value: String

Description: Returns the uppercase format of string a

Example:

Hive> Select Upper (' absed ') from lxw_dual;

Absed

Hive> Select UCase (' absed ') from lxw_dual;

Absed

8. String to lowercase function: lower,lcase

Syntax: Lower (String a) LCase (string a)

return value: String

Description: Returns the lowercase format of string a

Example:

Hive> Select lower (' absed ') from lxw_dual;

Absed

Hive> Select LCase (' absed ') from lxw_dual;

Absed

9. Go to space function: trim

Syntax: Trim (string A)

return value: String

Description: Remove spaces on both sides of the string

Example:

Hive> Select Trim (' abc ') from Lxw_dual;

Abc

10. Left to go space function: LTrim

Syntax: LTrim (String A)

return value: String

Note: Remove the space to the left of the string

Example:

Hive> Select LTrim (' abc ') from Lxw_dual;

Abc

11. On the right, go to the space function: RTrim

Syntax: RTrim (String A)

return value: String

Description: Remove the space to the right of the string

Example:

Hive> Select RTrim (' abc ') from Lxw_dual;

Abc

12. Regular expression substitution function: Regexp_replace

Syntax: Regexp_replace (String A, String B, String C)

return value: String

Description: Replaces the portion of string a that conforms to Java regular expression B with C. Note that in some cases an escape character is used, similar to the Regexp_replace function in Oracle.

Example:

Hive> Select Regexp_replace (' Foobar ', ' oo|ar ', ') from lxw_dual;

Fb

13. Regular expression parsing function: Regexp_extract

Syntax: Regexp_extract (string subject, string pattern, int index)

return value: String

Description: Returns the character specified by index by subject the string as a rule in the pattern regular expression.

Example:

Hive> Select Regexp_extract (' Foothebar ', ' foo (. *?) (bar) ', 1) fromlxw_dual;

The

Hive> Select Regexp_extract (' Foothebar ', ' foo (. *?) (bar) ', 2) fromlxw_dual;

Bar

Hive> Select Regexp_extract (' Foothebar ', ' foo (. *?) (bar) ', 0) fromlxw_dual;

Foothebar

Note that in some cases the escape character is used, and the following equals sign is escaped with a double vertical line, which is the rule of the Java regular expression.

Select Data_field,

Regexp_extract (Data_field, '. *?bgstart\\= ([^&]+) ', 1) as AAA,

Regexp_extract (Data_field, '. *?contentloaded_headstart\\= ([^&]+) ', 1) as BBB,

Regexp_extract (Data_field, '. *? appload2req\\= ([^&]+) ', 1) as CCC

From Pt_nginx_loginlog_st

where pt = ' 2012-03-26 ' limit 2;

URL parsing function: Parse_url

Syntax: Parse_url (String urlstring, String parttoextract [, Stringkeytoextract])

return value: String

Description: Returns the part specified in the URL. Valid values for Parttoextract are: HOST, PATH, QUERY, REF, PROTOCOL, authority, FILE, and USERINFO.

Example:

Hive> selectparse_url (' http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1 ', ' HOST ') fromlxw_dual;

Facebook.com

Hive> selectparse_url (' http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1 ', ' QUERY ', ' K1 ') from lxw_dual;

V1

JSON parsing function: Get_json_object

Syntax: Get_json_object (string json_string, String path)

return value: String

Description: Parses the JSON string json_string, returning the contents specified by path. If the input JSON string is not valid, then NULL is returned.

Example:

Hive> Select Get_json_object (' {' Store '):

> {"Fruit": \[{"Weight": 8, "type": "Apple"},{"weight": 9, "type": "Pear"}],

> "Bicycle": {"Price": 19.95, "Color": "Red"}

>},

> "Email": "[Email Protected]_for_json_udf_test.net",

> "owner": "Amy"

>}

> ', ' $.owner ') from lxw_dual;

Amy

16. Space String Function: space

Syntax: space (int n)

return value: String

Description: Returns a string of length n

Example:

Hive> Select Space (ten) from lxw_dual;

Hive> Select Length (space) from lxw_dual;

10

17. Repeating string functions: Repeat

Syntax: Repeat (string str, int n)

return value: String

Description: Returns the STR string after repeating n times

Example:

Hive> Select repeat (' abc ', 5) from Lxw_dual;

Abcabcabcabcabc

18. First character ASCII function: ASCII

Syntax: ASCII (string str)

return value: int

Description: Returns the ASCII code of the first character of a string str

Example:

Hive> Select ASCII (' ABCDE ') from lxw_dual;

97

19. Left complement function: Lpad

Syntax: Lpad (string str, int len, string pad)

return value: String

Description: Use the pad to make a left top to Len bit for str

Example:

Hive> Select Lpad (' abc ', ten, ' TD ') from Lxw_dual;

Tdtdtdtabc

Note: Unlike gp,oracle, the pad cannot be defaulted

20. Right complement function: Rpad

Syntax: Rpad (string str, int len, string pad)

return value: String

Description: Use the pad to make the right top up to Len bit for str

Example:

Hive> Select Rpad (' abc ', ten, ' TD ') from Lxw_dual;

Abctdtdtdt

21. Split String function: Split

Syntax: Split (String str, STRINGPAT)

return value: Array

Description: Splitting str by Pat string returns the segmented array of strings

Example:

Hive> Select Split (' Abtcdtef ', ' t ') from lxw_dual;

["AB", "CD", "EF"]

22. Collection Lookup Function: Find_in_set

Syntax: Find_in_set (String str, string strlist)

return value: int

Description: Returns the first occurrence of STR in strlist, where Strlist is a comma-separated string. If the STR character is not found, 0 is returned

Example:

Hive> Select Find_in_set (' ab ', ' ef,ab,de ') from lxw_dual;

2

Hive> Select Find_in_set (' at ', ' Ef,ab,de ') from lxw_dual;

0

Hive string manipulation [go]