subject:"Row timestamp"

Re: Help: setting hbase row timestamp in phoenix upserts ?

2017-11-30 Thread James Taylor

The only way I can think of accomplishing this is by using the raw HBase
APIs to write the data but using our utilities to write it in a Phoenix
compatible manner. For example, you could run an UPSERT VALUES statement,
use the PhoenixRuntime.getUncommittedDataIterator()method to get the Cells
that would have been written, update the Cell timestamp as needed, and do
an htable.batch() call to commit them.

On Wed, Nov 29, 2017 at 11:46 AM Pedro Boado  wrote:

> Hi,
>
> I'm looking for a little bit of help trying to get some light over
> ROW_TIMESTAMP.
>
> Some background over the problem ( simplified ) : I'm working in a project
> that needs to create a "enriched" replica of a RBDMS table based on a
> stream of cdc changes off that table.
>
> Each cdc event contains the timestamp of the change plus all the column
> values 'before' and 'after' the change . And each event is pushed to a
> kafka topic.  Because of certain "non-negotiable" design decisions kafka
> guarantees delivering each event at least once, but doesn't guarantee
> ordering for changes over the same row in the source table.
>
> The final step of the kafka-based flow is sinking the information into
> HBase/Phoenix.
>
> As I cannot get in order delivery guarantee from Kafka I need to use the
> cdc event timestamp to ensure that HBase keeps the latest change over a row.
>
> This fits perfectly well with an HBase table design with VERSIONS=1 and
> using the source event timestamp as HBase row/cells timestamp
>
> The thing is that I cannot find a way to define the value of the HBase
> cell from a Phoenix upsert.
>
> I came across the ROW_TIMESTAMP functionality, but I've just found ( I'm
> devastated now ) that the ROW_TIMESTAMP columns store the date in both
> hbase's cell timestamp and in the primary key, meaning that I cannot
> leverage that functionality to keep only the latest change.
>
> Is there a way of defining hbase's row timestamp when doing the UPSERT -
> even by setting it through some obscure hidden jdbc property - ?
>
> I want to avoid by all means doing a checkAndPut as the volume of changes
> is going to be quite bug.
>
>
>
> --
> Un saludo.
> Pedro Boado.
>

Help: setting hbase row timestamp in phoenix upserts ?

2017-11-29 Thread Pedro Boado

Hi,

I'm looking for a little bit of help trying to get some light over
ROW_TIMESTAMP.

Some background over the problem ( simplified ) : I'm working in a project
that needs to create a "enriched" replica of a RBDMS table based on a
stream of cdc changes off that table.

Each cdc event contains the timestamp of the change plus all the column
values 'before' and 'after' the change . And each event is pushed to a
kafka topic.  Because of certain "non-negotiable" design decisions kafka
guarantees delivering each event at least once, but doesn't guarantee
ordering for changes over the same row in the source table.

The final step of the kafka-based flow is sinking the information into
HBase/Phoenix.

As I cannot get in order delivery guarantee from Kafka I need to use the
cdc event timestamp to ensure that HBase keeps the latest change over a row.

This fits perfectly well with an HBase table design with VERSIONS=1 and
using the source event timestamp as HBase row/cells timestamp

The thing is that I cannot find a way to define the value of the HBase cell
from a Phoenix upsert.

I came across the ROW_TIMESTAMP functionality, but I've just found ( I'm
devastated now ) that the ROW_TIMESTAMP columns store the date in both
hbase's cell timestamp and in the primary key, meaning that I cannot
leverage that functionality to keep only the latest change.

Is there a way of defining hbase's row timestamp when doing the UPSERT -
even by setting it through some obscure hidden jdbc property - ?

I want to avoid by all means doing a checkAndPut as the volume of changes
is going to be quite bug.



-- 
Un saludo.
Pedro Boado.

RE: error when using hint on global index where table is using row timestamp mapping

2017-10-02 Thread Bulvik, Noam

Thanks
Any idea when 4.12 will be available ?

From: James Taylor [mailto:jamestay...@apache.org]
Sent: Monday, October 2, 2017 7:16 PM
To: user 
Cc: Sapir, Yoav 
Subject: Re: error when using hint on global index where table is using row 
timestamp mapping

I filed PHOENIX-4265, but I'm not able to repro the issue. Let's continue the 
discussion there. Please read the description for limitations on indexes with 
row_timestamp column too for 4.12.

Thanks,
James

On Mon, Oct 2, 2017 at 12:38 AM, Bulvik, Noam 
mailto:noam.bul...@teoco.com>> wrote:
It is simple TABLE with ~100 fields all are either timestamp, varchar or double

CREATE TABLE MY_TABLE
(
Row_time TIMESTAMP   not null,
 Row_idVARCHAR NOT NULL,
   A  VARCHAR ,
   B  VARCHAR ,
   B VARCHAR ,
   D  VARCHAR ,
   …some more field
..

CONSTRAINT PK PRIMARY KEY (Row_time ROW_TIMESTAMP, Row_id)
)  SALT_BUCKETS = 9

And some indexes like the ones below

CREATE INDEX A_GLOBAL_IDX ON MY_TABLE ("A");
CREATE INDEX B_GLOBAL_IDX ON MY_TABLE ("B");
CREATE INDEX C_GLOBAL_IDX ON MY_TABLE ("C");
…

The query is simply select * from MY_TABLE where A=""

Thanks for helping us out

From: Samarth Jain [mailto:sama...@apache.org<mailto:sama...@apache.org>]
Sent: Monday, October 2, 2017 9:43 AM
To: user@phoenix.apache.org<mailto:user@phoenix.apache.org>
Cc: Sapir, Yoav mailto:yoav.sa...@teoco.com>>
Subject: Re: error when using hint on global index where table is using row 
timestamp mapping

Hi Noam,

Can you pass on the DDL statements for the table and index and the query you 
are executing, please?

Thanks!

On Sun, Oct 1, 2017 at 2:01 AM, Bulvik, Noam 
mailto:noam.bul...@teoco.com>> wrote:
Hi

I have create a table and used the row timestamp mapping functionality. The key 
of the table is + column. I also created global index 
on one of the columns of the table  (XXX not one of the key columns).

When I am doing  explain select * from my_table where xxx='' I see that 
index is not used  (even though index is defined on XX column )

CLIENT 9-CHUNK PARALLEL 9-WAY ROUND ROBIN FULL SCAN OVER MY_TABLE
ROW TIMESTAMP FILTER [0, 1506847791496)
SERVER FILTER BY XXX = ''

When I am adding hint I get the following exception (this is from explain …, I 
get similar error when running the actual query )

java.lang.NullPointerException
   at 
org.apache.phoenix.compile.ScanRanges.getAscTimeRange(ScanRanges.java:674)
   at 
org.apache.phoenix.compile.ScanRanges.getRowTimestampColumnRange(ScanRanges.java:658)
   at 
org.apache.phoenix.compile.ScanRanges.create(ScanRanges.java:84)
   at 
org.apache.phoenix.compile.WhereOptimizer.pushKeyExpressionsToScan(WhereOptimizer.java:305)
   at 
org.apache.phoenix.compile.WhereOptimizer.pushKeyExpressionsToScan(WhereOptimizer.java:104)
   at 
