CODEWITHSUNDEEP
arrayshive
Diego Arias
Diego Arias

Reputation: 93

Extract last two elements of an array in HIVE

I have an array in a hive table, and I want to extract the two last elements of each array, something like this: ["a", "b", "c"] -> ["b", "c"]

I tried a code like this:

SELECT
 *,
 array[size]     AS term_n,
 array[size - 1] AS term_n_1
FROM
(SELECT *, size(array) AS size FROM MyTable);

But it didn't work, someone has any idea please?

Upvotes: 0

Views: 6320

Answers (3)

Jorge Alaiza
Jorge Alaiza

Reputation: 1

This is a solution to extract the last element of an array in the same query (notice it is not very optimal, and you can apply the same principle to extract n last elements of the array), the logic is to calculate the size of the last element (amount of letters minus the separator character) and then make a substring from 0 to the total size minus the calculated amount of characters to extract

Table of example:

col1  |   col2
--------------
row1  |   aaa-bbb-ccc-ddd

You want to get (extracting the last element, in this case "-ddd"):

row1  |   aaa-bbb-ccc

the query you may need:

select col1, substr(col2,0,length(col2)-(length(reverse(split(reverse(col2),'-')[0]))+1)) as shorted_col2_1element from example_table

If you want to add more elements you have to keep adding the positions in the second part of the operation.

Example to extract the last 2 elements:

select col1, substr(col2,0,length(col2)-(length(reverse(split(reverse(col2),'-')[0]))+1) + length(reverse(split(reverse(col2),'-')[1]))+1)) as shorted_col2_2element from example_table

after executing this second command line you will have something like: row1 | aaa-bbb

*As said previously this is a not optimal solution at all, but may help you

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

  1. array is a reserved word and should be qualified.
  2. An inner sub-query should be aliased.
  3. Array index start with 0. If the array size is 5 then the last index is 4.

Demo

with MyTable as (select array('A','B','C','D','E') as `array`)

SELECT  *
       ,`array`[size - 1] AS term_n
       ,`array`[size - 2] AS term_n_1

FROM   (SELECT *
               ,size(`array`)   AS size 

        FROM    MyTable
        ) t
;

+-----------------------+--------+--------+----------+
|        t.array        | t.size | term_n | term_n_1 |
+-----------------------+--------+--------+----------+
| ["A","B","C","D","E"] |      5 | E      | D        |
+-----------------------+--------+--------+----------+

Upvotes: 2

hlagos
hlagos

Reputation: 7947

I don't know the error that you are getting, but it should be something like

select
yourarray[size(yourarray)],
yourarray[size(yourarray)-1]
from mytable

Upvotes: 0

Related Questions