You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Nick Dimiduk <nd...@gmail.com> on 2019/05/13 21:18:47 UTC
Error using VARBINARY in Index
Hello,
I'm experimenting with a new data model using secondary indices and
stumbled into a surprise. My understanding is that arrays and VARBINARY are
permitted in rowkeys so long as they are the trailing rowkey member. Why is
this schema definition is not permitted?
CREATE TABLE IF NOT EXISTS t
(
a VARCHAR NOT NULL,
b VARBINARY
CONSTRAINT pk PRIMARY KEY (a)
);
CREATE INDEX IF NOT EXISTS t_index ON t(b) INCLUDE (a);
> Error: ERROR 1005 (42J03): The VARBINARY/ARRAY type can only be used as
the last part of a multi-part row key. columnName=T_INDEX.0:B
(state=42J03,code=1005)
I get the same error when adding an additional, non-null column to the
index definition.
CREATE TABLE IF NOT EXISTS t
(
a VARCHAR NOT NULL,
b VARCHAR NOT NULL,
c VARBINARY
CONSTRAINT pk PRIMARY KEY (a, b)
)
;
CREATE INDEX IF NOT EXISTS t_index ON t(b, c) INCLUDE (a);
> Error: ERROR 1005 (42J03): The VARBINARY/ARRAY type can only be used as
the last part of a multi-part row key. columnName=T_INDEX.0:C
(state=42J03,code=1005)
This is apache-phoenix-4.14.1-HBase-1.4.
Thanks,
Nick
Re: Error using VARBINARY in Index
Posted by Thomas D'Silva <td...@salesforce.com.INVALID>.
IndexMaintainer.buildUpdateMutation() is called from the client side for
immutable indexes and from the indexing coprocessor as well.
On Mon, May 13, 2019 at 5:52 PM Nick Dimiduk <nd...@gmail.com> wrote:
> -user, +dev
>
> I’d like to take a pass at updating our secondary index docs with the next
> level of details. Understanding data layout is important when designing an
> HBase schema, so I think users will generally be interested in how
> secondary indexes do that.
>
> Can someone point me in the direction of where the index data edits are
> created in code? I spent some time looking around but it’s not obvious to
> me. I’m looking at the 4.14.1-hbase-1.4 tag.
>
> Thanks,
> Nick
>
> On Mon, May 13, 2019 at 3:49 PM Nick Dimiduk <nd...@gmail.com> wrote:
>
> > Hi Pedro,
> >
> > Indeed, some restrictions do apply :)
> >
> > I’ve been experimenting with a local index, apologies for not mentioning
> > that initially. Though it seems the same restrictions apply either way.
> > What’s not obvious to the user is how the VARBINARY value ends up as a
> > non-trailing element in the rowkey — looking at the DDL, it seems
> perfectly
> > legal. I eventually found Rajeshbabu’s presentation from 2017 [0], which
> > includes some concrete details on slide 7. From his slide, I see that the
> > primary key columns are appended, thus making the VARBINARY non-trailing.
> >
> > Thanks,
> > Nick
> >
> > [0]:
> >
> >
> https://www.slideshare.net/rajeshbabuchintaguntla/local-secondary-indexes-in-apache-phoenix
> >
> > On Mon, May 13, 2019 at 3:10 PM Pedro Boado <pe...@gmail.com>
> wrote:
> >
> >> Hi,
> >>
> >> Indexes in Phoenix are implemented using an additional HBase table, and
> >> the index key fields are serialized as HBase table key.
> >>
> >> So same limitations apply to varbinary and varchar when used as index
> >> columns: they can only be used as the last column in the index key.
> >>
> >> Cheers,
> >> Pedro.
> >>
> >> On Mon, 13 May 2019, 22:19 Nick Dimiduk, <nd...@gmail.com> wrote:
> >>
> >>> Hello,
> >>>
> >>> I'm experimenting with a new data model using secondary indices and
> >>> stumbled into a surprise. My understanding is that arrays and
> VARBINARY are
> >>> permitted in rowkeys so long as they are the trailing rowkey member.
> Why is
> >>> this schema definition is not permitted?
> >>>
> >>> CREATE TABLE IF NOT EXISTS t
> >>> (
> >>> a VARCHAR NOT NULL,
> >>> b VARBINARY
> >>> CONSTRAINT pk PRIMARY KEY (a)
> >>> );
> >>> CREATE INDEX IF NOT EXISTS t_index ON t(b) INCLUDE (a);
> >>>
> >>> > Error: ERROR 1005 (42J03): The VARBINARY/ARRAY type can only be used
> >>> as the last part of a multi-part row key. columnName=T_INDEX.0:B
> >>> (state=42J03,code=1005)
> >>>
> >>> I get the same error when adding an additional, non-null column to the
> >>> index definition.
> >>>
> >>> CREATE TABLE IF NOT EXISTS t
> >>> (
> >>> a VARCHAR NOT NULL,
> >>> b VARCHAR NOT NULL,
> >>> c VARBINARY
> >>> CONSTRAINT pk PRIMARY KEY (a, b)
> >>> )
> >>> ;
> >>> CREATE INDEX IF NOT EXISTS t_index ON t(b, c) INCLUDE (a);
> >>>
> >>> > Error: ERROR 1005 (42J03): The VARBINARY/ARRAY type can only be used
> >>> as the last part of a multi-part row key. columnName=T_INDEX.0:C
> >>> (state=42J03,code=1005)
> >>>
> >>> This is apache-phoenix-4.14.1-HBase-1.4.
> >>>
> >>> Thanks,
> >>> Nick
> >>>
> >>
>
Re: Error using VARBINARY in Index
Posted by Nick Dimiduk <nd...@gmail.com>.
-user, +dev
I’d like to take a pass at updating our secondary index docs with the next
level of details. Understanding data layout is important when designing an
HBase schema, so I think users will generally be interested in how
secondary indexes do that.
Can someone point me in the direction of where the index data edits are
created in code? I spent some time looking around but it’s not obvious to
me. I’m looking at the 4.14.1-hbase-1.4 tag.
Thanks,
Nick
On Mon, May 13, 2019 at 3:49 PM Nick Dimiduk <nd...@gmail.com> wrote:
> Hi Pedro,
>
> Indeed, some restrictions do apply :)
>
> I’ve been experimenting with a local index, apologies for not mentioning
> that initially. Though it seems the same restrictions apply either way.
> What’s not obvious to the user is how the VARBINARY value ends up as a
> non-trailing element in the rowkey — looking at the DDL, it seems perfectly
> legal. I eventually found Rajeshbabu’s presentation from 2017 [0], which
> includes some concrete details on slide 7. From his slide, I see that the
> primary key columns are appended, thus making the VARBINARY non-trailing.
>
> Thanks,
> Nick
>
> [0]:
>
> https://www.slideshare.net/rajeshbabuchintaguntla/local-secondary-indexes-in-apache-phoenix
>
> On Mon, May 13, 2019 at 3:10 PM Pedro Boado <pe...@gmail.com> wrote:
>
>> Hi,
>>
>> Indexes in Phoenix are implemented using an additional HBase table, and
>> the index key fields are serialized as HBase table key.
>>
>> So same limitations apply to varbinary and varchar when used as index
>> columns: they can only be used as the last column in the index key.
>>
>> Cheers,
>> Pedro.
>>
>> On Mon, 13 May 2019, 22:19 Nick Dimiduk, <nd...@gmail.com> wrote:
>>
>>> Hello,
>>>
>>> I'm experimenting with a new data model using secondary indices and
>>> stumbled into a surprise. My understanding is that arrays and VARBINARY are
>>> permitted in rowkeys so long as they are the trailing rowkey member. Why is
>>> this schema definition is not permitted?
>>>
>>> CREATE TABLE IF NOT EXISTS t
>>> (
>>> a VARCHAR NOT NULL,
>>> b VARBINARY
>>> CONSTRAINT pk PRIMARY KEY (a)
>>> );
>>> CREATE INDEX IF NOT EXISTS t_index ON t(b) INCLUDE (a);
>>>
>>> > Error: ERROR 1005 (42J03): The VARBINARY/ARRAY type can only be used
>>> as the last part of a multi-part row key. columnName=T_INDEX.0:B
>>> (state=42J03,code=1005)
>>>
>>> I get the same error when adding an additional, non-null column to the
>>> index definition.
>>>
>>> CREATE TABLE IF NOT EXISTS t
>>> (
>>> a VARCHAR NOT NULL,
>>> b VARCHAR NOT NULL,
>>> c VARBINARY
>>> CONSTRAINT pk PRIMARY KEY (a, b)
>>> )
>>> ;
>>> CREATE INDEX IF NOT EXISTS t_index ON t(b, c) INCLUDE (a);
>>>
>>> > Error: ERROR 1005 (42J03): The VARBINARY/ARRAY type can only be used
>>> as the last part of a multi-part row key. columnName=T_INDEX.0:C
>>> (state=42J03,code=1005)
>>>
>>> This is apache-phoenix-4.14.1-HBase-1.4.
>>>
>>> Thanks,
>>> Nick
>>>
>>
Re: Error using VARBINARY in Index
Posted by Nick Dimiduk <nd...@gmail.com>.
Hi Pedro,
Indeed, some restrictions do apply :)
I’ve been experimenting with a local index, apologies for not mentioning
that initially. Though it seems the same restrictions apply either way.
What’s not obvious to the user is how the VARBINARY value ends up as a
non-trailing element in the rowkey — looking at the DDL, it seems perfectly
legal. I eventually found Rajeshbabu’s presentation from 2017 [0], which
includes some concrete details on slide 7. From his slide, I see that the
primary key columns are appended, thus making the VARBINARY non-trailing.
Thanks,
Nick
[0]:
https://www.slideshare.net/rajeshbabuchintaguntla/local-secondary-indexes-in-apache-phoenix
On Mon, May 13, 2019 at 3:10 PM Pedro Boado <pe...@gmail.com> wrote:
> Hi,
>
> Indexes in Phoenix are implemented using an additional HBase table, and
> the index key fields are serialized as HBase table key.
>
> So same limitations apply to varbinary and varchar when used as index
> columns: they can only be used as the last column in the index key.
>
> Cheers,
> Pedro.
>
> On Mon, 13 May 2019, 22:19 Nick Dimiduk, <nd...@gmail.com> wrote:
>
>> Hello,
>>
>> I'm experimenting with a new data model using secondary indices and
>> stumbled into a surprise. My understanding is that arrays and VARBINARY are
>> permitted in rowkeys so long as they are the trailing rowkey member. Why is
>> this schema definition is not permitted?
>>
>> CREATE TABLE IF NOT EXISTS t
>> (
>> a VARCHAR NOT NULL,
>> b VARBINARY
>> CONSTRAINT pk PRIMARY KEY (a)
>> );
>> CREATE INDEX IF NOT EXISTS t_index ON t(b) INCLUDE (a);
>>
>> > Error: ERROR 1005 (42J03): The VARBINARY/ARRAY type can only be used as
>> the last part of a multi-part row key. columnName=T_INDEX.0:B
>> (state=42J03,code=1005)
>>
>> I get the same error when adding an additional, non-null column to the
>> index definition.
>>
>> CREATE TABLE IF NOT EXISTS t
>> (
>> a VARCHAR NOT NULL,
>> b VARCHAR NOT NULL,
>> c VARBINARY
>> CONSTRAINT pk PRIMARY KEY (a, b)
>> )
>> ;
>> CREATE INDEX IF NOT EXISTS t_index ON t(b, c) INCLUDE (a);
>>
>> > Error: ERROR 1005 (42J03): The VARBINARY/ARRAY type can only be used as
>> the last part of a multi-part row key. columnName=T_INDEX.0:C
>> (state=42J03,code=1005)
>>
>> This is apache-phoenix-4.14.1-HBase-1.4.
>>
>> Thanks,
>> Nick
>>
>
Re: Error using VARBINARY in Index
Posted by Pedro Boado <pe...@gmail.com>.
Hi,
Indexes in Phoenix are implemented using an additional HBase table, and the
index key fields are serialized as HBase table key.
So same limitations apply to varbinary and varchar when used as index
columns: they can only be used as the last column in the index key.
Cheers,
Pedro.
On Mon, 13 May 2019, 22:19 Nick Dimiduk, <nd...@gmail.com> wrote:
> Hello,
>
> I'm experimenting with a new data model using secondary indices and
> stumbled into a surprise. My understanding is that arrays and VARBINARY are
> permitted in rowkeys so long as they are the trailing rowkey member. Why is
> this schema definition is not permitted?
>
> CREATE TABLE IF NOT EXISTS t
> (
> a VARCHAR NOT NULL,
> b VARBINARY
> CONSTRAINT pk PRIMARY KEY (a)
> );
> CREATE INDEX IF NOT EXISTS t_index ON t(b) INCLUDE (a);
>
> > Error: ERROR 1005 (42J03): The VARBINARY/ARRAY type can only be used as
> the last part of a multi-part row key. columnName=T_INDEX.0:B
> (state=42J03,code=1005)
>
> I get the same error when adding an additional, non-null column to the
> index definition.
>
> CREATE TABLE IF NOT EXISTS t
> (
> a VARCHAR NOT NULL,
> b VARCHAR NOT NULL,
> c VARBINARY
> CONSTRAINT pk PRIMARY KEY (a, b)
> )
> ;
> CREATE INDEX IF NOT EXISTS t_index ON t(b, c) INCLUDE (a);
>
> > Error: ERROR 1005 (42J03): The VARBINARY/ARRAY type can only be used as
> the last part of a multi-part row key. columnName=T_INDEX.0:C
> (state=42J03,code=1005)
>
> This is apache-phoenix-4.14.1-HBase-1.4.
>
> Thanks,
> Nick
>