phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sergey Belousov <sergey.belou...@gmail.com>
Subject Re: Having difficulty to creating VIEW in Phoenix
Date Thu, 05 Mar 2015 15:31:57 GMT
Thanks James
It looks like we already have a JIRA for UNPIVOT functionality. Actually I
find two of them
https://issues.apache.org/jira/browse/PHOENIX-1665
https://issues.apache.org/jira/browse/PHOENIX-953

So I guess we can just start discussion there ?
Could you please comment on
https://issues.apache.org/jira/browse/PHOENIX-1665?focusedCommentId=14347350&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-14347350
so we can hash out what we want in UNSET in the first interaction. It fells
like it would be more manageable if we have separate JIRA for each function
under one umbrella but I may be wrong.

Regards,
S



On Tue, Mar 3, 2015 at 9:01 PM, James Taylor <jamestaylor@apache.org> wrote:

> bq. would I be able to do something like that for now?
> No, not now. Support for UNION ALL is a WIP and views are currently
> limited to only a simple, non aggregate query.
>
> Please feel free to make suggestions for syntax in the JIRA you'll
> file. We try to follow ANSI SQL standards as much as possible.
>
> FWIW, we're looking at integrating with Apache Calcite which has
> support for MATERIALIZED VIEWS, so stay tuned.
>
> Thanks,
> James
>
> On Tue, Mar 3, 2015 at 7:44 AM, Sergey Belousov
> <sergey.belousov@gmail.com> wrote:
> > Hi James,
> >
> > Thank you for the prompt reply.
> >
> > My thinking of VIEW  on top VIEW at this point was mainly about having
> one
> > view to have wide table mapping from HBase.
> > Than I wanted to turn it around and make tall VIEW on top of wide VIEW .
> > Basically to have
> > key | timestamp | value (where timestamp is epoch + hour from cq and
> value
> > is a counter from that cq.) I am ok with going tall from the beginning
> but I
> > could not make it work
> >
> > Than I was thinking to try to do some aggregation on top of that tall
> VIEW.
> >
> > So lets say I have view like that:
> >
> > CREATE VIEW IF NOT EXISTS existent_hbase_table_name (
> > f1 UNSIGNED_INT NOT NULL,
> > f2 UNSIGNED_INT NOT NULL,
> > f3 UNSIGNED_INT NOT NULL,
> > timestamp UNSIGNED_LONG NOT NULL,
> > "h"."00T" UNSIGNED_LONG,
> > "h"."01T" UNSIGNED_LONG
> > CONSTRAINT pk PRIMARY KEY (f1, f2, f3, timestamp)
> > );
> >
> > How I can create VIEW where I make timestamp = timestamp+h*3600 and
> project
> > value from "h"."00T" (see my original post for more details)
> >
> > would I be able to do something like that for now?
> >
> > CREATE VIEW view_tall (f1, f2, f3, aggr_window, timestamp, cnt) AS
> > SELECT f1, f2, f3, 'daily' AS aggr_window, timestamp + 3600*1, h.00T AS
> cnt
> > FROM view_create_on_top_of_existent_hbase_table
> > UNION ALL
> > SELECT f1, f2, f3, 'daily' AS aggr_window, timestamp + 3600*2, h.01T AS
> cnt
> > FROM view_create_on_top_of_existent_hbase_table
> > UNION ALL
> > SELECT f1, f2, f3, 'daily' AS aggr_window, timestamp + 3600*2, h.01T AS
> cnt
> > FROM view_create_on_top_of_existent_hbase_table
> > ....
> >
> > Of course in the future UNPIVOT should help to avoid all that UNION thing
> > and in perfect world would give you functionality to specify column list
> > (regex) and how to turn in in to the row (separate collumn or make it
> part
> > of existent one (like in my case with timestamp)
> >
> > I will create JIRA. It looks like in Phoenix you guys model your grammar
> > base of PostgreSQL. Is that what you would like to see in that JIRA or I
> can
> > make suggestion base on grama from other SQLs? I definitely would be
> excited
> > to contribute but I do not feel like I have yet enough knowledge to do it
> > right and not to bug people with my questions (what is that, how to do
> this,
> > where are those and so on :)  I am still on reading all mailing
> > lists/code/docs and learning. My thinking about UNPIVOT was that
> basically
> > when you have HBase schema with key (or part of the key) as timestamp and
> > some aggregated numbers (hourly, daily, monthly) in different column
> > families (not that rare use of hbase) you basically should be able to
> turn
> > it around in very efficient way (since you can get all data for a
> day/month
> > in one get/or scans rows that are next for each other) and than project
> it
> > through phoenix as multiple rows applying expression specified in UNPIVOT
> > command.
> >
> > As for transforming data to another table I would say it would be
> something
> > you should specify during CREATE VIEW. I personally would try to model
> view
> > with thinking that I can have my main cases working well with just VIEW
> > (basically running scans against hbase table) and than if there is a
> problem
> > may be to have an option to have what Oracle call MATERIALIZED VIEWS
> >
> > My only motivation for this was to expose this in BI tool.
> >
> > Thank you
> > S
> >
> > On Tue, Mar 3, 2015 at 1:08 AM, James Taylor <jamestaylor@apache.org>
> wrote:
> >>
> >> 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