Reply
Highlighted
Explorer
Posts: 24
Registered: ‎08-29-2017

Case statement error in Beeline hive

Hi

I have a table in Hbase and row key is a combination of three columns

 

Hostname#starttime_type

 

I used split function to take the timestamp from the row key (hbid)

 

Then I applied hours(timestamp)

 

I am getting hour value as 0,1,2,3 etc

 

 

Now I want to group by hours range.

 

If case hour=0 then “print range 0-1”

If case hour=1 then “ print range 0-1”

If case hour=2 then “print range 2-4”

If case hour=3 then “print range 2-4”

 

case when hour(split(split(hbid,"#")[1],"_")[1])==0  

then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","0-2")

 

Select country,case when hour(split(split(hbid,"#")[1],"_")[1])==0  then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","0-2") when hour(split(split(hbid,"#")[1],"_")[1])==1 then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","0-2") when hour(split(split(hbid,"#")[1],"_")[1])==2 then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","2-4") when hour(split(split(hbid,"#")[1],"_")[1])==3 then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","2-4") end,column5 from table name group by case when hour(split(split(hbid,"#")[1],"_")[1])==0  then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","0-2") when hour(split(split(hbid,"#")[1],"_")[1])==1 then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","0-2") when hour(split(split(hbid,"#")[1],"_")[1])==2 then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","2-4") when hour(split(split(hbid,"#")[1],"_")[1])==3 then concat(split(split(split(hbid,"#")[1],"_")[1]," ")[0],"/","2-4") end,country

 

If I try to print hour(split(split(hbid,"#")[1],"_")[1]) I am getting all the values correctly like 0,1,2,3,4 upto 22.

But when I use case statements its not working.It shows only one value range 8-10 is printed.

 

I think case 0 – case 9 is not read directly goes to case 10 then only prints case 10 not goes to any other cases.

 

Can I know the proper syntax of case statements?

 

Is else statement mandatory in case statements?

 

Other than case any other statements like if else if we can use?

 

 

Announcements