hive sql find the latest record

Question

the table is:

create table test (
id string,
name string,
age string,
modified string)

data like this:

id    name   age  modifed
1     a      10   2011-11-11 11:11:11
1     a      11   2012-11-11 12:00:00
2     b      20   2012-12-10 10:11:12
2     b      20   2012-12-10 10:11:12
2     b      20   2012-12-12 10:11:12
2     b      20   2012-12-15 10:11:12

I want to get the latest record(include every colums id,name,age,modifed) group by id,as the data above,the correct result is:

1     a      11   2012-11-11 12:00:00
2     b      20   2012-12-15 10:11:12

I do like this:

insert overwrite table t 
select b.id, b.name, b.age, b.modified 
from (
        select id,max(modified) as modified 
        from test 
        group by id
) a 
left outer join test b on (a.id=b.id  and a.modified=b.modified);

This sql can get the right result,but when mass data，it runs slow.

**Is there any way to do this without left outer join? **

Answer 1

There's a nearly undocumented feature of Hive SQL (I found it in one of their Jira bug reports) that lets you do something like argmax() using struct()s. For example if you have a table like:

test_argmax
id,val,key
1,1,A
1,2,B
1,3,C
1,2,D
2,1,E
2,1,U
2,2,V
2,3,W
2,2,X
2,1,Y

You can do this:

select 
  max(struct(val, key, id)).col1 as max_val,
  max(struct(val, key, id)).col2 as max_key,
  max(struct(val, key, id)).col3 as max_id
from test_argmax
group by id

and get the result:

max_val,max_key,max_id
3,C,1
3,W,2

I think in case of ties on val (the first struct element) it will fall back to comparison on the second column. I also haven't figured out whether there's a neater syntax for getting the individual columns back out of the resulting struct, maybe using named_struct somehow?

Answer 2

Just slightly different approach than what has been answered in previous answer.

Below example uses hive windowing function to find out the latest record, read more here

SELECT t.id
    ,t.name
    ,t.age
    ,t.modified
FROM (
    SELECT id
        ,name
        ,age
        ,modified
        ,ROW_NUMBER() OVER (
            PARTITION BY id ORDER BY unix_timestamp(modified,'yyyy-MM-dd hh:mm:ss') DESC
            ) AS ROW_NUMBER   
    FROM test
    ) t
WHERE t.ROW_NUMBER <= 1;

The modified is string so converting it to timestamp using unix_timestamp(modified,'yyyy-MM-dd hh:mm:ss') then applying order by on timestamp.

Answer 3

There is a relatively recent feature of Hive SQL, analytic functions and the over clause. This should do the job without joins

select id, name, age, last_modified 
from ( select id, name, age, modified, 
              max( modified) over (partition by id) as last_modified 
       from test ) as sub
where   modified = last_modified 

What's going on here is that the subquery produces a new row with an extra column last_modified which has the latest modified timestamp for the corresponding person's id. (Similar to what group by would do) The key here is that the subquery gets you again one row per row in your original table and then you filter from that.

There is a chance that even the simpler solution works:

select  id, name, age,  
        max( modified) over (partition by id) last_modified 
from test 
where   modified = last_modified 

By the way, the same code would work in Impala, too.

Answer 4

Give this a try:

select t1.* from test t1
join (
  select id, max(modifed) maxModified from test
  group by id
) s
on t1.id = s.id and t1.modifed = s.maxModified

Fiddle here.

Left outer join solution here.

Let us know which one runs faster :)

Answer 5

try this

select id,name,age,modified from test
 where modified=max(modified)
 group by id,name

Answer 6

If u can make sure that row which has max modified also has max age in same id row set.

Try

select id, name, max(age), max(modified) 
from test
group by id, name

Answer 7

Presume the data is like this:

    id      name    age     modifed
    1       a       10      2011-11-11 11:11:11
    1       a       11      2012-11-11 12:00:00
    2       b       23      2012-12-10 10:11:12
    2       b       21      2012-12-10 10:11:12
    2       b       22      2012-12-15 10:11:12
    2       b       20      2012-12-15 10:11:12

then the result of the above query will give you - (notice the repeated 2, b having the same date time)

    1       a       11      2012-11-11 12:00:00
    2       b       22      2012-12-15 10:11:12
    2       b       20      2012-12-15 10:11:12

This query runs an additional group by and is less efficient but gives the correct result -

    select collect_set(b.id)[0], collect_set(b.name)[0], collect_set(b.age)[0], b.modified
    from
        (select id, max(modified) as modified from test group by id) a
      left outer join
        test b
      on
        (a.id=b.id and a.modified=b.modified)
    group by
      b.modified;

then the result of the above query will give you

    1       a       11      2012-11-11 12:00:00
    2       b       20      2012-12-15 10:11:12

Now if we improve the query a little - then in place of 3 MRs it runs only one Keping the result same -

    select id, collect_set(name)[0], collect_set(age)[0], max(modified)
    from test 
    group by id;

Note - this will slow down in case your group by field produces large results.

Answer 8

You can get the required result without using left outer join like this:

select * from test where (id, modified) in(select id, max(modified) from test group by id)

http://sqlfiddle.com/#!2/bfbd5/42