phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: CQ as the key?
Date Mon, 29 Sep 2014 18:26:07 GMT
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