You need to set hive.limit.pushdown.memory.usage; may be se to 0.3. This would still result in Table scan but the data that needs to be munched by other operators in the pipeline would be low. ... View more

Hive currently doesn't push predicates in to row_number; but it does push it into rank. May be you want to try rewriting view definition as WITH t1 AS ( SELECT *, rank() OVER ( PARTITION BY regionid, id, deviceid order by ts desc) AS rownos FROM positions ) SELECT distinct * FROM t1 WHERE rownos < 2; This will cause Hive to push limit in to Windowing Fns. ... View more