org.apache.phoenix.compile.WhereOptimizer.getKeyExpressionCombination(WhereOptimizer.java:385)
   at 
org.apache.phoenix.compile.QueryCompiler.getKeyExpressionCombinations(QueryCompiler.java:460)
   at 
org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:314)
   at 
org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:200)
   at 
org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:157)
   at 
org.apache.phoenix.optimize.QueryOptimizer.addPlan(QueryOptimizer.java:302)
   at 
org.apache.phoenix.optimize.QueryOptimizer.getHintedQueryPlan(QueryOptimizer.java:196)
   at 
org.apache.phoenix.optimize.QueryOptimizer.getApplicablePlans(QueryOptimizer.java:137)
   at 
org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:94)
   at 
org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:80)
   at 
org.apache.phoenix.execute.BaseQueryPlan.getExplainPlan(BaseQueryPlan.java:505)
   at 
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableExplainStatement.compilePlan(PhoenixStatement.java:568)
   at 
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableExplainStatement.compilePlan(PhoenixStatement.java:547)
   at 
org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:299)
   at 
org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:289)
   at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
   at 
org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:288)
   at 
org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:282)
   at 
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.

Re: error when using hint on global index where table is using row timestamp mapping

2017-10-02 Thread James Taylor

I filed PHOENIX-4265, but I'm not able to repro the issue. Let's continue
the discussion there. Please read the description for limitations on
indexes with row_timestamp column too for 4.12.

Thanks,
James

On Mon, Oct 2, 2017 at 12:38 AM, Bulvik, Noam  wrote:

> It is simple TABLE with ~100 fields all are either timestamp, varchar or
> double
>
>
>
> CREATE TABLE MY_TABLE
>
> (
>
> Row_time TIMESTAMP   not null,
>
>  Row_idVARCHAR NOT NULL,
>
>A  VARCHAR ,
>
>B  VARCHAR ,
>
>B VARCHAR ,
>
>D  VARCHAR ,
>
>…some more field
>
> ..
>
>
>
> CONSTRAINT PK PRIMARY KEY (Row_time ROW_TIMESTAMP, Row_id)
>
> )  SALT_BUCKETS = 9
>
>
>
> And some indexes like the ones below
>
>
>
> CREATE INDEX A_GLOBAL_IDX ON MY_TABLE ("A");
>
> CREATE INDEX B_GLOBAL_IDX ON MY_TABLE ("B");
>
> CREATE INDEX C_GLOBAL_IDX ON MY_TABLE ("C");
>
> …
>
>
>
> The query is simply select * from MY_TABLE where A=""
>
>
>
>
>
> Thanks for helping us out
>
>
>
> *From:* Samarth Jain [mailto:sama...@apache.org]
> *Sent:* Monday, October 2, 2017 9:43 AM
> *To:* user@phoenix.apache.org
> *Cc:* Sapir, Yoav 
> *Subject:* Re: error when using hint on global index where table is using
> row timestamp mapping
>
>
>
> Hi Noam,
>
>
>
> Can you pass on the DDL statements for the table and index and the query
> you are executing, please?
>
>
>
> Thanks!
>
>
>
> On Sun, Oct 1, 2017 at 2:01 AM, Bulvik, Noam 
> wrote:
>
> Hi
>
>
>
> I have create a table and used the row timestamp mapping functionality.
> The key of the table is + column. I also created
> global index on one of the columns of the table  (XXX not one of the key
> columns).
>
>
>
> When I am doing  explain select * from my_table where xxx='' I see
> that index is not used  (even though index is defined on XX column )
>
>
>
> CLIENT 9-CHUNK PARALLEL 9-WAY ROUND ROBIN FULL SCAN OVER
> MY_TABLE
>
> ROW TIMESTAMP FILTER [0, 1506847791496)
>
> SERVER FILTER BY XXX = ''
>
>
>
> When I am adding hint I get the following exception (this is from explain
> …, I get similar error when running the actual query )
>
>
>
> java.lang.NullPointerException
>
>at org.apache.phoenix.compile.ScanRanges.getAscTimeRange(
> ScanRanges.java:674)
>
>at org.apache.phoenix.compile.ScanRanges.
> getRowTimestampColumnRange(ScanRanges.java:658)
>
>at org.apache.phoenix.compile.ScanRanges.create(ScanRanges.
> java:84)
>
>at org.apache.phoenix.compile.WhereOptimizer.
> pushKeyExpressionsToScan(WhereOptimizer.java:305)
>
>at org.apache.phoenix.compile.WhereOptimizer.
> pushKeyExpressionsToScan(WhereOptimizer.java:104)
>
>at org.apache.phoenix.compile.WhereOptimizer.
> getKeyExpressionCombination(WhereOptimizer.java:385)
>
>at org.apache.phoenix.compile.QueryCompiler.
> getKeyExpressionCombinations(QueryCompiler.java:460)
>
>at org.apache.phoenix.compile.QueryCompiler.
> compileJoinQuery(QueryCompiler.java:314)
>
>at org.apache.phoenix.compile.QueryCompiler.compileSelect(
> QueryCompiler.java:200)
>
>at org.apache.phoenix.compile.QueryCompiler.compile(
> QueryCompiler.java:157)
>
>at org.apache.phoenix.optimize.QueryOptimizer.addPlan(
> QueryOptimizer.java:302)
>
>at org.apache.phoenix.optimize.QueryOptimizer.
> getHintedQueryPlan(QueryOptimizer.java:196)
>
>at org.apache.phoenix.optimize.QueryOptimizer.
> getApplicablePlans(QueryOptimizer.java:137)
>
>at org.apache.phoenix.optimize.QueryOptimizer.optimize(
> QueryOptimizer.java:94)
>
>at org.apache.phoenix.optimize.QueryOptimizer.optimize(
> QueryOptimizer.java:80)
>
>at org.apache.phoenix.execute.BaseQueryPlan.getExplainPlan(
> BaseQueryPlan.java:505)
>
>at org.apache.phoenix.jdbc.PhoenixStatement$
> ExecutableExplainStatement.compilePlan(PhoenixStatement.java:568)
>
>at org.apache.phoenix.jdbc.PhoenixStatement$
> ExecutableExplainStatement.compilePlan(PhoenixStatement.java:547)
>
>at org.apache.phoenix.jdbc.PhoenixStatement$1.call(
> PhoenixStatement.java:299)
>
>at org.apache.phoenix.jdbc.PhoenixSt

RE: error when using hint on global index where table is using row timestamp mapping

2017-10-02 Thread Bulvik, Noam

It is simple TABLE with ~100 fields all are either timestamp, varchar or double

CREATE TABLE MY_TABLE
(
Row_time TIMESTAMP   not null,
 Row_idVARCHAR NOT NULL,
   A  VARCHAR ,
   B  VARCHAR ,
   B VARCHAR ,
   D  VARCHAR ,
   …some more field
..

CONSTRAINT PK PRIMARY KEY (Row_time ROW_TIMESTAMP, Row_id)
)  SALT_BUCKETS = 9

And some indexes like the ones below

