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 <[email protected]> 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 <[email protected]>: > >> 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 >> <[email protected]> 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 > >
