syntaxhivecase-whengrouping-sets

Syntax of using case in grouping sets


The Hive query snippet in question is as below:

group by
  case
    when inte.subId is not null then 'int'
    else 'ext'
  end,
  taskType,
  result
grouping sets(
  (
    case
      when inte.subId is not null then 'int'
      else 'ext'
    end,  -- line 36
    taskType,
    result
  ),  -- line 39
  (
    taskType,
    result
  )
)

The log suggests some syntax error at line 36 and 39:

FAILED: ParseException line 36:7 missing ) at ',' near ')'
line 39:3 missing EOF at ',' near ')'

Any idea? If you need more info from me, feel free to comment.


Solution

  • OK, here is the solution suggested by @GordonLinoff in the comment. Basically, the idea is to use a sub-query where a new column Category is selected as

    case 
      when inte.subId is not null then 'int'
      else 'ext'
    end as Category
    

    Then, in the outer main query, the group part is simply

    group by Category, Type, Result
    grouping sets(
        (Category, Type, Result),
        (Type, Result)
    )
    

    As to why the syntax error in the question, we don't know for sure. Maybe, case cannot be used within grouping sets.