CREATE INDEX A_GLOBAL_IDX ON MY_TABLE ("A");
CREATE INDEX B_GLOBAL_IDX ON MY_TABLE ("B");
CREATE INDEX C_GLOBAL_IDX ON MY_TABLE ("C");
…

The query is simply select * from MY_TABLE where A=""


Thanks for helping us out

From: Samarth Jain [mailto:sama...@apache.org]
Sent: Monday, October 2, 2017 9:43 AM
To: user@phoenix.apache.org
Cc: Sapir, Yoav 
Subject: Re: error when using hint on global index where table is using row 
timestamp mapping

Hi Noam,

Can you pass on the DDL statements for the table and index and the query you 
are executing, please?

Thanks!

On Sun, Oct 1, 2017 at 2:01 AM, Bulvik, Noam 
mailto:noam.bul...@teoco.com>> wrote:
Hi

I have create a table and used the row timestamp mapping functionality. The key 
of the table is + column. I also created global index 
on one of the columns of the table  (XXX not one of the key columns).

When I am doing  explain select * from my_table where xxx='' I see that 
index is not used  (even though index is defined on XX column )

CLIENT 9-CHUNK PARALLEL 9-WAY ROUND ROBIN FULL SCAN OVER MY_TABLE
ROW TIMESTAMP FILTER [0, 1506847791496)
SERVER FILTER BY XXX = ''

When I am adding hint I get the following exception (this is from explain …, I 
get similar error when running the actual query )

java.lang.NullPointerException
   at 
org.apache.phoenix.compile.ScanRanges.getAscTimeRange(ScanRanges.java:674)
   at 
org.apache.phoenix.compile.ScanRanges.getRowTimestampColumnRange(ScanRanges.java:658)
   at 
org.apache.phoenix.compile.ScanRanges.create(ScanRanges.java:84)
   at 
org.apache.phoenix.compile.WhereOptimizer.pushKeyExpressionsToScan(WhereOptimizer.java:305)
   at 
org.apache.phoenix.compile.WhereOptimizer.pushKeyExpressionsToScan(WhereOptimizer.java:104)
   at 
org.apache.phoenix.compile.WhereOptimizer.getKeyExpressionCombination(WhereOptimizer.java:385)
   at 
org.apache.phoenix.compile.QueryCompiler.getKeyExpressionCombinations(QueryCompiler.java:460)
   at 
org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:314)
   at 
org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:200)
   at 
org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:157)
   at 
org.apache.phoenix.optimize.QueryOptimizer.addPlan(QueryOptimizer.java:302)
   at 
org.apache.phoenix.optimize.QueryOptimizer.getHintedQueryPlan(QueryOptimizer.java:196)
   at 
org.apache.phoenix.optimize.QueryOptimizer.getApplicablePlans(QueryOptimizer.java:137)
   at 
org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:94)
   at 
org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:80)
   at 
org.apache.phoenix.execute.BaseQueryPlan.getExplainPlan(BaseQueryPlan.java:505)
   at 
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableExplainStatement.compilePlan(PhoenixStatement.java:568)
   at 
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableExplainStatement.compilePlan(PhoenixStatement.java:547)
   at 
org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:299)
   at 
org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:289)
   at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
   at 
org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:288)
   at 
org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:282)
   at 
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1711)
   at workbench.sql.SqlCommand.execute(SqlCommand.java:555)
   at 
workbench.sql.StatementRunner.runStatement(StatementRunner.java:584)
   at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:3433)
   at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:2169)
   at workbench.gui.sql.SqlPanel$4.run(SqlPanel.java:2107)



I am using phoenix 4.11


Noam




PRIVILEGED AND CONFIDENTIAL
PLEASE NOTE: The information contained in this message is privileged and 
confidential, and is intended only for the use of the individual to whom it is 
addressed and others who have been specificall

Re: error when using hint on global index where table is using row timestamp mapping

2017-10-01 Thread Samarth Jain

Hi Noam,

Can you pass on the DDL statements for the table and index and the query
you are executing, please?

Thanks!

On Sun, Oct 1, 2017 at 2:01 AM, Bulvik, Noam  wrote:

> Hi
>
>
>
> I have create a table and used the row timestamp mapping functionality.
> The key of the table is + column. I also created
> global index on one of the columns of the table  (XXX not one of the key
> columns).
>
>
>
> When I am doing  explain select * from my_table where xxx='' I see
> that index is not used  (even though index is defined on XX column )
>
>
>
> CLIENT 9-CHUNK PARALLEL 9-WAY ROUND ROBIN FULL SCAN OVER
> MY_TABLE
>
> ROW TIMESTAMP FILTER [0, 1506847791496)
>
> SERVER FILTER BY XXX = ''
>
>
>
> When I am adding hint I get the following exception (this is from explain
> …, I get similar error when running the actual query )
>
>
>
> java.lang.NullPointerException
>
>at org.apache.phoenix.compile.ScanRanges.getAscTimeRange(
> ScanRanges.java:674)
>
>at org.apache.phoenix.compile.ScanRanges.
> getRowTimestampColumnRange(ScanRanges.java:658)
>
>at org.apache.phoenix.compile.ScanRanges.create(ScanRanges.
> java:84)
>
>at org.apache.phoenix.compile.WhereOptimizer.
> pushKeyExpressionsToScan(WhereOptimizer.java:305)
>
>at org.apache.phoenix.compile.WhereOptimizer.
> pushKeyExpressionsToScan(WhereOptimizer.java:104)
>
>at org.apache.phoenix.compile.WhereOptimizer.
> getKeyExpressionCombination(WhereOptimizer.java:385)
>
>at org.apache.phoenix.compile.QueryCompiler.
> getKeyExpressionCombinations(QueryCompiler.java:460)
>
>at org.apache.phoenix.compile.QueryCompiler.
> compileJoinQuery(QueryCompiler.java:314)
>
>at org.apache.phoenix.compile.QueryCompiler.compileSelect(
> QueryCompiler.java:200)
>
>at org.apache.phoenix.compile.QueryCompiler.compile(
> QueryCompiler.java:157)
>
>at org.apache.phoenix.optimize.QueryOptimizer.addPlan(
> QueryOptimizer.java:302)
>
>at org.apache.phoenix.optimize.QueryOptimizer.
> getHintedQueryPlan(QueryOptimizer.java:196)
>
>at org.apache.phoenix.optimize.QueryOptimizer.
> getApplicablePlans(QueryOptimizer.java:137)
>
>at org.apache.phoenix.optimize.QueryOptimizer.optimize(
> QueryOptimizer.java:94)
>
>at org.apache.phoenix.optimize.QueryOptimizer.optimize(
> QueryOptimizer.java:80)
>
>at org.apache.phoenix.execute.BaseQueryPlan.getExplainPlan(
> BaseQueryPlan.java:505)
>
>at org.apache.phoenix.jdbc.PhoenixStatement$
> ExecutableExplainStatement.compilePlan(PhoenixStatement.java:568)
>
>at org.apache.phoenix.jdbc.PhoenixStatement$
> ExecutableExplainStatement.compilePlan(PhoenixStatement.java:547)
>
>at org.apache.phoenix.jdbc.PhoenixStatement$1.call(
> PhoenixStatement.java:299)
>
>at org.apache.phoenix.jdbc.PhoenixStatement$1.call(
> PhoenixStatement.java:289)
>
>at org.apache.phoenix.call.CallRunner.run(CallRunner.
> java:53)
>
>at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(
> PhoenixStatement.java:288)
>
>at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(
> PhoenixStatement.java:282)
>
>at org.apache.phoenix.jdbc.PhoenixStatement.execute(
> PhoenixStatement.java:1711)
>
>at workbench.sql.SqlCommand.execute(SqlCommand.java:555)
>
>at workbench.sql.StatementRunner.
> runStatement(StatementRunner.java:584)
>
>at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:
> 3433)
>
>at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:
> 2169)
>
>at workbench.gui.sql.SqlPanel$4.run(SqlPanel.java:2107)
>
>
>
>
>
>
>
> I am using phoenix 4.11
>
>
>
>
>
> *Noam *
>
>
>
> --
>
> PRIVILEGED AND CONFIDENTIAL
> PLEASE NOTE: The information contained in this message is privileged and
> confidential, and is intended only for the use of the individual to whom it
> is addressed and others who have been specifically authorized to receive
> it. If you are not the intended recipient, you are hereby notified that any
> dissemination, distribution or copying of this communication is strictly
> prohibited. If you have received this communication in error, or if any
> problems occur with transmission, please contact sender. Thank you.
>

