You can create a table over an existing HBase table too, so change "view" to "table" and it will work fine. For immutable table indexes to be maintained, the updates must come in through Phoenix APIs. Since a view over an existing HBase table is read-only, it usually doesn't make sense for it to have a secondary index.
Thanks, James On Saturday, May 3, 2014, yixuan geng <[email protected]> wrote: > Hi James, > > Thanks for your reply. Looks like you created a new phoenix table and feed > data into int. In my case, I created a phoenix view on an existing > populated hbase table. I wonder if that changes the equation? > > What I did: > > I had an hbase table named "metadata_test". The table was created as "*create > 'metatadata_test', 'info' *". Some data has been populated into the table. > > So I created a phoenix *view* on top of this hbase table by > > create view \"metadata_test\" (pk VARCHAR PRIMARY KEY, \"info\".\"appid\" > VARCHAR,\"info\".\"counterid\" VARCHAR,\"info\".\"time\" > VARCHAR,\"info\".\"userid\" VARCHAR,\"info\".\"version\" VARCHAR)" > > Then I create followed the steps in my original post to create the index. > > > Do you think the index should work in this case? If so , I will go check > out the upgrade. > > > Thanks, > > Yixuan > > > > > 2014-05-03 11:07 GMT-07:00 James Taylor > <[email protected]<javascript:_e(%7B%7D,'cvml','[email protected]');> > >: > > Yes, Yixuan you're right - all your columns are in the index so it should > be used. I tested this with our 3.0.0 release (not sure what your CREATE > TABLE statement look like, though) and it works fine (see below), so you > may be running into a bug in our 2.2.2 release. Upgrade is easy and > painless: http://phoenix.incubator.apache.org/upgrade_from_2_2.html > > Thanks, > James > > 0: jdbc:phoenix:localhost> create table "metadata_test"("info"."appid" > VARCHAR, "info"."counterid" VARCHAR, "info"."time" DATE, k VARCHAR PRIMARY > KEY) immutable_rows=true; > No rows affected (2.228 seconds) > 0: jdbc:phoenix:localhost> create index "test_index" on > "metadata_test"("info"."appid","info"."counterid","info"."time"); > No rows affected (1.227 seconds) > 0: jdbc:phoenix:localhost> explain select "info"."appid" from > "metadata_test" where "info"."appid" = 'test_app' and "info"."counterid" = > 'test_counter'; > +------------+ > | PLAN | > +------------+ > | CLIENT PARALLEL 1-WAY RANGE SCAN OVER test_index > ['test_app','test_counter'] | > +------------+ > 1 row selected (0.023 seconds) > > > > On Sat, May 3, 2014 at 10:43 AM, yixuan geng <[email protected]> wrote: > > hi Job, > > In the example I provided, I think all columns in the query have been > covered by the index, right? > > Best, > Yixuan > > > On Saturday, May 3, 2014, Job Thomas <[email protected]> wrote: > > > In Phoenix if you select any column apart from indexed column it will > perform a full scan to get the resut. > But you can include required columns in the same index created. > > If you don't want to include column in the index table due to space > utilization or dynamic query , you can perform a work arouond for this. > Get the primary key from indexed table and using that key query the main > table to get required columns. once phoenix supports subquery we can achive > the same in one subquery. > > Thanks & Regards > Job M Thomas > ------------------------------ > *From:* yixuan geng [mailto:[email protected]] > *Sent:* Sat 5/3/2014 1:03 PM > *To:* [email protected] > *Subject:* Secondary index is not used > > Hi all, > > I am using the great secondary index feature on an existing immutable > table. I was able to successfully create the index and actually saw the > index table in hbase. > However, when I do "explain {query}", I always get " x-way full scan" > which I believe means the index i > > >
