HIVE FUNCTIONS
CONCAT
CONCAT function will concatenate the input strings. We can specify any number of strings separated by comma.
Example: CONCAT (‘Hive’,’-‘,’is’,’-‘,’a’,’-‘,’data warehouse’,’-‘,’in Hadoop’);
Output: Hive-is-a-data warehouse-in Hadoop
Output: Hive-is-a-data warehouse-in Hadoop
CONCAT_WS
So, every time we delimit the strings by ‘-‘. If it is common for all the strings, then Hive provides another command CONCAT_WS. Here you have to specify the delimit operator first.
Syntax: CONCAT_WS (‘-‘,’Hive’,’is’,’a’,’data warehouse’,’in Hadoop’);
Output: Hive-is-a-data warehouse-in Hadoop
Output: Hive-is-a-data warehouse-in Hadoop
REPEAT
REPEAT function will repeat the input string n times specified in the command.
Example: REPEAT(‘Hive’,3);
Output: HiveHiveHive.
Output: HiveHiveHive.
REVERSE
REVERSE function will reverse the characters in a string.
Example: REVERSE(‘Hive’);
Output: eviH
Output: eviH
TRIM
TRIM function will remove the spaces associated with a string.
Example: TRIM(‘ Hadoop ‘);
Output: Hadoop.
Output: Hadoop.
If we want to remove only leading or trailing spaces then we can specify the below commands respectively.
LTRIM(‘ Hadoop’);
RTRIM(‘Hadoop ‘);
RTRIM(‘Hadoop ‘);
RLIKE
RLIKE (Right-Like) is a special function in Hive where if any substring of A matches with B then it evaluates to true. It also obeys Java regular expression pattern. Users don’t need to put % symbol for a simple match in RLIKE.
Examples: ‘Express’ RLIKE ‘Exp’ –> True
‘Express’ RLIKE ‘^E.*’ –> True (Regular expression)
‘Express’ RLIKE ‘^E.*’ –> True (Regular expression)
Moreover, RLIKE will come handy when the string has some spaces. Without using TRIM function, RLIKE satisfies the required scenario. Suppose if A has value ‘Express ‘ (2 spaces additionally) and B has value ‘Express’. In these situations, RLIKE will work better without using TRIM.
‘Express ‘ RLIKE ‘Express’ –> True
Note: RLIKE evaluates to NULL if A or B is NULL
The UPPER or UCASE function converts the string into upper case letters.
Example: UPPER('HiVe') returns 'HIVE'
LOWER( string str ), LCASE( string str )
The LOWER or LCASE function converts the string into lower case letters.
Example: LOWER('HiVe') returns 'hive'
ASCII( string str )
The ASCII function converts the first character of the string into its numeric ascii value.
Example1: ASCII('hadoop') returns 104
Example2: ASCII('A') returns 65
FIND_IN_SET( string search_string, string source_string_list )
The FIND_IN_SET function searches for the search string in the source_string_list and returns the position of the first occurrence in the source string list. Here the source string list should be comma delimited one. It returns 0 if the first argument contains comma.
Example: FIND_IN_SET('ha','hao,mn,hc,ha,hef') returns 4
LENGTH( string str )
The LENGTH function returns the number of characters in a string.
Example: LENGTH('hive') returns 4
LPAD( string str, int len, string pad )
The LPAD function returns the string with a length of len characters left-padded with pad.
Example: LPAD('hive',6,'v') returns 'vvhive
RPAD( string str, int len, string pad )
The RPAD function returns the string with a length of len characters right-padded with pad.
Example: RPAD('hive',6,'v') returns 'hivevv'
SPACE( int number_of_spaces )
The SPACE function returns the specified number of spaces.
Example: SPACE(4) returns ' '
SPLIT( string str, string pat )
The SPLIT function splits the string around the pattern pat and returns an array of strings. You can specify regular expressions as patterns.
Example: SPLIT('hive:hadoop',':') returns ["hive","hadoop"]
SUBSTR( string source_str, int start_position [,int length] ), SUBSTRING( string source_str, int start_position [,int length] )
The SUBSTR or SUBSTRING function returns a part of the source string from the start position with the specified length of characters. If the length is not given, then it returns from the start position to the end of the string.
Example1: SUBSTR('hadoop',4) returns 'oop'
Example2: SUBSTR('hadoop',4,2) returns 'oo'
hi, the hive inbuilt function helped me to cut short my codings thanks for posting do continue ...
ReplyDeleteHadoop Training in Velachery | Hadoop Training
Big Data in Infrastructure Engineering Big Data Projects For Final Year Students
Deletea. Infrastructure Monitoring and Management
nice blog
ReplyDeleteBigdata in hadoop is the interesting topic and to get some important information. Big data hadoop online Course Hyderabad
ReplyDelete