5/10/2017

Format Currency, Percentage, Date, and Mask SSN in Hive SQL

In Hive SQL, to format Currency, Percentage and Date, use the following SQL:


SELECT
    CONCAT('$',format_number(COALESCE(12345.6789,0),2))                 AS `Sample Currency Format`,
    CONCAT(format_number(COALESCE(98.6543,0),2),'%')                    AS `Sample Currency Format`,
    from_unixtime(unix_timestamp(TO_DATE(CURRENT_DATE) , 'yyyy-MM-dd'), '01-01-yyyy') AS
    `Sample Date Format`,
    CONCAT('****-**',SUBSTR("123456789", LENGTH("123456789")-2, 3)) AS MaskedSSN








You could also apply the Round function if needed.


Happy coding~!

No comments:

Post a Comment

Root Cause Analysis