Hive SQL查找最新记录

Question

表格如下：

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

这样的数据：

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

我希望获得最新的记录（包括每个列的id，name，age，modifed），按照id分组，如上所示的数据，正确的结果是：

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

我喜欢这样做：

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);

这个 SQL 可以得到正确的结果，但是当数据量较大时，它运行缓慢。

**有没有不使用左外连接的方法？**

- qiulp

---

感谢您的问题和答案，它们完全解决了我的问题！ - eleforest

Answer 1

Hive SQL有一个几乎未记录的功能（我在他们的Jira错误报告中找到了它），可以使用struct()来执行类似于argmax()的操作。例如，如果您有这样一张表：

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

你可以这样做：

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

并获得结果：

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

如果在val上出现平局（第一个结构元素），我认为它将会回退到对第二列的比较。同时，我还没有找到一种更简洁的语法来从生成的结构中获取单独的列，也许可以使用named_struct？

- patricksurry

---

这是一个很棒的解决方案，我非常喜欢它！非常感谢你。 - Clemens Valiente

你能解释一下 col1、col2、col3 属性的作用吗？ - Quetzalcoatl

col1, ... are just the default field names for the (unnamed) struct. It might be clearer to write the more verbose max(named_struct('val', val, 'key', key, 'id', id)).id as max_id - patricksurry

Answer 2

这里提供了一种与之前回答不同的方法。

以下示例使用 Hive 窗口函数 查找最新记录，更多信息请参阅此处。

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;

将修改时间字符串转换为时间戳，使用 unix_timestamp(modified,'yyyy-MM-dd hh:mm:ss')，然后对时间戳进行排序。

- Rahul Sharma

---

我在想，我们是否需要unix_timestamp修饰符？如果modified是一个字符串，那么它就是不必要的，对吧？ - Diogo Santos

Answer 3

Hive SQL的一个比较新的特性是分析函数和over子句。这应该可以在不使用连接的情况下完成工作。

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 

这里发生的情况是，子查询会生成一个新行，其中包含具有相应人员id的最新修改时间戳的额外列 last_modified。(类似于 group by 执行的操作) 关键在于子查询让您再次获取原始表中每一行的一行，然后从中进行筛选。

甚至更简单的解决方案也有可能奏效：

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

顺便提一下，在 Impala 中这段代码也能够正常工作。

- Mateo

---

如何处理相同的 max(modified)？ - lydias

Answer 4

试着尝试一下这个：

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。

点击这里访问左外连接解决方案。

请告诉我们哪个运行更快 :)

- Mosty Mostacho

---

你的 SQL，耗时 325.579 秒，总 MapReduce CPU 时间花费为 11 分钟 36 秒 130 毫秒，6 个任务。 我的 SQL，耗时 220.736 秒，总 MapReduce CPU 时间花费为 12 分钟 13 秒 80 毫秒，5 个任务。 - qiulp

请注意，您的查询与我发布的第一个查询相同（我刚意识到），但是您的查询使用了不必要的左连接。内连接已足够。那么，关于我提供的真正的左外连接解决方案呢？可能需要更多时间。顺便说一下，请确保将 t1.* 替换为仅包含必要字段。 - Mosty Mostacho

在Hive中，仅支持等值连接、外连接和左半连接。Hive不支持非等值条件的连接条件，因为这样的条件很难表示为Map/Reduce作业。此外，在Hive中可以连接超过两个表。如果使用left join .. on .. and t1.modifed < t2.modifed这样的SQL语句，则会出现错误。 - qiulp

Answer 5

试一下这个

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

- SRIRAM

---

年龄是可以改变的，因此不能按“ID、姓名、年龄”分组，就像这样：1 a 10 2011-11-11 11:11:11 1 a 11 2012-11-11 12:00:00 - qiulp

Answer 6

如果您能确保在同一ID行集中，具有最大修改的行也具有最大年龄，请尝试。

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

- pensz

Answer 7

假设数据是这样的：

    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

如果执行上述查询，结果将为-（请注意2的重复，b具有相同的日期时间）

    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

这个查询运行了一个额外的分组操作，效率较低但可以得到正确的结果 -

    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;

那么上述查询的结果将会给你

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

现在，如果我们稍微改进一下查询 - 那么它只运行一个Keping而不是3个MR，结果保持不变 -

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

注意 - 如果您的分组字段产生大量结果，这将会减慢速度。

- user 923227

Answer 8

你可以不使用左外连接来获得所需的结果，像这样：

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

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

- aditya