Mailing-List: contact dev-help@drill.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@drill.apache.org
MIME-Version: 1.0
In-Reply-To: 
 <CAB=0=1-r3Yb0MovbW+Ray1fMf+5+FvRCPkyY6v31GAifCVNzEg@mail.gmail.com>
References: 
 <CAB=0=1-r3Yb0MovbW+Ray1fMf+5+FvRCPkyY6v31GAifCVNzEg@mail.gmail.com>
Date: Tue, 5 Apr 2016 14:18:27 +0100
Message-ID: 
 <CANeZ_zGsNFKaK8LQt9WKEZSh2DvuzG5i7LQAZNd0un2RWv1+5Q@mail.gmail.com>
Subject: Re: Simple query on 150 billion records
From: Darshan Singh <darshan.meel@gmail.com>
To: dev@drill.apache.org
Content-Type: multipart/alternative; boundary=047d7bf0d2d44026dc052fbcaefa

--047d7bf0d2d44026dc052fbcaefa
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hi,

How much data  you got from this query

create table ANALYSIS_RESULT as (
select Int32Field1 as SECONDS from hdfs.`/data/` where Int32Field2=3D123456
or Int32Field2=3D4567898);

As per your email you said single record.Also, in this query you used
Int32Field1 as Seconds whereas in the first query it was just seconds.Are
these same fields or do you have some sort for conversion for these fields
in first query.

A plan would be grateful as well.

Thanks

On Mon, Apr 4, 2016 at 3:09 PM, Fran=C3=A7ois M=C3=A9thot <fmethot78@gmail.=
com> wrote:

> Hi,
>
>   Querying 150 Billion records spread over ~21 000 parquets stored in hdf=
s
> on 13 nodes (6 cores each, Max Dir. Mem: 32GB, Max Heap 8 GB).
>
> Is their a known issue or drill limitation that would explain why the fir=
st
> query below can't return the expected single row and aggregation ?
>
> create table ANALYSIS_RESULT as (
> select to_date(to_timestamp((SECONDS)), count(1)
> from hdfs.`/data/
> where Int32Field2=3D123456 or Int32Field2=3D4567898
> group by to_date(to_timestamp((SECONDS)));
>
> After *20 hours*, SYSTEM ERROR: Foreman Exception: One more more nodes lo=
st
> connectivity during query.
>
>
> If we do the query in 2 steps:
> create table ANALYSIS_RESULT as (
> select Int32Field1 as SECONDS from hdfs.`/data/` where Int32Field2=3D1234=
56
> or Int32Field2=3D4567898);
>
> result was returned in *43 minutes* ( a single record ).
>
> select to_date(to_timestamp((SECONDS)), count(1)
> from ANALYSIS_RESULT
> group by to_date(to_timestamp((SECONDS));
>
> Aggregation of that single record is of course done in  < 1 second.
>    2016-04-04          1
>
>
>
> I also tried
> select to_date(to_timestamp((SECONDS)), count(1)  from (
> select Int32Field1 as SECONDS
> from hdfs.`/data/`
> where Int32Field2=3D123456 or Int32Field2=3D4567898)
> group by o_date(to_timestamp((SECONDS))
>
> Same thing: After *21 hours*, SYSTEM ERROR: Foreman Exception: One more
> more nodes lost connectivity during query.
>
>
> Thanks for your help
> Francois
>

--047d7bf0d2d44026dc052fbcaefa--