error when using hint on global index where table is using row timestamp mapping

2017-10-01 Thread Bulvik, Noam

Hi

I have create a table and used the row timestamp mapping functionality. The key 
of the table is + column. I also created global index 
on one of the columns of the table  (XXX not one of the key columns).

When I am doing  explain select * from my_table where xxx='' I see that 
index is not used  (even though index is defined on XX column )

CLIENT 9-CHUNK PARALLEL 9-WAY ROUND ROBIN FULL SCAN OVER MY_TABLE
ROW TIMESTAMP FILTER [0, 1506847791496)
SERVER FILTER BY XXX = ''

When I am adding hint I get the following exception (this is from explain ..., 
I get similar error when running the actual query )

java.lang.NullPointerException
   at 
org.apache.phoenix.compile.ScanRanges.getAscTimeRange(ScanRanges.java:674)
   at 
org.apache.phoenix.compile.ScanRanges.getRowTimestampColumnRange(ScanRanges.java:658)
   at 
org.apache.phoenix.compile.ScanRanges.create(ScanRanges.java:84)
   at 
org.apache.phoenix.compile.WhereOptimizer.pushKeyExpressionsToScan(WhereOptimizer.java:305)
   at 
org.apache.phoenix.compile.WhereOptimizer.pushKeyExpressionsToScan(WhereOptimizer.java:104)
   at 
org.apache.phoenix.compile.WhereOptimizer.getKeyExpressionCombination(WhereOptimizer.java:385)
   at 
org.apache.phoenix.compile.QueryCompiler.getKeyExpressionCombinations(QueryCompiler.java:460)
   at 
org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:314)
   at 
org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:200)
   at 
org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:157)
   at 
org.apache.phoenix.optimize.QueryOptimizer.addPlan(QueryOptimizer.java:302)
   at 
org.apache.phoenix.optimize.QueryOptimizer.getHintedQueryPlan(QueryOptimizer.java:196)
   at 
org.apache.phoenix.optimize.QueryOptimizer.getApplicablePlans(QueryOptimizer.java:137)
   at 
org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:94)
   at 
org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:80)
   at 
org.apache.phoenix.execute.BaseQueryPlan.getExplainPlan(BaseQueryPlan.java:505)
   at 
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableExplainStatement.compilePlan(PhoenixStatement.java:568)
   at 
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableExplainStatement.compilePlan(PhoenixStatement.java:547)
   at 
org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:299)
   at 
org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:289)
   at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
   at 
org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:288)
   at 
org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:282)
   at 
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1711)
   at workbench.sql.SqlCommand.execute(SqlCommand.java:555)
   at 
workbench.sql.StatementRunner.runStatement(StatementRunner.java:584)
   at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:3433)
   at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:2169)
   at workbench.gui.sql.SqlPanel$4.run(SqlPanel.java:2107)



I am using phoenix 4.11


Noam




PRIVILEGED AND CONFIDENTIAL
PLEASE NOTE: The information contained in this message is privileged and 
confidential, and is intended only for the use of the individual to whom it is 
addressed and others who have been specifically authorized to receive it. If 
you are not the intended recipient, you are hereby notified that any 
dissemination, distribution or copying of this communication is strictly 
prohibited. If you have received this communication in error, or if any 
problems occur with transmission, please contact sender. Thank you.

Re: Row timestamp usage

2017-05-22 Thread Michael Young

Sergey,
at this point I haven't noticed any issues with the CsvBulkLoadTool on my
version (HDP2.5 with phoenix-4.7.0.2.5.0.0-1245).  I don't have any
secondary indexes, if that is what you are referring to.

If you know any potential side effects, I can look for them.  I will check
JIRA as well.

Thanks for this information!

On Mon, May 22, 2017 at 4:41 PM, Sergey Soldatov 
wrote:

> AFAIK depending on the version of Phoenix you are using, you may
> experience problems with MR bulk load or indexes. Possible some other 'side
> effects' - try to search JIRAs for 'ROW TIMESTAMP".  There is no way to
> alter the column type except drop/create this column.
>
> Thanks,
> Sergey
>
>
> On Mon, May 22, 2017 at 3:45 PM, Michael Young 
> wrote:
>
>> I am using a DATE type column as one of the leading columns in my PK and
>> I am defining it as "ROW TIMESTAMP" to take advantage of the optimizations
>> mentioned here:https://phoenix.apache.org/rowtimestamp.html
>>
>> Are there any disadvantages to using this feature?  My PK has 20+ columns
>> (queries are done over date ranges so I am interested in any optimizations
>> which help such queries).  The value is set on UPSERT to the daily value,
>> the hour/minutes aren't really needed for my use cases so I just use
>> midnight 00:00.000 (eg. 2017-01-01 00:00:00.000).
>>
>> Once it's set, is there a way to alter the column type to be a regular
>> DATE type?  Or would I need to recreate the table?
>>
>> Just wondering out of curiosity in case there are instances where I
>> should not be using this feature.
>>
>> Cheers,
>> -Michael
>>
>>
>

Re: Row timestamp usage

2017-05-22 Thread Sergey Soldatov

AFAIK depending on the version of Phoenix you are using, you may experience
problems with MR bulk load or indexes. Possible some other 'side effects' -
try to search JIRAs for 'ROW TIMESTAMP".  There is no way to alter the
column type except drop/create this column.

Thanks,
Sergey


On Mon, May 22, 2017 at 3:45 PM, Michael Young  wrote:

> I am using a DATE type column as one of the leading columns in my PK and I
> am defining it as "ROW TIMESTAMP" to take advantage of the optimizations
> mentioned here:https://phoenix.apache.org/rowtimestamp.html
>
> Are there any disadvantages to using this feature?  My PK has 20+ columns
> (queries are done over date ranges so I am interested in any optimizations
> which help such queries).  The value is set on UPSERT to the daily value,
> the hour/minutes aren't really needed for my use cases so I just use
> midnight 00:00.000 (eg. 2017-01-01 00:00:00.000).
>
> Once it's set, is there a way to alter the column type to be a regular
> DATE type?  Or would I need to recreate the table?
>
> Just wondering out of curiosity in case there are instances where I should
> not be using this feature.
>
> Cheers,
> -Michael
>
>

