phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jean-Marc Spaggiari <jean-m...@spaggiari.org>
Subject Re: CQ as the key?
Date Wed, 01 Oct 2014 19:16:50 GMT
Hum. I see. Goal was to be able to put into this table using HBase,and
query using Phoenix.

also, idea of having the last part of the key as the column qualifier is to
be able to have row atomic operations (Like for the deletes).

Given what you just described, I will think about the options we have.

Thanks,

JM

2014-09-29 14:26 GMT-04:00 James Taylor <jamestaylor@apache.org>:

> A secondary index ends up as another HBase table. Typically Phoenix
> maintains this other table behind the scenes, keeping it in sync with
> the data table as you make changes. However, in this case since your
> data table is a view, Phoenix is not going to know about change to
> your data table (as the updates won't go through Phoenix APIs). If you
> go the route of secondary indexes, you'd need to maintain the index
> table yourself if your data table changes. Phoenix would automatically
> use the index when the query optimizer deems that it'll perform better
> in doing so. For example, if your query filtered on s.W, then the
> index might be used.
>
> There's no other way than this to get a column qualifier into the row key.
>
> Thanks,
> James
>
> On Mon, Sep 29, 2014 at 10:31 AM, Jean-Marc Spaggiari
> <jean-marc@spaggiari.org> wrote:
> > Hi James.
> >
> > Even if the table already exist? I mean, when you create an index it
> > automatically consider it as being the CQ?
> >
> > Goal is to have the rowkey and the CQ as the primary key and being able
> to
> > run SQL queries with Phoenix on top of an existing HBase table. Using
> create
> > index, is it going create some extrenal data to manage that? Or is it
> just
> > going to re-use the existing CQ?
> >
> > Thanks,
> >
> > JM
> >
> > 2014-09-29 12:47 GMT-04:00 James Taylor <jamestaylor@apache.org>:
> >
> >> Hi JM,
> >> Yes, that's possible - it's more-or-less what a secondary index is. So
> >> you'd define your table as you did in your first CREATE TABLE
> >> statement, and then you'd define a secondary index like this:
> >>
> >> CREATE INDEX S_W_IDX ON "asset_metadata" (W);
> >>
> >> You could also include other columns in the index to make it a covered
> >> index:
> >>
> >> CREATE INDEX S_W_IDX ON "asset_metadata" (W)
> >>     INCLUDE (P, N, E, S, M, T);
> >>
> >> where you'd add the columns you'd likely also use when you filter on
> >> s.W in a WHERE clause. Depending on your use case, you might choose
> >> immutable/mutable and local/global - take a look here for more info:
> >> http://phoenix.apache.org/secondary_indexing.html
> >>
> >> Thanks,
> >> James
> >>
> >> On Mon, Sep 29, 2014 at 6:15 AM, Jean-Marc Spaggiari
> >> <jean-marc@spaggiari.org> wrote:
> >> > Hi,
> >> >
> >> > Can I have a column qualifier part of a key?
> >> >
> >> > Doing this I define columns based on the RowKey:
> >> >
> >> > create view "asset_metadata" (
> >> >
> >> >    L unsigned_long not null,
> >> >
> >> >    A unsigned_long not null,
> >> >
> >> >    R bigint not null,
> >> >
> >> >    "s".W unsigned_long,
> >> >
> >> >    "s".P bigint,
> >> >
> >> >    "s".N varchar,
> >> >
> >> >    "s".E varchar,
> >> >
> >> >    "s".S unsigned_long,
> >> >
> >> >    "s".M unsigned_long,
> >> >
> >> >    "s".T unsigned_int,
> >> >
> >> >    CONSTRAINT pk primary key (L,A,R)
> >> >
> >> >    );
> >> >
> >> > But I would like to also have one key field as a CQ.
> >> >
> >> > something like:
> >> >
> >> > create view "asset_metadata" (
> >> >
> >> >    L unsigned_long not null,
> >> >
> >> >    A unsigned_long not null,
> >> >
> >> >    R bigint not null,
> >> >
> >> >    "s".W unsigned_long,
> >> >
> >> >    "s".P bigint,
> >> >
> >> >    "s".N varchar,
> >> >
> >> >    "s".E varchar,
> >> >
> >> >    "s".S unsigned_long,
> >> >
> >> >    "s".M unsigned_long,
> >> >
> >> >    "s".T unsigned_int,
> >> >
> >> >    CONSTRAINT pk primary key (L,A,R,S:W)
> >> >
> >> >    );
> >> >
> >> > Is that doable? Is there a specific syntax for that?
> >> >
> >> > Thanks,
> >> >
> >> > JM
> >
> >
>

Mime
View raw message