You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Anil <an...@gmail.com> on 2016/08/24 05:43:59 UTC
Extracting column values from Phoenix composite primary key
Hi,
I have created primary key with columns in phoenix.
is there any way to extract the column values from hbase row key ? Please
help.
Thanks,
Anil
Re: Extracting column values from Phoenix composite primary key
Posted by Anil <an...@gmail.com>.
Thanks James.
I am able to extract columns using row schema.
On 31 August 2016 at 02:50, James Taylor <ja...@apache.org> wrote:
> Anil,
> Phoenix's API is JDBC, so just be aware that you're embarking on usage of
> unsupported and mostly undocumented APIs. Not to say they will change, but
> they can and might. It'll require you looking at source code, running it in
> a debugger, and some trial and error to use these.
>
> For walking through a byte[] row key to get the primary key column values,
> use RowKeySchema and in particular the iterator methods. Lots of examples
> in the code. You can access the RowKeySchema from PTable which is the
> object representation of a table in Phoenix.
>
> For the inverse, building the byte[] of a row key, just use the UPSERT
> VALUES statement where you're setting the value of all primary key columns.
> Then you can use the PhoenixRuntime.getUncommittedDataIterator() call to
> get the List<KeyValue> that will be sent to the server (afterward you can
> do a connection.rollback()).
>
> HTH. Thanks,
>
> James
>
> On Tue, Aug 30, 2016 at 1:49 PM, Michael McAllister <
> mmcallister@homeaway.com> wrote:
>
>> Not yet sorry.
>>
>>
>>
>> Michael McAllister
>>
>> Staff Data Warehouse Engineer | Decision Systems
>>
>> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
>> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>>
>> This electronic communication (including any attachment) is
>> confidential. If you are not an intended recipient of this communication,
>> please be advised that any disclosure, dissemination, distribution, copying
>> or other use of this communication or any attachment is strictly
>> prohibited. If you have received this communication in error, please
>> notify the sender immediately by reply e-mail and promptly destroy all
>> electronic and printed copies of this communication and any attachment.
>>
>>
>>
>> *From: *Anil <an...@gmail.com>
>> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>> *Date: *Monday, August 29, 2016 at 9:48 PM
>>
>> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>> *Subject: *Re: Extracting column values from Phoenix composite primary
>> key
>>
>>
>>
>> HI Michael and All ,
>>
>>
>>
>> Did you get a chance to look into this ? Thanks.
>>
>>
>>
>>
>>
>> Thanks.
>>
>>
>>
>>
>>
>> On 26 August 2016 at 07:38, Anil <an...@gmail.com> wrote:
>>
>> HI Michael,
>>
>>
>>
>> Following the table create and upsert query -
>>
>>
>>
>> CREATE TABLE SAMPLE(TYPE VARCHAR NOT NULL, SOURCE VARCHAR NOT NULL, LABEL
>> VARCHAR NOT NULL, DIRECTION VARCHAR(10) NOT NULL, REVERSETIME UNSIGNED_LONG
>> NOT NULL, TARGET VARCHAR,"cf".ID VARCHAR, CONSTRAINT pk PRIMARY
>> KEY(TYPE,SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET)) COMPRESSION =
>> 'SNAPPY';
>>
>>
>>
>> upsert into SAMPLE(TYPE, SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET,
>> ID) values('test', 'src', 'label', 'direction', 134424245, 'target', 'id');
>>
>> .
>>
>> Thanks
>>
>>
>>
>>
>>
>> On 25 August 2016 at 20:50, Michael McAllister <mm...@homeaway.com>
>> wrote:
>>
>> Can you please provide the sample rowkey? It is blank in your email
>> below. Alternatively, an UPSERT VALUES statement I can use to insert a row
>> that I can work with myself.
>>
>>
>>
>> Michael McAllister
>>
>> Staff Data Warehouse Engineer | Decision Systems
>>
>> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
>> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>>
>> This electronic communication (including any attachment) is
>> confidential. If you are not an intended recipient of this communication,
>> please be advised that any disclosure, dissemination, distribution, copying
>> or other use of this communication or any attachment is strictly
>> prohibited. If you have received this communication in error, please
>> notify the sender immediately by reply e-mail and promptly destroy all
>> electronic and printed copies of this communication and any attachment.
>>
>>
>>
>> *From: *Anil <an...@gmail.com>
>> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>> *Date: *Thursday, August 25, 2016 at 10:08 AM
>> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>> *Subject: *Re: Extracting column values from Phoenix composite primary
>> key
>>
>>
>>
>> HI Michael,
>>
>>
>>
>> Table create statement :
>>
>>
>>
>> CREATE TABLE SAMPLE(TYPE VARCHAR NOT NULL, SOURCE VARCHAR NOT NULL, LABEL
>> VARCHAR NOT NULL, DIRECTION VARCHAR(10) NOT NULL, REVERSETIME UNSIGNED_LONG
>> NOT NULL, TARGET VARCHAR,"CF".ID VARCHAR,CONSTRAINT PK PRIMARY
>> KEY(TYPE,,SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET)) COMPRESSION =
>> 'SNAPPY'
>>
>>
>>
>> No salt buckets defined.
>>
>>
>>
>> Smaple table row key -
>>
>>
>>
>> byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes("test"),
>>
>> QueryConstants.SEPARATOR_BYTE_ARRAY,
>>
>> PVarchar.INSTANCE.toBytes("src"),
>>
>> QueryConstants.SEPARATOR_BYTE_ARRAY,
>>
>> PVarchar.INSTANCE.toBytes("label"),
>>
>> QueryConstants.SEPARATOR_BYTE_ARRAY,
>>
>> PVarchar.INSTANCE.toBytes("direction"),
>>
>> QueryConstants.SEPARATOR_BYTE_ARRAY,
>>
>> PUnsignedLong.INSTANCE.toBytes(1235464603853L),
>>
>> PVarchar.INSTANCE.toBytes("target"));
>>
>>
>>
>> i am trying to extract TARGET column. Thanks.
>>
>>
>>
>> Regards,
>>
>> Anil
>>
>>
>>
>> On 25 August 2016 at 19:29, Michael McAllister <mm...@homeaway.com>
>> wrote:
>>
>> Can you provide an example of one of the rowkeys, the values you are
>> expecting out of it, and the full table definition? Of importance in the
>> table definition will be whether you have salt buckets defined.
>>
>>
>>
>> Michael McAllister
>>
>> Staff Data Warehouse Engineer | Decision Systems
>>
>> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
>> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>>
>> This electronic communication (including any attachment) is
>> confidential. If you are not an intended recipient of this communication,
>> please be advised that any disclosure, dissemination, distribution, copying
>> or other use of this communication or any attachment is strictly
>> prohibited. If you have received this communication in error, please
>> notify the sender immediately by reply e-mail and promptly destroy all
>> electronic and printed copies of this communication and any attachment.
>>
>>
>>
>> *From: *Anil <an...@gmail.com>
>> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>> *Date: *Thursday, August 25, 2016 at 1:09 AM
>> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>> *Subject: *Re: Extracting column values from Phoenix composite primary
>> key
>>
>>
>>
>> HI Michael,
>>
>>
>>
>> Thank you for the response.
>>
>>
>>
>> Unfortunately, that is not working.
>>
>>
>>
>> Following is the snippet. one of the column is unsigned long and trying
>> to extract the last column (with value target in the below code).
>>
>> please correct me if i am doing wrong.
>>
>>
>>
>> byte SEPARATOR_BYTE = 0;
>>
>> byte[] SEPARATOR_BYTE_ARRAY = { 0 };
>>
>> byte[] startRow = ByteUtil.concat(
>>
>> PVarchar.INSTANCE.toBytes("test"),
>>
>> QueryConstants.SEPARATOR_BYTE_ARRAY,
>>
>> PVarchar.INSTANCE.toBytes("src"),
>>
>> QueryConstants.SEPARATOR_BYTE_ARRAY,
>>
>> PVarchar.INSTANCE.toBytes("label"),
>>
>> QueryConstants.SEPARATOR_BYTE_ARRAY,
>>
>> PVarchar.INSTANCE.toBytes("direction"),
>>
>> QueryConstants.SEPARATOR_BYTE_ARRAY,
>>
>> PUnsignedLong.INSTANCE.toBytes(1235464603853L),
>>
>> PVarchar.INSTANCE.toBytes("target"));
>>
>>
>>
>>
>>
>> List<String> cols = new ArrayList<String>();
>>
>> int j = 0;
>>
>> for (int i= 0 ; i < startRow.length ; i++){
>>
>> if (startRow[i] == SEPARATOR_BYTE){
>>
>> cols.add(Bytes.toString(startRow, j, i-j));
>>
>> j = i+1;
>>
>> }
>>
>> }
>>
>>
>>
>> cols.add(Bytes.toString(startRow, j, startRow.length - j));
>>
>>
>>
>> System.out.println(cols.size());
>>
>>
>>
>> String rowKey = Bytes.toString(startRow);
>>
>> String[] columns = rowKey.split("\u0000");
>>
>>
>>
>> System.out.println(columns.length);
>>
>>
>>
>> last entry in the array has special character too along with actual value.
>>
>>
>>
>> Can you point out the bug in the above code if any. Thanks.
>>
>>
>>
>> Regards,
>>
>> Anil
>>
>>
>>
>>
>>
>> On 25 August 2016 at 02:32, Michael McAllister <mm...@homeaway.com>
>> wrote:
>>
>> Anil
>>
>>
>>
>> If you split the rowkey on the zero byte character, you should end up
>> with the individual columns that made up your primary key. Details are
>> here:-
>>
>>
>>
>> https://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_
>> to_an_existing_HBase_table
>>
>>
>>
>> Michael McAllister
>>
>> Staff Data Warehouse Engineer | Decision Systems
>>
>> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
>> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>>
>> This electronic communication (including any attachment) is
>> confidential. If you are not an intended recipient of this communication,
>> please be advised that any disclosure, dissemination, distribution, copying
>> or other use of this communication or any attachment is strictly
>> prohibited. If you have received this communication in error, please
>> notify the sender immediately by reply e-mail and promptly destroy all
>> electronic and printed copies of this communication and any attachment.
>>
>>
>>
>> *From: *Anil <an...@gmail.com>
>> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>> *Date: *Wednesday, August 24, 2016 at 4:10 AM
>> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>> *Subject: *Re: Extracting column values from Phoenix composite primary
>> key
>>
>>
>>
>> Any inputs ? Thanks.
>>
>>
>>
>> On 24 August 2016 at 11:13, Anil <an...@gmail.com> wrote:
>>
>> Hi,
>>
>>
>>
>> I have created primary key with columns in phoenix.
>>
>> is there any way to extract the column values from hbase row key ? Please
>> help.
>>
>>
>>
>> Thanks,
>>
>> Anil
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
Re: Extracting column values from Phoenix composite primary key
Posted by James Taylor <ja...@apache.org>.
Anil,
Phoenix's API is JDBC, so just be aware that you're embarking on usage of
unsupported and mostly undocumented APIs. Not to say they will change, but
they can and might. It'll require you looking at source code, running it in
a debugger, and some trial and error to use these.
For walking through a byte[] row key to get the primary key column values,
use RowKeySchema and in particular the iterator methods. Lots of examples
in the code. You can access the RowKeySchema from PTable which is the
object representation of a table in Phoenix.
For the inverse, building the byte[] of a row key, just use the UPSERT
VALUES statement where you're setting the value of all primary key columns.
Then you can use the PhoenixRuntime.getUncommittedDataIterator() call to
get the List<KeyValue> that will be sent to the server (afterward you can
do a connection.rollback()).
HTH. Thanks,
James
On Tue, Aug 30, 2016 at 1:49 PM, Michael McAllister <
mmcallister@homeaway.com> wrote:
> Not yet sorry.
>
>
>
> Michael McAllister
>
> Staff Data Warehouse Engineer | Decision Systems
>
> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>
> This electronic communication (including any attachment) is confidential.
> If you are not an intended recipient of this communication, please be
> advised that any disclosure, dissemination, distribution, copying or other
> use of this communication or any attachment is strictly prohibited. If you
> have received this communication in error, please notify the sender
> immediately by reply e-mail and promptly destroy all electronic and printed
> copies of this communication and any attachment.
>
>
>
> *From: *Anil <an...@gmail.com>
> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Date: *Monday, August 29, 2016 at 9:48 PM
>
> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Subject: *Re: Extracting column values from Phoenix composite primary key
>
>
>
> HI Michael and All ,
>
>
>
> Did you get a chance to look into this ? Thanks.
>
>
>
>
>
> Thanks.
>
>
>
>
>
> On 26 August 2016 at 07:38, Anil <an...@gmail.com> wrote:
>
> HI Michael,
>
>
>
> Following the table create and upsert query -
>
>
>
> CREATE TABLE SAMPLE(TYPE VARCHAR NOT NULL, SOURCE VARCHAR NOT NULL, LABEL
> VARCHAR NOT NULL, DIRECTION VARCHAR(10) NOT NULL, REVERSETIME UNSIGNED_LONG
> NOT NULL, TARGET VARCHAR,"cf".ID VARCHAR, CONSTRAINT pk PRIMARY
> KEY(TYPE,SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET)) COMPRESSION =
> 'SNAPPY';
>
>
>
> upsert into SAMPLE(TYPE, SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET,
> ID) values('test', 'src', 'label', 'direction', 134424245, 'target', 'id');
>
> .
>
> Thanks
>
>
>
>
>
> On 25 August 2016 at 20:50, Michael McAllister <mm...@homeaway.com>
> wrote:
>
> Can you please provide the sample rowkey? It is blank in your email below.
> Alternatively, an UPSERT VALUES statement I can use to insert a row that I
> can work with myself.
>
>
>
> Michael McAllister
>
> Staff Data Warehouse Engineer | Decision Systems
>
> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>
> This electronic communication (including any attachment) is confidential.
> If you are not an intended recipient of this communication, please be
> advised that any disclosure, dissemination, distribution, copying or other
> use of this communication or any attachment is strictly prohibited. If you
> have received this communication in error, please notify the sender
> immediately by reply e-mail and promptly destroy all electronic and printed
> copies of this communication and any attachment.
>
>
>
> *From: *Anil <an...@gmail.com>
> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Date: *Thursday, August 25, 2016 at 10:08 AM
> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Subject: *Re: Extracting column values from Phoenix composite primary key
>
>
>
> HI Michael,
>
>
>
> Table create statement :
>
>
>
> CREATE TABLE SAMPLE(TYPE VARCHAR NOT NULL, SOURCE VARCHAR NOT NULL, LABEL
> VARCHAR NOT NULL, DIRECTION VARCHAR(10) NOT NULL, REVERSETIME UNSIGNED_LONG
> NOT NULL, TARGET VARCHAR,"CF".ID VARCHAR,CONSTRAINT PK PRIMARY
> KEY(TYPE,,SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET)) COMPRESSION =
> 'SNAPPY'
>
>
>
> No salt buckets defined.
>
>
>
> Smaple table row key -
>
>
>
> byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes("test"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PVarchar.INSTANCE.toBytes("src"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PVarchar.INSTANCE.toBytes("label"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PVarchar.INSTANCE.toBytes("direction"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PUnsignedLong.INSTANCE.toBytes(1235464603853L),
>
> PVarchar.INSTANCE.toBytes("target"));
>
>
>
> i am trying to extract TARGET column. Thanks.
>
>
>
> Regards,
>
> Anil
>
>
>
> On 25 August 2016 at 19:29, Michael McAllister <mm...@homeaway.com>
> wrote:
>
> Can you provide an example of one of the rowkeys, the values you are
> expecting out of it, and the full table definition? Of importance in the
> table definition will be whether you have salt buckets defined.
>
>
>
> Michael McAllister
>
> Staff Data Warehouse Engineer | Decision Systems
>
> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>
> This electronic communication (including any attachment) is confidential.
> If you are not an intended recipient of this communication, please be
> advised that any disclosure, dissemination, distribution, copying or other
> use of this communication or any attachment is strictly prohibited. If you
> have received this communication in error, please notify the sender
> immediately by reply e-mail and promptly destroy all electronic and printed
> copies of this communication and any attachment.
>
>
>
> *From: *Anil <an...@gmail.com>
> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Date: *Thursday, August 25, 2016 at 1:09 AM
> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Subject: *Re: Extracting column values from Phoenix composite primary key
>
>
>
> HI Michael,
>
>
>
> Thank you for the response.
>
>
>
> Unfortunately, that is not working.
>
>
>
> Following is the snippet. one of the column is unsigned long and trying to
> extract the last column (with value target in the below code).
>
> please correct me if i am doing wrong.
>
>
>
> byte SEPARATOR_BYTE = 0;
>
> byte[] SEPARATOR_BYTE_ARRAY = { 0 };
>
> byte[] startRow = ByteUtil.concat(
>
> PVarchar.INSTANCE.toBytes("test"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PVarchar.INSTANCE.toBytes("src"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PVarchar.INSTANCE.toBytes("label"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PVarchar.INSTANCE.toBytes("direction"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PUnsignedLong.INSTANCE.toBytes(1235464603853L),
>
> PVarchar.INSTANCE.toBytes("target"));
>
>
>
>
>
> List<String> cols = new ArrayList<String>();
>
> int j = 0;
>
> for (int i= 0 ; i < startRow.length ; i++){
>
> if (startRow[i] == SEPARATOR_BYTE){
>
> cols.add(Bytes.toString(startRow, j, i-j));
>
> j = i+1;
>
> }
>
> }
>
>
>
> cols.add(Bytes.toString(startRow, j, startRow.length - j));
>
>
>
> System.out.println(cols.size());
>
>
>
> String rowKey = Bytes.toString(startRow);
>
> String[] columns = rowKey.split("\u0000");
>
>
>
> System.out.println(columns.length);
>
>
>
> last entry in the array has special character too along with actual value.
>
>
>
> Can you point out the bug in the above code if any. Thanks.
>
>
>
> Regards,
>
> Anil
>
>
>
>
>
> On 25 August 2016 at 02:32, Michael McAllister <mm...@homeaway.com>
> wrote:
>
> Anil
>
>
>
> If you split the rowkey on the zero byte character, you should end up with
> the individual columns that made up your primary key. Details are here:-
>
>
>
> https://phoenix.apache.org/faq.html#How_I_map_Phoenix_
> table_to_an_existing_HBase_table
>
>
>
> Michael McAllister
>
> Staff Data Warehouse Engineer | Decision Systems
>
> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>
> This electronic communication (including any attachment) is confidential.
> If you are not an intended recipient of this communication, please be
> advised that any disclosure, dissemination, distribution, copying or other
> use of this communication or any attachment is strictly prohibited. If you
> have received this communication in error, please notify the sender
> immediately by reply e-mail and promptly destroy all electronic and printed
> copies of this communication and any attachment.
>
>
>
> *From: *Anil <an...@gmail.com>
> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Date: *Wednesday, August 24, 2016 at 4:10 AM
> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Subject: *Re: Extracting column values from Phoenix composite primary key
>
>
>
> Any inputs ? Thanks.
>
>
>
> On 24 August 2016 at 11:13, Anil <an...@gmail.com> wrote:
>
> Hi,
>
>
>
> I have created primary key with columns in phoenix.
>
> is there any way to extract the column values from hbase row key ? Please
> help.
>
>
>
> Thanks,
>
> Anil
>
>
>
>
>
>
>
>
>
>
>
Re: Extracting column values from Phoenix composite primary key
Posted by Michael McAllister <mm...@homeaway.com>.
Not yet sorry.
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image001.png@01D202D6.11003990]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
From: Anil <an...@gmail.com>
Reply-To: "user@phoenix.apache.org" <us...@phoenix.apache.org>
Date: Monday, August 29, 2016 at 9:48 PM
To: "user@phoenix.apache.org" <us...@phoenix.apache.org>
Subject: Re: Extracting column values from Phoenix composite primary key
HI Michael and All ,
Did you get a chance to look into this ? Thanks.
Thanks.
On 26 August 2016 at 07:38, Anil <an...@gmail.com>> wrote:
HI Michael,
Following the table create and upsert query -
CREATE TABLE SAMPLE(TYPE VARCHAR NOT NULL, SOURCE VARCHAR NOT NULL, LABEL VARCHAR NOT NULL, DIRECTION VARCHAR(10) NOT NULL, REVERSETIME UNSIGNED_LONG NOT NULL, TARGET VARCHAR,"cf".ID VARCHAR, CONSTRAINT pk PRIMARY KEY(TYPE,SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET)) COMPRESSION = 'SNAPPY';
upsert into SAMPLE(TYPE, SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET, ID) values('test', 'src', 'label', 'direction', 134424245, 'target', 'id');
.
Thanks
On 25 August 2016 at 20:50, Michael McAllister <mm...@homeaway.com>> wrote:
Can you please provide the sample rowkey? It is blank in your email below. Alternatively, an UPSERT VALUES statement I can use to insert a row that I can work with myself.
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image002.png@01D202D6.11003990]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
From: Anil <an...@gmail.com>>
Reply-To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@phoenix.apache.org>>
Date: Thursday, August 25, 2016 at 10:08 AM
To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@phoenix.apache.org>>
Subject: Re: Extracting column values from Phoenix composite primary key
HI Michael,
Table create statement :
CREATE TABLE SAMPLE(TYPE VARCHAR NOT NULL, SOURCE VARCHAR NOT NULL, LABEL VARCHAR NOT NULL, DIRECTION VARCHAR(10) NOT NULL, REVERSETIME UNSIGNED_LONG NOT NULL, TARGET VARCHAR,"CF".ID VARCHAR,CONSTRAINT PK PRIMARY KEY(TYPE,,SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET)) COMPRESSION = 'SNAPPY'
No salt buckets defined.
Smaple table row key -
byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes("test"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("src"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("label"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("direction"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PUnsignedLong.INSTANCE.toBytes(1235464603853L),
PVarchar.INSTANCE.toBytes("target"));
i am trying to extract TARGET column. Thanks.
Regards,
Anil
On 25 August 2016 at 19:29, Michael McAllister <mm...@homeaway.com>> wrote:
Can you provide an example of one of the rowkeys, the values you are expecting out of it, and the full table definition? Of importance in the table definition will be whether you have salt buckets defined.
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image003.png@01D202D6.11003990]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
From: Anil <an...@gmail.com>>
Reply-To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@phoenix.apache.org>>
Date: Thursday, August 25, 2016 at 1:09 AM
To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@phoenix.apache.org>>
Subject: Re: Extracting column values from Phoenix composite primary key
HI Michael,
Thank you for the response.
Unfortunately, that is not working.
Following is the snippet. one of the column is unsigned long and trying to extract the last column (with value target in the below code).
please correct me if i am doing wrong.
byte SEPARATOR_BYTE = 0;
byte[] SEPARATOR_BYTE_ARRAY = { 0 };
byte[] startRow = ByteUtil.concat(
PVarchar.INSTANCE.toBytes("test"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("src"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("label"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("direction"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PUnsignedLong.INSTANCE.toBytes(1235464603853L),
PVarchar.INSTANCE.toBytes("target"));
List<String> cols = new ArrayList<String>();
int j = 0;
for (int i= 0 ; i < startRow.length ; i++){
if (startRow[i] == SEPARATOR_BYTE){
cols.add(Bytes.toString(startRow, j, i-j));
j = i+1;
}
}
cols.add(Bytes.toString(startRow, j, startRow.length - j));
System.out.println(cols.size());
String rowKey = Bytes.toString(startRow);
String[] columns = rowKey.split("\u0000");
System.out.println(columns.length);
last entry in the array has special character too along with actual value.
Can you point out the bug in the above code if any. Thanks.
Regards,
Anil
On 25 August 2016 at 02:32, Michael McAllister <mm...@homeaway.com>> wrote:
Anil
If you split the rowkey on the zero byte character, you should end up with the individual columns that made up your primary key. Details are here:-
https://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_to_an_existing_HBase_table
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image004.png@01D202D6.11003990]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
From: Anil <an...@gmail.com>>
Reply-To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@phoenix.apache.org>>
Date: Wednesday, August 24, 2016 at 4:10 AM
To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@phoenix.apache.org>>
Subject: Re: Extracting column values from Phoenix composite primary key
Any inputs ? Thanks.
On 24 August 2016 at 11:13, Anil <an...@gmail.com>> wrote:
Hi,
I have created primary key with columns in phoenix.
is there any way to extract the column values from hbase row key ? Please help.
Thanks,
Anil
Re: Extracting column values from Phoenix composite primary key
Posted by Anil <an...@gmail.com>.
HI Michael and All ,
Did you get a chance to look into this ? Thanks.
Thanks.
On 26 August 2016 at 07:38, Anil <an...@gmail.com> wrote:
> HI Michael,
>
> Following the table create and upsert query -
>
> CREATE TABLE SAMPLE(TYPE VARCHAR NOT NULL, SOURCE VARCHAR NOT NULL, LABEL
> VARCHAR NOT NULL, DIRECTION VARCHAR(10) NOT NULL, REVERSETIME UNSIGNED_LONG
> NOT NULL, TARGET VARCHAR,"cf".ID VARCHAR, CONSTRAINT pk PRIMARY
> KEY(TYPE,SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET)) COMPRESSION =
> 'SNAPPY';
>
> upsert into SAMPLE(TYPE, SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET,
> ID) values('test', 'src', 'label', 'direction', 134424245, 'target', 'id');
> .
> Thanks
>
>
> On 25 August 2016 at 20:50, Michael McAllister <mm...@homeaway.com>
> wrote:
>
>> Can you please provide the sample rowkey? It is blank in your email
>> below. Alternatively, an UPSERT VALUES statement I can use to insert a row
>> that I can work with myself.
>>
>>
>>
>> Michael McAllister
>>
>> Staff Data Warehouse Engineer | Decision Systems
>>
>> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
>> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>>
>> This electronic communication (including any attachment) is
>> confidential. If you are not an intended recipient of this communication,
>> please be advised that any disclosure, dissemination, distribution, copying
>> or other use of this communication or any attachment is strictly
>> prohibited. If you have received this communication in error, please
>> notify the sender immediately by reply e-mail and promptly destroy all
>> electronic and printed copies of this communication and any attachment.
>>
>>
>>
>> *From: *Anil <an...@gmail.com>
>> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>> *Date: *Thursday, August 25, 2016 at 10:08 AM
>> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>> *Subject: *Re: Extracting column values from Phoenix composite primary
>> key
>>
>>
>>
>> HI Michael,
>>
>>
>>
>> Table create statement :
>>
>>
>>
>> CREATE TABLE SAMPLE(TYPE VARCHAR NOT NULL, SOURCE VARCHAR NOT NULL, LABEL
>> VARCHAR NOT NULL, DIRECTION VARCHAR(10) NOT NULL, REVERSETIME UNSIGNED_LONG
>> NOT NULL, TARGET VARCHAR,"CF".ID VARCHAR,CONSTRAINT PK PRIMARY
>> KEY(TYPE,,SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET)) COMPRESSION =
>> 'SNAPPY'
>>
>>
>>
>> No salt buckets defined.
>>
>>
>>
>> Smaple table row key -
>>
>>
>>
>> byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes("test"),
>>
>> QueryConstants.SEPARATOR_BYTE_ARRAY,
>>
>> PVarchar.INSTANCE.toBytes("src"),
>>
>> QueryConstants.SEPARATOR_BYTE_ARRAY,
>>
>> PVarchar.INSTANCE.toBytes("label"),
>>
>> QueryConstants.SEPARATOR_BYTE_ARRAY,
>>
>> PVarchar.INSTANCE.toBytes("direction"),
>>
>> QueryConstants.SEPARATOR_BYTE_ARRAY,
>>
>> PUnsignedLong.INSTANCE.toBytes(1235464603853L),
>>
>> PVarchar.INSTANCE.toBytes("target"));
>>
>>
>>
>> i am trying to extract TARGET column. Thanks.
>>
>>
>>
>> Regards,
>>
>> Anil
>>
>>
>>
>> On 25 August 2016 at 19:29, Michael McAllister <mm...@homeaway.com>
>> wrote:
>>
>> Can you provide an example of one of the rowkeys, the values you are
>> expecting out of it, and the full table definition? Of importance in the
>> table definition will be whether you have salt buckets defined.
>>
>>
>>
>> Michael McAllister
>>
>> Staff Data Warehouse Engineer | Decision Systems
>>
>> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
>> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>>
>> This electronic communication (including any attachment) is
>> confidential. If you are not an intended recipient of this communication,
>> please be advised that any disclosure, dissemination, distribution, copying
>> or other use of this communication or any attachment is strictly
>> prohibited. If you have received this communication in error, please
>> notify the sender immediately by reply e-mail and promptly destroy all
>> electronic and printed copies of this communication and any attachment.
>>
>>
>>
>> *From: *Anil <an...@gmail.com>
>> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>> *Date: *Thursday, August 25, 2016 at 1:09 AM
>> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>> *Subject: *Re: Extracting column values from Phoenix composite primary
>> key
>>
>>
>>
>> HI Michael,
>>
>>
>>
>> Thank you for the response.
>>
>>
>>
>> Unfortunately, that is not working.
>>
>>
>>
>> Following is the snippet. one of the column is unsigned long and trying
>> to extract the last column (with value target in the below code).
>>
>> please correct me if i am doing wrong.
>>
>>
>>
>> byte SEPARATOR_BYTE = 0;
>>
>> byte[] SEPARATOR_BYTE_ARRAY = { 0 };
>>
>> byte[] startRow = ByteUtil.concat(
>>
>> PVarchar.INSTANCE.toBytes("test"),
>>
>> QueryConstants.SEPARATOR_BYTE_ARRAY,
>>
>> PVarchar.INSTANCE.toBytes("src"),
>>
>> QueryConstants.SEPARATOR_BYTE_ARRAY,
>>
>> PVarchar.INSTANCE.toBytes("label"),
>>
>> QueryConstants.SEPARATOR_BYTE_ARRAY,
>>
>> PVarchar.INSTANCE.toBytes("direction"),
>>
>> QueryConstants.SEPARATOR_BYTE_ARRAY,
>>
>> PUnsignedLong.INSTANCE.toBytes(1235464603853L),
>>
>> PVarchar.INSTANCE.toBytes("target"));
>>
>>
>>
>>
>>
>> List<String> cols = new ArrayList<String>();
>>
>> int j = 0;
>>
>> for (int i= 0 ; i < startRow.length ; i++){
>>
>> if (startRow[i] == SEPARATOR_BYTE){
>>
>> cols.add(Bytes.toString(startRow, j, i-j));
>>
>> j = i+1;
>>
>> }
>>
>> }
>>
>>
>>
>> cols.add(Bytes.toString(startRow, j, startRow.length - j));
>>
>>
>>
>> System.out.println(cols.size());
>>
>>
>>
>> String rowKey = Bytes.toString(startRow);
>>
>> String[] columns = rowKey.split("\u0000");
>>
>>
>>
>> System.out.println(columns.length);
>>
>>
>>
>> last entry in the array has special character too along with actual value.
>>
>>
>>
>> Can you point out the bug in the above code if any. Thanks.
>>
>>
>>
>> Regards,
>>
>> Anil
>>
>>
>>
>>
>>
>> On 25 August 2016 at 02:32, Michael McAllister <mm...@homeaway.com>
>> wrote:
>>
>> Anil
>>
>>
>>
>> If you split the rowkey on the zero byte character, you should end up
>> with the individual columns that made up your primary key. Details are
>> here:-
>>
>>
>>
>> https://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_
>> to_an_existing_HBase_table
>>
>>
>>
>> Michael McAllister
>>
>> Staff Data Warehouse Engineer | Decision Systems
>>
>> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
>> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>>
>> This electronic communication (including any attachment) is
>> confidential. If you are not an intended recipient of this communication,
>> please be advised that any disclosure, dissemination, distribution, copying
>> or other use of this communication or any attachment is strictly
>> prohibited. If you have received this communication in error, please
>> notify the sender immediately by reply e-mail and promptly destroy all
>> electronic and printed copies of this communication and any attachment.
>>
>>
>>
>> *From: *Anil <an...@gmail.com>
>> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>> *Date: *Wednesday, August 24, 2016 at 4:10 AM
>> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>> *Subject: *Re: Extracting column values from Phoenix composite primary
>> key
>>
>>
>>
>> Any inputs ? Thanks.
>>
>>
>>
>> On 24 August 2016 at 11:13, Anil <an...@gmail.com> wrote:
>>
>> Hi,
>>
>>
>>
>> I have created primary key with columns in phoenix.
>>
>> is there any way to extract the column values from hbase row key ? Please
>> help.
>>
>>
>>
>> Thanks,
>>
>> Anil
>>
>>
>>
>>
>>
>>
>>
>
>
Re: Extracting column values from Phoenix composite primary key
Posted by Anil <an...@gmail.com>.
HI Michael,
Following the table create and upsert query -
CREATE TABLE SAMPLE(TYPE VARCHAR NOT NULL, SOURCE VARCHAR NOT NULL, LABEL
VARCHAR NOT NULL, DIRECTION VARCHAR(10) NOT NULL, REVERSETIME UNSIGNED_LONG
NOT NULL, TARGET VARCHAR,"cf".ID VARCHAR, CONSTRAINT pk PRIMARY
KEY(TYPE,SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET)) COMPRESSION =
'SNAPPY';
upsert into SAMPLE(TYPE, SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET, ID)
values('test', 'src', 'label', 'direction', 134424245, 'target', 'id');
.
Thanks
On 25 August 2016 at 20:50, Michael McAllister <mm...@homeaway.com>
wrote:
> Can you please provide the sample rowkey? It is blank in your email below.
> Alternatively, an UPSERT VALUES statement I can use to insert a row that I
> can work with myself.
>
>
>
> Michael McAllister
>
> Staff Data Warehouse Engineer | Decision Systems
>
> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>
> This electronic communication (including any attachment) is confidential.
> If you are not an intended recipient of this communication, please be
> advised that any disclosure, dissemination, distribution, copying or other
> use of this communication or any attachment is strictly prohibited. If you
> have received this communication in error, please notify the sender
> immediately by reply e-mail and promptly destroy all electronic and printed
> copies of this communication and any attachment.
>
>
>
> *From: *Anil <an...@gmail.com>
> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Date: *Thursday, August 25, 2016 at 10:08 AM
> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Subject: *Re: Extracting column values from Phoenix composite primary key
>
>
>
> HI Michael,
>
>
>
> Table create statement :
>
>
>
> CREATE TABLE SAMPLE(TYPE VARCHAR NOT NULL, SOURCE VARCHAR NOT NULL, LABEL
> VARCHAR NOT NULL, DIRECTION VARCHAR(10) NOT NULL, REVERSETIME UNSIGNED_LONG
> NOT NULL, TARGET VARCHAR,"CF".ID VARCHAR,CONSTRAINT PK PRIMARY
> KEY(TYPE,,SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET)) COMPRESSION =
> 'SNAPPY'
>
>
>
> No salt buckets defined.
>
>
>
> Smaple table row key -
>
>
>
> byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes("test"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PVarchar.INSTANCE.toBytes("src"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PVarchar.INSTANCE.toBytes("label"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PVarchar.INSTANCE.toBytes("direction"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PUnsignedLong.INSTANCE.toBytes(1235464603853L),
>
> PVarchar.INSTANCE.toBytes("target"));
>
>
>
> i am trying to extract TARGET column. Thanks.
>
>
>
> Regards,
>
> Anil
>
>
>
> On 25 August 2016 at 19:29, Michael McAllister <mm...@homeaway.com>
> wrote:
>
> Can you provide an example of one of the rowkeys, the values you are
> expecting out of it, and the full table definition? Of importance in the
> table definition will be whether you have salt buckets defined.
>
>
>
> Michael McAllister
>
> Staff Data Warehouse Engineer | Decision Systems
>
> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>
> This electronic communication (including any attachment) is confidential.
> If you are not an intended recipient of this communication, please be
> advised that any disclosure, dissemination, distribution, copying or other
> use of this communication or any attachment is strictly prohibited. If you
> have received this communication in error, please notify the sender
> immediately by reply e-mail and promptly destroy all electronic and printed
> copies of this communication and any attachment.
>
>
>
> *From: *Anil <an...@gmail.com>
> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Date: *Thursday, August 25, 2016 at 1:09 AM
> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Subject: *Re: Extracting column values from Phoenix composite primary key
>
>
>
> HI Michael,
>
>
>
> Thank you for the response.
>
>
>
> Unfortunately, that is not working.
>
>
>
> Following is the snippet. one of the column is unsigned long and trying to
> extract the last column (with value target in the below code).
>
> please correct me if i am doing wrong.
>
>
>
> byte SEPARATOR_BYTE = 0;
>
> byte[] SEPARATOR_BYTE_ARRAY = { 0 };
>
> byte[] startRow = ByteUtil.concat(
>
> PVarchar.INSTANCE.toBytes("test"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PVarchar.INSTANCE.toBytes("src"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PVarchar.INSTANCE.toBytes("label"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PVarchar.INSTANCE.toBytes("direction"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PUnsignedLong.INSTANCE.toBytes(1235464603853L),
>
> PVarchar.INSTANCE.toBytes("target"));
>
>
>
>
>
> List<String> cols = new ArrayList<String>();
>
> int j = 0;
>
> for (int i= 0 ; i < startRow.length ; i++){
>
> if (startRow[i] == SEPARATOR_BYTE){
>
> cols.add(Bytes.toString(startRow, j, i-j));
>
> j = i+1;
>
> }
>
> }
>
>
>
> cols.add(Bytes.toString(startRow, j, startRow.length - j));
>
>
>
> System.out.println(cols.size());
>
>
>
> String rowKey = Bytes.toString(startRow);
>
> String[] columns = rowKey.split("\u0000");
>
>
>
> System.out.println(columns.length);
>
>
>
> last entry in the array has special character too along with actual value.
>
>
>
> Can you point out the bug in the above code if any. Thanks.
>
>
>
> Regards,
>
> Anil
>
>
>
>
>
> On 25 August 2016 at 02:32, Michael McAllister <mm...@homeaway.com>
> wrote:
>
> Anil
>
>
>
> If you split the rowkey on the zero byte character, you should end up with
> the individual columns that made up your primary key. Details are here:-
>
>
>
> https://phoenix.apache.org/faq.html#How_I_map_Phoenix_
> table_to_an_existing_HBase_table
>
>
>
> Michael McAllister
>
> Staff Data Warehouse Engineer | Decision Systems
>
> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>
> This electronic communication (including any attachment) is confidential.
> If you are not an intended recipient of this communication, please be
> advised that any disclosure, dissemination, distribution, copying or other
> use of this communication or any attachment is strictly prohibited. If you
> have received this communication in error, please notify the sender
> immediately by reply e-mail and promptly destroy all electronic and printed
> copies of this communication and any attachment.
>
>
>
> *From: *Anil <an...@gmail.com>
> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Date: *Wednesday, August 24, 2016 at 4:10 AM
> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Subject: *Re: Extracting column values from Phoenix composite primary key
>
>
>
> Any inputs ? Thanks.
>
>
>
> On 24 August 2016 at 11:13, Anil <an...@gmail.com> wrote:
>
> Hi,
>
>
>
> I have created primary key with columns in phoenix.
>
> is there any way to extract the column values from hbase row key ? Please
> help.
>
>
>
> Thanks,
>
> Anil
>
>
>
>
>
>
>
Re: Extracting column values from Phoenix composite primary key
Posted by Michael McAllister <mm...@homeaway.com>.
Can you please provide the sample rowkey? It is blank in your email below. Alternatively, an UPSERT VALUES statement I can use to insert a row that I can work with myself.
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image001.png@01D1FEBA.4B29AE50]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
From: Anil <an...@gmail.com>
Reply-To: "user@phoenix.apache.org" <us...@phoenix.apache.org>
Date: Thursday, August 25, 2016 at 10:08 AM
To: "user@phoenix.apache.org" <us...@phoenix.apache.org>
Subject: Re: Extracting column values from Phoenix composite primary key
HI Michael,
Table create statement :
CREATE TABLE SAMPLE(TYPE VARCHAR NOT NULL, SOURCE VARCHAR NOT NULL, LABEL VARCHAR NOT NULL, DIRECTION VARCHAR(10) NOT NULL, REVERSETIME UNSIGNED_LONG NOT NULL, TARGET VARCHAR,"CF".ID VARCHAR,CONSTRAINT PK PRIMARY KEY(TYPE,,SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET)) COMPRESSION = 'SNAPPY'
No salt buckets defined.
Smaple table row key -
byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes("test"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("src"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("label"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("direction"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PUnsignedLong.INSTANCE.toBytes(1235464603853L),
PVarchar.INSTANCE.toBytes("target"));
i am trying to extract TARGET column. Thanks.
Regards,
Anil
On 25 August 2016 at 19:29, Michael McAllister <mm...@homeaway.com>> wrote:
Can you provide an example of one of the rowkeys, the values you are expecting out of it, and the full table definition? Of importance in the table definition will be whether you have salt buckets defined.
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image002.png@01D1FEBA.4B29AE50]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
From: Anil <an...@gmail.com>>
Reply-To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@phoenix.apache.org>>
Date: Thursday, August 25, 2016 at 1:09 AM
To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@phoenix.apache.org>>
Subject: Re: Extracting column values from Phoenix composite primary key
HI Michael,
Thank you for the response.
Unfortunately, that is not working.
Following is the snippet. one of the column is unsigned long and trying to extract the last column (with value target in the below code).
please correct me if i am doing wrong.
byte SEPARATOR_BYTE = 0;
byte[] SEPARATOR_BYTE_ARRAY = { 0 };
byte[] startRow = ByteUtil.concat(
PVarchar.INSTANCE.toBytes("test"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("src"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("label"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("direction"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PUnsignedLong.INSTANCE.toBytes(1235464603853L),
PVarchar.INSTANCE.toBytes("target"));
List<String> cols = new ArrayList<String>();
int j = 0;
for (int i= 0 ; i < startRow.length ; i++){
if (startRow[i] == SEPARATOR_BYTE){
cols.add(Bytes.toString(startRow, j, i-j));
j = i+1;
}
}
cols.add(Bytes.toString(startRow, j, startRow.length - j));
System.out.println(cols.size());
String rowKey = Bytes.toString(startRow);
String[] columns = rowKey.split("\u0000");
System.out.println(columns.length);
last entry in the array has special character too along with actual value.
Can you point out the bug in the above code if any. Thanks.
Regards,
Anil
On 25 August 2016 at 02:32, Michael McAllister <mm...@homeaway.com>> wrote:
Anil
If you split the rowkey on the zero byte character, you should end up with the individual columns that made up your primary key. Details are here:-
https://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_to_an_existing_HBase_table
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image003.png@01D1FEBA.4B29AE50]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
From: Anil <an...@gmail.com>>
Reply-To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@phoenix.apache.org>>
Date: Wednesday, August 24, 2016 at 4:10 AM
To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@phoenix.apache.org>>
Subject: Re: Extracting column values from Phoenix composite primary key
Any inputs ? Thanks.
On 24 August 2016 at 11:13, Anil <an...@gmail.com>> wrote:
Hi,
I have created primary key with columns in phoenix.
is there any way to extract the column values from hbase row key ? Please help.
Thanks,
Anil
Re: Extracting column values from Phoenix composite primary key
Posted by Anil <an...@gmail.com>.
HI Michael,
Table create statement :
CREATE TABLE SAMPLE(TYPE VARCHAR NOT NULL, SOURCE VARCHAR NOT NULL, LABEL
VARCHAR NOT NULL, DIRECTION VARCHAR(10) NOT NULL, REVERSETIME UNSIGNED_LONG
NOT NULL, TARGET VARCHAR,"CF".ID VARCHAR,CONSTRAINT PK PRIMARY
KEY(TYPE,,SOURCE, LABEL, DIRECTION, REVERSETIME, TARGET)) COMPRESSION =
'SNAPPY'
No salt buckets defined.
Smaple table row key -
byte[] startRow = ByteUtil.concat(PVarchar.INSTANCE.toBytes("test"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("src"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("label"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("direction"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PUnsignedLong.INSTANCE.toBytes(1235464603853L),
PVarchar.INSTANCE.toBytes("target"));
i am trying to extract TARGET column. Thanks.
Regards,
Anil
On 25 August 2016 at 19:29, Michael McAllister <mm...@homeaway.com>
wrote:
> Can you provide an example of one of the rowkeys, the values you are
> expecting out of it, and the full table definition? Of importance in the
> table definition will be whether you have salt buckets defined.
>
>
>
> Michael McAllister
>
> Staff Data Warehouse Engineer | Decision Systems
>
> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>
> This electronic communication (including any attachment) is confidential.
> If you are not an intended recipient of this communication, please be
> advised that any disclosure, dissemination, distribution, copying or other
> use of this communication or any attachment is strictly prohibited. If you
> have received this communication in error, please notify the sender
> immediately by reply e-mail and promptly destroy all electronic and printed
> copies of this communication and any attachment.
>
>
>
> *From: *Anil <an...@gmail.com>
> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Date: *Thursday, August 25, 2016 at 1:09 AM
> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Subject: *Re: Extracting column values from Phoenix composite primary key
>
>
>
> HI Michael,
>
>
>
> Thank you for the response.
>
>
>
> Unfortunately, that is not working.
>
>
>
> Following is the snippet. one of the column is unsigned long and trying to
> extract the last column (with value target in the below code).
>
> please correct me if i am doing wrong.
>
>
>
> byte SEPARATOR_BYTE = 0;
>
> byte[] SEPARATOR_BYTE_ARRAY = { 0 };
>
> byte[] startRow = ByteUtil.concat(
>
> PVarchar.INSTANCE.toBytes("test"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PVarchar.INSTANCE.toBytes("src"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PVarchar.INSTANCE.toBytes("label"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PVarchar.INSTANCE.toBytes("direction"),
>
> QueryConstants.SEPARATOR_BYTE_ARRAY,
>
> PUnsignedLong.INSTANCE.toBytes(1235464603853L),
>
> PVarchar.INSTANCE.toBytes("target"));
>
>
>
>
>
> List<String> cols = new ArrayList<String>();
>
> int j = 0;
>
> for (int i= 0 ; i < startRow.length ; i++){
>
> if (startRow[i] == SEPARATOR_BYTE){
>
> cols.add(Bytes.toString(startRow, j, i-j));
>
> j = i+1;
>
> }
>
> }
>
>
>
> cols.add(Bytes.toString(startRow, j, startRow.length - j));
>
>
>
> System.out.println(cols.size());
>
>
>
> String rowKey = Bytes.toString(startRow);
>
> String[] columns = rowKey.split("\u0000");
>
>
>
> System.out.println(columns.length);
>
>
>
> last entry in the array has special character too along with actual value.
>
>
>
> Can you point out the bug in the above code if any. Thanks.
>
>
>
> Regards,
>
> Anil
>
>
>
>
>
> On 25 August 2016 at 02:32, Michael McAllister <mm...@homeaway.com>
> wrote:
>
> Anil
>
>
>
> If you split the rowkey on the zero byte character, you should end up with
> the individual columns that made up your primary key. Details are here:-
>
>
>
> https://phoenix.apache.org/faq.html#How_I_map_Phoenix_
> table_to_an_existing_HBase_table
>
>
>
> Michael McAllister
>
> Staff Data Warehouse Engineer | Decision Systems
>
> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>
> This electronic communication (including any attachment) is confidential.
> If you are not an intended recipient of this communication, please be
> advised that any disclosure, dissemination, distribution, copying or other
> use of this communication or any attachment is strictly prohibited. If you
> have received this communication in error, please notify the sender
> immediately by reply e-mail and promptly destroy all electronic and printed
> copies of this communication and any attachment.
>
>
>
> *From: *Anil <an...@gmail.com>
> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Date: *Wednesday, August 24, 2016 at 4:10 AM
> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Subject: *Re: Extracting column values from Phoenix composite primary key
>
>
>
> Any inputs ? Thanks.
>
>
>
> On 24 August 2016 at 11:13, Anil <an...@gmail.com> wrote:
>
> Hi,
>
>
>
> I have created primary key with columns in phoenix.
>
> is there any way to extract the column values from hbase row key ? Please
> help.
>
>
>
> Thanks,
>
> Anil
>
>
>
>
>
Re: Extracting column values from Phoenix composite primary key
Posted by Michael McAllister <mm...@homeaway.com>.
Can you provide an example of one of the rowkeys, the values you are expecting out of it, and the full table definition? Of importance in the table definition will be whether you have salt buckets defined.
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image001.png@01D1FEAF.09B0D210]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
From: Anil <an...@gmail.com>
Reply-To: "user@phoenix.apache.org" <us...@phoenix.apache.org>
Date: Thursday, August 25, 2016 at 1:09 AM
To: "user@phoenix.apache.org" <us...@phoenix.apache.org>
Subject: Re: Extracting column values from Phoenix composite primary key
HI Michael,
Thank you for the response.
Unfortunately, that is not working.
Following is the snippet. one of the column is unsigned long and trying to extract the last column (with value target in the below code).
please correct me if i am doing wrong.
byte SEPARATOR_BYTE = 0;
byte[] SEPARATOR_BYTE_ARRAY = { 0 };
byte[] startRow = ByteUtil.concat(
PVarchar.INSTANCE.toBytes("test"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("src"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("label"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("direction"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PUnsignedLong.INSTANCE.toBytes(1235464603853L),
PVarchar.INSTANCE.toBytes("target"));
List<String> cols = new ArrayList<String>();
int j = 0;
for (int i= 0 ; i < startRow.length ; i++){
if (startRow[i] == SEPARATOR_BYTE){
cols.add(Bytes.toString(startRow, j, i-j));
j = i+1;
}
}
cols.add(Bytes.toString(startRow, j, startRow.length - j));
System.out.println(cols.size());
String rowKey = Bytes.toString(startRow);
String[] columns = rowKey.split("\u0000");
System.out.println(columns.length);
last entry in the array has special character too along with actual value.
Can you point out the bug in the above code if any. Thanks.
Regards,
Anil
On 25 August 2016 at 02:32, Michael McAllister <mm...@homeaway.com>> wrote:
Anil
If you split the rowkey on the zero byte character, you should end up with the individual columns that made up your primary key. Details are here:-
https://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_to_an_existing_HBase_table
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image002.png@01D1FEAF.09B0D210]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
From: Anil <an...@gmail.com>>
Reply-To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@phoenix.apache.org>>
Date: Wednesday, August 24, 2016 at 4:10 AM
To: "user@phoenix.apache.org<ma...@phoenix.apache.org>" <us...@phoenix.apache.org>>
Subject: Re: Extracting column values from Phoenix composite primary key
Any inputs ? Thanks.
On 24 August 2016 at 11:13, Anil <an...@gmail.com>> wrote:
Hi,
I have created primary key with columns in phoenix.
is there any way to extract the column values from hbase row key ? Please help.
Thanks,
Anil
Re: Extracting column values from Phoenix composite primary key
Posted by Anil <an...@gmail.com>.
HI Michael,
Thank you for the response.
Unfortunately, that is not working.
Following is the snippet. one of the column is unsigned long and trying to
extract the last column (with value target in the below code).
please correct me if i am doing wrong.
byte SEPARATOR_BYTE = 0;
byte[] SEPARATOR_BYTE_ARRAY = { 0 };
byte[] startRow = ByteUtil.concat(
PVarchar.INSTANCE.toBytes("test"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("src"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("label"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PVarchar.INSTANCE.toBytes("direction"),
QueryConstants.SEPARATOR_BYTE_ARRAY,
PUnsignedLong.INSTANCE.toBytes(1235464603853L),
PVarchar.INSTANCE.toBytes("target"));
List<String> cols = new ArrayList<String>();
int j = 0;
for (int i= 0 ; i < startRow.length ; i++){
if (startRow[i] == SEPARATOR_BYTE){
cols.add(Bytes.toString(startRow, j, i-j));
j = i+1;
}
}
cols.add(Bytes.toString(startRow, j, startRow.length - j));
System.out.println(cols.size());
String rowKey = Bytes.toString(startRow);
String[] columns = rowKey.split("\u0000");
System.out.println(columns.length);
last entry in the array has special character too along with actual value.
Can you point out the bug in the above code if any. Thanks.
Regards,
Anil
On 25 August 2016 at 02:32, Michael McAllister <mm...@homeaway.com>
wrote:
> Anil
>
>
>
> If you split the rowkey on the zero byte character, you should end up with
> the individual columns that made up your primary key. Details are here:-
>
>
>
> https://phoenix.apache.org/faq.html#How_I_map_Phoenix_
> table_to_an_existing_HBase_table
>
>
>
> Michael McAllister
>
> Staff Data Warehouse Engineer | Decision Systems
>
> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>
> This electronic communication (including any attachment) is confidential.
> If you are not an intended recipient of this communication, please be
> advised that any disclosure, dissemination, distribution, copying or other
> use of this communication or any attachment is strictly prohibited. If you
> have received this communication in error, please notify the sender
> immediately by reply e-mail and promptly destroy all electronic and printed
> copies of this communication and any attachment.
>
>
>
> *From: *Anil <an...@gmail.com>
> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Date: *Wednesday, August 24, 2016 at 4:10 AM
> *To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Subject: *Re: Extracting column values from Phoenix composite primary key
>
>
>
> Any inputs ? Thanks.
>
>
>
> On 24 August 2016 at 11:13, Anil <an...@gmail.com> wrote:
>
> Hi,
>
>
>
> I have created primary key with columns in phoenix.
>
> is there any way to extract the column values from hbase row key ? Please
> help.
>
>
>
> Thanks,
>
> Anil
>
>
>
Re: Extracting column values from Phoenix composite primary key
Posted by Michael McAllister <mm...@homeaway.com>.
Anil
If you split the rowkey on the zero byte character, you should end up with the individual columns that made up your primary key. Details are here:-
https://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_to_an_existing_HBase_table
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image001.png@01D1FE20.E42A21B0]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
From: Anil <an...@gmail.com>
Reply-To: "user@phoenix.apache.org" <us...@phoenix.apache.org>
Date: Wednesday, August 24, 2016 at 4:10 AM
To: "user@phoenix.apache.org" <us...@phoenix.apache.org>
Subject: Re: Extracting column values from Phoenix composite primary key
Any inputs ? Thanks.
On 24 August 2016 at 11:13, Anil <an...@gmail.com>> wrote:
Hi,
I have created primary key with columns in phoenix.
is there any way to extract the column values from hbase row key ? Please help.
Thanks,
Anil
Re: Extracting column values from Phoenix composite primary key
Posted by Anil <an...@gmail.com>.
Any inputs ? Thanks.
On 24 August 2016 at 11:13, Anil <an...@gmail.com> wrote:
> Hi,
>
> I have created primary key with columns in phoenix.
> is there any way to extract the column values from hbase row key ? Please
> help.
>
> Thanks,
> Anil
>