UPDATE_CACHE_FREQUENCY on tables with a ROW_TIMESTAMP column
Hi, It seems that the UPDATE_CACHE_FREQUENCY setting is ignored on tables with a ROW_TIMESTAMP column set. This results in an RPC call to fetch the SYSTEM.CATALOG table for every query to such a table and resulting hotspotting on the node assigned the system catalog region. I was wondering if there is any way to avoid these calls on such a table? A little digging led me to PHOENIX-4187<https://issues.apache.org/jira/browse/PHOENIX-4187> <https://issues.apache.org/jira/browse/PHOENIX-4187> which introduced the change to ignore the cache for tables with a row timestamp column. I was wondering what the reasoning / thinking was for the stipulation that tables with row timestamp column cannot be cached? For reference we are using 4.14.0-HBase-1.4 version of the driver, which I realise is quite old, but if anyone has any incite that would be helpful. Below is the stack trace of when the cache is being updated. - waiting on <0x6cb6aa28> (a java.util.concurrent.FutureTask) - locked <0x6cb6aa28> (a java.util.concurrent.FutureTask) at sun.misc.Unsafe.park(Native Method) at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175) at java.util.concurrent.FutureTask.awaitDone(FutureTask.java:429) at java.util.concurrent.FutureTask.get(FutureTask.java:191) at org.apache.hadoop.hbase.client.HTable.coprocessorService(HTable.java:1787) at org.apache.hadoop.hbase.client.HTable.coprocessorService(HTable.java:1743) at org.apache.phoenix.query.ConnectionQueryServicesImpl.metaDataCoprocessorExec(ConnectionQueryServicesImpl.java:1362) at org.apache.phoenix.query.ConnectionQueryServicesImpl.metaDataCoprocessorExec(ConnectionQueryServicesImpl.java:1343) at org.apache.phoenix.query.ConnectionQueryServicesImpl.getTable(ConnectionQueryServicesImpl.java:1560) at org.apache.phoenix.schema.MetaDataClient.updateCache(MetaDataClient.java:643) at org.apache.phoenix.schema.MetaDataClient.updateCache(MetaDataClient.java:538) at org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.createTableRef(FromCompiler.java:573) at org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.(FromCompiler.java:391) at org.apache.phoenix.compile.FromCompiler.getResolverForQuery(FromCompiler.java:228) at org.apache.phoenix.compile.FromCompiler.getResolverForQuery(FromCompiler.java:206) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:482) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:456) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:302) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:291) at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:290) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:283) at org.apache.phoenix.jdbc.PhoenixPreparedStatement.executeQuery(PhoenixPreparedStatement.java:193) at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:698) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:639) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:690) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:717) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:767) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:211) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:219) Many thanks Fergus Fergus Nelson Infrastructure Architect [image.jpeg] fnel...@reliaquest.com / ReliaQuest.com<https://reliaquest.com/>
RE: Problem with ROW_TIMESTAMP
Ok, thanks. I don't think that's an option for now, as I'm using a 3rd party
tool to load data, but might pursue that later if we need more control.
I think I've figured out an alternate solution that works for my needs with
setting a TTL directly on the HBase table.
Thanks,
Dave.
-Original Message-
From: Josh Elser
Sent: June 10, 2019 10:44 AM
To: user@phoenix.apache.org
Subject: Re: Problem with ROW_TIMESTAMP
When you want to use Phoenix to query your data, you're going to have a much
better time if you also use Phoenix to load the data.
Unless you specifically know what you're doing (and how to properly serialize
the data into HBase so that Phoenix can read it), you should use Phoenix to
both read and write your data.
On 6/7/19 1:31 PM, David Auclair wrote:
> Hi,
>
> I’m having a problem with ROW_TIMESTAMP not producing proper output:
>
> Versions (Based on HDP 2.6.5):
>
> HBase Shell = Version 1.1.2.2.6.5.1100-53 (assuming HBase is the same
> version?)
>
> Phoenix = phoenix-4.7.0.2.6.5.1100-53-client.jar
>
> Phoenix Sqlline = 1.1.8
>
> In the HBase shell, I create a table & cf:
>
> hbase(main):001:0> create "threatintel","t"
>
> Once I’ve loaded some data, I can see:
>
> column=t:v, timestamp=1559914430391, value={"ip":"222.102.76.151"}
>
> Via Phoenix I’m trying to create a mapping to the existing table:
>
> create table "threatintel" (ts TIMESTAMP NOT NULL, "t"."v" varchar,
> CONSTRAINT pk PRIMARY KEY (ts ROW_TIMESTAMP));
>
> Via Phoenix sqlline:
>
> Select * from “threatintel” limit 1;
>
> Results in:
>
> | 292264620-05-14 12:29:06.783 | {"ip":"203.198.118.221"} |
>
> Pretty sure that timestamp is incorrect. (And no, that wasn’t the same
> datapoint, but the timestamps were all within a few seconds of each
> other from a bulk import)
>
> Did I do something wrong? Any other info I can provide?
>
> Thanks in advance,
>
> David Auclair
>
Re: Problem with ROW_TIMESTAMP
When you want to use Phoenix to query your data, you're going to have a
much better time if you also use Phoenix to load the data.
Unless you specifically know what you're doing (and how to properly
serialize the data into HBase so that Phoenix can read it), you should
use Phoenix to both read and write your data.
On 6/7/19 1:31 PM, David Auclair wrote:
Hi,
I’m having a problem with ROW_TIMESTAMP not producing proper output:
Versions (Based on HDP 2.6.5):
HBase Shell = Version 1.1.2.2.6.5.1100-53 (assuming HBase is the same
version?)
Phoenix = phoenix-4.7.0.2.6.5.1100-53-client.jar
Phoenix Sqlline = 1.1.8
In the HBase shell, I create a table & cf:
hbase(main):001:0> create "threatintel","t"
Once I’ve loaded some data, I can see:
column=t:v, timestamp=1559914430391, value={"ip":"222.102.76.151"}
Via Phoenix I’m trying to create a mapping to the existing table:
create table "threatintel" (ts TIMESTAMP NOT NULL, "t"."v" varchar,
CONSTRAINT pk PRIMARY KEY (ts ROW_TIMESTAMP));
Via Phoenix sqlline:
Select * from “threatintel” limit 1;
Results in:
| 292264620-05-14 12:29:06.783 | {"ip":"203.198.118.221"} |
Pretty sure that timestamp is incorrect. (And no, that wasn’t the same
datapoint, but the timestamps were all within a few seconds of each
other from a bulk import)
Did I do something wrong? Any other info I can provide?
Thanks in advance,
David Auclair
Problem with ROW_TIMESTAMP
Hi,
I'm having a problem with ROW_TIMESTAMP not producing proper output:
Versions (Based on HDP 2.6.5):
HBase Shell = Version 1.1.2.2.6.5.1100-53 (assuming HBase is the same version?)
Phoenix = phoenix-4.7.0.2.6.5.1100-53-client.jar
Phoenix Sqlline = 1.1.8
In the HBase shell, I create a table & cf:
hbase(main):001:0> create "threatintel","t"
Once I've loaded some data, I can see:
column=t:v, timestamp=1559914430391, value={"ip":"222.102.76.151"}
Via Phoenix I'm trying to create a mapping to the existing table:
create table "threatintel" (ts TIMESTAMP NOT NULL, "t"."v" varchar, CONSTRAINT
pk PRIMARY KEY (ts ROW_TIMESTAMP));
Via Phoenix sqlline:
Select * from "threatintel" limit 1;
Results in:
| 292264620-05-14 12:29:06.783 | {"ip":"203.198.118.221"} |
Pretty sure that timestamp is incorrect. (And no, that wasn't the same
datapoint, but the timestamps were all within a few seconds of each other from
a bulk import)
Did I do something wrong? Any other info I can provide?
Thanks in advance,
David Auclair
Re: ROW_TIMESTAMP
Right. Will do in this way. Thank you again. :-) James Taylor wrote > Yes, I think having your own LAST_UPDATED column would be the best option > currently. > > On Fri, Feb 2, 2018 at 1:18 PM, Alberto Bengoa < > alberto@.com > > > wrote: > >> Hello James, >> >> Thanks for replying. >> >> It really seems that PHOENIX-4552 potentially fits to my purpose. I'll >> track >> this JIRA to get updates about it. >> >> BTW, considering nowadays, there's no option except to update some date >> type >> field on client side every upsert? >> >> Thank you so much. >> >> Alberto >> >> >> James Taylor wrote >> > Hi Alberto, >> > Sounds like you need PHOENIX-4552. If you agree, let's continue the >> > discussion over there. >> > Thanks, >> > James >> > >> > On Fri, Feb 2, 2018 at 9:05 AM, Alberto Bengoa < >> >> > alberto@.com >> >> > > >> > wrote: >> > >> >> Hello Folks, >> >> >> >> I'm working on a project where we need to identify when a row was >> changed >> >> (updated fields). I was wondering if ROW_TIMESTAMP would help me to >> reach >> >> this goal. >> >> >> >> I created the test table bellow, and inserted some data: >> >> >> >> create table test( >> >> a integer not null, >> >> b integer, >> >> last_update date not null >> >> CONSTRAINT PK PRIMARY KEY (a, last_update row_timestamp) >> >> ); >> >> >> >> upsert into test (a, b) values (1, 1); >> >> upsert into test (a, b) values (2, 2); >> >> upsert into test (a, b) values (3, 4); >> >> >> >> 0: jdbc:phoenix:> select * from test; >> >> +++--+ >> >> | A | B | LAST_UPDATE| >> >> +++--+ >> >> | 1 | 1 | 2018-02-02 16:33:52.345 | >> >> | 2 | 2 | 2018-02-02 16:33:56.714 | >> >> | 3 | 4 | 2018-02-02 16:34:00.281 | >> >> +++--+ >> >> 3 rows selected (0.041 seconds) >> >> >> >> So, I've tried to update B value where A = 3; >> >> >> >> 0: jdbc:phoenix:> upsert into test (a, b) values (3, 3); >> >> >> >> Then, I have one "new" row, not an updated row as I need: >> >> >> >> 0: jdbc:phoenix:> select * from test; >> >> +++--+ >> >> | A | B | LAST_UPDATE| >> >> +++--+ >> >> | 1 | 1 | 2018-02-02 16:33:52.345 | >> >> | 2 | 2 | 2018-02-02 16:33:56.714 | >> >> | 3 | 4 | 2018-02-02 16:34:00.281 | >> >> | 3 | 3 | 2018-02-02 16:36:31.890 | >> >> +++--+ >> >> 4 rows selected (0.052 seconds) >> >> >> >> I understand that LAST_UPDATE column is part of the PRIMARY KEY and, >> from >> >> this perspective, it's in fact should be a NEW row. But, on the other >> >> hand, >> >> this not fits my case, because actually I'll have a new row after each >> >> "update" (and I have lots of updates). >> >> >> >> There's any alternative to this on the Phoenix side? I was not >> expecting >> >> to have to call a now() function from client side all the time to >> update >> >> a >> >> last_update field. >> >> >> >> Maybe another kind of CONSTRAINT that would be used? >> >> >> >> Phoenix version 4.7 here. >> >> >> >> Thanks in advanced! >> >> >> >> Cheers, >> >> Alberto >> >> >> >> >> >> >> >> >> >> -- >> Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/ >> -- Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/
Re: ROW_TIMESTAMP
Yes, I think having your own LAST_UPDATED column would be the best option currently. On Fri, Feb 2, 2018 at 1:18 PM, Alberto Bengoa wrote: > Hello James, > > Thanks for replying. > > It really seems that PHOENIX-4552 potentially fits to my purpose. I'll > track > this JIRA to get updates about it. > > BTW, considering nowadays, there's no option except to update some date > type > field on client side every upsert? > > Thank you so much. > > Alberto > > > James Taylor wrote > > Hi Alberto, > > Sounds like you need PHOENIX-4552. If you agree, let's continue the > > discussion over there. > > Thanks, > > James > > > > On Fri, Feb 2, 2018 at 9:05 AM, Alberto Bengoa < > > > alberto@.com > > > > > > wrote: > > > >> Hello Folks, > >> > >> I'm working on a project where we need to identify when a row was > changed > >> (updated fields). I was wondering if ROW_TIMESTAMP would help me to > reach > >> this goal. > >> > >> I created the test table bellow, and inserted some data: > >> > >> create table test( > >> a integer not null, > >> b integer, > >> last_update date not null > >> CONSTRAINT PK PRIMARY KEY (a, last_update row_timestamp) > >> ); > >> > >> upsert into test (a, b) values (1, 1); > >> upsert into test (a, b) values (2, 2); > >> upsert into test (a, b) values (3, 4); > >> > >> 0: jdbc:phoenix:> select * from test; > >> +++--+ > >> | A | B | LAST_UPDATE| > >> +++--+ > >> | 1 | 1 | 2018-02-02 16:33:52.345 | > >> | 2 | 2 | 2018-02-02 16:33:56.714 | > >> | 3 | 4 | 2018-02-02 16:34:00.281 | > >> +++--+ > >> 3 rows selected (0.041 seconds) > >> > >> So, I've tried to update B value where A = 3; > >> > >> 0: jdbc:phoenix:> upsert into test (a, b) values (3, 3); > >> > >> Then, I have one "new" row, not an updated row as I need: > >> > >> 0: jdbc:phoenix:> select * from test; > >> +++--+ > >> | A | B | LAST_UPDATE| > >> +++--+ > >> | 1 | 1 | 2018-02-02 16:33:52.345 | > >> | 2 | 2 | 2018-02-02 16:33:56.714 | > >> | 3 | 4 | 2018-02-02 16:34:00.281 | > >> | 3 | 3 | 2018-02-02 16:36:31.890 | > >> +++--+ > >> 4 rows selected (0.052 seconds) > >> > >> I understand that LAST_UPDATE column is part of the PRIMARY KEY and, > from > >> this perspective, it's in fact should be a NEW row. But, on the other > >> hand, > >> this not fits my case, because actually I'll have a new row after each > >> "update" (and I have lots of updates). > >> > >> There's any alternative to this on the Phoenix side? I was not expecting > >> to have to call a now() function from client side all the time to update > >> a > >> last_update field. > >> > >> Maybe another kind of CONSTRAINT that would be used? > >> > >> Phoenix version 4.7 here. > >> > >> Thanks in advanced! > >> > >> Cheers, > >> Alberto > >> > >> > > > > > > -- > Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/ >
Re: ROW_TIMESTAMP
Hello James, Thanks for replying. It really seems that PHOENIX-4552 potentially fits to my purpose. I'll track this JIRA to get updates about it. BTW, considering nowadays, there's no option except to update some date type field on client side every upsert? Thank you so much. Alberto James Taylor wrote > Hi Alberto, > Sounds like you need PHOENIX-4552. If you agree, let's continue the > discussion over there. > Thanks, > James > > On Fri, Feb 2, 2018 at 9:05 AM, Alberto Bengoa < > alberto@.com > > > wrote: > >> Hello Folks, >> >> I'm working on a project where we need to identify when a row was changed >> (updated fields). I was wondering if ROW_TIMESTAMP would help me to reach >> this goal. >> >> I created the test table bellow, and inserted some data: >> >> create table test( >> a integer not null, >> b integer, >> last_update date not null >> CONSTRAINT PK PRIMARY KEY (a, last_update row_timestamp) >> ); >> >> upsert into test (a, b) values (1, 1); >> upsert into test (a, b) values (2, 2); >> upsert into test (a, b) values (3, 4); >> >> 0: jdbc:phoenix:> select * from test; >> +++--+ >> | A | B | LAST_UPDATE| >> +++--+ >> | 1 | 1 | 2018-02-02 16:33:52.345 | >> | 2 | 2 | 2018-02-02 16:33:56.714 | >> | 3 | 4 | 2018-02-02 16:34:00.281 | >> +++--+ >> 3 rows selected (0.041 seconds) >> >> So, I've tried to update B value where A = 3; >> >> 0: jdbc:phoenix:> upsert into test (a, b) values (3, 3); >> >> Then, I have one "new" row, not an updated row as I need: >> >> 0: jdbc:phoenix:> select * from test; >> +++--+ >> | A | B | LAST_UPDATE| >> +++--+ >> | 1 | 1 | 2018-02-02 16:33:52.345 | >> | 2 | 2 | 2018-02-02 16:33:56.714 | >> | 3 | 4 | 2018-02-02 16:34:00.281 | >> | 3 | 3 | 2018-02-02 16:36:31.890 | >> +++--+ >> 4 rows selected (0.052 seconds) >> >> I understand that LAST_UPDATE column is part of the PRIMARY KEY and, from >> this perspective, it's in fact should be a NEW row. But, on the other >> hand, >> this not fits my case, because actually I'll have a new row after each >> "update" (and I have lots of updates). >> >> There's any alternative to this on the Phoenix side? I was not expecting >> to have to call a now() function from client side all the time to update >> a >> last_update field. >> >> Maybe another kind of CONSTRAINT that would be used? >> >> Phoenix version 4.7 here. >> >> Thanks in advanced! >> >> Cheers, >> Alberto >> >> -- Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/
Re: ROW_TIMESTAMP
Hi Alberto, Sounds like you need PHOENIX-4552. If you agree, let's continue the discussion over there. Thanks, James On Fri, Feb 2, 2018 at 9:05 AM, Alberto Bengoa wrote: > Hello Folks, > > I'm working on a project where we need to identify when a row was changed > (updated fields). I was wondering if ROW_TIMESTAMP would help me to reach > this goal. > > I created the test table bellow, and inserted some data: > > create table test( > a integer not null, > b integer, > last_update date not null > CONSTRAINT PK PRIMARY KEY (a, last_update row_timestamp) > ); > > upsert into test (a, b) values (1, 1); > upsert into test (a, b) values (2, 2); > upsert into test (a, b) values (3, 4); > > 0: jdbc:phoenix:> select * from test; > +++--+ > | A | B | LAST_UPDATE| > +++--+ > | 1 | 1 | 2018-02-02 16:33:52.345 | > | 2 | 2 | 2018-02-02 16:33:56.714 | > | 3 | 4 | 2018-02-02 16:34:00.281 | > +++--+ > 3 rows selected (0.041 seconds) > > So, I've tried to update B value where A = 3; > > 0: jdbc:phoenix:> upsert into test (a, b) values (3, 3); > > Then, I have one "new" row, not an updated row as I need: > > 0: jdbc:phoenix:> select * from test; > +++--+ > | A | B | LAST_UPDATE| > +++--+ > | 1 | 1 | 2018-02-02 16:33:52.345 | > | 2 | 2 | 2018-02-02 16:33:56.714 | > | 3 | 4 | 2018-02-02 16:34:00.281 | > | 3 | 3 | 2018-02-02 16:36:31.890 | > +++--+ > 4 rows selected (0.052 seconds) > > I understand that LAST_UPDATE column is part of the PRIMARY KEY and, from > this perspective, it's in fact should be a NEW row. But, on the other hand, > this not fits my case, because actually I'll have a new row after each > "update" (and I have lots of updates). > > There's any alternative to this on the Phoenix side? I was not expecting > to have to call a now() function from client side all the time to update a > last_update field. > > Maybe another kind of CONSTRAINT that would be used? > > Phoenix version 4.7 here. > > Thanks in advanced! > > Cheers, > Alberto > >
ROW_TIMESTAMP
Hello Folks, I'm working on a project where we need to identify when a row was changed (updated fields). I was wondering if ROW_TIMESTAMP would help me to reach this goal. I created the test table bellow, and inserted some data: create table test( a integer not null, b integer, last_update date not null CONSTRAINT PK PRIMARY KEY (a, last_update row_timestamp) ); upsert into test (a, b) values (1, 1); upsert into test (a, b) values (2, 2); upsert into test (a, b) values (3, 4); 0: jdbc:phoenix:> select * from test; +++--+ | A | B | LAST_UPDATE| +++--+ | 1 | 1 | 2018-02-02 16:33:52.345 | | 2 | 2 | 2018-02-02 16:33:56.714 | | 3 | 4 | 2018-02-02 16:34:00.281 | +++--+ 3 rows selected (0.041 seconds) So, I've tried to update B value where A = 3; 0: jdbc:phoenix:> upsert into test (a, b) values (3, 3); Then, I have one "new" row, not an updated row as I need: 0: jdbc:phoenix:> select * from test; +++--+ | A | B | LAST_UPDATE| +++--+ | 1 | 1 | 2018-02-02 16:33:52.345 | | 2 | 2 | 2018-02-02 16:33:56.714 | | 3 | 4 | 2018-02-02 16:34:00.281 | | 3 | 3 | 2018-02-02 16:36:31.890 | +++--+ 4 rows selected (0.052 seconds) I understand that LAST_UPDATE column is part of the PRIMARY KEY and, from this perspective, it's in fact should be a NEW row. But, on the other hand, this not fits my case, because actually I'll have a new row after each "update" (and I have lots of updates). There's any alternative to this on the Phoenix side? I was not expecting to have to call a now() function from client side all the time to update a last_update field. Maybe another kind of CONSTRAINT that would be used? Phoenix version 4.7 here. Thanks in advanced! Cheers, Alberto
store ROW_TIMESTAMP as TIMESTAMP in nanoseconds
Hi Team, I would like to store ROW_TIMESTAMP as TIMESTAMP in nanoseconds, what is the best way to generate Timestamp with nanoseconds and when I query using ROW_TIMESTAMP stored in nanoseconds do I loose the precision to milliseconds. Thanks
Re: Cannot upsert row_timestamp value
Hi, I got the same error when upserting row_timestamp value. I'm using phoenix-4.7.0 It seems that you find a solution, but I did not see anything useful following the link you replied to. Could you let me know how you solve the problem? Thanks Xindian -- View this message in context: http://apache-phoenix-user-list.1124778.n5.nabble.com/Cannot-upsert-row-timestamp-value-tp3218p3534.html Sent from the Apache Phoenix User List mailing list archive at Nabble.com.
ROW_TIMESTAMP doesn't work with TIMESTAMP datatype.
I'm encountering the following issue with Phoenix: create table rtt (ts TIMESTAMP PRIMARY KEY ROW_TIMESTAMP); select * from rtt where ts < now(); java.lang.NullPointerException at org.apache.phoenix.compile.ScanRanges.getAscTimeRange(ScanRanges.java:680) 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.WhereCompiler.compile(WhereCompiler.java:149) at org.apache.phoenix.compile.WhereCompiler.compile(WhereCompiler.java:100) at org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:556) at org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:507) at org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:202) at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:157) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:420) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:394) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:280) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:270) at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:269) at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1515) at sqlline.Commands.execute(Commands.java:822) at sqlline.Commands.sql(Commands.java:732) at sqlline.SqlLine.dispatch(SqlLine.java:813) at sqlline.SqlLine.begin(SqlLine.java:686) at sqlline.SqlLine.start(SqlLine.java:398) at sqlline.SqlLine.main(SqlLine.java:291) There is no problem when I use the TIME, DATE, and UNSIGNED_LONG datatypes. I am using phoenix-4.9 from the phoenix-for-cloudera branch, so I'm not sure if this is a bug in the main branch or just in mine. Could anyone check if you also encounter this behavior?
Re: ROW_TIMESTAMP weird behaviour
Hi,
Does ‘phoenix.query.dateFormatTimeZone’ work as expected?
Though dateFormatTimeZone option and HBase servers are set to UTC+9 timezone,
UTC+9 based timestamp values are still hidden.
What I expect is that those values are available immediately right after upsert.
phoenix-4.8.0-HBase-1.1 is used! : )
Regards,
NaHeon
On 2017-02-07 23:45 (+0900), Ankit Singhal wrote:
> It's actually getting added but may be due to timezone difference, your>
> values are going in future.>
> You can set the local time zone by setting phoenix.query.dateFormatTimeZone>
> https://phoenix.apache.org/tuning.html>
>
> On Tue, Feb 7, 2017 at 6:34 PM, Dhaval Modi wrote:>
>
> > Thanks Ankit.>
> >>
> > My issue is relevant to PHOENIX-3176.>
> >>
> > But additional observation is, any timestamp value after 13:oo hours of>
> > the same day is not added.>
> >>
> > 0: jdbc:phoenix:> select * from DUMMY;>
> > +--+>
> > | XXX_TS |>
> > +--+>
> > | 2017-01-01 15:02:21.050 |>
> > | 2017-01-02 15:02:21.050 |>
> > | 2017-01-13 15:02:21.050 |>
> > | 2017-02-06 15:02:21.050 |>
> > | 2017-02-07 11:02:21.050 |>
> > | 2017-02-07 11:03:21.050 |>
> > | 2017-02-07 12:02:21.050 |>
> > +--+>
> > 7 rows selected (0.044 seconds)>
> > 0: jdbc:phoenix:> upsert into DUMMY values('2017-02-07T*12:03:21.050'*);>
> > 1 row affected (0.01 seconds)>
> > 0: jdbc:phoenix:> select * from DUMMY;>
> > +--+>
> > | XXX_TS |>
> > +--+>
> > | 2017-01-01 15:02:21.050 |>
> > | 2017-01-02 15:02:21.050 |>
> > | 2017-01-13 15:02:21.050 |>
> > | 2017-02-06 15:02:21.050 |>
> > | 2017-02-07 11:02:21.050 |>
> > | 2017-02-07 11:03:21.050 |>
> > | 2017-02-07 12:02:21.050 |>
> > *| 2017-02-07 12:03:21.050 |*>
> > +--+>
> > 8 rows selected (0.047 seconds)>
> > 0: jdbc:phoenix:> upsert into DUMMY values('2017-02-07T*13:03:21.050*');>
> > 1 row affected (0.009 seconds)>
> > 0: jdbc:phoenix:> select * from DUMMY;>
> > +--+>
> > | XXX_TS |>
> > +--+>
> > | 2017-01-01 15:02:21.050 |>
> > | 2017-01-02 15:02:21.050 |>
> > | 2017-01-13 15:02:21.050 |>
> > | 2017-02-06 15:02:21.050 |>
> > | 2017-02-07 11:02:21.050 |>
> > | 2017-02-07 11:03:21.050 |>
> > | 2017-02-07 12:02:21.050 |>
> > | 2017-02-07 12:03:21.050 |>
> > +--+>
> > 8 rows selected (0.04 seconds)>
> >>
> >>
> >>
> >>
> >>
> >>
> > Regards,>
> > Dhaval Modi>
> > dhavalmod...@gmail.com>
> >>
> > On 7 February 2017 at 15:28, Ankit Singhal >
> > wrote:>
> >>
> >> I think you are also hitting https://issues.apache.>
> >> org/jira/browse/PHOENIX-3176.>
> >>>
> >> On Tue, Feb 7, 2017 at 2:18 PM, Dhaval Modi >
> >> wrote:>
> >>>
> >>> Hi Pedro,>
> >>>>
> >>> Upserted key are different. One key is for July month & other for>
> >>> January month.>
> >>> 1. '2017-*07*-02T15:02:21.050'>
> >>> 2. '2017-*01*-02T15:02:21.050'>
> >>>>
> >>>>
> >>> Regards,>
> >>> Dhaval Modi>
> >>> dhavalmod...@gmail.com>
> >>>>
> >>> On 7 February 2017 at 13:18, Pedro Boado wrote:>
> >>>>
> >>>> Hi.>
> >>>>>
> >>>> I don't think it's weird. That column is PK and you've upserted twice>
> >>>> the same key value so first one is inserted and second one is updated.>
> >>>>>
> >>>> Regards.>
> >>>>>
> >>>>>
> >>>>>
> >>>> On 7 Feb 2017 04:59, "Dhaval Modi" wrote:>
> >>>>>
> >>>>> Hi All,>
> >>>>>>
> >>>>> I am facing abnormal scenarios with ROW_TIMESTAMP.>
> >>>>>>
> >>>>> I created table in Phoenix as below:>
> >>>>> CREATE TABLE DUMMY(XXX_TS TIMESTAMP NOT NULL CONSTRAINT pk PRIMARY KEY>
> >>>>> (XXX_TS ROW_TIMESTAMP))>
> >>>>> where "XXX_TS" is used as ROW_TIMESTAMP.>
> >>>>>>
> >>>>> Now, I am trying to add data:>
> >>>>> upsert into DUMMY values('2017-07-02T15:02:21.050');>
> >>>>> upsert into DUMMY values('2017-01-02T15:02:21.050');>
> >>>>>>
> >>>>> I am only seeing one entry.>
> >>>>> *==*>
> >>>>> *0: jdbc:phoenix:> select * from DUMMY;*>
> >>>>> *+--+*>
> >>>>> *| XXX_TS |*>
> >>>>> *+--+*>
> >>>>> *| 2017-01-02 15:02:21.050 |*>
> >>>>> *+--+*>
> >>>>> *1 row selected (0.039 seconds)*>
> >>>>> *==*>
> >>>>>>
> >>>>>>
> >>>>> Additional info:>
> >>>>> System date of HBase & Phoenix: mar feb 7 05:57:37 CET 2017>
> >>>>>>
> >>>>>>
> >>>>> Regards,>
> >>>>> Dhaval Modi>
> >>>>> dhavalmod...@gmail.com>
> >>>>>>
> >>>>>
> >>>>
> >>>
> >>
>
Re: Cannot upsert row_timestamp value
Thanks alot!!! It works. : )
Rdgards,
NaHeon
2017-02-26 15:33 GMT+09:00 Dhaval Modi :
> Hi NaHeon Kim,
>
> Please refer to mailing list:
> https://lists.apache.org/thread.html/fb747661f535b0a407bf38e6b961a2
> c68634815189c80a7d612366b1@%3Cuser.phoenix.apache.org%3E
>
>
> I also faced similar issue.
>
>
> Regards,
> Dhaval Modi
> dhavalmod...@gmail.com
>
> On 26 February 2017 at 10:34, NaHeon Kim wrote:
>
>> Hi all,
>>
>> UPSERT into a table with ROW_TIMESTAMP column is not possible.
>> I'm using phoenix-4.8.0-hbase-1.1.
>>
>> Table Schema:
>>
>> create table my_table (
>>obj_id varchar(20) not null,
>>create_dt timestamp not null,
>>keyword varchar(100) not null,
>>count integer
>>constraint pk primary key (obj_id, create_dt row_timestamp, keyword)
>> );
>>
>>
>> 1) Spark Integration
>> No rows are inserted. No errors.
>>
>> 2) sqlline.py - timestamp column is in a query
>> No rows are inserted.
>>
>> upsert into my_table (obj_id, create_dt, keyword, count)
>> values ('objid', '2017-02-26 13:48:00', 'k', 100);
>> 3) sqlline.py - timestamp column is not in a query
>> This throws an exception:
>>
>>> java.lang.ArrayIndexOutOfBoundsException: 8
>>> at org.apache.phoenix.execute.MutationState.getNewRowKeyWithRow
>>> Timestamp(MutationState.java:548)
>>> at org.apache.phoenix.execute.MutationState.generateMutations(M
>>> utationState.java:627)
>>> at org.apache.phoenix.execute.MutationState.addRowMutations(Mut
>>> ationState.java:566)
>>> at org.apache.phoenix.execute.MutationState.send(MutationState.java:908)
>>> at org.apache.phoenix.execute.MutationState.send(MutationState.
>>> java:1329)
>>> at org.apache.phoenix.execute.MutationState.commit(MutationStat
>>> e.java:1161)
>>> at org.apache.phoenix.jdbc.PhoenixConnection$3.call(PhoenixConn
>>> ection.java:529)
>>
>>
>> upsert into my_table (obj_id, keyword, count)
>> values ('objid', 'k', 100);
>>
>> Everything works well without row_timestamp.
>> Thanks in advance! : )
>>
>
>
Re: Cannot upsert row_timestamp value
Hi NaHeon Kim,
Please refer to mailing list:
https://lists.apache.org/thread.html/fb747661f535b0a407bf38e6b961a2c68634815189c80a7d612366b1@%3Cuser.phoenix.apache.org%3E
I also faced similar issue.
Regards,
Dhaval Modi
dhavalmod...@gmail.com
On 26 February 2017 at 10:34, NaHeon Kim wrote:
> Hi all,
>
> UPSERT into a table with ROW_TIMESTAMP column is not possible.
> I'm using phoenix-4.8.0-hbase-1.1.
>
> Table Schema:
>
> create table my_table (
>obj_id varchar(20) not null,
>create_dt timestamp not null,
>keyword varchar(100) not null,
>count integer
>constraint pk primary key (obj_id, create_dt row_timestamp, keyword)
> );
>
>
> 1) Spark Integration
> No rows are inserted. No errors.
>
> 2) sqlline.py - timestamp column is in a query
> No rows are inserted.
>
> upsert into my_table (obj_id, create_dt, keyword, count)
> values ('objid', '2017-02-26 13:48:00', 'k', 100);
> 3) sqlline.py - timestamp column is not in a query
> This throws an exception:
>
>> java.lang.ArrayIndexOutOfBoundsException: 8
>> at org.apache.phoenix.execute.MutationState.getNewRowKeyWithRowTimestamp(
>> MutationState.java:548)
>> at org.apache.phoenix.execute.MutationState.generateMutations(
>> MutationState.java:627)
>> at org.apache.phoenix.execute.MutationState.addRowMutations(
>> MutationState.java:566)
>> at org.apache.phoenix.execute.MutationState.send(MutationState.java:908)
>> at org.apache.phoenix.execute.MutationState.send(MutationState.java:1329)
>> at org.apache.phoenix.execute.MutationState.commit(
>> MutationState.java:1161)
>> at org.apache.phoenix.jdbc.PhoenixConnection$3.call(
>> PhoenixConnection.java:529)
>
>
> upsert into my_table (obj_id, keyword, count)
> values ('objid', 'k', 100);
>
> Everything works well without row_timestamp.
> Thanks in advance! : )
>
Cannot upsert row_timestamp value
Hi all,
UPSERT into a table with ROW_TIMESTAMP column is not possible.
I'm using phoenix-4.8.0-hbase-1.1.
Table Schema:
create table my_table (
obj_id varchar(20) not null,
create_dt timestamp not null,
keyword varchar(100) not null,
count integer
constraint pk primary key (obj_id, create_dt row_timestamp, keyword)
);
1) Spark Integration
No rows are inserted. No errors.
2) sqlline.py - timestamp column is in a query
No rows are inserted.
upsert into my_table (obj_id, create_dt, keyword, count)
values ('objid', '2017-02-26 13:48:00', 'k', 100);
3) sqlline.py - timestamp column is not in a query
This throws an exception:
> java.lang.ArrayIndexOutOfBoundsException: 8
> at
> org.apache.phoenix.execute.MutationState.getNewRowKeyWithRowTimestamp(MutationState.java:548)
> at
> org.apache.phoenix.execute.MutationState.generateMutations(MutationState.java:627)
> at
> org.apache.phoenix.execute.MutationState.addRowMutations(MutationState.java:566)
> at org.apache.phoenix.execute.MutationState.send(MutationState.java:908)
> at org.apache.phoenix.execute.MutationState.send(MutationState.java:1329)
> at org.apache.phoenix.execute.MutationState.commit(MutationState.java:1161)
> at
> org.apache.phoenix.jdbc.PhoenixConnection$3.call(PhoenixConnection.java:529)
upsert into my_table (obj_id, keyword, count)
values ('objid', 'k', 100);
Everything works well without row_timestamp.
Thanks in advance! : )
Re: ROW_TIMESTAMP weird behaviour
It's actually getting added but may be due to timezone difference, your
values are going in future.
You can set the local time zone by setting phoenix.query.dateFormatTimeZone
https://phoenix.apache.org/tuning.html
On Tue, Feb 7, 2017 at 6:34 PM, Dhaval Modi wrote:
> Thanks Ankit.
>
> My issue is relevant to PHOENIX-3176.
>
> But additional observation is, any timestamp value after 13:oo hours of
> the same day is not added.
>
> 0: jdbc:phoenix:> select * from DUMMY;
> +--+
> | XXX_TS |
> +--+
> | 2017-01-01 15:02:21.050 |
> | 2017-01-02 15:02:21.050 |
> | 2017-01-13 15:02:21.050 |
> | 2017-02-06 15:02:21.050 |
> | 2017-02-07 11:02:21.050 |
> | 2017-02-07 11:03:21.050 |
> | 2017-02-07 12:02:21.050 |
> +--+
> 7 rows selected (0.044 seconds)
> 0: jdbc:phoenix:> upsert into DUMMY values('2017-02-07T*12:03:21.050'*);
> 1 row affected (0.01 seconds)
> 0: jdbc:phoenix:> select * from DUMMY;
> +--+
> | XXX_TS |
> +--+
> | 2017-01-01 15:02:21.050 |
> | 2017-01-02 15:02:21.050 |
> | 2017-01-13 15:02:21.050 |
> | 2017-02-06 15:02:21.050 |
> | 2017-02-07 11:02:21.050 |
> | 2017-02-07 11:03:21.050 |
> | 2017-02-07 12:02:21.050 |
> *| 2017-02-07 12:03:21.050 |*
> +--+
> 8 rows selected (0.047 seconds)
> 0: jdbc:phoenix:> upsert into DUMMY values('2017-02-07T*13:03:21.050*');
> 1 row affected (0.009 seconds)
> 0: jdbc:phoenix:> select * from DUMMY;
> +--+
> | XXX_TS |
> +--+
> | 2017-01-01 15:02:21.050 |
> | 2017-01-02 15:02:21.050 |
> | 2017-01-13 15:02:21.050 |
> | 2017-02-06 15:02:21.050 |
> | 2017-02-07 11:02:21.050 |
> | 2017-02-07 11:03:21.050 |
> | 2017-02-07 12:02:21.050 |
> | 2017-02-07 12:03:21.050 |
> +--+
> 8 rows selected (0.04 seconds)
>
>
>
>
>
>
> Regards,
> Dhaval Modi
> dhavalmod...@gmail.com
>
> On 7 February 2017 at 15:28, Ankit Singhal
> wrote:
>
>> I think you are also hitting https://issues.apache.
>> org/jira/browse/PHOENIX-3176.
>>
>> On Tue, Feb 7, 2017 at 2:18 PM, Dhaval Modi
>> wrote:
>>
>>> Hi Pedro,
>>>
>>> Upserted key are different. One key is for July month & other for
>>> January month.
>>> 1. '2017-*07*-02T15:02:21.050'
>>> 2. '2017-*01*-02T15:02:21.050'
>>>
>>>
>>> Regards,
>>> Dhaval Modi
>>> dhavalmod...@gmail.com
>>>
>>> On 7 February 2017 at 13:18, Pedro Boado wrote:
>>>
>>>> Hi.
>>>>
>>>> I don't think it's weird. That column is PK and you've upserted twice
>>>> the same key value so first one is inserted and second one is updated.
>>>>
>>>> Regards.
>>>>
>>>>
>>>>
>>>> On 7 Feb 2017 04:59, "Dhaval Modi" wrote:
>>>>
>>>>> Hi All,
>>>>>
>>>>> I am facing abnormal scenarios with ROW_TIMESTAMP.
>>>>>
>>>>> I created table in Phoenix as below:
>>>>> CREATE TABLE DUMMY(XXX_TS TIMESTAMP NOT NULL CONSTRAINT pk PRIMARY KEY
>>>>> (XXX_TS ROW_TIMESTAMP))
>>>>> where "XXX_TS" is used as ROW_TIMESTAMP.
>>>>>
>>>>> Now, I am trying to add data:
>>>>> upsert into DUMMY values('2017-07-02T15:02:21.050');
>>>>> upsert into DUMMY values('2017-01-02T15:02:21.050');
>>>>>
>>>>> I am only seeing one entry.
>>>>> *==*
>>>>> *0: jdbc:phoenix:> select * from DUMMY;*
>>>>> *+--+*
>>>>> *| XXX_TS |*
>>>>> *+--+*
>>>>> *| 2017-01-02 15:02:21.050 |*
>>>>> *+--+*
>>>>> *1 row selected (0.039 seconds)*
>>>>> *==*
>>>>>
>>>>>
>>>>> Additional info:
>>>>> System date of HBase & Phoenix: mar feb 7 05:57:37 CET 2017
>>>>>
>>>>>
>>>>> Regards,
>>>>> Dhaval Modi
>>>>> dhavalmod...@gmail.com
>>>>>
>>>>
>>>
>>
>
Re: ROW_TIMESTAMP weird behaviour
Thanks Ankit.
My issue is relevant to PHOENIX-3176.
But additional observation is, any timestamp value after 13:oo hours of the
same day is not added.
0: jdbc:phoenix:> select * from DUMMY;
+--+
| XXX_TS |
+--+
| 2017-01-01 15:02:21.050 |
| 2017-01-02 15:02:21.050 |
| 2017-01-13 15:02:21.050 |
| 2017-02-06 15:02:21.050 |
| 2017-02-07 11:02:21.050 |
| 2017-02-07 11:03:21.050 |
| 2017-02-07 12:02:21.050 |
+--+
7 rows selected (0.044 seconds)
0: jdbc:phoenix:> upsert into DUMMY values('2017-02-07T*12:03:21.050'*);
1 row affected (0.01 seconds)
0: jdbc:phoenix:> select * from DUMMY;
+--+
| XXX_TS |
+--+
| 2017-01-01 15:02:21.050 |
| 2017-01-02 15:02:21.050 |
| 2017-01-13 15:02:21.050 |
| 2017-02-06 15:02:21.050 |
| 2017-02-07 11:02:21.050 |
| 2017-02-07 11:03:21.050 |
| 2017-02-07 12:02:21.050 |
*| 2017-02-07 12:03:21.050 |*
+--+
8 rows selected (0.047 seconds)
0: jdbc:phoenix:> upsert into DUMMY values('2017-02-07T*13:03:21.050*');
1 row affected (0.009 seconds)
0: jdbc:phoenix:> select * from DUMMY;
+--+
| XXX_TS |
+--+
| 2017-01-01 15:02:21.050 |
| 2017-01-02 15:02:21.050 |
| 2017-01-13 15:02:21.050 |
| 2017-02-06 15:02:21.050 |
| 2017-02-07 11:02:21.050 |
| 2017-02-07 11:03:21.050 |
| 2017-02-07 12:02:21.050 |
| 2017-02-07 12:03:21.050 |
+--+
8 rows selected (0.04 seconds)
Regards,
Dhaval Modi
dhavalmod...@gmail.com
On 7 February 2017 at 15:28, Ankit Singhal wrote:
> I think you are also hitting https://issues.apache.
> org/jira/browse/PHOENIX-3176.
>
> On Tue, Feb 7, 2017 at 2:18 PM, Dhaval Modi
> wrote:
>
>> Hi Pedro,
>>
>> Upserted key are different. One key is for July month & other for January
>> month.
>> 1. '2017-*07*-02T15:02:21.050'
>> 2. '2017-*01*-02T15:02:21.050'
>>
>>
>> Regards,
>> Dhaval Modi
>> dhavalmod...@gmail.com
>>
>> On 7 February 2017 at 13:18, Pedro Boado wrote:
>>
>>> Hi.
>>>
>>> I don't think it's weird. That column is PK and you've upserted twice
>>> the same key value so first one is inserted and second one is updated.
>>>
>>> Regards.
>>>
>>>
>>>
>>> On 7 Feb 2017 04:59, "Dhaval Modi" wrote:
>>>
>>>> Hi All,
>>>>
>>>> I am facing abnormal scenarios with ROW_TIMESTAMP.
>>>>
>>>> I created table in Phoenix as below:
>>>> CREATE TABLE DUMMY(XXX_TS TIMESTAMP NOT NULL CONSTRAINT pk PRIMARY KEY
>>>> (XXX_TS ROW_TIMESTAMP))
>>>> where "XXX_TS" is used as ROW_TIMESTAMP.
>>>>
>>>> Now, I am trying to add data:
>>>> upsert into DUMMY values('2017-07-02T15:02:21.050');
>>>> upsert into DUMMY values('2017-01-02T15:02:21.050');
>>>>
>>>> I am only seeing one entry.
>>>> *==*
>>>> *0: jdbc:phoenix:> select * from DUMMY;*
>>>> *+--+*
>>>> *| XXX_TS |*
>>>> *+--+*
>>>> *| 2017-01-02 15:02:21.050 |*
>>>> *+--+*
>>>> *1 row selected (0.039 seconds)*
>>>> *==*
>>>>
>>>>
>>>> Additional info:
>>>> System date of HBase & Phoenix: mar feb 7 05:57:37 CET 2017
>>>>
>>>>
>>>> Regards,
>>>> Dhaval Modi
>>>> dhavalmod...@gmail.com
>>>>
>>>
>>
>
Re: ROW_TIMESTAMP weird behaviour
I think you are also hitting
https://issues.apache.org/jira/browse/PHOENIX-3176.
On Tue, Feb 7, 2017 at 2:18 PM, Dhaval Modi wrote:
> Hi Pedro,
>
> Upserted key are different. One key is for July month & other for January
> month.
> 1. '2017-*07*-02T15:02:21.050'
> 2. '2017-*01*-02T15:02:21.050'
>
>
> Regards,
> Dhaval Modi
> dhavalmod...@gmail.com
>
> On 7 February 2017 at 13:18, Pedro Boado wrote:
>
>> Hi.
>>
>> I don't think it's weird. That column is PK and you've upserted twice the
>> same key value so first one is inserted and second one is updated.
>>
>> Regards.
>>
>>
>>
>> On 7 Feb 2017 04:59, "Dhaval Modi" wrote:
>>
>>> Hi All,
>>>
>>> I am facing abnormal scenarios with ROW_TIMESTAMP.
>>>
>>> I created table in Phoenix as below:
>>> CREATE TABLE DUMMY(XXX_TS TIMESTAMP NOT NULL CONSTRAINT pk PRIMARY KEY
>>> (XXX_TS ROW_TIMESTAMP))
>>> where "XXX_TS" is used as ROW_TIMESTAMP.
>>>
>>> Now, I am trying to add data:
>>> upsert into DUMMY values('2017-07-02T15:02:21.050');
>>> upsert into DUMMY values('2017-01-02T15:02:21.050');
>>>
>>> I am only seeing one entry.
>>> *==*
>>> *0: jdbc:phoenix:> select * from DUMMY;*
>>> *+--+*
>>> *| XXX_TS |*
>>> *+--+*
>>> *| 2017-01-02 15:02:21.050 |*
>>> *+--+*
>>> *1 row selected (0.039 seconds)*
>>> *==*
>>>
>>>
>>> Additional info:
>>> System date of HBase & Phoenix: mar feb 7 05:57:37 CET 2017
>>>
>>>
>>> Regards,
>>> Dhaval Modi
>>> dhavalmod...@gmail.com
>>>
>>
>
Re: ROW_TIMESTAMP weird behaviour
Hi Pedro,
Upserted key are different. One key is for July month & other for January
month.
1. '2017-*07*-02T15:02:21.050'
2. '2017-*01*-02T15:02:21.050'
Regards,
Dhaval Modi
dhavalmod...@gmail.com
On 7 February 2017 at 13:18, Pedro Boado wrote:
> Hi.
>
> I don't think it's weird. That column is PK and you've upserted twice the
> same key value so first one is inserted and second one is updated.
>
> Regards.
>
>
>
> On 7 Feb 2017 04:59, "Dhaval Modi" wrote:
>
>> Hi All,
>>
>> I am facing abnormal scenarios with ROW_TIMESTAMP.
>>
>> I created table in Phoenix as below:
>> CREATE TABLE DUMMY(XXX_TS TIMESTAMP NOT NULL CONSTRAINT pk PRIMARY KEY
>> (XXX_TS ROW_TIMESTAMP))
>> where "XXX_TS" is used as ROW_TIMESTAMP.
>>
>> Now, I am trying to add data:
>> upsert into DUMMY values('2017-07-02T15:02:21.050');
>> upsert into DUMMY values('2017-01-02T15:02:21.050');
>>
>> I am only seeing one entry.
>> *==*
>> *0: jdbc:phoenix:> select * from DUMMY;*
>> *+--+*
>> *| XXX_TS |*
>> *+--+*
>> *| 2017-01-02 15:02:21.050 |*
>> *+--+*
>> *1 row selected (0.039 seconds)*
>> *==*
>>
>>
>> Additional info:
>> System date of HBase & Phoenix: mar feb 7 05:57:37 CET 2017
>>
>>
>> Regards,
>> Dhaval Modi
>> dhavalmod...@gmail.com
>>
>
Re: ROW_TIMESTAMP weird behaviour
Hi.
I don't think it's weird. That column is PK and you've upserted twice the
same key value so first one is inserted and second one is updated.
Regards.
On 7 Feb 2017 04:59, "Dhaval Modi" wrote:
> Hi All,
>
> I am facing abnormal scenarios with ROW_TIMESTAMP.
>
> I created table in Phoenix as below:
> CREATE TABLE DUMMY(XXX_TS TIMESTAMP NOT NULL CONSTRAINT pk PRIMARY KEY
> (XXX_TS ROW_TIMESTAMP))
> where "XXX_TS" is used as ROW_TIMESTAMP.
>
> Now, I am trying to add data:
> upsert into DUMMY values('2017-07-02T15:02:21.050');
> upsert into DUMMY values('2017-01-02T15:02:21.050');
>
> I am only seeing one entry.
> *==*
> *0: jdbc:phoenix:> select * from DUMMY;*
> *+--+*
> *| XXX_TS |*
> *+--+*
> *| 2017-01-02 15:02:21.050 |*
> *+--+*
> *1 row selected (0.039 seconds)*
> *==*
>
>
> Additional info:
> System date of HBase & Phoenix: mar feb 7 05:57:37 CET 2017
>
>
> Regards,
> Dhaval Modi
> dhavalmod...@gmail.com
>
ROW_TIMESTAMP weird behaviour
Hi All,
I am facing abnormal scenarios with ROW_TIMESTAMP.
I created table in Phoenix as below:
CREATE TABLE DUMMY(XXX_TS TIMESTAMP NOT NULL CONSTRAINT pk PRIMARY KEY
(XXX_TS ROW_TIMESTAMP))
where "XXX_TS" is used as ROW_TIMESTAMP.
Now, I am trying to add data:
upsert into DUMMY values('2017-07-02T15:02:21.050');
upsert into DUMMY values('2017-01-02T15:02:21.050');
I am only seeing one entry.
*==*
*0: jdbc:phoenix:> select * from DUMMY;*
*+--+*
*| XXX_TS |*
*+--+*
*| 2017-01-02 15:02:21.050 |*
*+--+*
*1 row selected (0.039 seconds)*
*==*
Additional info:
System date of HBase & Phoenix: mar feb 7 05:57:37 CET 2017
Regards,
Dhaval Modi
dhavalmod...@gmail.com
Re: Problems with Phoenix bulk loader when using row_timestamp feature
FYI… The sample data that I loaded in the table was based on the current timestamp with each additional row increasing that value by 1 minute so the current time up to 999,999 minutes into the future. Turns out this was a bug that prevents the scanner from reading timestamp values greater than the current time. More details here: https://issues.apache.org/jira/browse/PHOENIX-3176 From: default mailto:rtemple...@hortonworks.com>> Reply-To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" mailto:user@phoenix.apache.org>> Date: Wednesday, August 10, 2016 at 1:12 PM To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" mailto:user@phoenix.apache.org>> Subject: Re: Problems with Phoenix bulk loader when using row_timestamp feature 0: jdbc:phoenix:localhost:2181> explain select count(*) from historian.data; +--+ | PLAN | +--+ | CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER HISTORIAN.DATA | | ROW TIMESTAMP FILTER [0, 1470852712807) | | SERVER FILTER BY FIRST KEY ONLY | | SERVER AGGREGATE INTO SINGLE ROW | +--+ 4 rows selected (0.071 seconds) From: Samarth Jain mailto:sama...@apache.org>> Reply-To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" mailto:user@phoenix.apache.org>> Date: Wednesday, August 10, 2016 at 12:05 AM To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" mailto:user@phoenix.apache.org>> Subject: Re: Problems with Phoenix bulk loader when using row_timestamp feature Ryan, Can you tell us what the explain plan says for the select count(*) query. - Samarth On Tue, Aug 9, 2016 at 12:58 PM, Ryan Templeton mailto:rtemple...@hortonworks.com>> wrote: I am working on a project that will be consuming sensor data. The “fact” table is defined as: CREATE TABLE historian.data ( assetid unsigned_int not null, metricid unsigned_int not null, ts timestamp not null, val double CONSTRAINT pk PRIMARY KEY (assetid, metricid, tsp)) IMMUTABLE_ROWS=true; I generated a 1million row csv sample dataset and use the Phoenix bulk loader to load this data up. The tool reports that all 1,000,000 rows were loaded successfully which I can confirm via sqlline. I then dropped and recreated the table to take advantage of the row_timestamp feature drop table historian.data; CREATE TABLE historian.data ( assetid unsigned_int not null, metricid unsigned_int not null, ts timestamp not null, val double CONSTRAINT pk PRIMARY KEY (assetid, metricid, ts row_timestamp)) IMMUTABLE_ROWS=true; I reran the bulk loader utility which says it completed successfully [rtempleton@M1 phoenix-client]$ bin/psql.py localhost:2181 -t HISTORIAN.DATA /tmp/data.csv SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/usr/hdp/2.4.3.0-180/phoenix/phoenix-4.4.0.2.4.3.0-180-client.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/hdp/2.4.3.0-180/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. 16/08/08 20:34:43 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable 16/08/08 20:34:44 WARN shortcircuit.DomainSocketFactory: The short-circuit local reads feature cannot be used because libhadoop cannot be loaded. csv columns from database. CSV Upsert complete. 100 rows upserted Time: 65.985 sec(s) But when I run “select count(*) from historian.data” I see that only the first 572 rows appear in the table. These rows correlate to the the first 572 rows of the input file. 0: jdbc:phoenix:localhost:2181> select count(*) from historian.data; +--+ | COUNT(1) | +--+ | 572 | +--+ 1 row selected (4.541 seconds) 0: jdbc:phoenix:localhost:2181> select min(ts), max(ts) from historian.data; +--+--+ | MIN(TS) | MAX(TS) | +--+--+ | 2016-08-08 11:05:15.000 | 2016-08-08 20:36:15.000 | +--+—+ Any ideas? Thanks, Ryan
Re: Problems with Phoenix bulk loader when using row_timestamp feature
Samarth, filed PHOENIX-3176 for the same. On Wed, Aug 10, 2016 at 11:42 PM, Ryan Templeton wrote: > 0: jdbc:phoenix:localhost:2181> explain select count(*) from > historian.data; > > *+--+* > > *| * * PLAN ** |* > > *+--+* > > *| * CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER HISTORIAN.DATA* |* > > *| * ROW TIMESTAMP FILTER [0, 1470852712807)* |* > > *| * SERVER FILTER BY FIRST KEY ONLY * |* > > *| * SERVER AGGREGATE INTO SINGLE ROW* |* > > *+--+* > > 4 rows selected (0.071 seconds) > > From: Samarth Jain > Reply-To: "user@phoenix.apache.org" > Date: Wednesday, August 10, 2016 at 12:05 AM > To: "user@phoenix.apache.org" > Subject: Re: Problems with Phoenix bulk loader when using row_timestamp > feature > > Ryan, > > Can you tell us what the explain plan says for the select count(*) query. > > - Samarth > > > On Tue, Aug 9, 2016 at 12:58 PM, Ryan Templeton < > rtemple...@hortonworks.com> wrote: > >> I am working on a project that will be consuming sensor data. The “fact” >> table is defined as: >> >> CREATE TABLE historian.data ( >> assetid unsigned_int not null, >> metricid unsigned_int not null, >> ts timestamp not null, >> val double >> CONSTRAINT pk PRIMARY KEY (assetid, metricid, tsp)) >> IMMUTABLE_ROWS=true; >> >> I generated a 1million row csv sample dataset and use the Phoenix bulk >> loader to load this data up. The tool reports that all 1,000,000 rows were >> loaded successfully which I can confirm via sqlline. >> >> I then dropped and recreated the table to take advantage of the >> row_timestamp feature >> >> drop table historian.data; >> CREATE TABLE historian.data ( >> assetid unsigned_int not null, >> metricid unsigned_int not null, >> ts timestamp not null, >> val double >> CONSTRAINT pk PRIMARY KEY (assetid, metricid, ts row_timestamp)) >> IMMUTABLE_ROWS=true; >> >> I reran the bulk loader utility which says it completed successfully >> >> [rtempleton@M1 phoenix-client]$ bin/psql.py localhost:2181 -t >> HISTORIAN.DATA /tmp/data.csv >> >> SLF4J: Class path contains multiple SLF4J bindings. >> >> SLF4J: Found binding in [jar:file:/usr/hdp/2.4.3.0-180 >> /phoenix/phoenix-4.4.0.2.4.3.0-180-client.jar!/org/slf4j/im >> pl/StaticLoggerBinder.class] >> >> SLF4J: Found binding in [jar:file:/usr/hdp/2.4.3.0-180 >> /hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticL >> oggerBinder.class] >> >> SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an >> explanation. >> >> 16/08/08 20:34:43 WARN util.NativeCodeLoader: Unable to load >> native-hadoop library for your platform... using builtin-java classes where >> applicable >> >> 16/08/08 20:34:44 WARN shortcircuit.DomainSocketFactory: The >> short-circuit local reads feature cannot be used because libhadoop cannot >> be loaded. >> >> csv columns from database. >> >> CSV Upsert complete. 100 rows upserted >> >> Time: 65.985 sec(s) >> >> But when I run “select count(*) from historian.data” I see that only the >> first 572 rows appear in the table. These rows correlate to the the first >> 572 rows of the input file. >> >> 0: jdbc:phoenix:localhost:2181> select count(*) from historian.data; >> >> *+--+* >> >> *| **COUNT(1)** |* >> >> *+--+* >> >> *| *572 * |* >> >> *+--+* >> >> 1 row selected (4.541 seconds) >> >> 0: jdbc:phoenix:localhost:2181> select min(ts), max(ts) from >> historian.data; >> >> >> *+--+--+* >> >> *| **MIN(TS) ** | ** >> MAX(TS) ** |* >> >> >> *+--+--+* >> >> *| *2016-08-08 11:05:15.000 * | *2016-08-08 >> 20:36:15.000 * |* >> >> *+--+—+* >> >> >> >> >> Any ideas? >> >> >> Thanks, >> Ryan >> > >
Re: Problems with Phoenix bulk loader when using row_timestamp feature
0: jdbc:phoenix:localhost:2181> explain select count(*) from historian.data; +--+ | PLAN | +--+ | CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER HISTORIAN.DATA | | ROW TIMESTAMP FILTER [0, 1470852712807) | | SERVER FILTER BY FIRST KEY ONLY | | SERVER AGGREGATE INTO SINGLE ROW | +--+ 4 rows selected (0.071 seconds) From: Samarth Jain mailto:sama...@apache.org>> Reply-To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" mailto:user@phoenix.apache.org>> Date: Wednesday, August 10, 2016 at 12:05 AM To: "user@phoenix.apache.org<mailto:user@phoenix.apache.org>" mailto:user@phoenix.apache.org>> Subject: Re: Problems with Phoenix bulk loader when using row_timestamp feature Ryan, Can you tell us what the explain plan says for the select count(*) query. - Samarth On Tue, Aug 9, 2016 at 12:58 PM, Ryan Templeton mailto:rtemple...@hortonworks.com>> wrote: I am working on a project that will be consuming sensor data. The “fact” table is defined as: CREATE TABLE historian.data ( assetid unsigned_int not null, metricid unsigned_int not null, ts timestamp not null, val double CONSTRAINT pk PRIMARY KEY (assetid, metricid, tsp)) IMMUTABLE_ROWS=true; I generated a 1million row csv sample dataset and use the Phoenix bulk loader to load this data up. The tool reports that all 1,000,000 rows were loaded successfully which I can confirm via sqlline. I then dropped and recreated the table to take advantage of the row_timestamp feature drop table historian.data; CREATE TABLE historian.data ( assetid unsigned_int not null, metricid unsigned_int not null, ts timestamp not null, val double CONSTRAINT pk PRIMARY KEY (assetid, metricid, ts row_timestamp)) IMMUTABLE_ROWS=true; I reran the bulk loader utility which says it completed successfully [rtempleton@M1 phoenix-client]$ bin/psql.py localhost:2181 -t HISTORIAN.DATA /tmp/data.csv SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/usr/hdp/2.4.3.0-180/phoenix/phoenix-4.4.0.2.4.3.0-180-client.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/hdp/2.4.3.0-180/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. 16/08/08 20:34:43 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable 16/08/08 20:34:44 WARN shortcircuit.DomainSocketFactory: The short-circuit local reads feature cannot be used because libhadoop cannot be loaded. csv columns from database. CSV Upsert complete. 100 rows upserted Time: 65.985 sec(s) But when I run “select count(*) from historian.data” I see that only the first 572 rows appear in the table. These rows correlate to the the first 572 rows of the input file. 0: jdbc:phoenix:localhost:2181> select count(*) from historian.data; +--+ | COUNT(1) | +--+ | 572 | +--+ 1 row selected (4.541 seconds) 0: jdbc:phoenix:localhost:2181> select min(ts), max(ts) from historian.data; +--+--+ | MIN(TS) | MAX(TS) | +--+--+ | 2016-08-08 11:05:15.000 | 2016-08-08 20:36:15.000 | +--+—+ Any ideas? Thanks, Ryan
Re: Problems with Phoenix bulk loader when using row_timestamp feature
Ryan, Can you tell us what the explain plan says for the select count(*) query. - Samarth On Tue, Aug 9, 2016 at 12:58 PM, Ryan Templeton wrote: > I am working on a project that will be consuming sensor data. The “fact” > table is defined as: > > CREATE TABLE historian.data ( > assetid unsigned_int not null, > metricid unsigned_int not null, > ts timestamp not null, > val double > CONSTRAINT pk PRIMARY KEY (assetid, metricid, tsp)) > IMMUTABLE_ROWS=true; > > I generated a 1million row csv sample dataset and use the Phoenix bulk > loader to load this data up. The tool reports that all 1,000,000 rows were > loaded successfully which I can confirm via sqlline. > > I then dropped and recreated the table to take advantage of the > row_timestamp feature > > drop table historian.data; > CREATE TABLE historian.data ( > assetid unsigned_int not null, > metricid unsigned_int not null, > ts timestamp not null, > val double > CONSTRAINT pk PRIMARY KEY (assetid, metricid, ts row_timestamp)) > IMMUTABLE_ROWS=true; > > I reran the bulk loader utility which says it completed successfully > > [rtempleton@M1 phoenix-client]$ bin/psql.py localhost:2181 -t > HISTORIAN.DATA /tmp/data.csv > > SLF4J: Class path contains multiple SLF4J bindings. > > SLF4J: Found binding in [jar:file:/usr/hdp/2.4.3.0- > 180/phoenix/phoenix-4.4.0.2.4.3.0-180-client.jar!/org/slf4j/ > impl/StaticLoggerBinder.class] > > SLF4J: Found binding in [jar:file:/usr/hdp/2.4.3.0- > 180/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/ > StaticLoggerBinder.class] > > SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an > explanation. > > 16/08/08 20:34:43 WARN util.NativeCodeLoader: Unable to load native-hadoop > library for your platform... using builtin-java classes where applicable > > 16/08/08 20:34:44 WARN shortcircuit.DomainSocketFactory: The > short-circuit local reads feature cannot be used because libhadoop cannot > be loaded. > > csv columns from database. > > CSV Upsert complete. 100 rows upserted > > Time: 65.985 sec(s) > > But when I run “select count(*) from historian.data” I see that only the > first 572 rows appear in the table. These rows correlate to the the first > 572 rows of the input file. > > 0: jdbc:phoenix:localhost:2181> select count(*) from historian.data; > > *+--+* > > *| **COUNT(1)** |* > > *+--+* > > *| *572 * |* > > *+--+* > > 1 row selected (4.541 seconds) > > 0: jdbc:phoenix:localhost:2181> select min(ts), max(ts) from > historian.data; > > > *+--+--+* > > *| **MIN(TS) ** | ** > MAX(TS) ** |* > > > *+--+--+* > > *| *2016-08-08 11:05:15.000 * | *2016-08-08 20:36:15.000 >* |* > > *+--+—+* > > > > > Any ideas? > > > Thanks, > Ryan >
Problems with Phoenix bulk loader when using row_timestamp feature
I am working on a project that will be consuming sensor data. The “fact” table is defined as: CREATE TABLE historian.data ( assetid unsigned_int not null, metricid unsigned_int not null, ts timestamp not null, val double CONSTRAINT pk PRIMARY KEY (assetid, metricid, tsp)) IMMUTABLE_ROWS=true; I generated a 1million row csv sample dataset and use the Phoenix bulk loader to load this data up. The tool reports that all 1,000,000 rows were loaded successfully which I can confirm via sqlline. I then dropped and recreated the table to take advantage of the row_timestamp feature drop table historian.data; CREATE TABLE historian.data ( assetid unsigned_int not null, metricid unsigned_int not null, ts timestamp not null, val double CONSTRAINT pk PRIMARY KEY (assetid, metricid, ts row_timestamp)) IMMUTABLE_ROWS=true; I reran the bulk loader utility which says it completed successfully [rtempleton@M1 phoenix-client]$ bin/psql.py localhost:2181 -t HISTORIAN.DATA /tmp/data.csv SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/usr/hdp/2.4.3.0-180/phoenix/phoenix-4.4.0.2.4.3.0-180-client.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/hdp/2.4.3.0-180/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. 16/08/08 20:34:43 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable 16/08/08 20:34:44 WARN shortcircuit.DomainSocketFactory: The short-circuit local reads feature cannot be used because libhadoop cannot be loaded. csv columns from database. CSV Upsert complete. 100 rows upserted Time: 65.985 sec(s) But when I run “select count(*) from historian.data” I see that only the first 572 rows appear in the table. These rows correlate to the the first 572 rows of the input file. 0: jdbc:phoenix:localhost:2181> select count(*) from historian.data; +--+ | COUNT(1) | +--+ | 572 | +--+ 1 row selected (4.541 seconds) 0: jdbc:phoenix:localhost:2181> select min(ts), max(ts) from historian.data; +--+--+ | MIN(TS) | MAX(TS) | +--+--+ | 2016-08-08 11:05:15.000 | 2016-08-08 20:36:15.000 | +--+—+ Any ideas? Thanks, Ryan
Is there any way to use 'ROW_TIMESTAMP' on a exist phoenix table?
We have a Phoenix table, all data was created by Phoenix SQL. Normally, we use Phoenix SQL directly. But sometimes, we also need to export data incrementally by data's created time. Currently, we use HBase API directly by timestamps. The new feature 'row_timestamp' seems to be more suitable for our new demands. So we want to use Phoenix SQL directly, instead of HBase API. But it seems that 'row_timestamp' doesn't works well with 'alert'. Is there any way to solve this coordinate problem?
Re: weird result I got when I try row_timestamp feature
sorry for bothering. I did some test today, and find out why it got the
wrong result.
It was my mistake.
I use a process as data loader , and when I upgraded my server to 4.6.0, I
forgot the data loader still use the 4.4.0 phoenix.
4.4.0 phoenix client did not reset timestamp in hbase cell, so when I query
data from phoenix with ROW TIMESTAMP FILTER, it always got wrong result.
On Sat, Dec 12, 2015 at 2:34 AM, Samarth Jain wrote:
> Hi Roc,
>
> FWIW, looking at your schema, it doesn't look like you are using the
> ROW_TIMESTAMP feature. The constraint part of your DDL needs to be changed
> like this:
>
> CONSTRAINT my_pk PRIMARY KEY (
> server_timestamp ROW_TIMESTAMP,
> app_id, client_ip,
> cluster_id,host_id,api
> )
>
> For the issue of getting different counts, mind filing a JIRA? It would be
> ideal if you could come up with a minimum set of data needed to reproduce
> this issue.
>
> Thanks,
> Samarth
>
>
>
>
>
>
>
> On Fri, Dec 11, 2015 at 1:32 AM, Roc Chu wrote:
>
>> Hi all
>>
>> I try to use phoenix 4.6 to deal with time series data,
>>
>> this is my table schema
>>
>> create table log_data_46201512(
>> server_timestamp timestamp not null,
>> app_id varchar,
>> client_timestamp timestamp,
>> client_ip INTEGER not null,
>> cluster_id varchar,
>> host_id varchar,
>> device_uuid varchar,
>> url varchar,
>> api varchar,
>> sdk varchar,
>> device varchar,
>> market_id varchar,
>> language varchar,
>> response_time INTEGER,
>> response_code INTEGER,
>> bytes_read INTEGER,
>> bytes_uploaded INTEGER,
>> description varchar,
>> CONSTRAINT my_pk PRIMARY KEY (
>> server_timestamp, app_id, client_ip,
>> cluster_id,host_id,api
>> )
>> )salt_buckets=128,compression='snappy'
>>
>> I inserted some test data into this table.
>> the server_timestamp in my test data had a few minutes behind the current
>> time.
>>
>> after finished the data load process, I did 2 queries,
>> as below
>>
>> ---
>> 0: jdbc:phoenix:ROCVM7501> select count(*) cont, round(server_timestamp,
>> 'MINUTE', 10) min from log_data_46201512 where server_timestamp >
>> to_date('2015-12-11 06:15:00', '-MM-dd HH:mm:ss') and server_timestamp
>> < to_date('2015-12-11 07:30:00', '-MM-dd HH:mm:ss') group by min;
>>
>> +--+-+
>> | CONT | MIN |
>> +--+-+
>> | 790615 | 2015-12-11 06:20:00.000 |
>> | 40 | 2015-12-11 06:30:00.000 |
>> | 45 | 2015-12-11 06:40:00.000 |
>> | 25 | 2015-12-11 06:50:00.000 |
>> | 20 | 2015-12-11 07:00:00.000 |
>> +--+-+
>>
>> -
>>
>> 0: jdbc:phoenix:ROCVM7501> select count(*) cont, round(server_timestamp,
>> 'MINUTE', 10) min from log_data_46201512 where server_timestamp >
>> to_date('2015-12-11 06:15:00', '-MM-dd HH:mm:ss') group by min;
>>
>> +--+-+
>> | CONT | MIN |
>> +--+-+
>> | 992508 | 2015-12-11 06:20:00.000 |
>> | 40 | 2015-12-11 06:30:00.000 |
>> | 45 | 2015-12-11 06:40:00.000 |
>> | 25 | 2015-12-11 06:50:00.000 |
>> | 31 | 2015-12-11 07:00:00.000 |
>> | 27 | 2015-12-11 07:10:00.000 |
>> | 35 | 2015-12-11 07:20:00.000 |
>> | 31 | 2015-12-11 07:30:00.000 |
>> | 43 | 2015-12-11 07:40:00.000 |
>> | 7| 2015-12-11 07:50:00.000 |
>> +--+-+
>>
>>
>> I am confused about this. why I got different cont in first
>> row(MIN=2015-12-11
>> 06:20:00.000) in my result ?
>> did I do something wrong to cause that?
>>
>> can someone help me out? Thanks in advance.
>>
>>
>> I use the phoenix-4.6.0-HBase-0.98
>>
>> Greetings
>>
>>
>
Re: weird result I got when I try row_timestamp feature
Thanks for your reply.
In my test table, I used the row_timestamp feature and I lost ROW_TIMESTAMP
key word when I copied schema to this mail,
when I finished data load, I executed that query several times, and got
different cont,
it seemed that the result affected by where the data is, in hbase memstore
or in hfile.
I will do some more test. If I could reproduce with a much less test data,
I will make a issue in JIRA
On Sat, Dec 12, 2015 at 2:34 AM, Samarth Jain wrote:
> Hi Roc,
>
> FWIW, looking at your schema, it doesn't look like you are using the
> ROW_TIMESTAMP feature. The constraint part of your DDL needs to be changed
> like this:
>
> CONSTRAINT my_pk PRIMARY KEY (
> server_timestamp ROW_TIMESTAMP,
> app_id, client_ip,
> cluster_id,host_id,api
> )
>
> For the issue of getting different counts, mind filing a JIRA? It would be
> ideal if you could come up with a minimum set of data needed to reproduce
> this issue.
>
> Thanks,
> Samarth
>
>
>
>
>
>
>
> On Fri, Dec 11, 2015 at 1:32 AM, Roc Chu wrote:
>
>> Hi all
>>
>> I try to use phoenix 4.6 to deal with time series data,
>>
>> this is my table schema
>>
>> create table log_data_46201512(
>> server_timestamp timestamp not null,
>> app_id varchar,
>> client_timestamp timestamp,
>> client_ip INTEGER not null,
>> cluster_id varchar,
>> host_id varchar,
>> device_uuid varchar,
>> url varchar,
>> api varchar,
>> sdk varchar,
>> device varchar,
>> market_id varchar,
>> language varchar,
>> response_time INTEGER,
>> response_code INTEGER,
>> bytes_read INTEGER,
>> bytes_uploaded INTEGER,
>> description varchar,
>> CONSTRAINT my_pk PRIMARY KEY (
>> server_timestamp, app_id, client_ip,
>> cluster_id,host_id,api
>> )
>> )salt_buckets=128,compression='snappy'
>>
>> I inserted some test data into this table.
>> the server_timestamp in my test data had a few minutes behind the current
>> time.
>>
>> after finished the data load process, I did 2 queries,
>> as below
>>
>> ---
>> 0: jdbc:phoenix:ROCVM7501> select count(*) cont, round(server_timestamp,
>> 'MINUTE', 10) min from log_data_46201512 where server_timestamp >
>> to_date('2015-12-11 06:15:00', '-MM-dd HH:mm:ss') and server_timestamp
>> < to_date('2015-12-11 07:30:00', '-MM-dd HH:mm:ss') group by min;
>>
>> +--+-+
>> | CONT | MIN |
>> +--+-+
>> | 790615 | 2015-12-11 06:20:00.000 |
>> | 40 | 2015-12-11 06:30:00.000 |
>> | 45 | 2015-12-11 06:40:00.000 |
>> | 25 | 2015-12-11 06:50:00.000 |
>> | 20 | 2015-12-11 07:00:00.000 |
>> +--+-+
>>
>> -
>>
>> 0: jdbc:phoenix:ROCVM7501> select count(*) cont, round(server_timestamp,
>> 'MINUTE', 10) min from log_data_46201512 where server_timestamp >
>> to_date('2015-12-11 06:15:00', '-MM-dd HH:mm:ss') group by min;
>>
>> +--+-+
>> | CONT | MIN |
>> +--+-+
>> | 992508 | 2015-12-11 06:20:00.000 |
>> | 40 | 2015-12-11 06:30:00.000 |
>> | 45 | 2015-12-11 06:40:00.000 |
>> | 25 | 2015-12-11 06:50:00.000 |
>> | 31 | 2015-12-11 07:00:00.000 |
>> | 27 | 2015-12-11 07:10:00.000 |
>> | 35 | 2015-12-11 07:20:00.000 |
>> | 31 | 2015-12-11 07:30:00.000 |
>> | 43 | 2015-12-11 07:40:00.000 |
>> | 7| 2015-12-11 07:50:00.000 |
>> +--+-+
>>
>>
>> I am confused about this. why I got different cont in first
>> row(MIN=2015-12-11
>> 06:20:00.000) in my result ?
>> did I do something wrong to cause that?
>>
>> can someone help me out? Thanks in advance.
>>
>>
>> I use the phoenix-4.6.0-HBase-0.98
>>
>> Greetings
>>
>>
>
Re: weird result I got when I try row_timestamp feature
Hi Roc,
FWIW, looking at your schema, it doesn't look like you are using the
ROW_TIMESTAMP feature. The constraint part of your DDL needs to be changed
like this:
CONSTRAINT my_pk PRIMARY KEY (
server_timestamp ROW_TIMESTAMP,
app_id, client_ip,
cluster_id,host_id,api
)
For the issue of getting different counts, mind filing a JIRA? It would be
ideal if you could come up with a minimum set of data needed to reproduce
this issue.
Thanks,
Samarth
On Fri, Dec 11, 2015 at 1:32 AM, Roc Chu wrote:
> Hi all
>
> I try to use phoenix 4.6 to deal with time series data,
>
> this is my table schema
>
> create table log_data_46201512(
> server_timestamp timestamp not null,
> app_id varchar,
> client_timestamp timestamp,
> client_ip INTEGER not null,
> cluster_id varchar,
> host_id varchar,
> device_uuid varchar,
> url varchar,
> api varchar,
> sdk varchar,
> device varchar,
> market_id varchar,
> language varchar,
> response_time INTEGER,
> response_code INTEGER,
> bytes_read INTEGER,
> bytes_uploaded INTEGER,
> description varchar,
> CONSTRAINT my_pk PRIMARY KEY (
> server_timestamp, app_id, client_ip,
> cluster_id,host_id,api
> )
> )salt_buckets=128,compression='snappy'
>
> I inserted some test data into this table.
> the server_timestamp in my test data had a few minutes behind the current
> time.
>
> after finished the data load process, I did 2 queries,
> as below
>
> ---
> 0: jdbc:phoenix:ROCVM7501> select count(*) cont, round(server_timestamp,
> 'MINUTE', 10) min from log_data_46201512 where server_timestamp >
> to_date('2015-12-11 06:15:00', '-MM-dd HH:mm:ss') and server_timestamp
> < to_date('2015-12-11 07:30:00', '-MM-dd HH:mm:ss') group by min;
>
> +--+-+
> | CONT | MIN |
> +--+-+
> | 790615 | 2015-12-11 06:20:00.000 |
> | 40 | 2015-12-11 06:30:00.000 |
> | 45 | 2015-12-11 06:40:00.000 |
> | 25 | 2015-12-11 06:50:00.000 |
> | 20 | 2015-12-11 07:00:00.000 |
> +--+-+
>
> -
>
> 0: jdbc:phoenix:ROCVM7501> select count(*) cont, round(server_timestamp,
> 'MINUTE', 10) min from log_data_46201512 where server_timestamp >
> to_date('2015-12-11 06:15:00', '-MM-dd HH:mm:ss') group by min;
>
> +--+-+
> | CONT | MIN |
> +--+-+
> | 992508 | 2015-12-11 06:20:00.000 |
> | 40 | 2015-12-11 06:30:00.000 |
> | 45 | 2015-12-11 06:40:00.000 |
> | 25 | 2015-12-11 06:50:00.000 |
> | 31 | 2015-12-11 07:00:00.000 |
> | 27 | 2015-12-11 07:10:00.000 |
> | 35 | 2015-12-11 07:20:00.000 |
> | 31 | 2015-12-11 07:30:00.000 |
> | 43 | 2015-12-11 07:40:00.000 |
> | 7| 2015-12-11 07:50:00.000 |
> +--+-+
>
>
> I am confused about this. why I got different cont in first row(MIN=2015-12-11
> 06:20:00.000) in my result ?
> did I do something wrong to cause that?
>
> can someone help me out? Thanks in advance.
>
>
> I use the phoenix-4.6.0-HBase-0.98
>
> Greetings
>
>
weird result I got when I try row_timestamp feature
Hi all
I try to use phoenix 4.6 to deal with time series data,
this is my table schema
create table log_data_46201512(
server_timestamp timestamp not null,
app_id varchar,
client_timestamp timestamp,
client_ip INTEGER not null,
cluster_id varchar,
host_id varchar,
device_uuid varchar,
url varchar,
api varchar,
sdk varchar,
device varchar,
market_id varchar,
language varchar,
response_time INTEGER,
response_code INTEGER,
bytes_read INTEGER,
bytes_uploaded INTEGER,
description varchar,
CONSTRAINT my_pk PRIMARY KEY (
server_timestamp, app_id, client_ip,
cluster_id,host_id,api
)
)salt_buckets=128,compression='snappy'
I inserted some test data into this table.
the server_timestamp in my test data had a few minutes behind the current
time.
after finished the data load process, I did 2 queries,
as below
---
0: jdbc:phoenix:ROCVM7501> select count(*) cont, round(server_timestamp,
'MINUTE', 10) min from log_data_46201512 where server_timestamp >
to_date('2015-12-11 06:15:00', '-MM-dd HH:mm:ss') and server_timestamp
< to_date('2015-12-11 07:30:00', '-MM-dd HH:mm:ss') group by min;
+--+-+
| CONT | MIN |
+--+-+
| 790615 | 2015-12-11 06:20:00.000 |
| 40 | 2015-12-11 06:30:00.000 |
| 45 | 2015-12-11 06:40:00.000 |
| 25 | 2015-12-11 06:50:00.000 |
| 20 | 2015-12-11 07:00:00.000 |
+--+-+
-
0: jdbc:phoenix:ROCVM7501> select count(*) cont, round(server_timestamp,
'MINUTE', 10) min from log_data_46201512 where server_timestamp >
to_date('2015-12-11 06:15:00', '-MM-dd HH:mm:ss') group by min;
+--+-+
| CONT | MIN |
+--+-+
| 992508 | 2015-12-11 06:20:00.000 |
| 40 | 2015-12-11 06:30:00.000 |
| 45 | 2015-12-11 06:40:00.000 |
| 25 | 2015-12-11 06:50:00.000 |
| 31 | 2015-12-11 07:00:00.000 |
| 27 | 2015-12-11 07:10:00.000 |
| 35 | 2015-12-11 07:20:00.000 |
| 31 | 2015-12-11 07:30:00.000 |
| 43 | 2015-12-11 07:40:00.000 |
| 7| 2015-12-11 07:50:00.000 |
+--+-+
I am confused about this. why I got different cont in first row(MIN=2015-12-11
06:20:00.000) in my result ?
did I do something wrong to cause that?
can someone help me out? Thanks in advance.
I use the phoenix-4.6.0-HBase-0.98
Greetings