Row timestamp usage

2017-05-22 Thread Michael Young

I am using a DATE type column as one of the leading columns in my PK and I
am defining it as "ROW TIMESTAMP" to take advantage of the optimizations
mentioned here:https://phoenix.apache.org/rowtimestamp.html

Are there any disadvantages to using this feature?  My PK has 20+ columns
(queries are done over date ranges so I am interested in any optimizations
which help such queries).  The value is set on UPSERT to the daily value,
the hour/minutes aren't really needed for my use cases so I just use
midnight 00:00.000 (eg. 2017-01-01 00:00:00.000).

Once it's set, is there a way to alter the column type to be a regular DATE
type?  Or would I need to recreate the table?

Just wondering out of curiosity in case there are instances where I should
not be using this feature.

Cheers,
-Michael

Re: Row timestamp

2017-03-12 Thread NaHeon Kim

According to row timestamp documentation 
(https://phoenix.apache.org/rowtimestamp.html 
<https://phoenix.apache.org/rowtimestamp.html>), I don’t think you can.
It says one of primary key could be set to row timestamp column.

Regards,
NaHeon

On 2017-03-11 22:34 (+0900), Batyrshin Alexander <0...@gmail.com> wrote: 
> So main idea behind of "Row Timestamp" feature is to give ability to set 
> HBase cell timestamp via UPSERT?> 
> Is it possible to get cell timestamp for already created HBase table with row 
> keys without timestamp?> 
> 
> As for example. I tried to execute query from page:> 
> 
> 0: jdbc:phoenix:> CREATE TABLE DESTINATION_METRICS_TABLE (CREATED_DATE NOT 
> NULL DATE, METRIC_ID NOT NULL CHAR(15), METRIC_VALUE LONG CONSTRAINT PK 
> PRIMARY KEY(CREATED_DATE ROW_TIMESTAMP, METRIC_ID)) SALT_BUCKETS = 8;> 
> Error: ERROR 601 (42P00): Syntax error. Encountered "NOT" at line 1, column 
> 54. (state=42P00,code=601)> 
> 
> Fixed query is: CREATE TABLE DESTINATION_METRICS_TABLE (CREATED_DATE DATE NOT 
> NULL , METRIC_ID CHAR(15) NOT NULL , METRIC_VALUE UNSIGNED_LONG CONSTRAINT PK 
> PRIMARY KEY(CREATED_DATE ROW_TIMESTAMP, METRIC_ID)) SALT_BUCKETS = 8;> 
> 
> > On 10 Mar 2017, at 19:39, Samarth Jain  wrote:> 
> > > 
> > This is because you are using now() for created. If you used a different 
> > date then with TEST_ROW_TIMESTAMP1, the cell timestamp would be that date 
> > where as with TEST_ROW_TIMESTAMP2 it would be the server side time.> 
> > > 
> > Also, which examples are broken on the page?> 
> > > 
> > On Thu, Mar 9, 2017 at 11:28 AM, Batyrshin Alexander <0x62...@gmail.com 
> > > wrote:> 
> >  Hello,> 
> > Im trying to understand what excatly Phoenix row timestamp is> 
> > I created 2 tables for test:> 
> > > 
> > CREATE TABLE test_row_timestamp1(> 
> > id varchar NOT NULL,> 
> > created TIMESTAMP NOT NULL,> 
> > foo varchar,> 
> > CONSTRAINT PK PRIMARY KEY( id, created ROW_TIMESTAMP )> 
> > )> 
> > > 
> > CREATE TABLE test_row_timestamp2(> 
> > id varchar NOT NULL,> 
> > created TIMESTAMP NOT NULL,> 
> > foo varchar,> 
> > CONSTRAINT PK PRIMARY KEY( id, created )> 
> > )> 
> > > 
> > upsert into test_row_timestamp1 (id, created, foo) values ('1', now(), 
> > 'bar');> 
> > upsert into test_row_timestamp2 (id, created, foo) values ('1', now(), 
> > 'bar');> 
> > > 
> > And result is:> 
> > > 
> > hbase(main):004:0> scan 'TEST_ROW_TIMESTAMP1', { LIMIT=>10}> 
> > ROW  
> > COLUMN+CELL> 
> >  1\x00\x80\x00\x01Z\xB4\x80:6\x00\x00\x00\x00
> > column=0:FOO, timestamp=1489086986806, value=bar> 
> >  1\x00\x80\x00\x01Z\xB4\x80:6\x00\x00\x00\x00
> > column=0:_0, timestamp=1489086986806, value=x> 
> > > 
> > hbase(main):005:0> scan 'TEST_ROW_TIMESTAMP2', { LIMIT=>10}> 
> > ROW  
> > COLUMN+CELL> 
> >  1\x00\x80\x00\x01Z\xB4\x80M\xE6\x00\x00\x00\x00 
> > column=0:FOO, timestamp=1489086991848, value=bar> 
> >  1\x00\x80\x00\x01Z\xB4\x80M\xE6\x00\x00\x00\x00 
> > column=0:_0, timestamp=1489086991848, value=x> 
> > > 
> > Both tables has the same row key pattern id + 0x00 + timestamp> 
> > I expect that test_row_timestamp1 will utilise native hbase timestamp that 
> > is part of "real" hbase key.> 
> > > 
> > > 
> > PS. Examples at https://phoenix.apache.org/rowtimestamp.html 
> > <https://phoenix.apache.org/rowtimestamp.html> are broken> 
> > > 
> 
>

Re: Row timestamp

2017-03-11 Thread Batyrshin Alexander

So main idea behind of "Row Timestamp" feature is to give ability to set HBase 
cell timestamp via UPSERT?
Is it possible to get cell timestamp for already created HBase table with row 
keys without timestamp?

As for example. I tried to execute query from page:

0: jdbc:phoenix:> CREATE TABLE DESTINATION_METRICS_TABLE (CREATED_DATE NOT NULL 
DATE, METRIC_ID NOT NULL CHAR(15), METRIC_VALUE LONG CONSTRAINT PK PRIMARY 
KEY(CREATED_DATE ROW_TIMESTAMP, METRIC_ID)) SALT_BUCKETS = 8;
Error: ERROR 601 (42P00): Syntax error. Encountered "NOT" at line 1, column 54. 
(state=42P00,code=601)

Fixed query is: CREATE TABLE DESTINATION_METRICS_TABLE (CREATED_DATE DATE NOT 
NULL , METRIC_ID CHAR(15) NOT NULL , METRIC_VALUE UNSIGNED_LONG CONSTRAINT PK 
PRIMARY KEY(CREATED_DATE ROW_TIMESTAMP, METRIC_ID)) SALT_BUCKETS = 8;

> On 10 Mar 2017, at 19:39, Samarth Jain  wrote:
> 
> This is because you are using now() for created. If you used a different date 
> then with TEST_ROW_TIMESTAMP1, the cell timestamp would be that date where as 
> with TEST_ROW_TIMESTAMP2 it would be the server side time.
> 
> Also, which examples are broken on the page?
> 
> On Thu, Mar 9, 2017 at 11:28 AM, Batyrshin Alexander <0x62...@gmail.com 
> <mailto:0x62...@gmail.com>> wrote:
>  Hello,
> Im trying to understand what excatly Phoenix row timestamp is
> I created 2 tables for test:
> 
> CREATE TABLE test_row_timestamp1(
> id varchar NOT NULL,
> created TIMESTAMP NOT NULL,
> foo varchar,
> CONSTRAINT PK PRIMARY KEY( id, created ROW_TIMESTAMP )
> )
> 
> CREATE TABLE test_row_timestamp2(
> id varchar NOT NULL,
> created TIMESTAMP NOT NULL,
> foo varchar,
> CONSTRAINT PK PRIMARY KEY( id, created )
> )
> 
> upsert into test_row_timestamp1 (id, created, foo) values ('1', now(), 'bar');
> upsert into test_row_timestamp2 (id, created, foo) values ('1', now(), 'bar');
> 
> And result is:
> 
> hbase(main):004:0> scan 'TEST_ROW_TIMESTAMP1', { LIMIT=>10}
> ROW  COLUMN+CELL
>  1\x00\x80\x00\x01Z\xB4\x80:6\x00\x00\x00\x00
> column=0:FOO, timestamp=1489086986806, value=bar
>  1\x00\x80\x00\x01Z\xB4\x80:6\x00\x00\x00\x00column=0:_0, 
> timestamp=1489086986806, value=x
> 
> hbase(main):005:0> scan 'TEST_ROW_TIMESTAMP2', { LIMIT=>10}
> ROW  COLUMN+CELL
>  1\x00\x80\x00\x01Z\xB4\x80M\xE6\x00\x00\x00\x00 
> column=0:FOO, timestamp=1489086991848, value=bar
>  1\x00\x80\x00\x01Z\xB4\x80M\xE6\x00\x00\x00\x00 column=0:_0, 
> timestamp=1489086991848, value=x
> 
> Both tables has the same row key pattern id + 0x00 + timestamp
> I expect that test_row_timestamp1 will utilise native hbase timestamp that is 
> part of "real" hbase key.
> 
> 
> PS. Examples at https://phoenix.apache.org/rowtimestamp.html 
> <https://phoenix.apache.org/rowtimestamp.html> are broken
>

Re: Row timestamp

2017-03-10 Thread Samarth Jain

This is because you are using now() for created. If you used a different
date then with TEST_ROW_TIMESTAMP1, the cell timestamp would be that date
where as with TEST_ROW_TIMESTAMP2 it would be the server side time.

Also, which examples are broken on the page?

On Thu, Mar 9, 2017 at 11:28 AM, Batyrshin Alexander <0x62...@gmail.com>
wrote:

>  Hello,
> Im trying to understand what excatly Phoenix row timestamp is
> I created 2 tables for test:
>
> CREATE TABLE test_row_timestamp1(
> id varchar NOT NULL,
> created TIMESTAMP NOT NULL,
> foo varchar,
> CONSTRAINT PK PRIMARY KEY( id, created ROW_TIMESTAMP )
> )
>
> CREATE TABLE test_row_timestamp2(
> id varchar NOT NULL,
> created TIMESTAMP NOT NULL,
> foo varchar,
> CONSTRAINT PK PRIMARY KEY( id, created )
> )
>
> upsert into test_row_timestamp1 (id, created, foo) values ('1', now(),
> 'bar');
> upsert into test_row_timestamp2 (id, created, foo) values ('1', now(),
> 'bar');
>
> And result is:
>
> hbase(main):004:0> scan 'TEST_ROW_TIMESTAMP1', { LIMIT=>10}
> ROW
>  COLUMN+CELL
>  1\x00\x80\x00\x01Z\xB4\x80:6\x00\x00\x00\x00
>  column=0:FOO, timestamp=1489086986806, value=bar
>  1\x00\x80\x00\x01Z\xB4\x80:6\x00\x00\x00\x00
>  column=0:_0, timestamp=1489086986806, value=x
>
> hbase(main):005:0> scan 'TEST_ROW_TIMESTAMP2', { LIMIT=>10}
> ROW
>  COLUMN+CELL
>  1\x00\x80\x00\x01Z\xB4\x80M\xE6\x00\x00\x00\x00
> column=0:FOO, timestamp=1489086991848, value=bar
>  1\x00\x80\x00\x01Z\xB4\x80M\xE6\x00\x00\x00\x00
> column=0:_0, timestamp=1489086991848, value=x
>
> Both tables has the same row key pattern id + 0x00 + timestamp
> I expect that test_row_timestamp1 will utilise native hbase timestamp
> that is part of "real" hbase key.
>
>
> PS. Examples at https://phoenix.apache.org/rowtimestamp.html are broken
>

Row timestamp

2017-03-09 Thread Batyrshin Alexander

 Hello,
Im trying to understand what excatly Phoenix row timestamp is
I created 2 tables for test:

CREATE TABLE test_row_timestamp1(
id varchar NOT NULL,
created TIMESTAMP NOT NULL,
foo varchar,
CONSTRAINT PK PRIMARY KEY( id, created ROW_TIMESTAMP )
)

CREATE TABLE test_row_timestamp2(
id varchar NOT NULL,
created TIMESTAMP NOT NULL,
foo varchar,
CONSTRAINT PK PRIMARY KEY( id, created )
)

upsert into test_row_timestamp1 (id, created, foo) values ('1', now(), 'bar');
upsert into test_row_timestamp2 (id, created, foo) values ('1', now(), 'bar');

And result is:

hbase(main):004:0> scan 'TEST_ROW_TIMESTAMP1', { LIMIT=>10}
ROW  COLUMN+CELL
 1\x00\x80\x00\x01Z\xB4\x80:6\x00\x00\x00\x00column=0:FOO, 
timestamp=1489086986806, value=bar
 1\x00\x80\x00\x01Z\xB4\x80:6\x00\x00\x00\x00column=0:_0, 
timestamp=1489086986806, value=x

hbase(main):005:0> scan 'TEST_ROW_TIMESTAMP2', { LIMIT=>10}
ROW  COLUMN+CELL
 1\x00\x80\x00\x01Z\xB4\x80M\xE6\x00\x00\x00\x00 column=0:FOO, 
timestamp=1489086991848, value=bar
 1\x00\x80\x00\x01Z\xB4\x80M\xE6\x00\x00\x00\x00 column=0:_0, 
timestamp=1489086991848, value=x

Both tables has the same row key pattern id + 0x00 + timestamp
I expect that test_row_timestamp1 will utilise native hbase timestamp that is 
part of "real" hbase key.


PS. Examples at https://phoenix.apache.org/rowtimestamp.html 
<https://phoenix.apache.org/rowtimestamp.html> are broken

Re: Row timestamp support in 4.6

2015-12-04 Thread pierre lacave

Thanks Samarth,

It does work with BIGINT, which is fine for my use case.

I raised https://issues.apache.org/jira/browse/PHOENIX-2493 for the
UNSIGNED_LONG issue

*Pierre Lacave*
171 Skellig House, Custom House, Lower Mayor street, Dublin 1, Ireland
Phone :   +353879128708

On Fri, Dec 4, 2015 at 8:04 PM, Samarth Jain  wrote:

> Pierre,
>
> Thanks for reporting this. Do you mind filing a JIRA? Also, as a
> workaround, can you check if changing the data type from UNSIGNED_LONG to
> BIGINT resolves the issue?
>
> -Samarth
>
>
> On Friday, December 4, 2015, pierre lacave  wrote:
>
>>
>> Hi,
>>
>> I am trying to use the ROW_TIMESTAMP mapping featured in 4.6 as described
>> in https ://
>> phoenix.apache.org
>> /
>> rowtimestamp.html
>> 
>>
>> However when inserting a timestamp in nanosecond I get the following
>> exception saying the value cannot be less than zero?
>>
>> Inserting micros,micros or sec result in same result?
>>
>> Any idea what's happening?
>>
>> Thanks
>>
>> 0: jdbc:phoenix:hadoop1-dc:2181:/hbase> CREATE TABLE TEST (t
>> UNSIGNED_LONG NOT NULL CONSTRAINT pk PRIMARY KEY (t ROW_TIMESTAMP) );
>>
>> No rows affected (1.654 seconds)
>>
>> 0: jdbc:phoenix:hadoop1-dc:2181:/hbase> UPSERT INTO TEST (t) VALUES
>> (14491610811);
>>
>> Error: ERROR 201 (22000): Illegal data. Value of a column designated as
>> ROW_TIMESTAMP cannot be less than zero (state=22000,code=201)
>>
>> java.sql.SQLException: ERROR 201 (22000): Illegal data. Value of a column
>> designated as ROW_TIMESTAMP cannot be less than zero
>>
>> at
>> org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:396)
>>
>> at
>> org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
>>
>> at
>> org.apache.phoenix.schema.IllegalDataException.(IllegalDataException.java:38)
>>
>> at
>> org.apache.phoenix.compile.UpsertCompiler.setValues(UpsertCompiler.java:135)
>>
>> at
>> org.apache.phoenix.compile.UpsertCompiler.access$400(UpsertCompiler.java:114)
>>
>> at
>> org.apache.phoenix.compile.UpsertCompiler$3.execute(UpsertCompiler.java:882)
>>
>> at
>> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:322)
>>
>> at
>> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:314)
>>
>> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>>
>> at
>> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:312)
>>
>> at
>> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1435)
>>
>> at sqlline.Commands.execute(Commands.java:822)
>>
>> at sqlline.Commands.sql(Commands.java:732)
>>
>> at sqlline.SqlLine.dispatch(SqlLine.java:808)
>>
>> at sqlline.SqlLine.begin(SqlLine.java:681)
>>
>> at sqlline.SqlLine.start(SqlLine.java:398)
>>
>> at sqlline.SqlLine.main(SqlLine.java:292)
>>
>

