Thank you James, The work arround is very helpful!!!
Thanks, --Rafit On Sun, Aug 23, 2015 at 10:52 PM, James Taylor <[email protected]> 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 < > [email protected]> 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 >> > >
