Simple and complex views

[Julian Hyde <jh...@apache.org>]

I was speaking with Rajesh and Ankit at DataWorks Summit today and they said that Phoenix views have to be “simple”, that is, made up of only SELECT … WHERE.

Is that the case? If so, are there any plans to lift the restriction, and allow views with, say, JOIN, UNION and GROUP BY?

The more complex views would not allow inserts/upserts/deletes, because a single row in the view would not necessarily correspond to a single row in the underlying table, but nevertheless, complex views are extremely useful for read-only queries. Calcite allows view expansion for complex views, and even for views upon views, so it should be straightforward to do in 5.0.

Julian

Re: Simple and complex views

[Julian Hyde <jh...@apache.org>]

Thanks, I've added some comments to
https://issues.apache.org/jira/browse/PHOENIX-1505.

I'd also like to understand why Phoenix needs indexes on views (most
DBMSs only allow indexes on base tables). We can cover that in the
same discussion.

On Fri, Jun 16, 2017 at 8:05 AM, James Taylor <ja...@apache.org> wrote:
> Yes, that's the case today (documented here[1]). The two relevant JIRAs are
> PHOENIX-1505 and PHOENIX-1506. It'd be great if someone was interested in
> pursuing these. If so, let's discuss on the JIRAs.
>
>     James
>
> [1] http://phoenix.apache.org/views.html#Limitations
>
> On Thu, Jun 15, 2017 at 9:20 PM Julian Hyde <jh...@apache.org> wrote:
>
>> I was speaking with Rajesh and Ankit at DataWorks Summit today and they
>> said that Phoenix views have to be “simple”, that is, made up of only
>> SELECT … WHERE.
>>
>> Is that the case? If so, are there any plans to lift the restriction, and
>> allow views with, say, JOIN, UNION and GROUP BY?
>>
>> The more complex views would not allow inserts/upserts/deletes, because a
>> single row in the view would not necessarily correspond to a single row in
>> the underlying table, but nevertheless, complex views are extremely useful
>> for read-only queries. Calcite allows view expansion for complex views, and
>> even for views upon views, so it should be straightforward to do in 5.0.
>>
>> Julian
>>
>>

Re: Simple and complex views

[James Taylor <ja...@apache.org>]

Yes, that's the case today (documented here[1]). The two relevant JIRAs are
PHOENIX-1505 and PHOENIX-1506. It'd be great if someone was interested in
pursuing these. If so, let's discuss on the JIRAs.

    James

[1] http://phoenix.apache.org/views.html#Limitations

On Thu, Jun 15, 2017 at 9:20 PM Julian Hyde <jh...@apache.org> wrote:

> I was speaking with Rajesh and Ankit at DataWorks Summit today and they
> said that Phoenix views have to be “simple”, that is, made up of only
> SELECT … WHERE.
>
> Is that the case? If so, are there any plans to lift the restriction, and
> allow views with, say, JOIN, UNION and GROUP BY?
>
> The more complex views would not allow inserts/upserts/deletes, because a
> single row in the view would not necessarily correspond to a single row in
> the underlying table, but nevertheless, complex views are extremely useful
> for read-only queries. Calcite allows view expansion for complex views, and
> even for views upon views, so it should be straightforward to do in 5.0.
>
> Julian
>
>