How to access the last element in an array ?
Solution 1
reverse(split(reverse(session), '-')[0])
Although this might be a bit more expensive than the regex solution ;)
Solution 2
Because Non-constant expressions for array indexes not supported in hive.
There will be some other ways to solve your problem:
-
use
regexp_extract
, such as :select regexp_extract(session, '(\-[^\-]+)', 1) as user from my_table;
use custom hive function : example and document could be found in hive document
Solution 3
One more method without reverse
, using array size()-1
. This method is more efficient because it does not produce intermediate reversed string, it works only with array.
select array[size(array)-1] as userID
from
( select split(session,'-') array from your_table ) s;
Comments
-
qiuxiafei almost 2 years
In my hive table, the
session
field is a string in format like:ip-sessionID-userID
orarea-sessionID-userID
There's 3 or 4 fields separated by "
-
", but userID is always the last one.i wanna select userID, but how to access the last field? In python, there's something like:
arr[-1]
but in hive, how to achieve this? The following SQL seems not correct.
select split(session,"\-")[-1] as user from my_table;
Thanks!
-
mel over 8 yearsThis regex solution did not work in case array only have one element.
-
pensz over 8 yearstry
regexp_extract(session, '([^\-]+)$', 1)
? -
Mike Wise over 8 yearsLove this answer. Thanks.