Re: Row timestamp support in 4.6

2015-12-04 Thread Samarth Jain

Pierre,

Thanks for reporting this. Do you mind filing a JIRA? Also, as a
workaround, can you check if changing the data type from UNSIGNED_LONG to
BIGINT resolves the issue?

-Samarth

On Friday, December 4, 2015, pierre lacave  wrote:

>
> Hi,
>
> I am trying to use the ROW_TIMESTAMP mapping featured in 4.6 as described
> in https ://
> phoenix.apache.org
> /
> rowtimestamp.html
> 
>
> However when inserting a timestamp in nanosecond I get the following
> exception saying the value cannot be less than zero?
>
> Inserting micros,micros or sec result in same result?
>
> Any idea what's happening?
>
> Thanks
>
> 0: jdbc:phoenix:hadoop1-dc:2181:/hbase> CREATE TABLE TEST (t UNSIGNED_LONG
> NOT NULL CONSTRAINT pk PRIMARY KEY (t ROW_TIMESTAMP) );
>
> No rows affected (1.654 seconds)
>
> 0: jdbc:phoenix:hadoop1-dc:2181:/hbase> UPSERT INTO TEST (t) VALUES
> (14491610811);
>
> Error: ERROR 201 (22000): Illegal data. Value of a column designated as
> ROW_TIMESTAMP cannot be less than zero (state=22000,code=201)
>
> java.sql.SQLException: ERROR 201 (22000): Illegal data. Value of a column
> designated as ROW_TIMESTAMP cannot be less than zero
>
> at
> org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:396)
>
> at
> org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
>
> at
> org.apache.phoenix.schema.IllegalDataException.(IllegalDataException.java:38)
>
> at
> org.apache.phoenix.compile.UpsertCompiler.setValues(UpsertCompiler.java:135)
>
> at
> org.apache.phoenix.compile.UpsertCompiler.access$400(UpsertCompiler.java:114)
>
> at
> org.apache.phoenix.compile.UpsertCompiler$3.execute(UpsertCompiler.java:882)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:322)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:314)
>
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:312)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1435)
>
> at sqlline.Commands.execute(Commands.java:822)
>
> at sqlline.Commands.sql(Commands.java:732)
>
> at sqlline.SqlLine.dispatch(SqlLine.java:808)
>
> at sqlline.SqlLine.begin(SqlLine.java:681)
>
> at sqlline.SqlLine.start(SqlLine.java:398)
>
> at sqlline.SqlLine.main(SqlLine.java:292)
>

