You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Oscar Morante <sp...@gmail.com> on 2016/07/20 11:06:48 UTC
Partition prunning using CURRENT_DATE?
I'm trying to trigger partition prunning like this:
select *
from dfs.`json/by-date`
where dir0 = cast(current_date as varchar);
But apparently, it only works when passing a literal. Am I missing
something?
Thanks,
--
Oscar Morante
"Self-education is, I firmly believe, the only kind of education there is."
-- Isaac Asimov.
Re: Partition prunning using CURRENT_DATE?
Posted by Oscar Morante <sp...@gmail.com>.
Great, thanks! I'm gonna try swapping the view file and see how it
goes.
On Thu, Jul 21, 2016 at 11:09:45AM -0500, John Omernik wrote:
>Yes, I have a view that has the hard coded date in it. It wasn't difficult,
>and using the REST API was actually fairly neat/clean. I agree with you,
>it would be nice, but this worked pretty well for me too. (I also wonder
>if you could just change the raw view def file and update the date)
>
>
>John
>
>
>On Thu, Jul 21, 2016 at 2:26 AM, Oscar Morante <sp...@gmail.com> wrote:
>
>> Hi John,
>> I've been following your trail of emails :) Thanks for sharing all that
>> info, it's very useful.
>>
>> I think I'm trying to do something very similar to what you did. I have
>> data flowing from Storm into S3 and I wanted to be able to periodically
>> preprocess/repartition into new folder and then have views to merge recent
>> data from the raw Storm files and old data from the
>> preprocessed/repartitioned folders. These views are intended to be used
>> from Tableau.
>>
>> I guess I can create a small process that checks when a new folder with
>> preprocessed data is available and replaces the appropriate view files with
>> new versions that have the proper date string. But it would be a lot nicer
>> to just do it in the view and have a dumb process executing the periodic
>> queries.
>>
>> How did you "solve" it in the end? If I can ask.
>>
>> Thanks,
>> Oscar
>>
>>
>> On Wed, Jul 20, 2016 at 07:26:20AM -0500, John Omernik wrote:
>>
>>> I think I ran into that issue before and (someone will correct me if I am
>>> wrong) the issue is that current_date is only materialized AFTER planning.
>>> Thus the pruning, which occurs during planning doesn't happen. Is this a
>>> programatic query or just something that is being done for users? I know
>>> my
>>> issue was I wanted a view that showed only the current date, and I
>>> struggled to come up with a good solution to that.
>>>
>>> John
>>>
>>>
>>> On Wed, Jul 20, 2016 at 6:06 AM, Oscar Morante <sp...@gmail.com>
>>> wrote:
>>>
>>> I'm trying to trigger partition prunning like this:
>>>>
>>>> select *
>>>> from dfs.`json/by-date`
>>>> where dir0 = cast(current_date as varchar);
>>>>
>>>> But apparently, it only works when passing a literal. Am I missing
>>>> something?
>>>>
>>>> Thanks,
>>>>
>>>
>>
--
Oscar Morante
"Self-education is, I firmly believe, the only kind of education there is."
-- Isaac Asimov.
Re: Partition prunning using CURRENT_DATE?
Posted by John Omernik <jo...@omernik.com>.
Yes, I have a view that has the hard coded date in it. It wasn't difficult,
and using the REST API was actually fairly neat/clean. I agree with you,
it would be nice, but this worked pretty well for me too. (I also wonder
if you could just change the raw view def file and update the date)
John
On Thu, Jul 21, 2016 at 2:26 AM, Oscar Morante <sp...@gmail.com> wrote:
> Hi John,
> I've been following your trail of emails :) Thanks for sharing all that
> info, it's very useful.
>
> I think I'm trying to do something very similar to what you did. I have
> data flowing from Storm into S3 and I wanted to be able to periodically
> preprocess/repartition into new folder and then have views to merge recent
> data from the raw Storm files and old data from the
> preprocessed/repartitioned folders. These views are intended to be used
> from Tableau.
>
> I guess I can create a small process that checks when a new folder with
> preprocessed data is available and replaces the appropriate view files with
> new versions that have the proper date string. But it would be a lot nicer
> to just do it in the view and have a dumb process executing the periodic
> queries.
>
> How did you "solve" it in the end? If I can ask.
>
> Thanks,
> Oscar
>
>
> On Wed, Jul 20, 2016 at 07:26:20AM -0500, John Omernik wrote:
>
>> I think I ran into that issue before and (someone will correct me if I am
>> wrong) the issue is that current_date is only materialized AFTER planning.
>> Thus the pruning, which occurs during planning doesn't happen. Is this a
>> programatic query or just something that is being done for users? I know
>> my
>> issue was I wanted a view that showed only the current date, and I
>> struggled to come up with a good solution to that.
>>
>> John
>>
>>
>> On Wed, Jul 20, 2016 at 6:06 AM, Oscar Morante <sp...@gmail.com>
>> wrote:
>>
>> I'm trying to trigger partition prunning like this:
>>>
>>> select *
>>> from dfs.`json/by-date`
>>> where dir0 = cast(current_date as varchar);
>>>
>>> But apparently, it only works when passing a literal. Am I missing
>>> something?
>>>
>>> Thanks,
>>>
>>
>
Re: Partition prunning using CURRENT_DATE?
Posted by Oscar Morante <sp...@gmail.com>.
Hi John,
I've been following your trail of emails :) Thanks for sharing all that
info, it's very useful.
I think I'm trying to do something very similar to what you did. I have
data flowing from Storm into S3 and I wanted to be able to periodically
preprocess/repartition into new folder and then have views to merge
recent data from the raw Storm files and old data from the
preprocessed/repartitioned folders. These views are intended to be used
from Tableau.
I guess I can create a small process that checks when a new folder with
preprocessed data is available and replaces the appropriate view files
with new versions that have the proper date string. But it would be a
lot nicer to just do it in the view and have a dumb process executing
the periodic queries.
How did you "solve" it in the end? If I can ask.
Thanks,
Oscar
On Wed, Jul 20, 2016 at 07:26:20AM -0500, John Omernik wrote:
>I think I ran into that issue before and (someone will correct me if I am
>wrong) the issue is that current_date is only materialized AFTER planning.
>Thus the pruning, which occurs during planning doesn't happen. Is this a
>programatic query or just something that is being done for users? I know my
>issue was I wanted a view that showed only the current date, and I
>struggled to come up with a good solution to that.
>
>John
>
>
>On Wed, Jul 20, 2016 at 6:06 AM, Oscar Morante <sp...@gmail.com> wrote:
>
>> I'm trying to trigger partition prunning like this:
>>
>> select *
>> from dfs.`json/by-date`
>> where dir0 = cast(current_date as varchar);
>>
>> But apparently, it only works when passing a literal. Am I missing
>> something?
>>
>> Thanks,
Re: Partition prunning using CURRENT_DATE?
Posted by John Omernik <jo...@omernik.com>.
I think I ran into that issue before and (someone will correct me if I am
wrong) the issue is that current_date is only materialized AFTER planning.
Thus the pruning, which occurs during planning doesn't happen. Is this a
programatic query or just something that is being done for users? I know my
issue was I wanted a view that showed only the current date, and I
struggled to come up with a good solution to that.
John
On Wed, Jul 20, 2016 at 6:06 AM, Oscar Morante <sp...@gmail.com> wrote:
> I'm trying to trigger partition prunning like this:
>
> select *
> from dfs.`json/by-date`
> where dir0 = cast(current_date as varchar);
>
> But apparently, it only works when passing a literal. Am I missing
> something?
>
> Thanks,
>
> --
> Oscar Morante
> "Self-education is, I firmly believe, the only kind of education there is."
> -- Isaac Asimov.
>