An filter expression with CAST as int can not be currently pushed down to HBase and must execute in Drill with a full table scan.
Regarding #4 The field 'trans_id' is already CASTed as VARCHAR in the view and hence you do not need to reCAST in the query. Can you try the following? select trans_id,trans_date,device_id,game,trans_tpe,trans_amt,state,country from maprfs.views.view_maprdb3 where trans_id ='7654'; On Wed, Feb 4, 2015 at 3:26 PM, Sudhakar Thota <[email protected]> wrote: > Hi Drillers, > > I am encountering some strange situation with views when it comes to > accessing a record using drill. Here is the complete story. > > 1. Table in MapR DB, called trans > > 2. Access the table with row_key cast to varchar gets access by row_key > and is very quick. > select cast(t.cf.device_id as INT) from evan.trans t where cast(row_key as > varchar(20))='7654’; > > 3. Access the table with row_key cast to int gets the record after a long > time which is by full-table-scan. > select cast(t.cf.device_id as INT) from evan.trans t where cast(row_key as > int)=7654; > > 4. Created a view with all the columns in the table and casting the > row_key to varchar(20), accessing the view using drill make a > full-table-scan. > ------------------------------------ > create or replace view view_maprdb3 as > SELECT CAST(`row_key` AS VARCHAR(20)) AS `trans_id`, > CAST(`t`.`cf`['trans_date'] AS DATE) AS `trans_date`, > CAST(`t`.`cf`['device_id'] AS INTEGER) AS `device_id`, > CAST(`t`.`cf`['game'] AS VARCHAR(50)) AS `game`, > CAST(`t`.`cf`['trans_type'] AS VARCHAR(20)) AS `trans_tpe`, > CAST(`t`.`cf`['trans_amt'] AS INTEGER) AS `trans_amt`, > CAST(`t`.`cf`['state'] AS VARCHAR(2)) AS `state`, CAST(`t`.`cf`['country'] > AS VARCHAR(2)) AS `country` > FROM `evan`.`trans` AS `t`; > > select > trans_id,trans_date,device_id,game,trans_tpe,trans_amt,state,country from > maprfs.views.view_maprdb3 where cast(trans_id as varchar(20))='7654'; > ------------------------------------ > > 5. Created a view with all the columns in the table and casting row_key to > int, accessing the view using drill make full-table-scan. > ------------------------------------ > Create or replace view view_maprdb as > SELECT CAST(`row_key` AS INTEGER) AS `trans_id`, > CAST(`t`.`cf`['trans_date'] AS DATE) AS `trans_date`, > CAST(`t`.`cf`['device_id'] AS INTEGER) AS `device_id`, > CAST(`t`.`cf`['game'] AS VARCHAR(50)) AS `game`, > CAST(`t`.`cf`['trans_type'] AS VARCHAR(20)) AS `trans_tpe`, > CAST(`t`.`cf`['trans_amt'] AS INTEGER) AS `trans_amt`, > CAST(`t`.`cf`['state'] AS VARCHAR(2)) AS `state`, CAST(`t`.`cf`['country'] > AS VARCHAR(2)) AS `country` > FROM `maprdb`.`/user/epeck/tables/trans` AS `t`; > > select > trans_id,trans_date,device_id,game,trans_tpe,trans_amt,state,country from > maprfs.epeck.view_maprdb where cast(trans_id as varchar(20))='7654’; > select > trans_id,trans_date,device_id,game,trans_tpe,trans_amt,state,country from > maprfs.epeck.view_maprdb where trans_id=7654; > ------------------------------------ > > > I think I am either defining the view wrong or drill is not doing the > expected. I have included the drill calls and explain plans in the attached > file. > > Please let me if you had similar experience, also let me know if you have > any work around. > > > > > > Thanks > Sudhakar Thota > > >