Row timestamp support in 4.6

2015-12-04 Thread pierre lacave

Hi,

I am trying to use the ROW_TIMESTAMP mapping featured in 4.6 as described
in https ://
phoenix.apache.org
/
rowtimestamp.html


However when inserting a timestamp in nanosecond I get the following
exception saying the value cannot be less than zero?

Inserting micros,micros or sec result in same result?

Any idea what's happening?

Thanks

0: jdbc:phoenix:hadoop1-dc:2181:/hbase> CREATE TABLE TEST (t UNSIGNED_LONG
NOT NULL CONSTRAINT pk PRIMARY KEY (t ROW_TIMESTAMP) );

No rows affected (1.654 seconds)

0: jdbc:phoenix:hadoop1-dc:2181:/hbase> UPSERT INTO TEST (t) VALUES
(14491610811);

Error: ERROR 201 (22000): Illegal data. Value of a column designated as
ROW_TIMESTAMP cannot be less than zero (state=22000,code=201)

java.sql.SQLException: ERROR 201 (22000): Illegal data. Value of a column
designated as ROW_TIMESTAMP cannot be less than zero

at
org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:396)

at
org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)

at
org.apache.phoenix.schema.IllegalDataException.(IllegalDataException.java:38)

at
org.apache.phoenix.compile.UpsertCompiler.setValues(UpsertCompiler.java:135)

at
org.apache.phoenix.compile.UpsertCompiler.access$400(UpsertCompiler.java:114)

at
org.apache.phoenix.compile.UpsertCompiler$3.execute(UpsertCompiler.java:882)

at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:322)

at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:314)

at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)

at
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:312)

at
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1435)

