impala-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alex Behm <alex.b...@cloudera.com>
Subject Re: WITH clause
Date Wed, 19 Oct 2016 18:17:13 GMT
I'm afraid there is no simple workaround other than NDV() or materializing
your WITH-clause manually with CREATE TABLE AS SELECT or similar.

On Wed, Oct 19, 2016 at 8:58 AM, Buntu Dev <buntudev@gmail.com> wrote:

> Thanks Alex.
>
> Heres my current query where I need multiple COUNT(DISTINCT()) but the
> only way currently is to get the data from the subquery:
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> WITH x AS
> ( SELECT col1, col2, col3
>   FROM t
>   WHERE <where clause> )
> SELECT COUNT(DISTINCT(col1))
> FROM x
> UNION ALL
> SELECT COUNT(DISTINCT(col2))
> FROM x
> UNION ALL
> SELECT COUNT(DISTINCT(col3))
> FROM x;
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> I can't use NVD() unfortunately, so if there is any work-around then
> please do let me know.
>
>
> Thanks!
>
>
> On Wed, Oct 19, 2016 at 6:40 AM, Alex Behm <alex.behm@cloudera.com> wrote:
>
>> Impala will execute the WITH-clause multiple times.
>>
>> See: https://issues.cloudera.org/browse/IMPALA-1719
>>
>> On Wed, Oct 19, 2016 at 2:02 AM, Buntu Dev <buntudev@gmail.com> wrote:
>>
>>> I got a subquery that is used more than once in a query. If I use the
>>> WITH clause for the subquery, does it help reduce the query time or does
>>> impala have inherent optimization where the subquery is only executed once
>>> and then reused everywhere it is used irrespective of the WITH clause?
>>> Please help clarify.
>>>
>>> Thanks!
>>>
>>
>>
>

Mime
View raw message