phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Abhishek Gupta <abhila...@gmail.com>
Subject Re: Issues with LAST_VALUE aggregation UDF
Date Wed, 12 Sep 2018 05:10:48 GMT
Thank you so much for the response Josh.

Will check on 4.14 and get back to you with the results.

Thanks

On Tue, Sep 11, 2018 at 8:45 PM Josh Elser <elserj@apache.org> wrote:

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

Mime
View raw message