You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by ka...@barclays.com on 2016/02/10 18:03:15 UTC
Multiple versions for single row key
Hello,
HBase tables support multiple versions (default is 3) for single row key. I am trying to see how efficiently this can be achieved in Phoenix (don't want to create view on existing HBase table, just want to go with new Phoenix table).
Is it better to create a separate secondary key column in Phoenix table which is of course not unique and setup secondary index on this column for faster querying?
Also, is this JIRA related what I am asking?
https://issues.apache.org/jira/browse/PHOENIX-590
Sorry if this has been answered before and thanks in advance.
Regards
Kannan.
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________
Re: Multiple versions for single row key
Posted by Thomas D'Silva <td...@salesforce.com>.
PHOENIX-590 is currently unassigned, so I'm not sure when it will be
implemented. We are always looking for contributions.
On Mon, Feb 22, 2016 at 1:23 PM, <ka...@barclays.com> wrote:
> Thanks Thomas. Do we have rough estimate of when PHOENIX-590 will be done?
>
> -----Original Message-----
> From: Thomas D'Silva [mailto:tdsilva@salesforce.com]
> Sent: Monday, February 22, 2016 14:59
> To: user@phoenix.apache.org
> Subject: Re: Multiple versions for single row key
>
> You need to set the versions attribute of the scan :
> scan ‘t1’, {RAW => true, VERSIONS => 10}
>
> As James said in a previous post, getting all versions of a row using a phoenix query is not implemented yet
> (https://issues.apache.org/jira/browse/PHOENIX-590)
>
> Thanks,
> Thomas
>
> On Mon, Feb 22, 2016 at 9:06 AM, <ka...@barclays.com> wrote:
>> Hi James,
>>
>>
>>
>> I don’t quite get it to work or I didn’t understand how it’s supposed
>> to work.
>>
>>
>>
>> I have created a simple table to test this.
>>
>>
>>
>> Create table statement with support for 5 versions:
>>
>> create table MULTI_ROW_KEYS (ID VARCHAR PRIMARY KEY, PRICE DOUBLE)
>> VERSIONS=5
>>
>>
>>
>> Sample java code snippet to get connection using calendar:
>>
>> private static void executeQuery(Calendar cal, String sql) throws
>> SQLException {
>>
>> cal.add(Calendar.HOUR, 1);
>>
>> Properties props = new Properties();
>>
>> props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
>> Long.toString(cal.getTimeInMillis()));
>>
>> connection = DriverManager.getConnection("jdbc:phoenix:" +
>> zooKeeperHostPort, props); // zooKeeperHostPort – host/port
>>
>> connection.createStatement().execute(sql);
>>
>> connection.commit();
>>
>> }
>>
>> Calendar cal = Calendar.getInstance();
>>
>> cal.set(2106, 1, 22, 10, 0, 0);
>>
>> executeQuery(cal, "UPSERT INTO MULTI_ROW_KEYS VALUES ('a', 100)");
>>
>> executeQuery(cal, "UPSERT INTO MULTI_ROW_KEYS VALUES ('a', 200)");
>>
>>
>>
>> After running first executeQuery, I ran scan command in HBase shell:
>>
>> hbase(main):017:0> scan 'MULTI_ROW_KEYS'
>>
>> ROW COLUMN+CELL
>>
>> a column=0:PRICE, timestamp=4296294000943,
>> value=\xC0Y\x00\x00\x00\x00\x00\x01
>>
>> a column=0:_0, timestamp=4296294000943,
>> value=
>>
>> 1 row(s) in 0.0260 seconds
>>
>>
>>
>> This is an expected output (timestamp=4296294000943 => 2/22/2106,
>> 10:00:00 AM GMT-5:00)
>>
>>
>>
>> hbase(main):018:0> scan 'MULTI_ROW_KEYS'
>>
>> ROW COLUMN+CELL
>>
>> a column=0:PRICE, timestamp=4296297600943,
>> value=\xC0i\x00\x00\x00\x00\x00\x01
>>
>> a column=0:_0, timestamp=4296297600943,
>> value=
>>
>> 1 row(s) in 0.0160 seconds
>>
>>
>>
>> I am expecting two rows but getting only one with updated timestamp
>> (timestamp=4296297600943 => 2/22/2106, 11:00:00 AM GMT-5:00).
>>
>>
>>
>> Why? Do I need to send additional parameters in scan command? Also
>> running select query on this table from SQuirreL doesn’t return any results.
>>
>>
>>
>> What should be the connection setting to select all the two rows using JDBC?
>>
>>
>>
>> Running query (select * from MULTI_ROW_KEYS) with this connection
>> property (cal.set(2106, 1, 22, 11, 0, 0)), returns only first row (ID: a, PRICE:
>> 100.0) and
>>
>> Running query (select * from MULTI_ROW_KEYS) with this connection
>> property (cal.set(2106, 1, 22, 12, 0, 0)), returns only first row (ID: a, PRICE:
>> 200.0)
>>
>>
>>
>> I tried setting the timestamp in the connection property to much later
>> date but returns only the last row.
>>
>>
>>
>>
>>
>> Thanks in advance
>>
>>
>>
>> Kannan.
>>
>>
>>
>>
>>
>>
>>
>> From: Ramanathan, Kannan: IT (NYK)
>> Sent: Thursday, February 11, 2016 13:20
>> To: user@phoenix.apache.org
>> Subject: RE: Multiple versions for single row key
>>
>>
>>
>> Thanks a lot James, I’ll try this.
>>
>>
>>
>> From: James Taylor [mailto:jamestaylor@apache.org]
>> Sent: Thursday, February 11, 2016 12:43
>> To: user@phoenix.apache.org
>> Subject: Re: Multiple versions for single row key
>>
>>
>>
>> Hi Kannan,
>>
>>
>>
>> Yes, you can keep 3 versions of a cell in Phoenix (just add VERSIONS=3
>> to your DDL statement), however you'll only see one version when you
>> query (by default, the latest - see [1] for how to see an earlier version).
>> PHOENIX-590 (not implemented) is about seeing all versions..
>>
>>
>>
>> HTH,
>>
>> James
>>
>>
>>
>> [1]
>> https://phoenix.apache.org/faq.html#Can_phoenix_work_on_tables_with_ar
>> bitrary_timestamp_as_flexible_as_HBase_API
>>
>> On Thursday, February 11, 2016, <ka...@barclays.com> wrote:
>>
>> Any suggestions?
>>
>>
>>
>> From: Ramanathan, Kannan: IT (NYK)
>> Sent: Wednesday, February 10, 2016 12:03
>> To: user@phoenix.apache.org
>> Subject: Multiple versions for single row key
>>
>>
>>
>> Hello,
>>
>>
>>
>> HBase tables support multiple versions (default is 3) for single row
>> key. I am trying to see how efficiently this can be achieved in
>> Phoenix (don’t want to create view on existing HBase table, just want
>> to go with new Phoenix table).
>>
>>
>>
>> Is it better to create a separate secondary key column in Phoenix
>> table which is of course not unique and setup secondary index on this
>> column for faster querying?
>>
>>
>>
>> Also, is this JIRA related what I am asking?
>>
>>
>>
>> https://issues.apache.org/jira/browse/PHOENIX-590
>>
>>
>>
>> Sorry if this has been answered before and thanks in advance.
>>
>>
>>
>> Regards
>>
>> Kannan.
>>
>> _______________________________________________
>>
>> This message is for information purposes only, it is not a
>> recommendation, advice, offer or solicitation to buy or sell a product
>> or service nor an official confirmation of any transaction. It is
>> directed at persons who are professionals and is not intended for
>> retail customer use. Intended for recipient only. This message is subject to the terms at:
>> www.barclays.com/emaildisclaimer.
>>
>> For important disclosures, please see:
>> www.barclays.com/salesandtradingdisclaimer regarding market commentary
>> from Barclays Sales and/or Trading, who are active market
>> participants; and in respect of Barclays Research, including
>> disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
>>
>> _______________________________________________
>>
>> _______________________________________________
>>
>> This message is for information purposes only, it is not a
>> recommendation, advice, offer or solicitation to buy or sell a product
>> or service nor an official confirmation of any transaction. It is
>> directed at persons who are professionals and is not intended for
>> retail customer use. Intended for recipient only. This message is subject to the terms at:
>> www.barclays.com/emaildisclaimer.
>>
>> For important disclosures, please see:
>> www.barclays.com/salesandtradingdisclaimer regarding market commentary
>> from Barclays Sales and/or Trading, who are active market
>> participants; and in respect of Barclays Research, including
>> disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
>>
>> _______________________________________________
>>
>> _______________________________________________
>>
>> This message is for information purposes only, it is not a
>> recommendation, advice, offer or solicitation to buy or sell a product
>> or service nor an official confirmation of any transaction. It is
>> directed at persons who are professionals and is not intended for
>> retail customer use. Intended for recipient only. This message is subject to the terms at:
>> www.barclays.com/emaildisclaimer.
>>
>> For important disclosures, please see:
>> www.barclays.com/salesandtradingdisclaimer regarding market commentary
>> from Barclays Sales and/or Trading, who are active market
>> participants; and in respect of Barclays Research, including
>> disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
>>
>> _______________________________________________
>>
>> _______________________________________________
>>
>> This message is for information purposes only, it is not a
>> recommendation, advice, offer or solicitation to buy or sell a product
>> or service nor an official confirmation of any transaction. It is
>> directed at persons who are professionals and is not intended for
>> retail customer use. Intended for recipient only. This message is subject to the terms at:
>> www.barclays.com/emaildisclaimer.
>>
>> For important disclosures, please see:
>> www.barclays.com/salesandtradingdisclaimer regarding market commentary
>> from Barclays Sales and/or Trading, who are active market
>> participants; and in respect of Barclays Research, including
>> disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
>>
>> _______________________________________________
>
> _______________________________________________
>
> This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
RE: Multiple versions for single row key
Posted by ka...@barclays.com.
Thanks Thomas. Do we have rough estimate of when PHOENIX-590 will be done?
-----Original Message-----
From: Thomas D'Silva [mailto:tdsilva@salesforce.com]
Sent: Monday, February 22, 2016 14:59
To: user@phoenix.apache.org
Subject: Re: Multiple versions for single row key
You need to set the versions attribute of the scan :
scan ‘t1’, {RAW => true, VERSIONS => 10}
As James said in a previous post, getting all versions of a row using a phoenix query is not implemented yet
(https://issues.apache.org/jira/browse/PHOENIX-590)
Thanks,
Thomas
On Mon, Feb 22, 2016 at 9:06 AM, <ka...@barclays.com> wrote:
> Hi James,
>
>
>
> I don’t quite get it to work or I didn’t understand how it’s supposed
> to work.
>
>
>
> I have created a simple table to test this.
>
>
>
> Create table statement with support for 5 versions:
>
> create table MULTI_ROW_KEYS (ID VARCHAR PRIMARY KEY, PRICE DOUBLE)
> VERSIONS=5
>
>
>
> Sample java code snippet to get connection using calendar:
>
> private static void executeQuery(Calendar cal, String sql) throws
> SQLException {
>
> cal.add(Calendar.HOUR, 1);
>
> Properties props = new Properties();
>
> props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
> Long.toString(cal.getTimeInMillis()));
>
> connection = DriverManager.getConnection("jdbc:phoenix:" +
> zooKeeperHostPort, props); // zooKeeperHostPort – host/port
>
> connection.createStatement().execute(sql);
>
> connection.commit();
>
> }
>
> Calendar cal = Calendar.getInstance();
>
> cal.set(2106, 1, 22, 10, 0, 0);
>
> executeQuery(cal, "UPSERT INTO MULTI_ROW_KEYS VALUES ('a', 100)");
>
> executeQuery(cal, "UPSERT INTO MULTI_ROW_KEYS VALUES ('a', 200)");
>
>
>
> After running first executeQuery, I ran scan command in HBase shell:
>
> hbase(main):017:0> scan 'MULTI_ROW_KEYS'
>
> ROW COLUMN+CELL
>
> a column=0:PRICE, timestamp=4296294000943,
> value=\xC0Y\x00\x00\x00\x00\x00\x01
>
> a column=0:_0, timestamp=4296294000943,
> value=
>
> 1 row(s) in 0.0260 seconds
>
>
>
> This is an expected output (timestamp=4296294000943 => 2/22/2106,
> 10:00:00 AM GMT-5:00)
>
>
>
> hbase(main):018:0> scan 'MULTI_ROW_KEYS'
>
> ROW COLUMN+CELL
>
> a column=0:PRICE, timestamp=4296297600943,
> value=\xC0i\x00\x00\x00\x00\x00\x01
>
> a column=0:_0, timestamp=4296297600943,
> value=
>
> 1 row(s) in 0.0160 seconds
>
>
>
> I am expecting two rows but getting only one with updated timestamp
> (timestamp=4296297600943 => 2/22/2106, 11:00:00 AM GMT-5:00).
>
>
>
> Why? Do I need to send additional parameters in scan command? Also
> running select query on this table from SQuirreL doesn’t return any results.
>
>
>
> What should be the connection setting to select all the two rows using JDBC?
>
>
>
> Running query (select * from MULTI_ROW_KEYS) with this connection
> property (cal.set(2106, 1, 22, 11, 0, 0)), returns only first row (ID: a, PRICE:
> 100.0) and
>
> Running query (select * from MULTI_ROW_KEYS) with this connection
> property (cal.set(2106, 1, 22, 12, 0, 0)), returns only first row (ID: a, PRICE:
> 200.0)
>
>
>
> I tried setting the timestamp in the connection property to much later
> date but returns only the last row.
>
>
>
>
>
> Thanks in advance
>
>
>
> Kannan.
>
>
>
>
>
>
>
> From: Ramanathan, Kannan: IT (NYK)
> Sent: Thursday, February 11, 2016 13:20
> To: user@phoenix.apache.org
> Subject: RE: Multiple versions for single row key
>
>
>
> Thanks a lot James, I’ll try this.
>
>
>
> From: James Taylor [mailto:jamestaylor@apache.org]
> Sent: Thursday, February 11, 2016 12:43
> To: user@phoenix.apache.org
> Subject: Re: Multiple versions for single row key
>
>
>
> Hi Kannan,
>
>
>
> Yes, you can keep 3 versions of a cell in Phoenix (just add VERSIONS=3
> to your DDL statement), however you'll only see one version when you
> query (by default, the latest - see [1] for how to see an earlier version).
> PHOENIX-590 (not implemented) is about seeing all versions..
>
>
>
> HTH,
>
> James
>
>
>
> [1]
> https://phoenix.apache.org/faq.html#Can_phoenix_work_on_tables_with_ar
> bitrary_timestamp_as_flexible_as_HBase_API
>
> On Thursday, February 11, 2016, <ka...@barclays.com> wrote:
>
> Any suggestions?
>
>
>
> From: Ramanathan, Kannan: IT (NYK)
> Sent: Wednesday, February 10, 2016 12:03
> To: user@phoenix.apache.org
> Subject: Multiple versions for single row key
>
>
>
> Hello,
>
>
>
> HBase tables support multiple versions (default is 3) for single row
> key. I am trying to see how efficiently this can be achieved in
> Phoenix (don’t want to create view on existing HBase table, just want
> to go with new Phoenix table).
>
>
>
> Is it better to create a separate secondary key column in Phoenix
> table which is of course not unique and setup secondary index on this
> column for faster querying?
>
>
>
> Also, is this JIRA related what I am asking?
>
>
>
> https://issues.apache.org/jira/browse/PHOENIX-590
>
>
>
> Sorry if this has been answered before and thanks in advance.
>
>
>
> Regards
>
> Kannan.
>
> _______________________________________________
>
> This message is for information purposes only, it is not a
> recommendation, advice, offer or solicitation to buy or sell a product
> or service nor an official confirmation of any transaction. It is
> directed at persons who are professionals and is not intended for
> retail customer use. Intended for recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary
> from Barclays Sales and/or Trading, who are active market
> participants; and in respect of Barclays Research, including
> disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
>
> _______________________________________________
>
> This message is for information purposes only, it is not a
> recommendation, advice, offer or solicitation to buy or sell a product
> or service nor an official confirmation of any transaction. It is
> directed at persons who are professionals and is not intended for
> retail customer use. Intended for recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary
> from Barclays Sales and/or Trading, who are active market
> participants; and in respect of Barclays Research, including
> disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
>
> _______________________________________________
>
> This message is for information purposes only, it is not a
> recommendation, advice, offer or solicitation to buy or sell a product
> or service nor an official confirmation of any transaction. It is
> directed at persons who are professionals and is not intended for
> retail customer use. Intended for recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary
> from Barclays Sales and/or Trading, who are active market
> participants; and in respect of Barclays Research, including
> disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
>
> _______________________________________________
>
> This message is for information purposes only, it is not a
> recommendation, advice, offer or solicitation to buy or sell a product
> or service nor an official confirmation of any transaction. It is
> directed at persons who are professionals and is not intended for
> retail customer use. Intended for recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary
> from Barclays Sales and/or Trading, who are active market
> participants; and in respect of Barclays Research, including
> disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________
Re: Multiple versions for single row key
Posted by Thomas D'Silva <td...@salesforce.com>.
You need to set the versions attribute of the scan :
scan ‘t1’, {RAW => true, VERSIONS => 10}
As James said in a previous post, getting all versions of a row using
a phoenix query is not implemented yet
(https://issues.apache.org/jira/browse/PHOENIX-590)
Thanks,
Thomas
On Mon, Feb 22, 2016 at 9:06 AM, <ka...@barclays.com> wrote:
> Hi James,
>
>
>
> I don’t quite get it to work or I didn’t understand how it’s supposed to
> work.
>
>
>
> I have created a simple table to test this.
>
>
>
> Create table statement with support for 5 versions:
>
> create table MULTI_ROW_KEYS (ID VARCHAR PRIMARY KEY, PRICE DOUBLE)
> VERSIONS=5
>
>
>
> Sample java code snippet to get connection using calendar:
>
> private static void executeQuery(Calendar cal, String sql) throws
> SQLException {
>
> cal.add(Calendar.HOUR, 1);
>
> Properties props = new Properties();
>
> props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
> Long.toString(cal.getTimeInMillis()));
>
> connection = DriverManager.getConnection("jdbc:phoenix:" +
> zooKeeperHostPort, props); // zooKeeperHostPort – host/port
>
> connection.createStatement().execute(sql);
>
> connection.commit();
>
> }
>
> Calendar cal = Calendar.getInstance();
>
> cal.set(2106, 1, 22, 10, 0, 0);
>
> executeQuery(cal, "UPSERT INTO MULTI_ROW_KEYS VALUES ('a', 100)");
>
> executeQuery(cal, "UPSERT INTO MULTI_ROW_KEYS VALUES ('a', 200)");
>
>
>
> After running first executeQuery, I ran scan command in HBase shell:
>
> hbase(main):017:0> scan 'MULTI_ROW_KEYS'
>
> ROW COLUMN+CELL
>
> a column=0:PRICE, timestamp=4296294000943,
> value=\xC0Y\x00\x00\x00\x00\x00\x01
>
> a column=0:_0, timestamp=4296294000943,
> value=
>
> 1 row(s) in 0.0260 seconds
>
>
>
> This is an expected output (timestamp=4296294000943 => 2/22/2106, 10:00:00
> AM GMT-5:00)
>
>
>
> hbase(main):018:0> scan 'MULTI_ROW_KEYS'
>
> ROW COLUMN+CELL
>
> a column=0:PRICE, timestamp=4296297600943,
> value=\xC0i\x00\x00\x00\x00\x00\x01
>
> a column=0:_0, timestamp=4296297600943,
> value=
>
> 1 row(s) in 0.0160 seconds
>
>
>
> I am expecting two rows but getting only one with updated timestamp
> (timestamp=4296297600943 => 2/22/2106, 11:00:00 AM GMT-5:00).
>
>
>
> Why? Do I need to send additional parameters in scan command? Also running
> select query on this table from SQuirreL doesn’t return any results.
>
>
>
> What should be the connection setting to select all the two rows using JDBC?
>
>
>
> Running query (select * from MULTI_ROW_KEYS) with this connection property
> (cal.set(2106, 1, 22, 11, 0, 0)), returns only first row (ID: a, PRICE:
> 100.0) and
>
> Running query (select * from MULTI_ROW_KEYS) with this connection property
> (cal.set(2106, 1, 22, 12, 0, 0)), returns only first row (ID: a, PRICE:
> 200.0)
>
>
>
> I tried setting the timestamp in the connection property to much later date
> but returns only the last row.
>
>
>
>
>
> Thanks in advance
>
>
>
> Kannan.
>
>
>
>
>
>
>
> From: Ramanathan, Kannan: IT (NYK)
> Sent: Thursday, February 11, 2016 13:20
> To: user@phoenix.apache.org
> Subject: RE: Multiple versions for single row key
>
>
>
> Thanks a lot James, I’ll try this.
>
>
>
> From: James Taylor [mailto:jamestaylor@apache.org]
> Sent: Thursday, February 11, 2016 12:43
> To: user@phoenix.apache.org
> Subject: Re: Multiple versions for single row key
>
>
>
> Hi Kannan,
>
>
>
> Yes, you can keep 3 versions of a cell in Phoenix (just add VERSIONS=3 to
> your DDL statement), however you'll only see one version when you query (by
> default, the latest - see [1] for how to see an earlier version).
> PHOENIX-590 (not implemented) is about seeing all versions..
>
>
>
> HTH,
>
> James
>
>
>
> [1]
> https://phoenix.apache.org/faq.html#Can_phoenix_work_on_tables_with_arbitrary_timestamp_as_flexible_as_HBase_API
>
> On Thursday, February 11, 2016, <ka...@barclays.com> wrote:
>
> Any suggestions?
>
>
>
> From: Ramanathan, Kannan: IT (NYK)
> Sent: Wednesday, February 10, 2016 12:03
> To: user@phoenix.apache.org
> Subject: Multiple versions for single row key
>
>
>
> Hello,
>
>
>
> HBase tables support multiple versions (default is 3) for single row key. I
> am trying to see how efficiently this can be achieved in Phoenix (don’t want
> to create view on existing HBase table, just want to go with new Phoenix
> table).
>
>
>
> Is it better to create a separate secondary key column in Phoenix table
> which is of course not unique and setup secondary index on this column for
> faster querying?
>
>
>
> Also, is this JIRA related what I am asking?
>
>
>
> https://issues.apache.org/jira/browse/PHOENIX-590
>
>
>
> Sorry if this has been answered before and thanks in advance.
>
>
>
> Regards
>
> Kannan.
>
> _______________________________________________
>
> This message is for information purposes only, it is not a recommendation,
> advice, offer or solicitation to buy or sell a product or service nor an
> official confirmation of any transaction. It is directed at persons who are
> professionals and is not intended for retail customer use. Intended for
> recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary from
> Barclays Sales and/or Trading, who are active market participants; and in
> respect of Barclays Research, including disclosures relating to specific
> issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
>
> _______________________________________________
>
> This message is for information purposes only, it is not a recommendation,
> advice, offer or solicitation to buy or sell a product or service nor an
> official confirmation of any transaction. It is directed at persons who are
> professionals and is not intended for retail customer use. Intended for
> recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary from
> Barclays Sales and/or Trading, who are active market participants; and in
> respect of Barclays Research, including disclosures relating to specific
> issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
>
> _______________________________________________
>
> This message is for information purposes only, it is not a recommendation,
> advice, offer or solicitation to buy or sell a product or service nor an
> official confirmation of any transaction. It is directed at persons who are
> professionals and is not intended for retail customer use. Intended for
> recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary from
> Barclays Sales and/or Trading, who are active market participants; and in
> respect of Barclays Research, including disclosures relating to specific
> issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
>
> _______________________________________________
>
> This message is for information purposes only, it is not a recommendation,
> advice, offer or solicitation to buy or sell a product or service nor an
> official confirmation of any transaction. It is directed at persons who are
> professionals and is not intended for retail customer use. Intended for
> recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary from
> Barclays Sales and/or Trading, who are active market participants; and in
> respect of Barclays Research, including disclosures relating to specific
> issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
RE: Multiple versions for single row key
Posted by ka...@barclays.com.
Hi James,
I don’t quite get it to work or I didn’t understand how it’s supposed to work.
I have created a simple table to test this.
Create table statement with support for 5 versions:
create table MULTI_ROW_KEYS (ID VARCHAR PRIMARY KEY, PRICE DOUBLE) VERSIONS=5
Sample java code snippet to get connection using calendar:
private static void executeQuery(Calendar cal, String sql) throws SQLException {
cal.add(Calendar.HOUR, 1);
Properties props = new Properties();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(cal.getTimeInMillis()));
connection = DriverManager.getConnection("jdbc:phoenix:" + zooKeeperHostPort, props); // zooKeeperHostPort – host/port
connection.createStatement().execute(sql);
connection.commit();
}
Calendar cal = Calendar.getInstance();
cal.set(2106, 1, 22, 10, 0, 0);
executeQuery(cal, "UPSERT INTO MULTI_ROW_KEYS VALUES ('a', 100)");
executeQuery(cal, "UPSERT INTO MULTI_ROW_KEYS VALUES ('a', 200)");
After running first executeQuery, I ran scan command in HBase shell:
hbase(main):017:0> scan 'MULTI_ROW_KEYS'
ROW COLUMN+CELL
a column=0:PRICE, timestamp=4296294000943, value=\xC0Y\x00\x00\x00\x00\x00\x01
a column=0:_0, timestamp=4296294000943, value=
1 row(s) in 0.0260 seconds
This is an expected output (timestamp=4296294000943 => 2/22/2106, 10:00:00 AM GMT-5:00)
hbase(main):018:0> scan 'MULTI_ROW_KEYS'
ROW COLUMN+CELL
a column=0:PRICE, timestamp=4296297600943, value=\xC0i\x00\x00\x00\x00\x00\x01
a column=0:_0, timestamp=4296297600943, value=
1 row(s) in 0.0160 seconds
I am expecting two rows but getting only one with updated timestamp (timestamp=4296297600943 => 2/22/2106, 11:00:00 AM GMT-5:00).
Why? Do I need to send additional parameters in scan command? Also running select query on this table from SQuirreL doesn’t return any results.
What should be the connection setting to select all the two rows using JDBC?
Running query (select * from MULTI_ROW_KEYS) with this connection property (cal.set(2106, 1, 22, 11, 0, 0)), returns only first row (ID: a, PRICE: 100.0) and
Running query (select * from MULTI_ROW_KEYS) with this connection property (cal.set(2106, 1, 22, 12, 0, 0)), returns only first row (ID: a, PRICE: 200.0)
I tried setting the timestamp in the connection property to much later date but returns only the last row.
Thanks in advance
Kannan.
From: Ramanathan, Kannan: IT (NYK)
Sent: Thursday, February 11, 2016 13:20
To: user@phoenix.apache.org
Subject: RE: Multiple versions for single row key
Thanks a lot James, I’ll try this.
From: James Taylor [mailto:jamestaylor@apache.org]
Sent: Thursday, February 11, 2016 12:43
To: user@phoenix.apache.org<ma...@phoenix.apache.org>
Subject: Re: Multiple versions for single row key
Hi Kannan,
Yes, you can keep 3 versions of a cell in Phoenix (just add VERSIONS=3 to your DDL statement), however you'll only see one version when you query (by default, the latest - see [1] for how to see an earlier version). PHOENIX-590 (not implemented) is about seeing all versions..
HTH,
James
[1] https://phoenix.apache.org/faq.html#Can_phoenix_work_on_tables_with_arbitrary_timestamp_as_flexible_as_HBase_API
On Thursday, February 11, 2016, <ka...@barclays.com>> wrote:
Any suggestions?
From: Ramanathan, Kannan: IT (NYK)
Sent: Wednesday, February 10, 2016 12:03
To: user@phoenix.apache.org<javascript:_e(%7B%7D,'cvml','user@phoenix.apache.org');>
Subject: Multiple versions for single row key
Hello,
HBase tables support multiple versions (default is 3) for single row key. I am trying to see how efficiently this can be achieved in Phoenix (don’t want to create view on existing HBase table, just want to go with new Phoenix table).
Is it better to create a separate secondary key column in Phoenix table which is of course not unique and setup secondary index on this column for faster querying?
Also, is this JIRA related what I am asking?
https://issues.apache.org/jira/browse/PHOENIX-590
Sorry if this has been answered before and thanks in advance.
Regards
Kannan.
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer<http://www.barclays.com/emaildisclaimer>.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer<http://www.barclays.com/salesandtradingdisclaimer> regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer<http://www.barclays.com/emaildisclaimer>.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer<http://www.barclays.com/salesandtradingdisclaimer> regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer<http://www.barclays.com/emaildisclaimer>.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer<http://www.barclays.com/salesandtradingdisclaimer> regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________
RE: Multiple versions for single row key
Posted by ka...@barclays.com.
Thanks a lot James, I’ll try this.
From: James Taylor [mailto:jamestaylor@apache.org]
Sent: Thursday, February 11, 2016 12:43
To: user@phoenix.apache.org
Subject: Re: Multiple versions for single row key
Hi Kannan,
Yes, you can keep 3 versions of a cell in Phoenix (just add VERSIONS=3 to your DDL statement), however you'll only see one version when you query (by default, the latest - see [1] for how to see an earlier version). PHOENIX-590 (not implemented) is about seeing all versions..
HTH,
James
[1] https://phoenix.apache.org/faq.html#Can_phoenix_work_on_tables_with_arbitrary_timestamp_as_flexible_as_HBase_API
On Thursday, February 11, 2016, <ka...@barclays.com>> wrote:
Any suggestions?
From: Ramanathan, Kannan: IT (NYK)
Sent: Wednesday, February 10, 2016 12:03
To: user@phoenix.apache.org<javascript:_e(%7B%7D,'cvml','user@phoenix.apache.org');>
Subject: Multiple versions for single row key
Hello,
HBase tables support multiple versions (default is 3) for single row key. I am trying to see how efficiently this can be achieved in Phoenix (don’t want to create view on existing HBase table, just want to go with new Phoenix table).
Is it better to create a separate secondary key column in Phoenix table which is of course not unique and setup secondary index on this column for faster querying?
Also, is this JIRA related what I am asking?
https://issues.apache.org/jira/browse/PHOENIX-590
Sorry if this has been answered before and thanks in advance.
Regards
Kannan.
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer<http://www.barclays.com/emaildisclaimer>.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer<http://www.barclays.com/salesandtradingdisclaimer> regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer<http://www.barclays.com/emaildisclaimer>.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer<http://www.barclays.com/salesandtradingdisclaimer> regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________
Re: Multiple versions for single row key
Posted by James Taylor <ja...@apache.org>.
Hi Kannan,
Yes, you can keep 3 versions of a cell in Phoenix (just add VERSIONS=3 to
your DDL statement), however you'll only see one version when you query (by
default, the latest - see [1] for how to see an earlier
version). PHOENIX-590 (not implemented) is about seeing all versions..
HTH,
James
[1]
https://phoenix.apache.org/faq.html#Can_phoenix_work_on_tables_with_arbitrary_timestamp_as_flexible_as_HBase_API
On Thursday, February 11, 2016, <ka...@barclays.com> wrote:
> Any suggestions?
>
>
>
> *From:* Ramanathan, Kannan: IT (NYK)
> *Sent:* Wednesday, February 10, 2016 12:03
> *To:* user@phoenix.apache.org
> <javascript:_e(%7B%7D,'cvml','user@phoenix.apache.org');>
> *Subject:* Multiple versions for single row key
>
>
>
> Hello,
>
>
>
> HBase tables support multiple versions (default is 3) for single row key.
> I am trying to see how efficiently this can be achieved in Phoenix (don’t
> want to create view on existing HBase table, just want to go with new
> Phoenix table).
>
>
>
> Is it better to create a separate secondary key column in Phoenix table
> which is of course not unique and setup secondary index on this column for
> faster querying?
>
>
>
> Also, is this JIRA related what I am asking?
>
>
>
> https://issues.apache.org/jira/browse/PHOENIX-590
>
>
>
> Sorry if this has been answered before and thanks in advance.
>
>
>
> Regards
>
> Kannan.
>
> _______________________________________________
>
> This message is for information purposes only, it is not a recommendation,
> advice, offer or solicitation to buy or sell a product or service nor an
> official confirmation of any transaction. It is directed at persons who are
> professionals and is not intended for retail customer use. Intended for
> recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary
> from Barclays Sales and/or Trading, who are active market participants; and
> in respect of Barclays Research, including disclosures relating to specific
> issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
>
> _______________________________________________
>
> This message is for information purposes only, it is not a recommendation,
> advice, offer or solicitation to buy or sell a product or service nor an
> official confirmation of any transaction. It is directed at persons who are
> professionals and is not intended for retail customer use. Intended for
> recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary
> from Barclays Sales and/or Trading, who are active market participants; and
> in respect of Barclays Research, including disclosures relating to specific
> issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
>
RE: Multiple versions for single row key
Posted by ka...@barclays.com.
Any suggestions?
From: Ramanathan, Kannan: IT (NYK)
Sent: Wednesday, February 10, 2016 12:03
To: user@phoenix.apache.org
Subject: Multiple versions for single row key
Hello,
HBase tables support multiple versions (default is 3) for single row key. I am trying to see how efficiently this can be achieved in Phoenix (don't want to create view on existing HBase table, just want to go with new Phoenix table).
Is it better to create a separate secondary key column in Phoenix table which is of course not unique and setup secondary index on this column for faster querying?
Also, is this JIRA related what I am asking?
https://issues.apache.org/jira/browse/PHOENIX-590
Sorry if this has been answered before and thanks in advance.
Regards
Kannan.
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer<http://www.barclays.com/emaildisclaimer>.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer<http://www.barclays.com/salesandtradingdisclaimer> regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________