CODEWITHSUNDEEP
datehadoophivecasehiveql
SBB
SBB

Reputation: 8970

Hive query with case statement

I am trying to use a field in my data called priority in order to drive a numerical value for the DATE_ADD function. Essentially, the priority determines how many days before the issue is out of SLA.

I am trying to use this priority by saying:

pseudo code - If priority=p0, DATE_ADD (date, INTERVAL 1 day) Else If priority=p1, DATE_ADD (date, INTERVAL 15 day)

Here is my code I am trying:

SELECT 
    jira.jiraid as `JIRA / FR`,
    jira.priority as `Priority`,
    DATE_FORMAT(jira.created,"MM/dd/Y") as `Date Jira Created`,
    DATE_FORMAT(DATE_ADD(jira.created, INTERVAL 
        CASE jira.status 
            WHEN "P0" THEN 1
            WHEN "P1" THEN 15
            WHEN "P2" THEN 40
            WHEN "P3" THEN 70
        ELSE 70
    END day),"MM/dd/Y") as `Date when Out of SLA`
    FROM jira

Does hive support this type of if/else statements?

Upvotes: 1

Views: 3572

Answers (1)

leftjoin
leftjoin

Reputation: 38290

You do not need to use INTERVAL in Hive for adding days. date_add function accepts integer days. Calculate interval in the subquery, this will work and look cleaner:

select
      s.jiraid as `JIRA / FR`,
      s.priority as `Priority`,
      DATE_FORMAT(s.created,'MM/dd/Y') as `Date Jira Created`,
      DATE_FORMAT(DATE_ADD(s.created, s.days_interval),'MM/dd/Y') as `Date when Out of SLA`
 from
(
SELECT 
      j.jiraid,
      j.priority,
      j.created, 
      CASE j.status 
            WHEN 'P0' THEN 1
            WHEN 'P1' THEN 15
            WHEN 'P2' THEN 40
            ELSE           70
      END as days_interval
  FROM jira j
)s;

Though you can calculate case statement inside date_add function, placing case statement as a function parameter and it should also work.

Upvotes: 1

Related Questions