hadoophive

convert TO_CHAR, IS_DATE to hive query

I want to convert specific data to Hive. However, functions available in Oracle cannot be used in Hive. How can I solve this? The applied conversion rule is as follows.

DECODE(TRUE, IS_DATE(TO_CHAR(columnname , 'YYYYMMDD')), 'YYYYMMDD',NULL)

In the case of DECODE, it was confirmed that it could be processed with IF. But I couldn't find a way to change IS_DATE function and TO_CHAR function.

Solution

  • Oracle does not have an IS_DATE function. Are you sure this is not a user-defined function? If so then you will need to look at the source code and check what it does and duplicate that in Hive.

    DECODE(a, b, c, d) can be rewritten as a CASE expression:

    CASE WHEN a = b THEN c ELSE d END

    So your code (assuming that columnname is a DATE and you are using TO_CHAR to convert it to a string and then IS_DATE checks if it is a valid date, which seems pointless as it will only not be a valid date when columnname is NULL) would convert to:

    CASE
WHEN CAST(columnname AS STRING FORMAT 'YYYYMMDD') IS NOT NULL
THEN 'YYYYMMDD'
ELSE NULL
END

    or, more simply:

    CASE
WHEN columnname IS NOT NULL
THEN 'YYYYMMDD'
ELSE NULL
END