phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rafit Izhak-Ratzin <ra...@robinsystems.com>
Subject Re: Group by a divided value (e.g., time/10) returns NULL.
Date Mon, 24 Aug 2015 19:27:13 GMT
Thank you James,

The work arround is very helpful!!!

Thanks,
 --Rafit

On Sun, Aug 23, 2015 at 10:52 PM, James Taylor <jamestaylor@apache.org>
wrote:

> Hi Rafit,
> Looks like a bug. Please file a JIRA. The following seems to work as a
> workaround:
>
> select cast(time/10.0 as integer) as tm, hostname, avg(usage) from test
> group by hostname, tm;
>
> You might also consider using a date[1] type instead of an integer and
> then using the TRUNC function[2] which work with both numeric and time
> types.
>
> Thanks,
> James
>
> [1] https://phoenix.apache.org/language/datatypes.html#date_type
> [2] https://phoenix.apache.org/language/functions.html#trunc
>
> On Thu, Aug 20, 2015 at 11:09 PM, Rafit Izhak-Ratzin <
> rafit@robinsystems.com> wrote:
>
>> Hi all,
>>
>> My environment includes:  phoenix version 4.5.0,  HBase version 0.98,
>> Cloudera 5.2
>>
>> When I group by a divided value (e.g., time/10) the divided value will
>> return as NULL.
>> Please see the simple examples below,
>>
>> Thanks in advance,
>> --Rafit
>>
>>
>> create table test(time integer not null, hostname varchar not null,usage
>> float constraint pk PRIMARY KEY(time, hostname));
>> upsert into test values(1439853441,'qa8',3.28);
>> upsert into test values(1439853449,'qa8',3.28);
>> upsert into test values(1439853459,'qa8',3.28);
>> upsert into test values(1439853458,'qa8',3.27);
>> upsert into test values(1439853457,'qa8',6.27);
>> upsert into test values(1439853462,'qa8',8.27);
>> upsert into test values(1439853462,'qa9',8.27);
>> upsert into test values(1439853457,'qa9',6.27);
>>
>>
>> 0: jdbc:phoenix:localhost> select * from test;
>>
>> +------------------------------------------+------------------------------------------+----------------------------------------+
>> |                   TIME                 |                 HOSTNAME
>>  |                  USAGE           |
>>
>> +------------------------------------------+------------------------------------------+----------------------------------------+
>> | 1439853441                         | qa8
>>        | 3.28                                 |
>> | 1439853449                         | qa8
>>        | 3.28                                 |
>> | 1439853457                         | qa8
>>        | 6.27                                 |
>> | 1439853457                         | qa9
>>        | 6.27                                 |
>> | 1439853458                         | qa8
>>        | 3.27                                 |
>> | 1439853459                         | qa8
>>        | 3.28                                 |
>> | 1439853462                         | qa8
>>        | 8.27                                 |
>> | 1439853462                         | qa9
>>        | 8.27                                 |
>>
>> +------------------------------------------+------------------------------------------+----------------------------------------+
>>
>> 0: jdbc:phoenix:localhost> select time/10, hostname, usage from test;
>>
>> +----------------------------------------+------------------------------------------+------------------------------------------+
>> |              (TIME / 10)           |                 HOSTNAME        |
>>                  USAGE              |
>>
>> +----------------------------------------+------------------------------------------+------------------------------------------+
>> | 143985344                         | qa8
>>      | 3.28                                   |
>> | 143985344                         | qa8
>>      | 3.28                                   |
>> | 143985345                         | qa8
>>      | 6.27                                   |
>> | 143985345                         | qa9
>>      | 6.27                                   |
>> | 143985345                         | qa8
>>      | 3.27                                   |
>> | 143985345                         | qa8
>>      | 3.28                                   |
>> | 143985346                         | qa8
>>      | 8.27                                   |
>> | 143985346                         | qa9
>>      | 8.27                                   |
>>
>> +----------------------------------------+------------------------------------------+------------------------------------------+
>>
>> 0: jdbc:phoenix:localhost> select time/10 as tm, hostname, avg(usage)
>> from test group by hostname, tm;
>>
>> +----------------------------------------+------------------------------------------+------------------------------------------+
>> |                   TM                  |                 HOSTNAME
>>  |                AVG(USAGE)       |
>>
>> +----------------------------------------+------------------------------------------+------------------------------------------+
>> | null                                   |
>>            | 3.2799                                   |
>> | null                                   |
>>            | 4.2733                                   |
>> | null                                   |
>>            | 6.2699                                   |
>> | null                                   |
>>            | 8.27                                      |
>> | null                                   |
>>            | 8.27                                      |
>>
>> +----------------------------------------+------------------------------------------+------------------------------------------+
>> *hostname is empty, time/10 is null*
>>
>> *I thought it might be related to the fact that the time is a primary key
>> so I ran the next test case:*
>>
>> 0: jdbc:phoenix:localhost> create table test1(time integer not null,
>> hostname varchar not null,usage float,period integer constraint pk PRIMARY
>> KEY(time, hostname));
>> 0: jdbc:phoenix:localhost> upsert into test1
>> values(1439853462,'qa9',8.27,1439853462);
>> 0: jdbc:phoenix:localhost> upsert into test1
>> values(1439853461,'qa9',8.27,1439853362);
>> 0: jdbc:phoenix:localhost> upsert into test1
>> values(1439853461,'qa9',5.27,1439853461);
>> 0: jdbc:phoenix:localhost> upsert into test1
>> values(1439853451,'qa9',4.27,1439853451);
>> 0: jdbc:phoenix:localhost> select * from test1;
>>
>> +-------------------------------------+-------------------------------------+--------------------------------------+---------------------------------------+
>> |                   TIME            |            HOSTNAME       |
>>      USAGE              |               PERIOD             |
>>
>> +-------------------------------------+-------------------------------------+--------------------------------------+---------------------------------------+
>> | 1439853451                    | qa9                                |
>> 4.27                                | 1439853451                      |
>> | 1439853461                    | qa9                                |
>> 5.27                                | 1439853461                      |
>> | 1439853462                    | qa9                                |
>> 8.27                                | 1439853462                      |
>>
>> +-------------------------------------+------------------------------------+--------------------------------------+----------------------------------------+
>>
>> 0: jdbc:phoenix:localhost> select period/10 as tm, hostname, usage from
>> test1 ;
>>
>> +----------------------------------------+------------------------------------------+------------------------------------------+
>> |                   TM                  |                 HOSTNAME
>>  |                  USAGE             |
>>
>> +----------------------------------------+------------------------------------------+------------------------------------------+
>> | 143985345                         | qa9
>>     | 4.27                                    |
>> | 143985346                         | qa9
>>     | 5.27                                    |
>> | 143985346                         | qa9
>>     | 8.27                                    |
>>
>> +----------------------------------------+------------------------------------------+------------------------------------------+
>>
>>
>> +----------------------------------------+------------------------------------------+------------------------------------------+
>> |                   TM                  |                 HOSTNAME
>>  |                AVG(USAGE)      |
>>
>> +----------------------------------------+------------------------------------------+------------------------------------------+
>> | null                                   | qa8
>>            | 4.2699                                |
>> | null                                   | qa9
>>            | 4.2699                                |
>> | null                                   | qa9
>>            | 6.77                                    |
>>
>> +----------------------------------------+------------------------------------------+------------------------------------------+
>>
>>
>> Now we can see the hostname (it is not an empty field as before) but tm
>> is still null.
>>
>> Thanks,
>> --Rafit
>>
>
>

Mime
View raw message