You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Nan Xu <na...@gmail.com> on 2017/06/22 22:59:38 UTC
phoenix query modtime
I have a phoenix table created on existing hbase table, and want to query
something like
select * from mytable where modtime>'2010-01-01',
how do I query phoenix like this? seems it doesn't have a modtime column if
I don't do the modtime mapping, which I can not do because it has to be
part of the primary key.
Thanks,
Nan
Re: phoenix query modtime
Posted by James Taylor <ja...@apache.org>.
It's a reasonable request IMHO - please file a JIRA so we can discuss how
this could be supported.
Thanks,
James
On Sat, Jun 24, 2017 at 4:27 PM Ankit Singhal <an...@gmail.com>
wrote:
>
> Yes, and also to avoid returning an incomplete row for the same primary
> key because of different timestamp for the column's cell.
>
> On Sat, Jun 24, 2017 at 4:20 AM, Randy Hu <ru...@gmail.com> wrote:
>
>> First HBase does not have a concept of "row timestamp". Timestamp is part
>> of each cell. The closest to row timestamp is probably the latest
>> timestamp
>> from all cells with same row key.
>>
>> The reason that timestamp column need to be part of primary key is that
>> there could be multiple values with different timestamps but same row key,
>> family. and qualifier. The SQL result data model does not support such two
>> dimension structure well. The best Phoenix can do in the SQL model is to
>> return list of values in array, but then the cell timestamp need to be
>> returned in another array with same length under different column name.
>> You
>> can imagine the mess and awkwardness it could be if going to that
>> direction. It's much straightforward to make time stamp part of primary
>> key, so the two dimension structure could be represented by multiple rows
>> naturally in SQL result.
>>
>> Randy
>>
>>
>>
>>
>> --
>> View this message in context:
>> http://apache-phoenix-user-list.1124778.n5.nabble.com/phoenix-query-modtime-tp3702p3716.html
>> Sent from the Apache Phoenix User List mailing list archive at Nabble.com.
>>
>
>
Re: phoenix query modtime
Posted by Ankit Singhal <an...@gmail.com>.
Yes, and also to avoid returning an incomplete row for the same primary key
because of different timestamp for the column's cell.
On Sat, Jun 24, 2017 at 4:20 AM, Randy Hu <ru...@gmail.com> wrote:
> First HBase does not have a concept of "row timestamp". Timestamp is part
> of each cell. The closest to row timestamp is probably the latest timestamp
> from all cells with same row key.
>
> The reason that timestamp column need to be part of primary key is that
> there could be multiple values with different timestamps but same row key,
> family. and qualifier. The SQL result data model does not support such two
> dimension structure well. The best Phoenix can do in the SQL model is to
> return list of values in array, but then the cell timestamp need to be
> returned in another array with same length under different column name. You
> can imagine the mess and awkwardness it could be if going to that
> direction. It's much straightforward to make time stamp part of primary
> key, so the two dimension structure could be represented by multiple rows
> naturally in SQL result.
>
> Randy
>
>
>
>
> --
> View this message in context: http://apache-phoenix-user-
> list.1124778.n5.nabble.com/phoenix-query-modtime-tp3702p3716.html
> Sent from the Apache Phoenix User List mailing list archive at Nabble.com.
>
Re: phoenix query modtime
Posted by Randy Hu <ru...@gmail.com>.
First HBase does not have a concept of "row timestamp". Timestamp is part
of each cell. The closest to row timestamp is probably the latest timestamp
from all cells with same row key.
The reason that timestamp column need to be part of primary key is that
there could be multiple values with different timestamps but same row key,
family. and qualifier. The SQL result data model does not support such two
dimension structure well. The best Phoenix can do in the SQL model is to
return list of values in array, but then the cell timestamp need to be
returned in another array with same length under different column name. You
can imagine the mess and awkwardness it could be if going to that
direction. It's much straightforward to make time stamp part of primary
key, so the two dimension structure could be represented by multiple rows
naturally in SQL result.
Randy
--
View this message in context: http://apache-phoenix-user-list.1124778.n5.nabble.com/phoenix-query-modtime-tp3702p3716.html
Sent from the Apache Phoenix User List mailing list archive at Nabble.com.
Re: phoenix query modtime
Posted by Nan Xu <na...@gmail.com>.
thanks, useful stuff. I am a bit confuse, from the document
"Only a primary key column of type TIME, DATE, TIMESTAMP, BIGINT,
UNSIGNED_LONG can be designated as ROW_TIMESTAMP"
why this modtime mapping column has to be part of the primary key? it's a
lot more flexible if such a constraint not exist. why a regular phoenix
column cannot do this?
Nan
On Jun 23, 2017 10:02 AM, "Ankit Singhal" <an...@gmail.com> wrote:
> Ah, you are trying to use the row_timestamp feature, not sure if there is
> a way from SQL if that column is not in pk.
>
> but if you are open to using an unexposed API, here is some snippet
> (please use them at your discretion as these APIs are internal and have no
> guarantee to be consistent in versions )
>
> PreparedStatement statement = conn.preparedStatement("SELECT * FROM " +
> tableName);
> QueryPlan plan = statement.unwrap(PhoenixStatement.class).getQueryPlan();
> Scan scan = plan.getContext().getScan();
> scan.setTimeRange(minStamp, maxStamp);
> rs = statement.executeQuery();
>
>
> On Fri, Jun 23, 2017 at 8:05 PM, Nan Xu <na...@gmail.com> wrote:
>
>> sorry, maybe I did not make it clear, I have a hbase table, already
>> formatted with phoenix format and has composite key, I can query all the
>> columns I want, but I can not query the hbase modtime in phoenix query, any
>> way to do this?
>>
>> Nan
>>
>> On Fri, Jun 23, 2017 at 1:23 AM, Ankit Singhal <an...@gmail.com>
>> wrote:
>>
>>> If you have composite columns in your row key of HBase table and they
>>> are not formed through Phoenix then you can't access an individual column
>>> of primary key by Phoenix SQL too.
>>> Try composing the whole PK and use them in a filter or may check if you
>>> can use regex functions[1] or LIKE operator.
>>>
>>> [1] https://phoenix.apache.org/language/functions.html#regexp_substr
>>>
>>> On Fri, Jun 23, 2017 at 4:29 AM, Nan Xu <na...@gmail.com> wrote:
>>>
>>>> I have a phoenix table created on existing hbase table, and want to
>>>> query something like
>>>> select * from mytable where modtime>'2010-01-01',
>>>>
>>>> how do I query phoenix like this? seems it doesn't have a modtime
>>>> column if I don't do the modtime mapping, which I can not do because it has
>>>> to be part of the primary key.
>>>>
>>>> Thanks,
>>>> Nan
>>>>
>>>
>>>
>>
>
Re: phoenix query modtime
Posted by Ankit Singhal <an...@gmail.com>.
Ah, you are trying to use the row_timestamp feature, not sure if there is a
way from SQL if that column is not in pk.
but if you are open to using an unexposed API, here is some snippet (please
use them at your discretion as these APIs are internal and have no
guarantee to be consistent in versions )
PreparedStatement statement = conn.preparedStatement("SELECT * FROM " +
tableName);
QueryPlan plan = statement.unwrap(PhoenixStatement.class).getQueryPlan();
Scan scan = plan.getContext().getScan();
scan.setTimeRange(minStamp, maxStamp);
rs = statement.executeQuery();
On Fri, Jun 23, 2017 at 8:05 PM, Nan Xu <na...@gmail.com> wrote:
> sorry, maybe I did not make it clear, I have a hbase table, already
> formatted with phoenix format and has composite key, I can query all the
> columns I want, but I can not query the hbase modtime in phoenix query, any
> way to do this?
>
> Nan
>
> On Fri, Jun 23, 2017 at 1:23 AM, Ankit Singhal <an...@gmail.com>
> wrote:
>
>> If you have composite columns in your row key of HBase table and they are
>> not formed through Phoenix then you can't access an individual column of
>> primary key by Phoenix SQL too.
>> Try composing the whole PK and use them in a filter or may check if you
>> can use regex functions[1] or LIKE operator.
>>
>> [1] https://phoenix.apache.org/language/functions.html#regexp_substr
>>
>> On Fri, Jun 23, 2017 at 4:29 AM, Nan Xu <na...@gmail.com> wrote:
>>
>>> I have a phoenix table created on existing hbase table, and want to
>>> query something like
>>> select * from mytable where modtime>'2010-01-01',
>>>
>>> how do I query phoenix like this? seems it doesn't have a modtime column
>>> if I don't do the modtime mapping, which I can not do because it has to be
>>> part of the primary key.
>>>
>>> Thanks,
>>> Nan
>>>
>>
>>
>
Re: phoenix query modtime
Posted by Nan Xu <na...@gmail.com>.
sorry, maybe I did not make it clear, I have a hbase table, already
formatted with phoenix format and has composite key, I can query all the
columns I want, but I can not query the hbase modtime in phoenix query, any
way to do this?
Nan
On Fri, Jun 23, 2017 at 1:23 AM, Ankit Singhal <an...@gmail.com>
wrote:
> If you have composite columns in your row key of HBase table and they are
> not formed through Phoenix then you can't access an individual column of
> primary key by Phoenix SQL too.
> Try composing the whole PK and use them in a filter or may check if you
> can use regex functions[1] or LIKE operator.
>
> [1] https://phoenix.apache.org/language/functions.html#regexp_substr
>
> On Fri, Jun 23, 2017 at 4:29 AM, Nan Xu <na...@gmail.com> wrote:
>
>> I have a phoenix table created on existing hbase table, and want to query
>> something like
>> select * from mytable where modtime>'2010-01-01',
>>
>> how do I query phoenix like this? seems it doesn't have a modtime column
>> if I don't do the modtime mapping, which I can not do because it has to be
>> part of the primary key.
>>
>> Thanks,
>> Nan
>>
>
>
Re: phoenix query modtime
Posted by Ankit Singhal <an...@gmail.com>.
If you have composite columns in your row key of HBase table and they are
not formed through Phoenix then you can't access an individual column of
primary key by Phoenix SQL too.
Try composing the whole PK and use them in a filter or may check if you can
use regex functions[1] or LIKE operator.
[1] https://phoenix.apache.org/language/functions.html#regexp_substr
On Fri, Jun 23, 2017 at 4:29 AM, Nan Xu <na...@gmail.com> wrote:
> I have a phoenix table created on existing hbase table, and want to query
> something like
> select * from mytable where modtime>'2010-01-01',
>
> how do I query phoenix like this? seems it doesn't have a modtime column
> if I don't do the modtime mapping, which I can not do because it has to be
> part of the primary key.
>
> Thanks,
> Nan
>