Hi.
I am running CDH 5.3 with Hbase version 0.98.6+cdh5.3.0+73 and Phoenix v 4.2.2.
I have a table defined as follows:
create table if not exists dim_household_attribute(
data_source_id unsigned_int not null
,effective_end_date unsigned_date not null
,rpd_id unsigned_long not null
,provider_id varchar
,effective_start_date unsigned_date
,provider varchar
,product varchar
,product_version varchar
,originator varchar
,originator_locale varchar
,originator_sublocale varchar
,raw_file varchar
,creation_time unsigned_date
,batch_load_time unsigned_date
,guids varchar
,household_rpd_id unsigned_long
,data_source_household_id varchar
constraint pk primary key (data_source_id, effective_end_date, rpd_id)
) COMPRESSION = 'SNAPPY’;
The table has several million rows in it. I am having an issue with what would
seem to be a simple query on the first 2 columns of the primary key. If I run
the following select statement:
select data_source_id, effective_end_date
from dim_household_attribute
where data_source_id = 112000003
and effective_end_date = to_date('2099-12-31', 'yyyy-MM-dd')
limit 1;
I get the following error:
java.lang.RuntimeException: org.apache.phoenix.exception.PhoenixIOException:
Failed after retry of OutOfOrderScannerNextException: was there a rpc timeout?
at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2440)
at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2074)
at sqlline.SqlLine.print(SqlLine.java:1735)
at sqlline.SqlLine$Commands.execute(SqlLine.java:3683)
at sqlline.SqlLine$Commands.sql(SqlLine.java:3584)
at sqlline.SqlLine.dispatch(SqlLine.java:821)
at sqlline.SqlLine.begin(SqlLine.java:699)
at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441)
at sqlline.SqlLine.main(SqlLine.java:424)
However, if I only use the first column of the key, I get a result back very
quickly:
select data_source_id, effective_end_date
from dim_household_attribute
where data_source_id = 112000003
limit 1;
+------------------------------------------+---------------------+
| DATA_SOURCE_ID | EFFECTIVE_END_DATE |
+------------------------------------------+---------------------+
| 112000003 | 2015-10-17 |
+------------------------------------------+---------------------+
1 row selected (1.232 seconds)
Note that the date returning is 2015-10-17. If I change my original query to
use that date instead of the 2099-12-31 (which is a valid date in the system to
represent the row Is still valid) the query does return quickly.
Is there some configuration I am missing? Any help would be appreciated.
Thanks.
Kamran.
