phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gabriel Reid <gabriel.r...@gmail.com>
Subject Re: counting returns different results
Date Wed, 11 Jun 2014 08:08:10 GMT
Could you post the DDL statement for creating the table? And could you
also say which version of HBase and Phoenix are you running on?

On Wed, Jun 11, 2014 at 1:58 AM, Sean Huo <sean@crunchyroll.com> wrote:
> Forget about the above two queries, it seems that phoenix count() function
> has a real problem dealing with large data size.
>
> this 30 day query returns a count of 36076
>
> select count(1) from table t where device ='A' and date >
> to_date('2014-05-09','yyyy-MM-dd') and date <
> to_date('2014-06-09','yyyy-MM-dd');
>
> while the following query for a smaller day range returns  the correct
> 1303743
>
> select count(1) from table t where device ='A' and date >
> to_date('2014-06-01','yyyy-MM-dd') and date <
> to_date('2014-06-09','yyyy-MM-dd');
>
> The table has about 70 million rows per day.
>
>
> On Tue, Jun 10, 2014 at 2:43 PM, Sean Huo <sean@crunchyroll.com> wrote:
>>
>> The two queries seems to yield the same results for relatively small query
>> size, but diverge when the query size is really big (30*70 millions rows)
>>
>>
>> On Tue, Jun 10, 2014 at 2:07 PM, Gabriel Reid <gabriel.reid@gmail.com>
>> wrote:
>>>
>>> Hi Sean,
>>>
>>> That doesn't sound right -- any idea which of the queries (if either)
>>> is returning the correct results?
>>>
>>> Any chance you could try this out on a small test table and then post
>>> the relevant DDL and literal queries here?
>>>
>>> - Gabriel
>>>
>>>
>>> On Tue, Jun 10, 2014 at 10:30 PM, Sean Huo <sean@crunchyroll.com> wrote:
>>> > These two queries returns vastly different results:
>>> >
>>> > query1:
>>> > select count(1) from t where date > 'certain day' and device ='A';
>>> >
>>> > query2:
>>> >
>>> > select device, count(1) from t where date >'certain day' and device
>>> > ='A'
>>> > group by device;
>>> >
>>> > assuming table t has the  composite primary key of
>>> > date timestamp, and device varchar.
>>> >
>>> > What gives?
>>> >
>>
>>
>

Mime
View raw message