The versions you provided in the description make it sound like you're
actually using HDP's distribution of Apache Phoenix, not an official
Apache Phoenix release.
Please test against an Apache Phoenix release or contact Hortonworks for
support. It would not be unheard of that this issue has already been fixed.
On 9/11/18 10:01 AM, Abhishek Gupta wrote:
Hi,
We are trying to use the LAST_VALUE aggregation UDF in our Phoenix SQL
queries that serve REST APIs over analytics data. However we are seeing
a number of issues/limitations in this function.
First, there seems to be a bug where it fails for sparse columns that
have NULLs. Have created a JIRA ticket for that containing the details
of the exception and steps to reproduce
https://issues.apache.org/jira/browse/PHOENIX-4898
Second, the function works in a way that it ignores all the group by
columns except the column in the order by clause in finding the last
value. For example
Following is a dataset (pk1,pk2,pk3,pk4 and pk5 are Primary key columns
and m1-m3 are metric columns, I intend to do a SUM on m2 and m3 and a
last value on m1
pk1 . | pk2 | pk3 . | pk4 |
pk5 . | m1 | m2 | m3
------------------------------------------------------------------------------------------------------
| test | 201808010000 | app1 | plat1 | lang1 | 1
| 10 | 100 |
| test | 201808010000 | app1 | plat1 | lang2 | 2
| 10 | 100 |
| test | 201808010000 | app1 | plat2 | lang1 | 3
| 10 | 100 |
| test | 201808010000 | app2 | plat1 | lang1 | 4
| 10 | 100 |
| test | 201808010030 | app1 | plat1 | lang1 |
10 | 10 | 100 |
| test | 201808010030 | app1 | plat1 | lang2 |
20 | 10 | 100 |
| test | 201808010030 | app1 | plat2 | lang1 |
30 | 10 | 100 |
| test | 201808010030 | app2 | plat1 | lang1 |
40 | 10 | 100 |
| test | 201808010100 | app1 | plat1 | lang1 |
100 | 10 | 100 |
| test | 201808010100 | app1 | plat1 | lang2 |
200 | 10 | 100 |
| test | 201808010100 | app1 | plat2 | lang1 |
300 | 10 | 100 |
| test | 201808010100 | app2 | plat1 | lang1 |
400 | 10 | 100 |
| test | 201808010130 | app1 | plat1 | lang1 |
1000 | 10 | 100 |
| test | 201808010130 | app1 | plat1 | lang2 |
2000 | 10 | 100 |
| test | 201808010130 | app1 | plat2 | lang1 |
3000 | 10 | 100 |
| test | 201808010130 | app2 | plat1 | lang1 |
4000 | 10 | 100 |
| test | 201808010200 | app1 | plat1 | lang1 |
10000 | 10 | 100 |
| test | 201808010200 | app1 | plat1 | lang2 |
20000 | 10 | 100 |
| test | 201808010200 | app1 | plat2 | lang1 |
30000 | 10 | 100 |
| test | 201808010200 | app2 | plat1 | lang1 |
40000 | 10 | 100 |
If I run the following query (using all primary key columns in group by)
select TO_CHAR(TO_DATE(pk2,'yyyyMMddHHmm'),'yyyyMMddHH') as
t,pk3,pk4,pk5, last_value(m1) within group (order by pk2 asc) as oi,
sum(m2), sum(m3) from test_table group by pk1,t,pk3,pk4,pk5;
I get the correct result for last value as for each pk2 value which is
used in order by there is only 1 row
+-------------+---------+----------------+--------+--------+----------+----------+
| T | pk3 | pk4 | pk5 | OI | SUM(M2) | SUM(M3) |
+-------------+---------+----------------+--------+--------+----------+----------+
| 2018080100 | app1 | plat1 | lang1 | 10 | 20
| 200 |
| 2018080100 | app1 | plat1 | lang2 | 20 | 20
| 200 |
| 2018080100 | app1 | plat2 | lang1 | 30 | 20
| 200 |
| 2018080100 | app2 | plat1 | lang1 | 40 | 20
| 200 |
| 2018080101 | app1 | plat1 | lang1 | 1000 | 20
| 200 |
| 2018080101 | app1 | plat1 | lang2 | 2000 | 20
| 200 |
| 2018080101 | app1 | plat2 | lang1 | 3000 | 20
| 200 |
| 2018080101 | app2 | plat1 | lang1 | 4000 | 20
| 200 |
| 2018080102 | app1 | plat1 | lang1 | 10000 | 20
| 100 |
| 2018080102 | app1 | plat1 | lang2 | 20000 | 10
| 100 |
| 2018080102 | app1 | plat2 | lang1 | 30000 | 10
| 100 |
| 2018080102 | app2 | plat1 | lang1 | 40000 | 10
| 100 |
+-------------+---------+----------------+--------+--------+----------+----------+
However if I do I group by on less than all the primary columns the
LAST_VALUE function ignores the rest of the group by columns in sorting
and returns incorrect last_value
select TO_CHAR(TO_DATE(pk2,'yyyyMMddHHmm'),'yyyyMMddHH') as t, pk3,
last_value(m1) within group (order by pk2 asc) as oi, sum(m2),
sum(m3) from test_table group by pk1,t,pk3;
+-------------+---------+--------+----------+----------+
| T | pk3 | OI | SUM(M2) | SUM(M3) |
+-------------+---------+--------+----------+----------+
| 2018080100 | app1 | 10 | 60 | 600 |
| 2018080100 | app2 | 40 | 20 | 200 |
| 2018080101 | app1 | 1000 | 60 | 600 |
| 2018080101 | app2 | 4000 | 20 | 200 |
| 2018080102 | app1 | 10000 | 40 | 300 |
| 2018080102 | app2 | 40000 | 10 | 100 |
+-------------+---------+--------+----------+----------+
So instead of taking the last value of the group formed by 2018080100
and app1 i.e 30 it is picking the first i.e 10.
Thanks,
Abhishek
