Support Questions
Find answers, ask questions, and share your expertise

view vs table in hive - slow view

view vs table in hive - slow view

New Contributor

hi guys, @myoung 

I have 30 gb of - parquet file exposed as table with partitions and a view on top of the same table the table has 2000 circa columns 

why is that the same query I run against the table and then against the view makes the result of the view much slower. I did some test and I see consistent results: so question: (I could not find in hive doc) using view with large tables lead to significant slow performance given the overhead of the view layer? 

some numbers

query_id query_txt

query_

timed

notes
aggrAVG SELECT   `ISSUER`"," `PT_PRODUCT_TYPE`"," `_BUCKET`"," `PT_CARDHOLDER_TYPE`","   AVG(`CATEGORY_AMT_5 1279 default.bulletin_test_data_by_issuer_150x
aggrAVG SELECT   `ISSUER`"," `PT_PRODUCT_TYPE`"," `_BUCKET`"," `PT_CARDHOLDER_TYPE`","   AVG(`CATEGORY_AMT_5 1763 default.vw_bulletin_test_data_by_issuer_150x
aggrMAX SELECT   `ISSUER`"," `PT_PRODUCT_TYPE`"," `_BUCKET`"," `PT_CARDHOLDER_TYPE`","   MAX(`CTE_SUPIND_OL_ 1303 default.bulletin_test_data_by_issuer_150x
aggrMAX SELECT   `ISSUER`"," `PT_PRODUCT_TYPE`"," `_BUCKET`"," `PT_CARDHOLDER_TYPE`","   MAX(`CTE_SUPIND_OL_ 1833 default.vw_bulletin_test_data_by_issuer_150x
aggrCOUNT SELECT   `ISSUER`"," `PT_PRODUCT_TYPE`"," `_BUCKET`"," `PT_CARDHOLDER_TYPE`","   COUNT(`HIC_SUPIND_S 1341 default.bulletin_test_data_by_issuer_150x
aggrCOUNT SELECT   `ISSUER`"," `PT_PRODUCT_TYPE`"," `_BUCKET`"," `PT_CARDHOLDER_TYPE`","   COUNT(`HIC_SUPIND_S 1800 default.vw_bulletin_test_data_by_issuer_150x
aggrSUM SELECT   `ISSUER`"," `PT_PRODUCT_TYPE`"," `_BUCKET`"," `PT_CARDHOLDER_TYPE`","   SUM(`INF_SUPIND_PCT 1277 default.bulletin_test_data_by_issuer_150x
aggrSUM SELECT   `ISSUER`"," `PT_PRODUCT_TYPE`"," `_BUCKET`"," `PT_CARDHOLDER_TYPE`","   SUM(`INF_SUPIND_PCT 1742 default.vw_bulletin_test_data_by_issuer_150x
select_count select count(1) c from default.bulletin_test_data_by_issuer_150x LIMIT 10000 351 default.bulletin_test_data_by_issuer_150x
select_count select count(1) c from default.vw_bulletin_test_data_by_issuer_150x LIMIT 10000 933 default.vw_bulletin_test_data_by_issuer_150x
select_distinct select DISTINCT  `ISSUER`"," `PT_PRODUCT_TYPE`"," `_BUCKET`"," `PT_CARDHOLDER_TYPE`  from default.bu 333 default.bulletin_test_data_by_issuer_150x
select_distinct select DISTINCT  `ISSUER`"," `PT_PRODUCT_TYPE`"," `_BUCKET`"," `PT_CARDHOLDER_TYPE`  from default.vw 911 default.vw_bulletin_test_data_by_issuer_150x