Hive Macros examples
-
Data Type check – Check if a given column is a number
DROP TEMPORARY MACRO IF EXISTS isNumber; CREATE TEMPORARY MACRO isNumber (input INT) CASE WHEN CAST(input AS INT) IS NULL THEN 'NO' else 'YES' END ; -- Usage: SELECT isNumber(100), isNumber("123"), isNumber("12sd"); -- Output +------+------+------+ | _c0 | _c1 | _c2 | +------+------+------+ | YES | YES | NO | +------+------+------+ -
Get current time with pre-defined format
DROP TEMPORARY MACRO IF EXISTS current_time_preferred_format; CREATE TEMPORARY MACRO current_time_preferred_format(input string) CASE WHEN UPPER(input) = "DEFAULT" THEN FROM_UNIXTIME( unix_timestamp(), "yyyy-MM-dd'T'HH:mm:ss.sss") ELSE FROM_UNIXTIME( unix_timestamp(), input) END ; -- Default -- Usage: SELECT current_time_preferred_format ("DEFAULT"); -- Output: +--------------------------+ | _c0 | +--------------------------+ | 2018-11-18T21:14:05.005 | +--------------------------+ -- Custom -- Usage: SELECT current_time_preferred_format ("yyyy-MM-dd'T'HH:mm:ss"); -- Output: +----------------------+ | _c0 | +----------------------+ | 2018-11-18T21:14:29 | +----------------------+ -