Recent Posts

Friday, 25 May 2018

Hive Built-in Functions


Hive Built-in Functions
     Basically, to perform several operations there are some functions available. Similarly, in Hive also there are some built-in functions available. Such as Hive Collection Functions, Hive Date Functions, Hive Mathematical Functions, Hive Conditional Functions and Hive String Functions.

1. Collection Functions
     Basically, we use “Collection Functions” for collections. Here, collections are nothing but defined as a grouping of elements and returning single or array of elements depends on return type mentioned in the function name.
1. size (Map<K.V>)
Return Type: int
Description: It will fetch and give the components number in the map type

2. size(Array<T>)
Return Type:
int
Description: It will fetch and give the elements number in the array type

3. Map_keys(Map<K.V>)
Return Type:
Array<K>        
Description: It will fetch and gives an array containing the keys of the input map. Here array is in unordered

4. Map_values(Map<K.V>)
Return type:
Array<V>
Description: It will fetch and gives an array containing the values of the input map. Here array is in unordered

5. Sort_array(Array<T>) 
Return type:
Array<t>
Description: sorts the input array in ascending order of array and elements and returns it.

2. Hive Date Functions
     To perform date manipulations and conversion of date types from one type to another type we use Hive date Functions. 
1. Unix_Timestamp()    
Return type:
Unix_Timestamp()    
Description: We will get current Unix timestamp in seconds

2. To_date (string timestamp)
Return type:
string
Description: It will fetch and give the date part of a timestamp string.

3. year (string date)    
Return type:
int    
Description: It will fetch and give the year part of a date or a timestamp string

4. quarter (date/timestamp/string)
Return type:
int    
Description: It will fetch and give the quarter of the year for a date, timestamp, or string in the range 1 to 4

5. month (string date)
Return type:
int  
Description: It will give the month part of a date or a timestamp string

6. hour (string date)
Return type:
int  
Description: It will fetch and gives the hour of the timestamp

7. minute (string date)
Return type:
int  
Description: It will fetch and gives the minute of the timestamp

8. Date_sub(string starting date, int days)
Return type:
string    
Description: It will fetch and gives Subtraction of number of days to starting date

9. Current_date
Return type:
date    
Description: It will fetch and gives the current date at the start of query evaluation

10. LAST _day (string date)    
Return type:
String
Description: It will fetch and gives the last day of the month which the date belongs to

11. trunc (string date, string format)
Return type:
String
Description: It will fetch and gives date truncated to the unit specified by the format.
Supported formats in this:
MONTH/MON/MM, YEAR/YYYY/YY.

3. Mathematical Functions
1. round (DOUBLE X)    
Return Type:
DOUBLE
Description: It will fetch and returns the rounded BIGINT value of X

2. round(DOUBLE X, INT d)
Return Type:
DOUBLE    
Description: It will fetch and returns X rounded to d decimal places

3. bound(DOUBLE X)    
Return Type:
DOUBLE    
Description: It will fetch and returns the rounded BIGINT value of X using HALF_EVEN rounding mode

4. floor(DOUBLE X)    
Return Type:
BIGINT    
Description: It will fetch and returns the maximum BIGINT value that is equal to or less than X value

5. ceil(DOUBLE a), ceiling(DOUBLE a)    
Return Type:
BIGINT    
Description: It will fetch and returns the minimum BIGINT value that is equal to or greater than X value

6. rand(), rand(INT seed)    
Return Type:
DOUBLE    
Description: It will fetch and returns a random number that is distributed uniformly from 0 to .

4. Conditional Functions
     While it comes to conditional values checks in Hive, we use “Conditional Functions”.
1. if(Boolean testCondition, T valueTrue, T valueFalseOrNull)
Return Type: T   
Description:It will fetch and gives value True when Test Condition is of true, gives value False Or Null otherwise.

2. ISNULL(X)
Return Type: Boolean
Description: It will fetch and gives true if X is NULL and false otherwise.

3. ISNOTNULL(X)
Return Type: Boolean   
Description: It will fetch and gives true if X is not NULL and false otherwise.

5. Hive String Functions
     For String manipulations and string operations in Hive, we call Hive String Functions.
1. reverse(string X)
Return Type: string
Description: It will give the reversed string of X

2. rpad(string str, int length, string pad)
Return Type: string
Description: It will fetch and gives str, which is right-padded with the pad to a length of length(integer value)

3. rtrim(string X)
Return Type: string
Description: It will fetch and returns the string resulting from trimming spaces from the end (right-hand side) of X For example, rtrim(‘ results ‘) results in ‘ results’

4. space(int n)
Return Type: string
Description: It will fetch and gives a string of n spaces.

5. split(string str, string pat)
Return Type: array
Description: Splits str around pat (pat is a regular expression).

6. Str_to_map(text[, delimiter1, delimiter2])
Return Type:  map<String ,String>
Description: It will split text into key-value pairs using two delimiters.

1 comment: