You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Jean-Marc Spaggiari <je...@spaggiari.org> on 2014/09/29 15:15:44 UTC
CQ as the key?
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
Re: CQ as the key?
Posted by Jean-Marc Spaggiari <je...@spaggiari.org>.
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 <ja...@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
> <je...@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 <ja...@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
> >> <je...@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
> >
> >
>
Re: CQ as the key?
Posted by James Taylor <ja...@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
<je...@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 <ja...@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
>> <je...@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
>
>
Re: CQ as the key?
Posted by Jean-Marc Spaggiari <je...@spaggiari.org>.
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 <ja...@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
> <je...@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
>
Re: CQ as the key?
Posted by James Taylor <ja...@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
<je...@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