Hive View query is not using partition

Question

I have hive tables and views on top of that tables . When executing query on Table using where clause for partition columns , I see from explain plain that query is using partition column . But same query when running on view , it is showing from explain plan that there is no use of partition key. Please suggest

Sample code here , views are created on table with all columns filtered by country code in where clause ( Select * from where country_code='XX')

Query used for table

SELECT a.unique_id,
  a.country_code,
  a.rpt_prd,
  a.abv_cut_off_scor_flag,
  a.acct_und_promo_flag,
.
.
  b.arrg_cob_dt,
  b.arrg_id
 from 
 a
inner join 
 b
on a.country_code   = b.country_code
and a.product_code  = b.product_code
and a.rpt_prd       =b.rpt_prd
and a.unique_id     =b.unique_id
and a.arrg_id       = b.arrg_id
WHERE a.country_code='XX'
AND a.product_code  = 'YYYYY'
AND a.rpt_prd       ='20171231' ;

a
======================================
Partition Key for - a
 PARTITIONED BY (                                   |
|   `country_code` string,                           |
|   `product_code` string,                           |
|   `rpt_prd` string,                                |
|   `unique_id` string)  

b
=======================================
 PARTITIONED BY (                                   |
|   `country_code` string,                           |
|   `product_code` string,                           |
|   `rpt_prd` string,                                |
|   `unique_id` string)  



Query using Views:
===================

SELECT a.unique_id,
  a.country_code,
  a.rpt_prd,
  a.abv_cut_off_scor_flag,
  a.acct_und_promo_flag,
.
.
  b.arrg_cob_dt,
  b.arrg_id
from 
 a
inner join 
 b
on a.country_code   = b.country_code
and a.product_code  = b.product_code
and a.rpt_prd       =b.rpt_prd
and a.unique_id     =b.unique_id
and a.arrg_id       = b.arrg_id
WHERE a.country_code='XX'
AND a.product_code  = 'YYYYY'
AND a.rpt_prd       ='20171231' ;

add views creation script and your query .. – hlagos May 01 '18 at 19:05 — hlagos, May 01 '18 at 19:05

score 1 · Accepted Answer · answered May 02 '18 at 03:56

Since the view is working with the same base data as the actual table it ought to make use of the partition. That said, other people have had this issue

One possible workaround would be to make the view explicitly aware of the partition by creating a partitioned view Syntax looks like:

ALTER VIEW view_name ADD PARTITION (partition_col = column_name)

Hive View query is not using partition

1 Answers1