Major Description
PROBLEM: Hive doesn't not have the following built-in functions.
1) MONTHS_BETWEEN(d1,d2)
2) LAST_DAY(d)
3) FIRST_DAY(d)
4) ADD_MONTHS (date, integer)
5) CHR()
6) REPLACE(char, search_string, replacement_string)
7) TRANSLATE(expr, from_string, to_string)
Without these, not able to do certain analysis.
USE CASE:
1) MONTHS_BETWEEN(d1,d2)
2) LAST_DAY(d)
3) FIRST_DAY(d)
4) ADD_MONTHS (date, integer)
ADD_MONTHS returns the date date plus integer months. If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.
5) CHR()
CHR returns the character having the binary equivalent to n (where n is between 0 and 255)as a VARCHAR2 value
6) REPLACE(char, search_string, replacement_string)
REPLACE returns char with every occurrence of search_string replaced with replacement_string.
SELECT REPLACE('JACK and JUE','J','BL') "Changes";
Changes
--------------
BLACK and BLUE
7) TRANSLATE(expr, from_string, to_string)
TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string.
SELECT TRANSLATE('2KR 229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX-') (CHAR(26)) "License";
License
-------
9XX-999