at sqlline.Commands.execute(Commands.java:822)

at sqlline.Commands.sql(Commands.java:732)

at sqlline.SqlLine.dispatch(SqlLine.java:808)

at sqlline.SqlLine.begin(SqlLine.java:681)

at sqlline.SqlLine.start(SqlLine.java:398)

at sqlline.SqlLine.main(SqlLine.java:292)

Re: Query HBase column/row timestamp

2014-06-07 Thread Pham Phuong Tu

Hi,

Just insert one timestamp column with each phoenix row, you may use one
more column to poitn every row in one day to one "day_time" value.

Eg: "TIME":"1402046771","DAY_TIME":"1401987600"



2014-06-07 14:26 GMT+07:00 James Taylor :

> Hi Justin,
> What Jeffrey said is accurate. It would not be difficult to add a new
> built-in function for this purpose, but it's ambiguous as far as which
> KeyValue you'd use to get the timestamp as there is no "row"
> timestamp. Would you filter all columns against the timestamp? If all
> KeyValues for a row are filtered, would the entire row be filtered?
> How would this be specified in SQL?
>
> If you're interested, maybe take a stab at defining how it would work
> and file a JIRA?
>
> Thanks,
> James
>
> On Fri, Jun 6, 2014 at 3:46 PM, Jeffrey Zhong 
> wrote:
> >
> > As far as I know there is no way in Phoenix that you can explicitly query
> > data based on underlying hbase row key time stamp. Phoenix does provide a
> > connection property "CurrentSCN" which sets the upper bound of time
> stamps
> > but not point or range query against row key time stamp. You can
> explicitly
> > create a timestamp column in your table if there is a need.
> >
> > From: Justin Workman 
> > Reply-To: 
> > Date: Friday, June 6, 2014 2:26 PM
> > To: "u...@phoenix.incubator.apache.org" <
> u...@phoenix.incubator.apache.org>
> > Subject: Query HBase column/row timestamp
> >
> > Sorry if this question has already been answered somewhere, I haven't
> been
> > able to find it through normal channels or searching.
> >
> > Is it possible to query by the Hbase column or row timestamp value?
> > Basically I want to select all records, via Phoenix, where the last
> > timestamp is ~24 hours.
> >
> > Thanks
> > Justin
> >
> > CONFIDENTIALITY NOTICE
> > NOTICE: This message is intended for the use of the individual or entity
> to
> > which it is addressed and may contain information that is confidential,
> > privileged and exempt from disclosure under applicable law. If the
> reader of
> > this message is not the intended recipient, you are hereby notified that
> any
> > printing, copying, dissemination, distribution, disclosure or forwarding
> of
> > this communication is strictly prohibited. If you have received this
> > communication in error, please contact the sender immediately and delete
> it
> > from your system. Thank You.
>

Re: Query HBase column/row timestamp

2014-06-07 Thread James Taylor

Hi Justin,
What Jeffrey said is accurate. It would not be difficult to add a new
built-in function for this purpose, but it's ambiguous as far as which
KeyValue you'd use to get the timestamp as there is no "row"
timestamp. Would you filter all columns against the timestamp? If all
KeyValues for a row are filtered, would the entire row be filtered?
How would this be specified in SQL?

If you're interested, maybe take a stab at defining how it would work
and file a JIRA?

Thanks,
James

On Fri, Jun 6, 2014 at 3:46 PM, Jeffrey Zhong  wrote:
>
> As far as I know there is no way in Phoenix that you can explicitly query
> data based on underlying hbase row key time stamp. Phoenix does provide a
> connection property "CurrentSCN" which sets the upper bound of time stamps
> but not point or range query against row key time stamp. You can explicitly
> create a timestamp column in your table if there is a need.
>
> From: Justin Workman 
> Reply-To: 
> Date: Friday, June 6, 2014 2:26 PM
> To: "u...@phoenix.incubator.apache.org" 
> Subject: Query HBase column/row timestamp
>
> Sorry if this question has already been answered somewhere, I haven't been
> able to find it through normal channels or searching.
>
> Is it possible to query by the Hbase column or row timestamp value?
> Basically I want to select all records, via Phoenix, where the last
> timestamp is ~24 hours.
>
> Thanks
> Justin
>
> CONFIDENTIALITY NOTICE
> NOTICE: This message is intended for the use of the individual or entity to
> which it is addressed and may contain information that is confidential,
> privileged and exempt from disclosure under applicable law. If the reader of
> this message is not the intended recipient, you are hereby notified that any
> printing, copying, dissemination, distribution, disclosure or forwarding of
> this communication is strictly prohibited. If you have received this
> communication in error, please contact the sender immediately and delete it
> from your system. Thank You.

Re: Query HBase column/row timestamp

2014-06-06 Thread Jeffrey Zhong

As far as I know there is no way in Phoenix that you can explicitly query
data based on underlying hbase row key time stamp. Phoenix does provide a
connection property "CurrentSCN" which sets the upper bound of time stamps
but not point or range query against row key time stamp. You can explicitly
create a timestamp column in your table if there is a need.

From:  Justin Workman 
Reply-To:  
Date:  Friday, June 6, 2014 2:26 PM
To:  "u...@phoenix.incubator.apache.org" 
Subject:  Query HBase column/row timestamp

Sorry if this question has already been answered somewhere, I haven't been
able to find it through normal channels or searching.

Is it possible to query by the Hbase column or row timestamp value?
Basically I want to select all records, via Phoenix, where the last
timestamp is ~24 hours.

Thanks
Justin

-- 
CONFIDENTIALITY NOTICE
NOTICE: This message is intended for the use of the individual or entity to 
which it is addressed and may contain information that is confidential, 
privileged and exempt from disclosure under applicable law. If the reader 
of this message is not the intended recipient, you are hereby notified that 
any printing, copying, dissemination, distribution, disclosure or 
forwarding of this communication is strictly prohibited. If you have 
received this communication in error, please contact the sender immediately 
and delete it from your system. Thank You.

Query HBase column/row timestamp

2014-06-06 Thread Justin Workman

Sorry if this question has already been answered somewhere, I haven't been
able to find it through normal channels or searching.

Is it possible to query by the Hbase column or row timestamp value?
Basically I want to select all records, via Phoenix, where the last
timestamp is ~24 hours.

Thanks
Justin

Re: Help: setting hbase row timestamp in phoenix upserts ?

Help: setting hbase row timestamp in phoenix upserts ?

RE: error when using hint on global index where table is using row timestamp mapping

Re: error when using hint on global index where table is using row timestamp mapping

RE: error when using hint on global index where table is using row timestamp mapping

Re: error when using hint on global index where table is using row timestamp mapping

error when using hint on global index where table is using row timestamp mapping

Re: Row timestamp usage

Re: Row timestamp usage

Row timestamp usage

Re: Row timestamp

Re: Row timestamp

Re: Row timestamp

Row timestamp

Re: Row timestamp support in 4.6

Re: Row timestamp support in 4.6

Row timestamp support in 4.6

Re: Query HBase column/row timestamp

Re: Query HBase column/row timestamp

Re: Query HBase column/row timestamp

Query HBase column/row timestamp

21 matches

Site Navigation

Mail list logo

Footer information