You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@drill.apache.org by Divya Gehlot <di...@gmail.com> on 2018/10/11 07:40:12 UTC

Performance issue while fetching the latest data in multi directory hierarchy env

Hi,
I have data in below hierarchy
Date\
   Hour of the day \
          04
          08
          12
           .....
          60

I used the query like below
Select * from `schema_name`.`./path/to/directory/` where
dir0 = MAXDIR( `schema_name`.`./path/to/directory/`)
and its taking 3-5 minutes while fetching the data .

Then I tried another solution as below :
WITH t1 AS
(SELECT * FROM `schema_name`.`. ./path/to/directory/`
where dir0 = CURRENT_DATE )
SELECT *
   FROM t1  where dir1 = MAXDIR(`schema_name`.`. ./path/to/directory/`  ')
But this gives me empty result set and MAXDIR can pick up data only one
level deep.

Appreciate the advise/solution for it !

Thanks,
Divya

Re: Performance issue while fetching the latest data in multi directory hierarchy env

Posted by Divya Gehlot <di...@gmail.com>.

apologies on MAXDIR syntax
The correct syntax is MAXDIR( 'schema_name','./path/to/directory/')



On Thu, 11 Oct 2018 at 15:40, Divya Gehlot <di...@gmail.com> wrote:

> Hi,
> I have data in below hierarchy
> Date\
>    Hour of the day \
>           04
>           08
>           12
>            .....
>           60
>
> I used the query like below
> Select * from `schema_name`.`./path/to/directory/` where
> dir0 = MAXDIR( `schema_name`.`./path/to/directory/`)
> and its taking 3-5 minutes while fetching the data .
>
> Then I tried another solution as below :
> WITH t1 AS
> (SELECT * FROM `schema_name`.`. ./path/to/directory/`
> where dir0 = CURRENT_DATE )
> SELECT *
>    FROM t1  where dir1 = MAXDIR(`schema_name`.`. ./path/to/directory/`  ')
> But this gives me empty result set and MAXDIR can pick up data only one
> level deep.
>
> Appreciate the advise/solution for it !
>
> Thanks,
> Divya
>
>