phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: Having difficulty to creating VIEW in Phoenix
Date Tue, 03 Mar 2015 06:08:08 GMT
Hi Sergey,
It's possible to create a VIEW on a VIEW, but we don't support VIEWs
over aggregate queries current. It would not be that difficult to add
- if that's important for your use case, maybe you'd be interested in
contributing that?

As far as UNPIVOT, that's not really on our radar currently, but
please file a JIRA. Were you thinking to transform the data into
another table so you can efficiently query? Would adding a secondary
index help - note that as of 4.3, we support functional indexes, so
that may be something that helps you. If you did the UNPIVOT on a VIEW
directly, your query performance will not really improve unless you go
the route of a secondary index.

What your main motivation for being able to create these views and
unpivot? Will you expose these views in some find of BI tool?

Thanks,
James

On Mon, Mar 2, 2015 at 6:42 PM, Sergey Belousov
<sergey.belousov@gmail.com> wrote:
> Hi All
>
> Hope you guys can help me little bit with this one.
> I have a table in HBase with following structure (simplified)
>
> key: epoch in seconds rounded to the day.
> key <k1-4byte><k2-4byte><ts><k3-4byte>
> cf:  d
> cq:  0..23 (hourly counters)
>
> I have no problem to create horizontal VIEW so I can do query like
> SELECT k1, k2, ts, 01,02,03...23 FROM t1 GROUP BY
>
> but I would like an option to have a VIEW in phoenix where I will build ts
> by doing ts+hour*3600 (and may be even convert it to DATE)
> so I can have
>
> SELECT k1, k2, ts, count() FROM t1 GROUP BY ... what would resulted in 24
> records per day with ts being DDMMYYY hh:00 - (basically turn horizontal
> table to vertical)
> or
> SELECT k1, k2, ts, hour, count() FROM t1 GROUP BY ...
>
> Normally in my old days in SQL I could use some UNPIVOT or CROSS APPLY
> (depends on RDBMS) or simply do some union like
> SELECT k1, k2, ts+1*3600 AS EPOCH, h01 as COUNT_PER_HOUR  FROM t1
> UNION ALL
> SELECT k1, k2, ts+2*3600 AS EPOCH, h02 as COUNT_PER_HOUR  FROM t1
> ...
> UNION ALL
> SELECT k1, k2, ts+23*3600 AS EPOCH, h23 as COUNT_PER_HOUR  FROM t1
>
> but it does not look like I can do it in phoenix
>   #1 it does not look like I can create VIEW of the VIEW
>   #2 there is no UNION (yes I know work is happening on UNION ALL now)
>
> I hope someone has an idea how I can do it in phoenix with what we have now
> and if it even possible.
> An if not what will it take to make that happens? Even if lets say we got
> UNION ALL would it be enough end how well those kind of queries will live in
> phoenix?
>
> Would it be insane to even dream about lets say UNPIVOT when we would be
> able to point it to cf:regexstring (for selecting list of columns)
> and turn it into row ? Even just UNPIVOT (value for cell in
> (h:01,h:02...h:23) unpiv; What kind of amount of work we are looking for to
> implement something like this ?
>
>
> Thank you,
> S

Mime
View raw message