Hive Macros examples

By | November 18, 2018
Hive Macros examples

  1. 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   |
    +------+------+------+
    
  2. 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  |
    +----------------------+
    
  3. 
    
    
    

Leave a Reply

Your email address will not be published. Required fields are marked *