Hive is not filtering on the result of a UDF in the way I expect or desire it
to. The example below shows a very small sample table. The nested query that
follows the table dump attempts to filter the results on the basis of a
row_number UDF. The thing to note is that in the query results, 'rn'
increments steadily from 1 to 15. Next, note that the last clause of the query
intended to filter the result to only those result rows where 'rn' equals 1.
In other words, I expected the query to only return the first result row. What
am I missing? Thanks.
hive> set hive.cli.print.header=true;
hive> select * from test;
OK
foo bar user category value
foo bar user1 catA 1
foo bar user1 catB 2
foo bar user1 catC 3
foo bar user2 catA 99
foo bar user2 catB 98
foo bar user2 catC 97
foo bar user3 catA 100
foo bar user3 catB 100
foo bar user3 catC 100
foo bar user3 catA 200
foo bar user3 catB 200
foo bar user3 catC 200
foo bar user3 catA 300
foo bar user3 catB 300
foo bar user3 catC 300
Time taken: 0.092 seconds
hive> select * from (
> select *, row_number(foo) as rn
> from (
> select foo, bar, user, category, value
> from test
> where foo = 'foo'
> sort by foo ) a
> sort by rn ) b
> where rn = 1;
[...MAP_REDUCE...]
OK
foo bar user category value rn
foo bar user1 catA 1 1
foo bar user1 catB 2 2
foo bar user1 catC 3 3
foo bar user2 catA 99 4
foo bar user2 catB 98 5
foo bar user2 catC 97 6
foo bar user3 catA 100 7
foo bar user3 catB 100 8
foo bar user3 catC 100 9
foo bar user3 catA 200 10
foo bar user3 catB 200 11
foo bar user3 catC 200 12
foo bar user3 catA 300 13
foo bar user3 catB 300 14
foo bar user3 catC 300 15
Time taken: 87.933 seconds
hive>
________________________________________________________________________________
Keith Wiley kwi...@keithwiley.com keithwiley.com music.keithwiley.com
"I do not feel obliged to believe that the same God who has endowed us with
sense, reason, and intellect has intended us to forgo their use."
-- Galileo Galilei
________________________________________________________________________________
